TSQL To identify queries which are candidates for Parameterization

While discussing the concepts of 'optimize for ad hoc workloads' and 'Forced Parameterization' in SQL Server I decided to modify an existing query I wrote to analyse the query performance from the cache (more on it here) to now analyse the adhoc queries for candidates which could benefit from parameterization.

;WITH CTE(QueryCount, StatementTextForExample, plan_handle, QueyHash, QueryPlanHash, CacheObjType, 
    ObjType) 
AS 
( 
   SELECT
    COUNT(query_stats.query_hash) AS QueryCount
    , MIN(query_stats.query_text) AS StatementTextForExample
    , MIN(query_stats.plan_handle) AS plan_handle
    , query_stats.query_hash AS QueryHash
    , query_stats.query_plan_hash AS QueryPlanHash
    , query_stats.CacheObjType
    , query_stats.ObjType
   FROM  
       ( 
           SELECT  
      qs.query_hash
      , qs.query_plan_hash 
      , qs.plan_handle
      , cp.cacheobjtype as [CacheObjType]
      , cp.objtype as [ObjType]
      , 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 query_text 
           FROM  
               sys.dm_exec_query_stats AS qs 
      INNER JOIN sys.dm_exec_cached_plans cp ON cp.plan_handle = qs.plan_handle
               CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt 
           WHERE qt.[text] NOT LIKE '%sys.dm_exec_query_stats%' 
     AND cp.objtype = 'AdHoc'
     --AND qs.last_execution_time BETWEEN DATEADD(hour,-1,GETDATE()) AND GETDATE() --change hour time frame
       ) AS query_stats 
 GROUP BY query_stats.query_hash
  , query_stats.query_plan_hash
  , query_stats.CacheObjType
  , query_stats.ObjType
 HAVING COUNT(query_stats.query_hash) > 1
) 
SELECT  
   CTE.QueryCount
   , CTE.CacheObjType
   , CTE.ObjType
   , CTE.StatementTextForExample
   , tp.query_plan AS StatementPlan
   , CTE.QueyHash
   , CTE.QueryPlanHash 
FROM 
   CTE 
   OUTER APPLY sys.dm_exec_query_plan(CTE.plan_handle) AS tp 
ORDER BY CTE.QueryCount DESC; 
--ORDER BY [Total IO] DESC; 

When you identify these candidates you then need to look at what is the most appropriate resolution such as.

1. Rewrite the query at the application layer to ensure it is called with parameterization from the data provider
2. Rewrite the query as a stored procedure
3. Enable 'optimize for ad hoc workloads' on your SQL instance
   

   EXEC sys.sp_configure N'optimize for ad hoc workloads', N'0'
   GO
   RECONFIGURE WITH OVERRIDE
   GO
   

4. Enable 'Forced Parameterization' on the affected database
   

   USE [master]
   GO
   ALTER DATABASE [AdventureWorksPTO] SET PARAMETERIZATION SIMPLE WITH NO_WAIT
   GO
   

Of cause the appropriate option really depends on a larger view of your environment and applications that only you can determine. If you do have any thoughts on this please feel free to add them to the comments below to help others.

Word of caution too. Before adjusting any sp_configure settings with RECONFIGURE make sure you run the following to check for any settings which are not yet active. It is expected to see 'min server memory (MB)' in the results of this though if you leave the setting default at 0 as SQL must reserve the minimum memory possible which is 16mb.

SELECT * FROM sys.configurations
WHERE Value <> value_in_use

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.





 

Troubleshooting blocking locks in SQL Azure

I was faced with an interesting situation yesterday where a customer had created a blocking chain on their SQL Azure database. The scenario they described was:

A query took out and held a lock on object but the client connection closed and orphaned the session on the SQL Azure database. This resulted in other queries trying to access the object being blocked and timing out (even a SELECT *).

Given we do not have any graphical tools in SSMS to see current activity when you connect to a SQL Azure database, this offers a challenge for those that are use to going to Activity Monitor or the Reports to view this sort of information. Thankfully most of DMV's and DMF's have made their way into support on SQL Azure.

