Slow SQL Code (Batches and Stored Procedures)
The Slow SQL Code node reports useful information about SQL
batches
and stored procedures that take longer than the minimum value configured
in Teratrax Monitoring Agent to finish. By
default, the minimum value for slow SQL batches and
stored procedures is 1500 milliseconds. You can change the default
minimum value from the Defaults tab in Teratrax Monitoring Agent. This allows you to focus on
slow batches only and keep the footprint of Teratrax
Performance Monitor on the server at a very low level.
Setting the minimum value to 0 will capture all executed SQL code.
Teratrax recommends a value of 3000 milliseconds or more for servers
with a high volume of transactions.
It is useful to examine the duration, CPU time, and disk reads and
writes for each SQL item to determine
any correlation between the performance of individual SQL batches and
stored procedures and their effect on the server. For example, a slow
running stored procedure that reports a high CPU
time may indicate that the stored procedure is performing a lot of
calculations, such as queries involving aggregates. Knowing this, you
may decide to tune your stored procedure code or increase the capacity
of the processors on the server. In another scenario, the same slow
running stored procedure may report a high number of disk reads and
writes,
in which case you may want to consider checking your index structure
and/or further normalization to the tables.
Interpreting Captured SQL Code
Teratrax Performance Monitor captures all SQL code executed on the
server (batches and stored procedures) as long as its execution time
(Duration) is higher than the minimum value configured for slow SQL code
in Teratrax Monitoring Agent. The captured SQL code is reported under
two event types: Batch or RPC (Remote Procedure Call). When a stored
procedure (SP) is called, it is either called from the context of a
batch or a RPC. Stored procedure calls from the context of a batch will
be displayed inside the SQL text of the batch, possibly alongside other
stored procedures and/or SQL code. Stored procedure calls from the
context of a RPC will be displayed in the SQL text of the RPC.
To ensures that only top level code is reported, stored procedures
that are called from within other stored procedures are not captured.
RPC (Remote
Procedure Call)
When a stored procedure is programmatically called from an
application using a parameterized ADO stored procedure object, it is
deemed to be called from the context of a RPC. If the application is
calling the stored procedure using a regular SQL command object, it is
deemed to be called from the context of a batch. Ad-hoc query tools such
as Query Analyzer and Teratrax Database Manager run SQL queries from the
context of a batch.
Batch
A batch is a number of SQL statements sent to the server for
execution as a whole unit. The statements in the batch are compiled into
a single execution plan and may contain one or more stored procedure
calls. Batches are separated by the GO command in ad-hoc query tools
like Query Analyzer or Teratrax Database Manager. The database context
of a batch reported in Teratrax Performance Monitor is the last context
used before the end of the batch. Database context can be switch inside
a batch using the USE <database> SQL command.
Chart Pane
The chart pane in the Slow SQL Code node provides a visual way for you to instantly
benchmark slow SQL events among each other. When you sort the main list
by clicking on column headers, the chart is also sorted accordingly.
Details Pane
The Details pane in the Slow SQL Code node provides detailed
information about every SQL event in the list. The following is a
description of the various data elements:
- Time: Indicates the date and time when the SQL
batch or
stored procedure finished execution.
- Database: Database in which the
SQL batch or stored procedure was executed.
- Duration: Total time in
milliseconds it took for the SQL batch or stored procedure to complete.
- CPU Time: CPU time in milliseconds
it took for the SQL batch or stored procedure to complete.
- Reads: Total number of disk reads
(logical/cached) that SQL Server performed to execute the SQL
batch or
stored procedure.
- Writes: Total number of disk writes
(logical/cached) that SQL Server performed to execute the SQL
batch or
stored procedure.
- Login: Login name of the user that
initiated the SQL batch or stored procedure.
- Process: SQL Server process ID (spid)
assigned for the connection by SQL Server.
- Program: Application that executed
the batch or stored procedure.
- Computer: Client computer from
which the batch or stored procedure originated.
- SQL Event Type: Indicates the context from which a batch or
stored procedure was called. This is either a Batch, or a RPC (Remote
Procedure Call).
- SQL Script: In the event of a batch context, this box will
display the SQL text (code) inside the batch. In the event of a RPC
context, this box shows the procedure call with any parameters passed.
|
