[Summary view]

Differences Between: [Versions 310 and 403] [Versions 311 and 403] [Versions 39 and 403]

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