To troubleshoot the above scenario we then used the following queries to identify the culprit and eventually terminate it with the only option available.... KILL [spid]. To demonstrate this I have created the scenario on my Azure database through the use of PowerShell (but i am not sharing that as it is bad practice). The good news is that these queries will also work for your on-premise environments.

TSQL to identify the Blockers and Victims

WITH Blockers AS
    (select DISTINCT blocking_session_id as session_id
 from sys.dm_exec_requests
 where blocking_session_id > 0
)
SELECT 'Blocker' as type_desc
 , sys.dm_exec_sessions.session_id
 , sys.dm_exec_requests.start_time
 , sys.dm_exec_requests.status
 , sys.dm_exec_requests.command
 , sys.dm_exec_requests.wait_type
 , sys.dm_exec_requests.wait_time
 , sys.dm_exec_requests.blocking_session_id
 , '' AS stmt_text
FROM sys.dm_exec_sessions
LEFT JOIN sys.dm_exec_requests ON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id
INNER JOIN Blockers ON Blockers.session_id = sys.dm_exec_sessions.session_id
UNION
SELECT 'Victim' as type_desc
 , sys.dm_exec_sessions.session_id
 , sys.dm_exec_requests.start_time
 , sys.dm_exec_requests.status
 , sys.dm_exec_requests.command
 , sys.dm_exec_requests.wait_type
 , sys.dm_exec_requests.wait_time
 , sys.dm_exec_requests.blocking_session_id
 , ST.text AS stmt_text
FROM sys.dm_exec_sessions
INNER JOIN sys.dm_exec_requests ON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(sys.dm_exec_requests.sql_handle) AS ST
WHERE blocking_session_id > 0

The output of this query looks then like the following where you can clearly see the blocker and it's victims (in fact this example then has a 2nd layer of blocking).

TSQL to view the established locks within the current database

SELECT
 (CASE sys.dm_tran_locks.request_session_id
  WHEN -2 THEN 'ORPHANED DISTRIBUTED TRANSACTION'
  WHEN -3 THEN 'DEFERRED RECOVERY TRANSACTION'
  ELSE sys.dm_tran_locks.request_session_id
 END) AS session_id
 , DB_NAME(sys.dm_tran_locks.resource_database_id) AS database_name
 , sys.objects.name AS locked_obj_name
 , sys.dm_tran_locks.resource_type AS locked_resource
 , sys.dm_tran_locks.request_mode AS lock_type
 , ST.text AS stmt_text
 , sys.dm_exec_sessions.login_name AS login_name
 , sys.dm_exec_sessions.host_name AS host_name
 , sys.dm_tran_locks.request_status as request_status
FROM sys.dm_tran_locks
JOIN sys.objects ON sys.objects.object_id = sys.dm_tran_locks.resource_associated_entity_id
JOIN sys.dm_exec_sessions ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
JOIN sys.dm_exec_connections ON sys.dm_exec_connections.session_id = sys.dm_exec_sessions.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(sys.dm_exec_connections.most_recent_sql_handle) AS st
WHERE sys.dm_tran_locks.resource_database_id = DB_ID()
ORDER BY sys.dm_tran_locks.request_session_id

The output of this query shows the various locks which have been established by each session (one-to-many in sessions-to-locks). From here we can see which locks will have taken priority and potentially blocking more queries in the future.

So while you may not have a graphical view of this information we can definitely use our trusted DMV's and DMF's to gain access to the relevant data. Who knows with the rate of change it can surely only be a matter of time until Activity Monitor and the standard reports are available to us in SSMS for SQL Azure.

Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

RML now supports SQL 2012 (and SQL 2014)

If you use the Replay Markup Language (RML) tool set then like me you will have been frustrated by the fact that it hadn't been updated to work with trace files captured from SQL 2012. You could probably assume that this was because the SQL Profiler Trace feature for purpose of trace capture (data engine only) is flagged for depreciation in future released (see http://technet.microsoft.com/en-us/library/ms181091.aspx).

