source: branches/comu-ver2/data/module/adodb/perf/perf-mssql.inc.php @ 18701

Revision 18701, 4.7 KB checked in by nanasess, 14 years ago (diff)

Copyright の更新(#601)

  • Property svn:eol-style set to LF
  • Property svn:keywords set to Id Revision Date
  • Property svn:mime-type set to text/x-httpd-php
Line 
1<?php
2
3/*
4v4.992 10 Nov 2009  (c) 2000-2010 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
17if (!defined('ADODB_DIR')) die();
18
19/*
20    MSSQL has moved most performance info to Performance Monitor
21*/
22class perf_mssql 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_mssql(&$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()
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}
163
164?>
Note: See TracBrowser for help on using the repository browser.