SQL Server 2012 – Monitoring Slow Queries and Blocking Issues

performancequery-performanceslow-logsql-server-2012

I am using SQL Server 2012 and I want to get hold of the queries which are taking longer to execute along with the queries which are causing blocking on the server. When I look at the server for the running queries to identify any blocking I don't find any query.

I have setup extended events and getting only high level information from that. The tables which are getting timeouts but I need information which tells more about the queries which caused the timeouts.

Any suggestions???

Best Answer

You can make use of the dmv's sys.dm_exec_query_stats and sys.dm_exec_requests to analyse the resource consuming queries along with their duration for what long:

Use the query below to capture the same:

-- Execute the query inside target database
 SELECT TOP 10
      qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
      qs.total_elapsed_time / 1000000.0 AS total_seconds,
      qs.execution_count,
      SUBSTRING (qt.text,qs.statement_start_offset/2, 
      (CASE WHEN qs.statement_end_offset = -1 
      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
      ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
      o.name AS object_name,
      DB_NAME(qt.dbid) AS database_name
FROM 
      sys.dm_exec_query_stats qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
      LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE 
      qt.dbid = DB_ID()
ORDER BY 
      average_seconds DESC;

Moreover would suggest if you are using extended events, you can capture the same. For more on how to set up and use the same, please read this whitepaper from Jonathan Using SQL Server 2008 Extended Events which should get you going alon with here

Also, when the queries run , and what all process run at the background and to check if blocking is really out there along with any particular waits , would suggest you to use SP_WHOISACTIVE from How to Use sp_WhoIsActive to Find Slow SQL Server Queries

Also, if you prefer to use Profiler you can track and troubleshoot the same as explained in How to Identify Slow Running Queries with SQL Profiler

Related Solutions

Sql-server – TSQL: Find Queries causing too many SQL Compilations and SQL ReCompilations seperately

I don't think you can find this by an easy way but it is possible anyway to get through this. Profiler offers many event class types that can be used in analyzing the performance of a query. Start a new Profiler session and check following events:

Performance: Performance statistics
Stored Procedures: RPC:Completed
TSQL: SQL:BatchCompleted
TSQL: SQL: BatchStarting

Check to Show all columns and select each one of the columns under Performance: Performance statistics event only. The rest of events can be left with default setting.

Next, Select Column Filters and filter by DatabaseName and/or LoginName/ApplicationName/HostName etc.., if you know them. The purpose is to limit the number of rows dispalyed in Profiler and concentrate only on your needs.

Next, press Run and let it run for a while (2-3 min as long as you need). Analyse the results dispalyed looking primarily at: Performance statistics event.

If Performance Statistics will occur often it means that the plan of a query was cached for the first time, compiled, re-compiled or evicted from PlanCache. From my knowledge if a query does not have its query plan in Plan Cache - you will see 2 rows of PerformanceStatistics event and followed by SQL:BatchStarting, then SQL:BatchCompleted. It means that the Query Plan was first compiled, cached and then the query started and completed.

Look at following columns under Performance Statistics event:

SPID - ID of the session on which the event occurred. You can use it to identify the       
       row on SQL:BatchCompleted event which will display the SQL Query text and other  
       usefull information (Read/Writes, StartTime/EndTime)
Duration - Total time, in microseconds, spent during compilation.
EventSubClass - 0 = New batch SQL text that is not currently present in the cache.
                1 = Queries within a stored procedure have been compiled.
                2 = Queries within an ad hoc SQL statement have been compiled.
                3 = A cached query has been destroyed and the historical performance         
                    data associated with the plan is about to be destroyed.
                4 = A cached stored procedure has been removed from the cache and the  
                    historical performance data associated with it is about to be 
                    destroyed.

                5 = A cached trigger has been removed from the cache and the historical  
                    performance data associated with it is about to be destroyed.

Considering the EventSubClass number you can find out what happened with the Query Plan and take specific measures. Additionally you can add other columns to Stored Procedures and TSQL Event Classes if you are interseted in HostName, WindowsUser or other info from Profiler trace. Also the trace can be stored in a SQL table making the analyse more easy and much more customizable. Here is a link describing more the Performance Statistics Event Class.

SQL Server 2012 – Troubleshooting Extended Events for Selective XML Indexing

Not sure what you are seeing and not seeing in the event log but it works as expected for me (version 11.0.3000).

The mistake you did with your extra path expressions is that you forgot Stuff in the path.

Testing this with the extended events and looking at the execution plan for operator Table Valued Function XML Reader ... (there should be none), I found that one extra path is enough for your queries to make it use only the index to fetch the data [4] = '/Root/Stuff/Forms' as SQL nvarchar(25). You can have [3] = '/Root/Stuff/Forms' as XQUERY 'node()' in there as well and perhaps it will make a difference or not depending on what your XML look like.

Note 1: You have mixed up the names of the columns in the index and your query. That might contribute to the problems you have with extended event.

Note 2: The events for missing selective XML indexes is generated when the query is compiled. If the query plan is used from the cache you will not see any events.

Best Answer

Related Solutions

Sql-server – TSQL: Find Queries causing too many SQL Compilations and SQL ReCompilations seperately

SQL Server 2012 – Troubleshooting Extended Events for Selective XML Indexing

Related Question