Thankfully last week a new version was released which fully support SQL 2012 and even SQL 2014 CTP2 trace file definitions. I have just gone through the process of uninstalling my RML tools, installing the new version, and straight away I can process the trace files captured on a SQL 2012 instance. Hurray!

Be warned though you cannot use the new version of the Reporter tool to access any databases for previously processed trace data. I typically use this tool for performance analysis so I might keep the analysis databases around for a few weeks, so this just means I have to reprocess the trace files to generate reports compatible with the new version. Still worth it to get the benefit of the new supportability.


Description of the Replay Markup Language (RML) Utilities for SQL Server
http://support.microsoft.com/kb/944837

If you find that you cannot click on the navigational hyperlinks on the Main Performance Overview report (e.g. "Unique Batches", "Unique Statements", "Interesting Events", etc) then you will need to install the hotfix or ReportViewer.

Update fixes several Report Viewer issues after you install Visual Studio 2010 Service Pack 1
http://support.microsoft.com/kb/2549864
.....Or go directly to the download location at https://connect.microsoft.com/VisualStudio/Downloads/DownloadDetails.aspx?DownloadID=37409

TSQL to find Top Queries by Avg CPU, IO, Time

One of my favourite performance tools has certainly become the RML reports as you can quickly identify queries with common hot spots across performance metrics. However this requires a bit of work, you first have to configure your performance trace (usually with Diag Manager), capture the SQL Profiler Trace, and then analyse it with RML/SQLNexus. Of cause the UI with XEvents makes this a bit easier if you use that as your capture. However what if you want to look at a general overall performance of a server, or for a longer duration.
We know that SQL Server is always capturing data for us in the background and exposes that to us with DMVs and DMFs. So I came up with the following query which uses the DMVs/DMFs to report the top queries.
You can change the sort order at the end of the query however as I have included the Rank number per performance metric (CPU, IO, Time) you can quickly see queries which are in the top of each group without needing to resort the results or run multiple queries.
If you did want to run multiple queries with different sort columns, then the Query Number should remain the same as it is based on CPU and the reason i included this is to allow you to easily compare the queries across multiple executions (depending on the time between executions).
;WITH CTE([Query Num], [Total Executions], [Total CPU], [Avg CPU], [Avg CPU Rank], [Total IO], [Avg IO], [Avg IO Rank], [Total Elapsed Time], [Avg Elapsed Time], [Avg Elapsed Time Rank], [Sample Statement Text], [Query Plan Handle], [Statement Hash], [Query Plan Hash])
AS
(
    SELECT TOP 50
        ROW_NUMBER() OVER (ORDER BY (SUM(total_worker_time) / SUM(execution_count)) DESC) AS [Query Num]        , SUM(execution_count) AS [Total Executions]        , SUM(total_worker_time) AS [Total CPU]        , SUM(total_worker_time) / SUM(execution_count) AS [Avg CPU]        , RANK() OVER (ORDER BY (SUM(total_worker_time) / SUM(execution_count)) DESC) AS [Avg CPU Rank]        , SUM(total_physical_reads + total_logical_reads + total_logical_writes) AS [Total IO]        , SUM(total_physical_reads + total_logical_reads + total_logical_writes) / SUM(execution_count) AS [Avg IO]        , RANK() OVER (ORDER BY (SUM(total_physical_reads + total_logical_reads + total_logical_writes) / SUM(execution_count)) DESC) AS [Avg IO Rank]        , SUM(total_elapsed_time) AS [Total Elapsed Time]
        , SUM(total_elapsed_time) / SUM(execution_count) AS [Avg Elapsed Time]        , RANK() OVER (ORDER BY (SUM(total_elapsed_time) / SUM(execution_count)) DESC) AS [Avg Elapsed Time Rank]
        , MIN(query_text) AS [Sample Statement Text]        , MIN(plan_handle) AS [Query Plan Handle]        , query_hash AS [Statement Hash]        , query_plan_hash AS [Query Plan Hash]
    FROM 
    (
        SELECT 
            qs.*, 
            SUBSTRING(st.[text], qs.statement_start_offset/2, (
                CASE 
                    WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), st.[text])) * 2 
                    ELSE qs.statement_end_offset                  END - qs.statement_start_offset)/2 
            ) AS query_text
        FROM sys.dm_exec_query_stats AS qs
        CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS st
        WHERE st.[text] NOT LIKE '%sys.dm_%'
        --AND DateDiff(hour, last_execution_time, getdate()) < 1 --change hour time frame
    ) AS query_stats     GROUP BY query_hash, query_plan_hash )
