[Summary view]

Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]

   1  <?php
   2  
   3  /*
   4  @version   v5.21.0  2021-02-27
   5  @copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
   6  @copyright (c) 2014      Damien Regad, Mark Newnham and the ADOdb community
   7    Released under both BSD license and Lesser GPL library license.
   8    Whenever there is any discrepancy between the two licenses,
   9    the BSD license will take precedence. See License.txt.
  10    Set tabs to 4 for best viewing.
  11  
  12    Latest version is available at https://adodb.org/
  13  
  14    Library for basic performance monitoring and tuning
  15  
  16  */
  17  
  18  // security - hide paths
  19  if (!defined('ADODB_DIR')) die();
  20  
  21  /*
  22  	 MSSQL has moved most performance info to Performance Monitor
  23  */
  24  class perf_mssql extends adodb_perf{
  25  	 var $sql1 = 'cast(sql1 as text)';
  26  	 var $createTableSQL = "CREATE TABLE adodb_logsql (
  27  	 	   created datetime NOT NULL,
  28  	 	   sql0 varchar(250) NOT NULL,
  29  	 	   sql1 varchar(4000) NOT NULL,
  30  	 	   params varchar(3000) NOT NULL,
  31  	 	   tracer varchar(500) NOT NULL,
  32  	 	   timer decimal(16,6) NOT NULL
  33  	 	 )";
  34  
  35  	 var $settings = array(
  36  	 'Ratios',
  37  	 	 'data cache hit ratio' => array('RATIO',
  38  	 	 	 "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'",
  39  	 	 	 '=WarnCacheRatio'),
  40  	 	 'prepared sql hit ratio' => array('RATIO',
  41  	 	 	 array('dbcc cachestats','Prepared',1,100),
  42  	 	 	 ''),
  43  	 	 'adhoc sql hit ratio' => array('RATIO',
  44  	 	 	 array('dbcc cachestats','Adhoc',1,100),
  45  	 	 	 ''),
  46  	 'IO',
  47  	 	 'data reads' => array('IO',
  48  	 	 "select cntr_value from master.dbo.sysperfinfo where counter_name = 'Page reads/sec'"),
  49  	 	 'data writes' => array('IO',
  50  	 	 "select cntr_value from master.dbo.sysperfinfo where counter_name = 'Page writes/sec'"),
  51  
  52  	 'Data Cache',
  53  	 	 'data cache size' => array('DATAC',
  54  	 	 "select cntr_value*8192 from master.dbo.sysperfinfo where counter_name = 'Total Pages' and object_name='SQLServer:Buffer Manager'",
  55  	 	 	 '' ),
  56  	 	 'data cache blocksize' => array('DATAC',
  57  	 	 	 "select 8192",'page size'),
  58  	 'Connections',
  59  	 	 'current connections' => array('SESS',
  60  	 	 	 '=sp_who',
  61  	 	 	 ''),
  62  	 	 'max connections' => array('SESS',
  63  	 	 	 "SELECT @@MAX_CONNECTIONS",
  64  	 	 	 ''),
  65  
  66  	 	 false
  67  	 );
  68  
  69  
  70  	function __construct(&$conn)
  71  	 {
  72  	 	 if ($conn->dataProvider == 'odbc') {
  73  	 	 	 $this->sql1 = 'sql1';
  74  	 	 	 //$this->explain = false;
  75  	 	 }
  76  	 	 $this->conn = $conn;
  77  	 }
  78  
  79  	function Explain($sql,$partial=false)
  80  	 {
  81  
  82  	 	 $save = $this->conn->LogSQL(false);
  83  	 	 if ($partial) {
  84  	 	 	 $sqlq = $this->conn->qstr($sql.'%');
  85  	 	 	 $arr = $this->conn->GetArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq");
  86  	 	 	 if ($arr) {
  87  	 	 	 	 foreach($arr as $row) {
  88  	 	 	 	 	 $sql = reset($row);
  89  	 	 	 	 	 if (crc32($sql) == $partial) break;
  90  	 	 	 	 }
  91  	 	 	 }
  92  	 	 }
  93  
  94  	 	 $s = '<p><b>Explain</b>: '.htmlspecialchars($sql).'</p>';
  95  	 	 $this->conn->Execute("SET SHOWPLAN_ALL ON;");
  96  	 	 $sql = str_replace('?',"''",$sql);
  97  	 	 global $ADODB_FETCH_MODE;
  98  
  99  	 	 $save = $ADODB_FETCH_MODE;
 100  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 101  	 	 $rs = $this->conn->Execute($sql);
 102  	 	 //adodb_printr($rs);
 103  	 	 $ADODB_FETCH_MODE = $save;
 104  	 	 if ($rs && !$rs->EOF) {
 105  	 	 	 $rs->MoveNext();
 106  	 	 	 $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>';
 107  	 	 	 while (!$rs->EOF) {
 108  	 	 	 	 $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!!!!
 109  	 	 	 	 $rs->MoveNext();
 110  	 	 	 }
 111  	 	 	 $s .= '</table>';
 112  
 113  	 	 	 $rs->NextRecordSet();
 114  	 	 }
 115  
 116  	 	 $this->conn->Execute("SET SHOWPLAN_ALL OFF;");
 117  	 	 $this->conn->LogSQL($save);
 118  	 	 $s .= $this->Tracer($sql);
 119  	 	 return $s;
 120  	 }
 121  
 122  	function Tables()
 123  	 {
 124  	 global $ADODB_FETCH_MODE;
 125  
 126  	 	 $save = $ADODB_FETCH_MODE;
 127  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 128  	 	 //$this->conn->debug=1;
 129  	 	 $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>';
 130  	 	 $rs1 = $this->conn->Execute("select distinct name from sysobjects where xtype='U'");
 131  	 	 if ($rs1) {
 132  	 	 	 while (!$rs1->EOF) {
 133  	 	 	 	 $tab = $rs1->fields[0];
 134  	 	 	 	 $tabq = $this->conn->qstr($tab);
 135  	 	 	 	 $rs2 = $this->conn->Execute("sp_spaceused $tabq");
 136  	 	 	 	 if ($rs2) {
 137  	 	 	 	 	 $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>';
 138  	 	 	 	 	 $rs2->Close();
 139  	 	 	 	 }
 140  	 	 	 	 $rs1->MoveNext();
 141  	 	 	 }
 142  	 	 	 $rs1->Close();
 143  	 	 }
 144  	 	 $ADODB_FETCH_MODE = $save;
 145  	 	 return $s.'</table>';
 146  	 }
 147  
 148  	function sp_who()
 149  	 {
 150  	 	 $arr = $this->conn->GetArray('sp_who');
 151  	 	 return sizeof($arr);
 152  	 }
 153  
 154  	function HealthCheck($cli=false)
 155  	 {
 156  
 157  	 	 $this->conn->Execute('dbcc traceon(3604)');
 158  	 	 $html =  adodb_perf::HealthCheck($cli);
 159  	 	 $this->conn->Execute('dbcc traceoff(3604)');
 160  	 	 return $html;
 161  	 }
 162  
 163  
 164  }