Query Details Report Error

If we click on the query detail hyperlink on the Query Statistics History report or any other report with a query detail hyperlink:

We may get this error:

The error doesn’t make much sense so if we look at the SQL Server error log, we will find out that tempdb fill out:

Could not allocate space for object 'dbo.SORT temporary run storage:  142388431486976' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Moreover if we run SQL Server Profiler, we will find the stored procedure that the report uses:

exec snapshots.rpt_query_stats

Analyzing the T-SQL, the culprit is the subquery used to find the dense rank, it doesn’t have any "where" clause (line 29 below) so it will scan the entire snapshots.query_stats table.

 

    SELECT 
        REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (
            LEFT (LTRIM (stmtsql.query_text), 100)
            , CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), '   ', ' '), '  ', ' '), '  ', ' ') AS flat_query_text, 
        t.*, 
        master.dbo.fn_varbintohexstr (t.sql_handle) AS sql_handle_str, 
        stmtsql.*
    FROM 
    (
        SELECT 
            stat.sql_handle, stat.statement_start_offset, stat.statement_end_offset, snap.source_id, 
            SUM (stat.snapshot_execution_count) AS execution_count, 
            SUM (stat.snapshot_execution_count) / (@interval_sec / 60) AS executions_per_min, 
            SUM (stat.snapshot_worker_time / 1000) AS total_cpu, 
            SUM (stat.snapshot_worker_time / 1000) / @interval_sec AS avg_cpu_per_sec, 
            SUM (stat.snapshot_worker_time / 1000.0) / CASE SUM (stat.snapshot_execution_count) WHEN 0 THEN 1 ELSE SUM (stat.snapshot_execution_count) END AS avg_cpu_per_exec, 
            SUM (stat.snapshot_physical_reads) AS total_physical_reads, 
            SUM (stat.snapshot_physical_reads) / @interval_sec AS avg_physical_reads_per_sec, 
            SUM (stat.snapshot_physical_reads) / CASE SUM (stat.snapshot_execution_count) WHEN 0 THEN 1 ELSE SUM (stat.snapshot_execution_count) END AS avg_physical_reads_per_exec, 
            SUM (stat.snapshot_logical_writes) AS total_logical_writes, 
            SUM (stat.snapshot_logical_writes) / @interval_sec AS avg_logical_writes_per_sec, 
            SUM (stat.snapshot_logical_writes) / CASE SUM (stat.snapshot_execution_count) WHEN 0 THEN 1 ELSE SUM (stat.snapshot_execution_count) END AS avg_logical_writes_per_exec, 
            SUM (stat.snapshot_elapsed_time / 1000) AS total_elapsed_time, 
            SUM (stat.snapshot_elapsed_time / 1000) / @interval_sec AS avg_elapsed_time_per_sec, 
            SUM (stat.snapshot_elapsed_time / 1000.0) / CASE SUM (stat.snapshot_execution_count) WHEN 0 THEN 1 ELSE SUM (stat.snapshot_execution_count) END AS avg_elapsed_time_per_exec, 
            COUNT(*) AS row_count, COUNT(DISTINCT plan_number) AS plan_count
        FROM
        (
            SELECT *, DENSE_RANK() OVER (ORDER BY plan_handle, creation_time) AS plan_number
            FROM snapshots.query_stats s
        ) AS stat
        INNER JOIN core.snapshots snap ON stat.snapshot_id = snap.snapshot_id
        WHERE
            snap.instance_name = @instance_name 
            AND stat.sql_handle = @sql_handle 
            AND stat.statement_start_offset = @statement_start_offset 
            AND stat.statement_end_offset = @statement_end_offset
            AND snap.snapshot_time_id BETWEEN @start_snapshot_time_id AND @end_snapshot_time_id
        GROUP BY stat.sql_handle, stat.statement_start_offset, stat.statement_end_offset, snap.source_id
    ) t
    LEFT OUTER JOIN snapshots.notable_query_text sql ON t.sql_handle = sql.sql_handle and sql.source_id = t.source_id
    OUTER APPLY snapshots.fn_get_query_text (t.source_id, t.sql_handle, t.statement_start_offset, t.statement_end_offset) AS stmtsql

Looking at the query plan, it will sort the entire snapshots.query_stats table and it will cause tempdb full if there is a large amount of rows.