SELECT 
    [Query Num]
   , [Total Executions]
   , [Total CPU]
   , [Avg CPU]
   , [Avg CPU Rank]
   , [Total IO]
   , [Avg IO]
   , [Avg IO Rank]
   , [Total Elapsed Time]
   , [Avg Elapsed Time]
   , [Avg Elapsed Time Rank]  , DB_Name(qp.dbid) AS [DB Name]   , [Sample Statement Text]
   , qp.query_plan AS [Estimated Query Plan]FROM CTE
OUTER APPLY sys.dm_exec_query_plan([Query Plan Handle]) AS qp --ORDER BY [Avg CPU] DESC
ORDER BY [Avg IO] DESC --ORDER BY [Avg Elapsed Time] DESC


NOTE: This query includes a TOP 50 which improves performance but only looks at very recent data. I would recommend using the predicate on last_execution_time to restrict the data back to a valid time frame. This would still then include the total history for those requests but ensure that you are only looking at active queries.


Here is an example output. In this example I had cleared the cache and only a small test data so the Avg IO rank is the same for each query but this gives you an idea.


So far I’ve tested this on SQL 2008 R2 and above.
I’m also closing in on finalising the BETA release of my DBA Admin and Performance SSMS reports which will include this query and take this to a whole new level of visualisation.

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

TSQL to identify databases with high number of VLFs

If you aren't aware there can be significant impact to performance when a database has a high number of VLFs within it's Transaction Log. SQL Server divides each physical transaction log file internally into a number of virtual log files (VLFs) and it is these that contain the log records.  There is no fixed size for a VLF, and there is no fixed number which would be created within a physical log file. These values are all determined dynamically by SQL Server when creating or extending physical log files.  While SQL Server tries to maintain a small number of VLFs for the best performance, file growth can result in a database having a large number of VLFs because the following equation will be used to determine the number of files to create within the new space.

- Less than 64MB = 4 VLFs
- Greater than 64MB and less than 1GB = 8 VLFs
- Greater than or equal to 1GB = 16 VLFs

So for example if I initially create a 1GB transaction log file it will be divided into 16 64MB VLFs, then if it grew a few times by 10 Mb before being switched to 10% the following trend would be experienced and result in a quickly increasing number of VLFs when then compared to manually growing the file:

Action	Space Added	VLFs Created	Total Size	Total VLFs
Initial Size	1024 Mb	16	1024 Mb	16
Auto Grow	10 Mb	4	1034 Mb	20
Auto Grow	10 Mb	4	1044 Mb	24
Auto Grow	10 Mb	4	1054 Mb	28
Auto Grow	10 Mb	4	1064 Mb	32
Auto Grow	106 Mb	8	1170 Mb	40
Auto Grow	117 Mb	8	1287 Mb	48
Auto Grow	128 Mb	8	1415 Mb	56
Auto Grow	141 Mb	8	1556 Mb	64
Auto Grow	155 Mb	8	1711 Mb	72
Auto Grow	171 Mb	8	1882 Mb	80
Auto Grow	188 Mb	8	2070 Mb	88

The only way to reduce the number of VLFs is to then Shrink the file and manually grow the file by a set size such as:

