SQL Server - How to improve Execution Plan Reuse

 Before the first time a query is run , SQL Server Query Optimizer compiles a cost effective execution plan. Compiling the plan is CPU cycle expensive. Maximising Execution Plan Reuse helps query response time.

Recompilations are normal in SQL Server. For example these conditions will make  an Excecution Plan invalid and cause a recompile. From BOL

1) Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).

2)Changes to any indexes used by the execution plan.

3)Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.

4)Dropping an index used by the execution plan.

How to identify excessive recompilations and troubleshoot

1)    Find statements with Recompilations –   This query returns the top 5 statements based on plan generation number

        
  SELECT TOP 5
qst.plan_generation_num,
qst.execution_count,
stm.TEXT,
DB_NAME(stm.dbid) AS DbName,
stm.objectid

FROM sys.dm_exec_query_stats qst
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS stm
ORDER BY qst.plan_generation_num DESC

 

Another useful tool is  Profiler .Check the SQL:StmtRecompile event class

2)    Identify the lists of queries with high levels of recompilations. Are they Adhoc or Dynamic Queries?

3)    If Adhoc or Dynamic Queries exist, rewrite the query . Try and write as Prepared Statements, Stored Procedures or sp_executesql

4)    If the list of queries with recompilations are not Adhoc or Dynamic queries but the levels of recompilations remain high, review the query for a rewite. Try and write as Prepared Statements, Stored Procedures or sp_executesql. If that doesn’t improve the recompilation levels – consider using the PARAMETERIZATION between forced and simple

 Read More

SQL Server execution plan as text - SQL Server DBA

SQL Server – SQL Execution plan and SET SHOWPLAN_XML ...

SQL Server Query Optimizer and Statistics

 

SQL Server – Find queries causing Lock Timeouts per second

Lock Timeouts (timeout > 0)/sec is part  Locks counter part of the SQLServer:Locks object.Accrding to BOL : “Number of lock requests per second that timed out, but excluding requests for NOWAIT locks”

It is not uncommon to monitor on lock timeouts . The can cause bottlenecks and it is important  to identify queries which cause these timeouts. The overall objective is to decrease the amount of time a lock is held.     The goal is to have 0 during normal Production time.

One of the resources SQL Server can lock out on is Extent. A lock on a contiguous group of 8 pages.

Other Lock Timeout Types include include .:  AllocUnit,HoBT,Metadata,Application,RID , Key , Page ,Object,File,Database    

How to View the Lock Timeouts (timeout > 0)/sec counter

Method 1
To view the counter use Perfmon – check Window Perfmon scripting, SQL Server perfmon and how to perfmon
Add the counter  \\MYINST1:Locks(Extent)\Lock Timeouts(timeout > 0)/sec

Method 2 Note: these are cumulative values

 

Select * from sys.dm_os_performance_counters 
where counter_name = 'Lock Timeouts/sec'   and instance_name = 'Extent'

 

How to Fix Locking Issues

1) Locate the queries with excessive locking delays. One method is to trace the event “Blocked Process report” using SQL Server Profiler

It’s a useful method as it allows an event to fire only when a certain threshold is met. For example , if you wanted the event to fire after 10 seconds

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'blocked process threshold', 10
RECONFIGURE

 

2) Start SQL Server Profiler  , set up a trace with the Blocked Process report, start the trace

3) You’ve now identified queries with excessive locking delays. The objective is to decrease the duration of time locks are held. Some command methods are:

Query tuning

Stored procedure versus adhoc queries

Analyse indexes – for example , if you have a heavily updated column don’t include it in a Clustered Index as it will will also lock the non clustered index via the row locator

Row versioning

Related Posts

SQL Server – How to troubleshoot query timeouts

Sys.dm_os_memory_clerks and AWE memory allocation

Performance Monitor Counters for SQL Server performance testing

SQL Server – Troubleshooting the SQL Profiler trace Attention class event

When executing a SQL Server trace the Attention  event class can appear. According to SQL Server Books Online  “The Attention event class indicates that an attention event, such as cancel, client-interrupt requests, or broken client connections, has occurred. Cancel operations can also be seen as part of implementing data access driver time-outs”

This gives a big clue i.e the client cancelled the query. It’s not clear why the query was cancelled by the client.

Some typical reasons

1)     Query timeout

2)     A user or agent stopped the application or the query was cancelled.

3)     Application issues – particularly multi threaded applications

 I have seen some correlation between the Attention class and poorly performing queries. If you think the cause may be a poorly performing query, capture the query and complete a query tuning exercise.

Find the query by identifying the SPID receiving the Attention event, trap the last SQL:StmtStarting, SQL:BatchStarting, or SP:StmtStarting just before the Attention

Related Posts

SQL Server – How to troubleshoot query timeouts

SQL Server SLEEPING MODE , locks and transactions

What is SQL Server Query Tuning?

ALTER TRACE and developers

Question: SQL Server Profile Trace ,during debugging and developing new applications  is invaluable – I currently have a bug I’m analysing where I need to know exactly what SQL statement is executed but without the SQL profile analyser trace functionality I’m at a bit of a loss unless you know of a better way?

Is there any reason I couldn’t ask to have ALTER TRACE permissions on the development instances of SQL?

Answer: Requesting SQL Server trace functionality is common. As a DBA – I ask developers to make the request – specifying the duration they require the ALTER TRACE privilege.  In older SQL server versions – sysadmin authority was required – which presented problems with SQL Server Security Policies.

 The ALTER TRACE syntax is :

GRANT ALTER TRACE TO hendrix

SQL Traces and c2 auditing are different

Sometimes end users interchange the terms : C2 auditing and Traces.
They server different purposes.

c2 auditing records all attempts (successfull and failed) on objects and statements. Typically a SQL Server DBA will use a c2 audit to monitor security compliance .

A trace will will monitor an event or a series of events. Events can be any source producing a trace events. Examples are deadlocks and DTC transactions, SQL Server Backups.Read BOL for a comprehensive list of events

To view if c2 auditing is turned on use:

--To view if c2 auditing is turned on use:

EXEC sp_configure 'c2 audit mode'


--To view if traces including user defined ones are running on SQL server 2005 use:

SELECT * FROM sys.fn_trace_getinfo(0) ;

Author: Jack Vamvas (http://www.sqlserver-dba.com)