Top SQL View (SQL Server Performance Analyst)

Go Up to Microsoft SQL Server Performance Analyst Statistics

One or two bad queries can cause a lot of trouble for the remaining sessions in a database, and so can one or two bad queries. It is important to find them before they get into a production system, but sometimes a few sneak through.

By applying custom filters and performance-related thresholds, the Top SQL view locates inefficient SQL. By applying filters to certain I/O and statistical counters, you hope to isolate queries that far exceed their nearest competitors in the same area (like disk reads). When you find them, you should reduce the number of sorts a query performs. Or, for a query that returns only a few records, you should try to minimize the number of rows a query processes.

Top SQL displays requested SQL for SQL Server datasources. The following tabs are available on the Top SQL page:

• Summary Tab
• [[#Criteria Tab|Criteria Tab]
• SQL Details Tab

Summary Tab

The Summary tab of the Top SQL page includes the following sections:

• Top SQL Statements - SQL Server
• Top SQL Details

Top SQL Statements - SQL Server

A lot of a database’s overall performance can be attributed to SQL statement execution. Poorly optimized SQL statements can drag an otherwise well-configured database down in terms of end user response times.

Before you can identify problem SQL in your database, you have to ask the question of what ‘bad SQL’ is. What criteria do you use when you begin the hunt for problem SQL in your critical systems? Understand that even the seasoned experts disagree on what constitutes efficient and inefficient SQL; so there is no way to sufficiently answer this question to every Oracle professional’s satisfaction. The table below describes some general criteria you can use when evaluating the output from various database monitors or personal diagnostic scripts:

Criteria	Description
Overall Response (Elapsed) Time	This is how much time the query took to parse, execute, and fetch the data needed to satisfy the query. It should not include the network time needed to make the round trip from the requesting client workstation to the database server.
CPU Time	This is how much CPU time the query took to parse, execute, and fetch the data needed to satisfy the query.
Physical I/O	Often used as the major statistic in terms of identifying good vs. bad SQL, this is a measure of how many disk reads the query caused to satisfy the user’s request. While you certainly want to control disk I/O where possible, it is important that you not focus solely on physical I/O as the single benchmark of inefficient SQL. Make no mistake, disk access is slower than memory access and also consumes processing time making the physical to logical transition, but you need to look at the entire I/O picture of a SQL statement, which includes looking at a statements’ logical I/O as well.
Logical I/O	This is a measure of how many memory reads the query took to satisfy the user’s request. The goal of tuning I/O for a query should be to examine both logical and physical I/O, and use appropriate mechanisms to keep both to a minimum.
Repetition	This is a measure of how often the query has been executed. A problem in this area is not as easy to spot as the others unless you know your application well. A query that takes a fraction of a second to execute may still be causing a headache on your system if it is executed erroneously over and over.

There are other criteria that you can examine like sort activity or access plan statistics (that show items like Cartesian joins and the like), but more often than not, these measures are reflected in the criteria listed above.

Fortunately, Microsoft records some of these statistics and Performance Analyst computes the rest, which makes tracking the SQL that has been submitted against a SQL Server much easier.

Location

Microsoft SQL Server Performance Analyst Statistics > Top SQL View (DBArtisan - SQL Server Performance Analyst) > Summary Tab

Metrics

When you begin to look for inefficient SQL in a database, there are two primary questions you need to answer: What has been the worst SQL that’s historically been run in my database? What is the worst SQL that’s running right now in my database? When Correction a slow system, you should be on the lookout for any query that shows an execution count that is significantly larger than any other query on the system. It may be that the query is in an inefficient Transact SQL loop, or other problematic programming construct. Only by bringing the query to the attention of the application developers will you know if the query is being mishandled from a programming standpoint.

Top SQL Details

A lot of a database’s overall performance can be attributed to SQL statement execution. Poorly optimized SQL statements can drag an otherwise well-configured database down in terms of end user response times.

Before you can identify problem SQL in your database, you have to ask the question of what ‘bad SQL’ is. What criteria do you use when you begin the hunt for problem SQL in your critical systems? Understand that even the seasoned experts disagree on what constitutes efficient and inefficient SQL; so there is no way to sufficiently answer this question to every SQL Server professional’s satisfaction. The Top SQL Details Section displays the top SQL statements across various performance categories. The table below describes the information available in this section:

Information	Description
Top For	Indicates the performance category for the SQL statement.
Usage	This displays the percent usage for this SQL statement in this performance category.
SQL text	A snippet of the actual SQL statement or code object (procedure, etc.)
Total Reads	The total physical reads caused by the statement since monitoring began.
Total Writes	The total physical writes caused by the statement since monitoring began.
Executions	The total number of times the statement or code object has been executed since monitoring began.
Avg CPU Time	The average CPU time used by the statement each time it is executed.
Avg Elapsed Time	The average elapsed response time experienced each time the statement is executed.

Location

Microsoft SQL Server Performance Analyst Statistics > Top SQL View (DBArtisan - SQL Server Performance Analyst) > Summary Tab

Metrics

When you begin to look for inefficient SQL in a database, there are two primary questions you need to answer: What has been the worst SQL that’s historically been run in my database? What is the worst SQL that’s running right now in my database? When Correction a slow system, you should be on the lookout for any query that shows an execution count that is significantly larger than any other query on the system. It may be that the query is in an inefficient Transact SQL loop, or other problematic programming construct. Only by bringing the query to the attention of the application developers will you know if the query is being mishandled from a programming standpoint.

Criteria Tab

The Top SQL view displays all SQL running or executed in the current datasource. You can use the lists and boxes to reorder the display according to your specific needs. The Criteria tab of the Top SQL page includes the following sections that you can mix and match to give you the information you want to find.

• Demographic Criteria
• Statistical Criteria
• Sort Criteria

SQL Details Tab

The SQL Details tab of the Top SQL page includes the following sections:

• Top SQL Results

Top SQL Results

Overall performance can be attributed to SQL statement execution. Poorly optimized SQL statements or stored procedures can drag an otherwise well-configured database down in terms of end user response times.

Before you can identify problem SQL in your database, you have to ask the question of what ‘bad SQL’ is. What criteria do you use when hunting for problem SQL in critical systems? Understand that even the seasoned experts disagree on what constitutes efficient and inefficient SQL; so there is no way to sufficiently answer this question to every SQL Server professional’s satisfaction. The Top SQL results grid displays the top SQL statements across various performance categories. The table below describes the information available in this section:

Information	Description
SQL Text	A snippet of the full SQL statement. Clicking the statement will cause the full statement to be presented in the Selected SQL Text grid.
Database	The database where the statement or code object’s execution originated.
Avg Reads	The average physical reads caused by the statement each time it is executed.
Total Reads	The total physical reads caused by the statement since monitoring began.
Avg Writes	The average physical writes caused by the statement each time it is executed.
Total Writes	The total physical writes caused by the statement since monitoring began.
Executions	The total number of times the statement or code object has been executed since monitoring began.
Avg Elapsed Time	The average elapsed response time experienced each time the statement is executed.
Elapsed Time	The total elapsed response time over all executions.
Avg CPU Time	The average CPU time used by the statement each time it is executed.
Total CPU Time	The total CPU time over all executions.
First Start Time	The date/time when the statement was first executed.

Location

Microsoft SQL Server Performance Analyst Statistics > Top SQL View (DBArtisan - SQL Server Performance Analyst) > SQL Details Tab

Selected SQLText

The Selected SQL Text window allows you to select any of the Top SQL result rows and get the complete SQL text. From here you can open an Explain Plan or Tune the SQL.

Location

Microsoft SQL Server Performance Analyst Statistics > Top SQL View (DBArtisan - SQL Server Performance Analyst) > Summary Tab