The workaround is to add "join" and the "where" clause (line 32 above) inside the subquery (line 31 below) to limit the number of rows.

 
    SELECT 
        REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (
            LEFT (LTRIM (stmtsql.query_text), 100)
            , CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' '), '   ', ' '), '  ', ' '), '  ', ' ') AS flat_query_text, 
        t.*, 
        master.dbo.fn_varbintohexstr (t.sql_handle) AS sql_handle_str, 
        stmtsql.*
    FROM 
    (
        SELECT 
            stat.sql_handle, stat.statement_start_offset, stat.statement_end_offset, stat.source_id, 
            SUM (stat.snapshot_execution_count) AS execution_count, 
            SUM (stat.snapshot_execution_count) / (@interval_sec / 60) AS executions_per_min, 
            SUM (stat.snapshot_worker_time / 1000) AS total_cpu, 
            SUM (stat.snapshot_worker_time / 1000) / @interval_sec AS avg_cpu_per_sec, 
            SUM (stat.snapshot_worker_time / 1000.0) / CASE SUM (stat.snapshot_execution_count) WHEN 0 THEN 1 ELSE SUM (stat.snapshot_execution_count) END AS avg_cpu_per_exec, 
            SUM (stat.snapshot_physical_reads) AS total_physical_reads, 
            SUM (stat.snapshot_physical_reads) / @interval_sec AS avg_physical_reads_per_sec, 
            SUM (stat.snapshot_physical_reads) / CASE SUM (stat.snapshot_execution_count) WHEN 0 THEN 1 ELSE SUM (stat.snapshot_execution_count) END AS avg_physical_reads_per_exec, 
            SUM (stat.snapshot_logical_writes) AS total_logical_writes, 
            SUM (stat.snapshot_logical_writes) / @interval_sec AS avg_logical_writes_per_sec, 
            SUM (stat.snapshot_logical_writes) / CASE SUM (stat.snapshot_execution_count) WHEN 0 THEN 1 ELSE SUM (stat.snapshot_execution_count) END AS avg_logical_writes_per_exec, 
            SUM (stat.snapshot_elapsed_time / 1000) AS total_elapsed_time, 
            SUM (stat.snapshot_elapsed_time / 1000) / @interval_sec AS avg_elapsed_time_per_sec, 
            SUM (stat.snapshot_elapsed_time / 1000.0) / CASE SUM (stat.snapshot_execution_count) WHEN 0 THEN 1 ELSE SUM (stat.snapshot_execution_count) END AS avg_elapsed_time_per_exec, 
            COUNT(*) AS row_count, COUNT(DISTINCT plan_number) AS plan_count
        FROM
        (
            SELECT s.*, snap.source_id, DENSE_RANK() OVER (ORDER BY plan_handle, creation_time) AS plan_number
            FROM snapshots.query_stats s
            INNER JOIN core.snapshots snap ON s.snapshot_id = snap.snapshot_id
            WHERE
               snap.instance_name = @instance_name 
               AND s.sql_handle = @sql_handle 
               AND s.statement_start_offset = @statement_start_offset 
               AND s.statement_end_offset = @statement_end_offset
               AND snap.snapshot_time_id BETWEEN @start_snapshot_time_id AND @end_snapshot_time_id
        ) AS stat
        --move this filter to the inner subquery above to avoid a large table scan and spill to tempdb
  --INNER JOIN core.snapshots snap ON stat.snapshot_id = snap.snapshot_id
        --WHERE
        --    snap.instance_name = @instance_name 
        --    AND stat.sql_handle = @sql_handle 
        --    AND stat.statement_start_offset = @statement_start_offset 
        --    AND stat.statement_end_offset = @statement_end_offset
        --    AND snap.snapshot_time_id BETWEEN @start_snapshot_time_id AND @end_snapshot_time_id
        GROUP BY stat.sql_handle, stat.statement_start_offset, stat.statement_end_offset, stat.source_id
    ) t
    LEFT OUTER JOIN snapshots.notable_query_text sql ON t.sql_handle = sql.sql_handle and sql.source_id = t.source_id
    OUTER APPLY snapshots.fn_get_query_text (t.source_id, t.sql_handle, t.statement_start_offset, t.statement_end_offset) AS stmtsql

