After the information is recorded into the SQL Server table, you can use SQL Server Query Analyzer to determine which queries on the system are consuming the most resources, and database administrators can concentrate on improving the queries that need the most help. For example, this query is typical of the analysis performed on data recorded from SQL Server Profiler into a SQL Server table:
SELECT TOP 3 TextData,CPU,Reads,Writes,Duration FROM profiler_out_table ORDER BY cpu desc
The query retrieves the top three consumers of CPU resources on the database server. Read and write I/O information, along with the duration of the queries in milliseconds is returned. If a large amount of information is recorded with the SQL Server Profiler, you should create indexes on the table to help speed analysis queries. For example, if CPU is going to be an important criteria for analyzing this table, you should create a nonclustered index on CPU column.
SQL Server Query Analyzer provides a Show stats I/O option under the General tab of the Connections Options dialog box. Select this checkbox for information about how much I/O is being consumed for the query just executed in SQL Server Query Analyzer.
For example, the query SELECT ckey1, col2 FROM testtable WHERE ckey1 = a returns this I/O information in addition to the result set when the Show stats I/O connection option is selected:
Table 'testtable'. Scan count 1, logical reads 400, physical reads 382, read-ahead reads 400.
Similarly, the query SELECT nkey1, col2 FROM testtable WHERE nkey1 = 5000 returns this I/O information in addition to the result set when the Show stats I/O connection option is selected:
Table 'testtable'. Scan count 1, logical reads 400, physical reads 282, read-ahead reads 400.
Using STATISTICS I/O is a good way to monitor the effect of query tuning. For example, create the two indexes on this sample table as recommended by Index Tuning Wizard and then run the queries again.
In the query SELECT ckey1, col2 FROM testtable WHERE ckey1 = a, the clustered index improved performance as indicated below. The query must fetch 20 percent of the table; therefore, the performance improvement is reasonable.
Table 'testtable'. Scan count 1, logical reads 91, physical reads 5, read-ahead reads 32.
In the query SELECT nkey1, col2 FROM testtable WHERE nkey1 = 5000, the creation of the nonclustered index had a dramatic effect on the performance of the query. Because only one row of the 10,000 row table must be retrieved for this query, the performance improvement with the nonclustered index is reasonable.
Table 'testtable'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0.
Showplan can be used to display detailed information about what the query optimizer is doing. SQL Server 7.0 provides text and graphical versions of Showplan. Graphical Showplan output can be displayed in the Results pane of SQL Server Query Analyzer by executing a Transact-SQL query with Ctrl+L. Icons indicate the operations that the query optimizer will perform if it executes the query. Arrows indicate the direction of data flow for the query. Details about each operation can be shown by holding the mouse pointer over the operation icon. The equivalent information can be returned in text-based showplan by executing SET SHOWPLAN_ALL ON. To reduce the query optimizer operation details from text-based showplan, execute SET SHOWPLAN_TEXT ON.
For more information, see SQL Server Books Online.
This section shows sample showplan plan output using the following example queries and the set showplan_text on option in SQL Server Query Analyzer.
Query:
SELECT ckey1,col2 FROM testtable WHERE ckey1 = 'a'
Text-based showplan output:
|--Clustered Index Seek(OBJECT:([test].[dbo].[testtable].[testtable2]), SEEK:([testtable].[ckey1]='a') ORDERED)
This query takes advantage of the clustered index on column ckey1, as indicated by Clustered Index Seek.
The illustration shows equivalent graphical showplan output.
If the clustered index is removed from the table, the query must use a table scan. The showplan output below indicates the change in behavior.
Text-based showplan output:
|--Table Scan(OBJECT:([test].[dbo].[testtable]), WHERE:([testtable].[ckey1]='a'))
The illustration shows equivalent graphical showplan output.
Table scans are the most efficient way to retrieve information from small tables. But on larger tables, table scans indicated by showplan are a warning that the table may need better indexes or that the existing indexes must have their statistics updated (by using the UPDATE STATISTICS statement). SQL Server 7.0 provides automatically updating indexes. You should let SQL Server automatically maintain index statistics because the maintenance helps guarantee queries will always work with good index statistics.
Query:
SELECT nkey1,col2 FROM testtable WHERE nkey1 = 5000
Text-based showplan output:
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([test].[dbo].[testtable])) |--Index Seek(OBJECT:([test].[dbo].[testtable].[testtable1]), SEEK:([testtable].[nkey1]=5000) ORDERED)
The illustration shows equivalent graphical showplan output.
This query uses the nonclustered index on the column nkey1, which is indicated by the Index Seek operation on the column nkey1. The Bookmark Lookup operation indicates that SQL Server must perform a bookmark lookup from the index page to the data page of the table to retrieve the requested data. The bookmark lookup was required because the query asked for the column col2, which was not part of the nonclustered index.
Query:
SELECT nkey1 FROM testtable WHERE nkey1 = 5000
Text-based showplan output:
|--Index Seek(OBJECT:([test].[dbo].[testtable].[testtable1]), SEEK:([testtable].[nkey1]=[@1]) ORDERED)
The illustration shows equivalent graphical showplan output.
This query uses the nonclustered index on nkey1 as a covering index. No bookmark lookup operation was needed for this query because all of the information required for the query (both SELECT and WHERE clauses) is provided by the nonclustered index. A bookmark lookup to the data pages is not required from the nonclustered index pages. I/O is reduced compared to the case in which a bookmark lookup was required.