Top SQL Statistics (Sybase ASE Performance Analyst)
Go Up to Sybase ASE Performance Analyst Statistics
A lot of a database's 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 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 results grid displays the top SQL statements across various performance categories.
The SQL Details tab of the Top SQL view presents information as follows:
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 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.
Active SQL Results
The Active SQL results section displays the following information in grid format:
- SQL Text: A snippet of the full SQL statement. Clicking on the statement will cause the full statement to be presented in the Selected SQL Text grid.
- Database: The name of the database.
- SPID: The process ID.
- CpuTime: The accumulated CPU time for the statement.
- Wait Time: The time the process has been waiting to be serviced, in milliseconds. A value of zero indicates there was no waiting.
- Memory Usage (KB): The number of pages in the procedure cache that is currently allocated to the process. A negative number indicates that pages are being released (freed) from the process.
- Physical Reads: The current cumulative number of physical disk reads issued by the process.
- Logical Reads: The current cumulative number logical reads issued by the process.
- Pages Modified: The number of pages that were modified by the process.
- Packets Received
- Packets Sent
- Start Time: The date/time when the statement was first executed.
- Batch ID
Selected SQL Text
In this section of SQL details, you see the fully expanded SQL statement that you highlighted in the Active SQL Results grid. By clicking Explain SQL you open the Explain Plan with its ISQL window.