Action	Space Added	VLFs Created	Total Size	Total VLFs
Initial Size	1024 Mb	16	1024 Mb	16
Manual Grow	1024 Mb	16	2048 Mb	32
Auto Grow	204 Mb	8	2252 Mb	40

Ideally you should keep the number of VLFs to as small as possible. In SQL 2012 there is now a warning raised when a database has >10,000 VLFs, although there is no warning available in earlier versions of SQL Server. You can use the following TSQL though to report on the number of VLFs per database within your SQL Instance. Then if you compare this with your auto-grow settings for the database you can determine the reason for why the count is the way it is.

SET NOCOUNT ON;
/* declare variables required */DECLARE @DatabaseId INT;
DECLARE @TSQL varchar(MAX);
DECLARE cur_DBs CURSOR FOR
SELECT database_id FROM sys.databases;
OPEN cur_DBs;
FETCH NEXT FROM cur_DBs INTO @DatabaseId

--These table variables will be used to store the dataDECLARE @tblAllDBs Table (DBName sysname
   , FileId INT
   , FileSize BIGINT
   , StartOffset BIGINT
   , FSeqNo INT
   , Status TinyInt
   , Parity INT
   , CreateLSN NUMERIC(25,0)
)IF '11' = substring(convert(char(12),serverproperty('productversion')), 1, 2)
BEGIN   DECLARE @tblVLFs2012 Table (RecoveryUnitId BIGINT
      , FileId INT
      , FileSize BIGINT
      , StartOffset BIGINT
      , FSeqNo INT
      , Status TinyInt
      , Parity INT
      , CreateLSN NUMERIC(25,0)
   );
END
ELSE
BEGIN   DECLARE @tblVLFs Table (
      FileId INT
      , FileSize BIGINT
      , StartOffset BIGINT
      , FSeqNo INT
      , Status TinyInt
      , Parity INT
      , CreateLSN NUMERIC(25,0)
   );
END
--loop through each database and get the infoWHILE @@FETCH_STATUS = 0
BEGIN   PRINT 'DB: ' + CONVERT(varchar(200), DB_NAME(@DatabaseId));
   SET @TSQL = 'dbcc loginfo('+CONVERT(varchar(12), @DatabaseId)+');';
   IF '11' = substring(convert(char(12),serverproperty('productversion')), 1, 2)
   BEGIN
      DELETE FROM @tblVLFs2012;
      INSERT INTO @tblVLFs2012
      EXEC(@TSQL);
      INSERT INTO @tblAllDBs
      SELECT DB_NAME(@DatabaseId)
         , FileId
         , FileSize
         , StartOffset
         , FSeqNo
         , Status
         , Parity
         , CreateLSN
      FROM @tblVLFs2012;
   END
   ELSE
   BEGIN
      DELETE FROM @tblVLFs;
      INSERT INTO @tblVLFs
      EXEC(@TSQL);
      INSERT INTO @tblAllDBs
      SELECT DB_NAME(@DatabaseId)
         , FileId
         , FileSize
         , StartOffset
         , FSeqNo
         , Status
         , Parity
         , CreateLSN
      FROM @tblVLFs;
   END
   FETCH NEXT FROM cur_DBs INTO @DatabaseId
ENDCLOSE cur_DBs;
DEALLOCATE cur_DBs;

--just for formating if output to TextPRINT '';
PRINT '';
PRINT '';

--Return the data based on what we have foundSELECT a.DBName
    , COUNT(a.FileId) AS [TotalVLFs]
    , MAX(b.[ActiveVLFs]) AS [ActiveVLFs]
    , (SUM(a.FileSize) / COUNT(a.FileId) / 1024) AS [AvgFileSizeKb]
FROM @tblAllDBs a
INNER JOIN (
    SELECT DBName
        , COUNT(FileId) [ActiveVLFs]
    FROM @tblAllDBs
    WHERE Status = 2
    GROUP BY DBName
) b
ON b.DBName = a.DBName
GROUP BY a.DBName
ORDER BY TotalVLFs DESC;

SET NOCOUNT OFF;


Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.