Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 31, 2001

SQL Server Performance Tuning: Pt. 1 - Page 4

By Aaron Goldman

Statistics IO

In Query Analyzer you can turn on the display of statistics by setting the session option:

Set Statistics IO ON

Or by changing the current connection properties under the Query menu:

SQL 7.0 Connection Options
SQL 7.0 Connection Options
(Click image for full size)

SQL 2000 Connection Options
SQL 2000 Connection Options
(Click image for full size)

Here is sample output for Statistics IO: [Editor's note: lines in the following output were reformatted for better display on this Web page.]

Table 'dbi'. 
  Scan count 1, logical reads 3, 
  physical reads 0, read-ahead reads 0.
Table 'matter_resolution'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'dbi_bm'. 
  Scan count 1, logical reads 5, 
  physical reads 2, read-ahead reads 0.
Table 'dbi'. 
  Scan count 1, logical reads 3, 
  physical reads 1, read-ahead reads 0.
Table 'initial_matter'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'processing_summary'. 
  Scan count 5, logical reads 2892, 
  physical reads 0, read-ahead reads 209.
Table 'amount_detail'. 
  Scan count 2, logical reads 2091, 
  physical reads 0, read-ahead reads 2096.
Table 'system_user'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'case_type_lu'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'processing_action_type_lu'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'dbi_bm_charge_event'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'event_by_dbi_bm'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.

Pages accessed is the important thing here. The output shows that the two tables processing_summary and amount_detail are the worst performing parts of the query with logical read counts in the thousands whereas the other tables only call for a few pages each.

Statistics IO is good for a quick read, but is very difficult to use for long stored procedures because the output is not delimited. There is no quick way to tell which SQL statement these statistics refer to.

Some Definitions:

Page: A unit of storage in SQL Server. In Version 7 a page is 8K and stores data, indexes, or code. Whenever SQL Server is looking through a table or an index, it pulls data from memory or disk a page at a time.

Data Page: As used in this article, a page containing table data as opposed to index data. Index entries point to table data pages.

Query Plan: The exact steps SQL Server uses to access or update data.

Optimizer: The component in SQL Server which generates a query plan.

Table Scan: Searching for data by checking each row in the table one by one--very slow for medium and larger data sets, quick for small datasets.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM