Databases Reference
In-Depth Information
This is not a complete list of reasons!
Recompilation can be one of the issues to cause slow query response. If the recompilation
issue is severe, it can constantly keep the CPU busy. If you want to verify that your database
server is not facing a recompilation issue, monitoring the SQL Server instance for compilation
and recompilation events can be useful.
In this recipe, we will learn how to monitor compilation and recompilation events at server-
level with Reliability and Performance Monitor.
Getting ready
In this recipe, we will gather compilation- and recompilation-related statistics for the following
performance counters, using Reliability and Performance Monitor:
F
SQL Server:SQL Statistics:SQL Compilations/sec
F
SQL Server:SQL Statistics:SQL Re-Compilations/sec
For this, we will execute a script that will create a stored procedure named
usp_
GetSalesOrderDetail_ProductID
. This stored procedure will accept
ProductID
,
and based on
ProductID
, it will return records from the
Sales.SalesOrderDetail
table.
We will call this stored procedure twice. However, before we call the stored procedure
a second time, we will rebuild the index defined on the
ProductID
column, so that it
causes recompilations.
Before you can continue with the recipe, here are the prerequisites that you should fulfill:
F
An instance of SQL Server 2012 Developer or Enterprise Evaluation edition
F
A sample
AdventureWorks2012
database on the SQL Server instance
How to do it...
Follow the ensuing steps to capture compilation and recompilation events through Reliability
and Performance Monitor:
1.
To start Reliability and Performance Monitor:
ii.
Press the Windows +
R
key combination to display the Run dialog box.
ii.
In this dialog box, type
perfmon.exe
and press
Enter
.
2.
When Reliability and Performance Monitor is started, to switch to Performance
Monitor view, click on the Performance Monitor node, under the Monitoring Tools
node, in the left-hand side console tree.
Search WWH ::
Custom Search