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.

Using PSSDiag/SQLDiag Manager to collect data on SQL 2012 Instances

If you are like me and you prefer using the PSSDiag / SQLDiag Manager to create the XML configuration file and run SQLDiag to collect performance data from your SQL Server, then you are probably wondering why there hasn't been an update to include SQL 2012 yet. Never fear there is only a small "hack" you need to do within the produced configuration file for it to run against SQL 2012.

First off use PSSDiag / SQLDiag Manager as you would normally for SQL 2008 R2. Here's a high level of what I configure when I am only looking to collect a Profiler Trace and PerfMon which will typically satisfy the needs of most SQL Engineers unless you are troubleshooting a lower level performance issue.

1. Select "SQL 2008" as the SQL Version
2. Enter the machine name
3. Enter the instance name (if required)
4. Ensure that Windows Auth is selected
5. Deselect Event Logs, Shutdown from Machine Wide Diagnostics
6. Deselect SQLDIAG, and Shutdown from Instance-Specific Diagnostics
7. Ensure that Trace is checked
8. Select either the _GeneralPerformance10.xml or DetailedPerformance10.xml template depending on the level of Statement detail you wish to collect.
9. Within the Custom Diagnostics section deselect the SQL Base, MSInfo, SQL Best Practises, SQL 2008 Perf Stats, SQL Dump
10. Click the Save button
11. Enter the path where you wish to save the .cab file.
12. When prompted select SQL 2008 R2

Now is where you must modify the generated XML so it will work with SQL 2012. Here is what to do:

 

1. Locate the cab file you generated and extract it
2. With in the extracted files, locate the PSSDiag.xml and open it in Notepad (or your favourite XML editor)
   
   
3. Locate the node and change the value of ssver to 11.
   
   The original xml will look like:
   
   
   The modified xml should now be:
   
4. Save the XML and move it back into the .cab if required.
5. Copy the files/cab to your server and run it as usual.

Additional Tip......

I will usually also modify the PSSDiag.cmd file to set an end time so that if I schedule the data collection it will stop without user interaction. Also this helps in case some operational issues arise and distract enough to forgot to stop the collection.

To do this add the /E parameter to the end of the call to SQLDiag and supply the duration in the format of +HH:MM. So if I was to set SQLDiag to stop after 2 hours the line would look like:

%diagEXE% /O output /I pssdiag.xml /P %1 %2 %3 %4 %5 %6 %7 %8 %9 /E +02:00



NOTE: I haven't actually gone through and validated if there are any different events we need to start capturing for SQL 2012, but should I find any I will update this post. Also I should point out that SQL Profiler has been marked as a depreciated feature and may be removed from future releases, which makes Extended Events the recommended method of capturing/monitoring SQL activity moving forward.

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.