Slow SQL Code (Batches and Stored Procedures)
The Slow SQL Code node reports detailed information about SQL batches
and stored procedures that take longer than the minimum value to finish. By
default, the minimum value for slow SQL batches and
stored procedures is 3000 milliseconds. You can change the default value
from
Teratrax Performance Agent.
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.
For SQL code items, Reads are logical (cached) 8-KB page reads and
Writes are physical (non-cached) 8-KB page writes.
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 Performance 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.
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 switched inside
a batch using the USE <database> SQL command.
RPC (Remote
Procedure Call)
When a stored procedure is programmatically called from an
application using a parameterized ADO stored procedure object, SQL
Server executes it as a RPC. If the application is
calling the stored procedure using a regular SQL command object, SQL
Server executes it as a batch. Ad-hoc query tools such
as Query Analyzer and Teratrax Database Manager run SQL queries from the
context of a batch.
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. The
chart shows trends in different colors for four types of values
(Duration, CPU, Reads, Writes):
Sorting the list by the CPU Time column
displays CPU time values in red bars in the chart.
Sorting the list by the Reads column displays
number of logical (cached) 8-KB page reads in green bars in the chart.
Sorting the list by the Writes column displays
number of physical (non-cached) 8-KB page writes in purple bars in the
chart.
Sorting the list by the Duration column or any
other column displays duration values in blue bars in the chart.
Eventually, a certain SQL item (like a backup) takes a significantly
longer duration, dwarfing all other items in the chart. If this happens,
you can right-click the item and check "Remove From Chart". This will
keep the item in the database but will not chart it. You can re-chart
the item at a later time by simply removing the check. This also applies
to CPU Time, Reads, and Writes.
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
batch or
RPC finished execution.
- Database: Database in which the
batch or RPC was executed.
- Duration: Total time in
milliseconds it took for the batch or RPC to complete.
- CPU Time: CPU time in milliseconds it took for the batch or
RPC to complete.
- Reads: Total number of 8-KB pages read
(Logical/cached) during the execution the batch or RPC.
- Writes: Total number of 8-KB pages written
(Physical/non-cached) during the execution the batch or RPC.
- Login: Login name of the user or application that
initiated the batch or RPC.
- Process: SQL Server process ID (spid)
assigned for the connection by SQL Server.
- Program: Application that executed the batch or RPC.
- Computer: Client computer from
which the batch or RPC originated.
- SQL Event Type: Either batch or RPC (Remote Procedure
Call).
- SQL Script: In the event of a batch, this box
displays the SQL text of the batch. In the event of a RPC, this box shows the procedure call with any parameters passed.
|
