"TYPO3 4.0.1: typo3_src-4.0.1/typo3/sysext/adodb/adodb/perf/perf-mssql.inc.php Source File", "datetime" => "Sat Dec 2 19:22:26 2006", "date" => "2 Dec 2006", "doxygenversion" => "1.4.6", "projectname" => "TYPO3 4.0.1", "projectnumber" => "4.0.1" ); get_header($doxygen_vars); ?>

perf-mssql.inc.php

00001 <?php
00002 
00003 /* 
00004 V4.90 8 June 2006  (c) 2000-2006 John Lim (jlim#natsoft.com.my). All rights reserved.
00005   Released under both BSD license and Lesser GPL library license. 
00006   Whenever there is any discrepancy between the two licenses, 
00007   the BSD license will take precedence. See License.txt. 
00008   Set tabs to 4 for best viewing.
00009   
00010   Latest version is available at http://adodb.sourceforge.net
00011   
00012   Library for basic performance monitoring and tuning 
00013   
00014 */
00015 
00016 // security - hide paths
00017 if (!defined('ADODB_DIR')) die();
00018 
00019 /*
00020         MSSQL has moved most performance info to Performance Monitor
00021 */
00022 class perf_mssql extends adodb_perf{
00023         var $sql1 = 'cast(sql1 as text)';
00024         var $createTableSQL = "CREATE TABLE adodb_logsql (
00025                   created datetime NOT NULL,
00026                   sql0 varchar(250) NOT NULL,
00027                   sql1 varchar(4000) NOT NULL,
00028                   params varchar(3000) NOT NULL,
00029                   tracer varchar(500) NOT NULL,
00030                   timer decimal(16,6) NOT NULL
00031                 )";
00032                 
00033         var $settings = array(
00034         'Ratios',
00035                 'data cache hit ratio' => array('RATIO',
00036                         "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'",
00037                         '=WarnCacheRatio'),
00038                 'prepared sql hit ratio' => array('RATIO',
00039                         array('dbcc cachestats','Prepared',1,100),
00040                         ''),
00041                 'adhoc sql hit ratio' => array('RATIO',
00042                         array('dbcc cachestats','Adhoc',1,100),
00043                         ''),
00044         'IO',
00045                 'data reads' => array('IO',
00046                 "select cntr_value from master.dbo.sysperfinfo where counter_name = 'Page reads/sec'"),
00047                 'data writes' => array('IO',
00048                 "select cntr_value from master.dbo.sysperfinfo where counter_name = 'Page writes/sec'"),
00049                         
00050         'Data Cache',
00051                 'data cache size' => array('DATAC',
00052                 "select cntr_value*8192 from master.dbo.sysperfinfo where counter_name = 'Total Pages' and object_name='SQLServer:Buffer Manager'",
00053                         '' ),
00054                 'data cache blocksize' => array('DATAC',
00055                         "select 8192",'page size'),
00056         'Connections',
00057                 'current connections' => array('SESS',
00058                         '=sp_who',
00059                         ''),
00060                 'max connections' => array('SESS',
00061                         "SELECT @@MAX_CONNECTIONS",
00062                         ''),
00063 
00064                 false
00065         );
00066         
00067         
00068         function perf_mssql(&$conn)
00069         {
00070                 if ($conn->dataProvider == 'odbc') {
00071                         $this->sql1 = 'sql1';
00072                         //$this->explain = false;
00073                 }
00074                 $this->conn =& $conn;
00075         }
00076         
00077         function Explain($sql,$partial=false)
00078         {
00079                 
00080                 $save = $this->conn->LogSQL(false);
00081                 if ($partial) {
00082                         $sqlq = $this->conn->qstr($sql.'%');
00083                         $arr = $this->conn->GetArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq");
00084                         if ($arr) {
00085                                 foreach($arr as $row) {
00086                                         $sql = reset($row);
00087                                         if (crc32($sql) == $partial) break;
00088                                 }
00089                         }
00090                 }
00091                 
00092                 $s = '<p><b>Explain</b>: '.htmlspecialchars($sql).'</p>';
00093                 $this->conn->Execute("SET SHOWPLAN_ALL ON;");
00094                 $sql = str_replace('?',"''",$sql);
00095                 global $ADODB_FETCH_MODE;
00096                 
00097                 $save = $ADODB_FETCH_MODE;
00098                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00099                 $rs =& $this->conn->Execute($sql);
00100                 //adodb_printr($rs);
00101                 $ADODB_FETCH_MODE = $save;
00102                 if ($rs) {
00103                         $rs->MoveNext();
00104                         $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>';
00105                         while (!$rs->EOF) {
00106                                 $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!!!!
00107                                 $rs->MoveNext();
00108                         }
00109                         $s .= '</table>';
00110                         
00111                         $rs->NextRecordSet();
00112                 }
00113                 
00114                 $this->conn->Execute("SET SHOWPLAN_ALL OFF;");
00115                 $this->conn->LogSQL($save);
00116                 $s .= $this->Tracer($sql);
00117                 return $s;
00118         }
00119         
00120         function Tables()
00121         {
00122         global $ADODB_FETCH_MODE;
00123         
00124                 $save = $ADODB_FETCH_MODE;
00125                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00126                 //$this->conn->debug=1;
00127                 $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>';
00128                 $rs1 = $this->conn->Execute("select distinct name from sysobjects where xtype='U'");
00129                 if ($rs1) {
00130                         while (!$rs1->EOF) {
00131                                 $tab = $rs1->fields[0];
00132                                 $tabq = $this->conn->qstr($tab);
00133                                 $rs2 = $this->conn->Execute("sp_spaceused $tabq");
00134                                 if ($rs2) {
00135                                         $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>';
00136                                         $rs2->Close();
00137                                 }
00138                                 $rs1->MoveNext();
00139                         }
00140                         $rs1->Close();
00141                 }
00142                 $ADODB_FETCH_MODE = $save;
00143                 return $s.'</table>';
00144         }
00145         
00146         function sp_who()
00147         {
00148                 $arr = $this->conn->GetArray('sp_who');
00149                 return sizeof($arr);
00150         }
00151         
00152         function HealthCheck($cli=false)
00153         {
00154                 
00155                 $this->conn->Execute('dbcc traceon(3604)');
00156                 $html =  adodb_perf::HealthCheck($cli);
00157                 $this->conn->Execute('dbcc traceoff(3604)');
00158                 return $html;
00159         }
00160         
00161         
00162 }
00163 
00164 ?>