Thus it will produce a most efficient query plan

 

Here it is the Connect item link

 

How many targets can a centralized MDW support?

In Data Collection terminology in books online, a target is an instance of the Database Engine in an edition of SQL Server that supports Data Collection.

The Management Data Warehouse (MDW) is a relational database used to store collected data. This database can be centralized and shared among multiple targets.

But I haven’t seen any guideline of how many.

We can test it out doing the following.

TEST SETUP

It is a 2 step process outlined below.

1. Create the centralized MDW in a SQL Server, e.g. SQL01 using the Configure the Management Data Warehouse Wizard as outlined on books online http://msdn.microsoft.com/en-us/library/bb933864.aspx
2. Configure Data Collection on a couple or more targets using the same data collection schedule. In this test I set up about 50 targets (e.g. SQL02, SQL03, etc) with a 15 min collection schedule using the following T-SQL:

 
exec msdb.dbo.sp_syscollector_set_warehouse_database_name @database_name = 'mdw'
exec msdb.dbo.sp_syscollector_set_warehouse_instance_name @instance_name = 'server'
exec msdb.dbo.sp_syscollector_set_cache_directory @cache_directory = 'c:\temp'
exec msdb.dbo.sp_syscollector_enable_collector
exec msdb.dbo.sp_syscollector_update_collection_set @name = 'Disk Usage', @days_until_expiration = 2
exec msdb.dbo.sp_syscollector_update_collection_set @name = 'Server Activity', @days_until_expiration = 2
exec msdb.dbo.sp_syscollector_update_collection_set @name = 'Query Statistics', @days_until_expiration = 2
exec msdb.dbo.sp_syscollector_update_collection_set @name = 'Server Activity', @schedule_name=N'CollectorSchedule_Every_15min'
exec msdb.dbo.sp_syscollector_update_collection_set @name = 'Query Statistics', @schedule_name=N'CollectorSchedule_Every_15min'

OBSERVATIONS

In one of the targets, we can look at the agent job activity for the next upload time to the MDW. As configured, all targets will upload at the same time, e.g. collection_set_2_upload next run is 5:15 PM. 

On the next run time, e.g. 5:15 PM run the following query on the MDW server (SQL01) to see what is going on.

 
select
r.session_id
,r.blocking_session_id
,r.status
,r.last_wait_type
,r.wait_resource
,r.wait_type
,r.wait_time
,t.text
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.plan_handle) t

In the output above, each data uploading session is waiting on the same KEY resource lock. We can query sys.partitions using hobt_id = 72057594046447616 and find that the index is UN_performance_counter_path on the table performance_counter_instance.

Why a bulk insert is causing blocking on an index key?

If we look a the index properties, we find that the “Ignore duplicated values” option have been turned on.

If we look at the SSIS package of the data collector PerfCountersUpload, we can find the data flow for performance_counters_instances table.

If we look at the data flow, we can see that the performance counters file is bulk loaded into performance_counter_instances table to find new performance counters instance paths.

What is the logic to find the new performance counters instance paths?

Well it appears that logic is pretty simple: load the file and ignore the duplicate paths, since we found earlier that the index UN_performance_counter_path has the “Ignore duplicate values” option turn on.

This would answer the question of why the bulk insert is causing blocking: because it is bulk inserting duplicates and has to place a LCK_M_RS_U lock on the index key.

The side effect of this behavior is that it serializes the bulk inserts. No matter how many targets are loading data concurrently to the central MDW, it will insert/ignore one duplicate path at the time.

How long can the blocked data loads wait?

On my test of 50 targets some of the data collection jobs failed with this message

The thread "ExecMasterPackage" has timed out 3600 seconds after being signaled to stop.  Process Exit Code 259.  The step failed.

We can infer that the number of targets supported is a function of how long a data load can wait for the KEY lock before the job times out.

CONCLUSION

The number of targets a shared MDW can support could be estimated by the number of sequential data loads that can be done in less than 1 hour.

Example: to make the math simple, let's assume each data load takes about 2 min and all start at the same time, since the inserts are serialized due to KEY lock, the first 30 targets will succeed and the 31st target will time out.

If we have hundreds of SQL Servers, it would be better to have local MDW databases and then have a custom job that gathers the data in a custom central MDW database. I will explore this option in a future post.