1 <?php

2 

3 /*

4 V5.19 23-Apr-2014 (c) 2000-2014 John Lim (jlim#natsoft.com). All rights reserved.

5  Released under both BSD license and Lesser GPL library license.

6  Whenever there is any discrepancy between the two licenses,

7  the BSD license will take precedence. See License.txt.

8  Set tabs to 4 for best viewing.

9 

10  Latest version is available at http://adodb.sourceforge.net

11 

12  Library for basic performance monitoring and tuning

13 

14 */

15 

16 // security - hide paths

17 if (!defined('ADODB_DIR')) die();

18 

19 /*

20  MSSQL has moved most performance info to Performance Monitor

21 */

22 class perf_mssqlnative extends adodb_perf{

23  var $sql1 = 'cast(sql1 as text)';

24  var $createTableSQL = "CREATE TABLE adodb_logsql (

25  created datetime NOT NULL,

26  sql0 varchar(250) NOT NULL,

27  sql1 varchar(4000) NOT NULL,

28  params varchar(3000) NOT NULL,

29  tracer varchar(500) NOT NULL,

30  timer decimal(16,6) NOT NULL

31  )";

32 

33  var $settings = array(

34  'Ratios',

35  'data cache hit ratio' => array('RATIO',

36  "select round((a.cntr_value*100.0)/b.cntr_value,2) from master.dbo.sysperfinfo a, master.dbo.sysperfinfo b where a.counter_name = 'Buffer cache hit ratio' and b.counter_name='Buffer cache hit ratio base'",

37  '=WarnCacheRatio'),

38  'prepared sql hit ratio' => array('RATIO',

39  array('dbcc cachestats','Prepared',1,100),

40  ''),

41  'adhoc sql hit ratio' => array('RATIO',

42  array('dbcc cachestats','Adhoc',1,100),

43  ''),

44  'IO',

45  'data reads' => array('IO',

46  "select cntr_value from master.dbo.sysperfinfo where counter_name = 'Page reads/sec'"),

47  'data writes' => array('IO',

48  "select cntr_value from master.dbo.sysperfinfo where counter_name = 'Page writes/sec'"),

49 

50  'Data Cache',

51  'data cache size' => array('DATAC',

52  "select cntr_value*8192 from master.dbo.sysperfinfo where counter_name = 'Total Pages' and object_name='SQLServer:Buffer Manager'",

53  '' ),

54  'data cache blocksize' => array('DATAC',

55  "select 8192",'page size'),

56  'Connections',

57  'current connections' => array('SESS',

58  '=sp_who',

59  ''),

60  'max connections' => array('SESS',

61  "SELECT @@MAX_CONNECTIONS",

62  ''),

63 

64  false

65  );

66 

67 

68  function perf_mssqlnative(&$conn)

69  {

70  if ($conn->dataProvider == 'odbc') {

71  $this->sql1 = 'sql1';

72  //$this->explain = false;

73  }

74  $this->conn =& $conn;

75  }

76 

77  function Explain($sql,$partial=false)

78  {

79 

80  $save = $this->conn->LogSQL(false);

81  if ($partial) {

82  $sqlq = $this->conn->qstr($sql.'%');

83  $arr = $this->conn->GetArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq");

84  if ($arr) {

85  foreach($arr as $row) {

86  $sql = reset($row);

87  if (crc32($sql) == $partial) break;

88  }

89  }

90  }

91 

92  $s = '<p><b>Explain</b>: '.htmlspecialchars($sql).'</p>';

93  $this->conn->Execute("SET SHOWPLAN_ALL ON;");

94  $sql = str_replace('?',"''",$sql);

95  global $ADODB_FETCH_MODE;

96 

97  $save = $ADODB_FETCH_MODE;

98  $ADODB_FETCH_MODE = ADODB_FETCH_NUM;

99  $rs =& $this->conn->Execute($sql);

100  //adodb_printr($rs);

101  $ADODB_FETCH_MODE = $save;

102  if ($rs) {

103  $rs->MoveNext();

104  $s .= '<table bgcolor=white border=0 cellpadding="1" callspacing=0><tr><td nowrap align=center> Rows<td nowrap align=center> IO<td nowrap align=center> CPU<td align=left> &nbsp; &nbsp; Plan</tr>';

105  while (!$rs->EOF) {

106  $s .= '<tr><td>'.round($rs->fields[8],1).'<td>'.round($rs->fields[9],3).'<td align=right>'.round($rs->fields[10],3).'<td nowrap><pre>'.htmlspecialchars($rs->fields[0])."</td></pre></tr>\n"; ## NOTE CORRUPT </td></pre> tag is intentional!!!!

107  $rs->MoveNext();

108  }

109  $s .= '</table>';

110 

111  $rs->NextRecordSet();

112  }

113 

114  $this->conn->Execute("SET SHOWPLAN_ALL OFF;");

115  $this->conn->LogSQL($save);

116  $s .= $this->Tracer($sql);

117  return $s;

118  }

119 

120  function Tables($orderby='1')

121  {

122  global $ADODB_FETCH_MODE;

123 

124  $save = $ADODB_FETCH_MODE;

125  $ADODB_FETCH_MODE = ADODB_FETCH_NUM;

126  //$this->conn->debug=1;

127  $s = '<table border=1 bgcolor=white><tr><td><b>tablename</b></td><td><b>size_in_k</b></td><td><b>index size</b></td><td><b>reserved size</b></td></tr>';

128  $rs1 = $this->conn->Execute("select distinct name from sysobjects where xtype='U'");

129  if ($rs1) {

130  while (!$rs1->EOF) {

131  $tab = $rs1->fields[0];

132  $tabq = $this->conn->qstr($tab);

133  $rs2 = $this->conn->Execute("sp_spaceused $tabq");

134  if ($rs2) {

135  $s .= '<tr><td>'.$tab.'</td><td align=right>'.$rs2->fields[3].'</td><td align=right>'.$rs2->fields[4].'</td><td align=right>'.$rs2->fields[2].'</td></tr>';

136  $rs2->Close();

137  }

138  $rs1->MoveNext();

139  }

140  $rs1->Close();

141  }

142  $ADODB_FETCH_MODE = $save;

143  return $s.'</table>';

144  }

145 

146  function sp_who()

147  {

148  $arr = $this->conn->GetArray('sp_who');

149  return sizeof($arr);

150  }

151 

152  function HealthCheck($cli=false)

153  {

154 

155  $this->conn->Execute('dbcc traceon(3604)');

156  $html = adodb_perf::HealthCheck($cli);

157  $this->conn->Execute('dbcc traceoff(3604)');

158  return $html;

159  }

160 

161 

162 }