Performance Checks


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


SELECT
index_group_handle, index_handle
, avg_total_user_cost, avg_user_impact, user_seeks, user_scans
INTO #PreWorkMissingIndexes
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle


SELECT
sql_handle, plan_handle, total_elapsed_time, total_worker_time
, total_logical_reads, total_logical_writes, total_clr_time
, execution_count, statement_start_offset, statement_end_offset
INTO #PreWorkQuerySnapShot
FROM sys.dm_exec_query_stats


SELECT
[object_name], [counter_name], [instance_name]
, [cntr_value], [cntr_type]
INTO #PreWorkOSSnapShot
FROM sys.dm_os_performance_counters


SELECT
wait_type, waiting_tasks_count
, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
INTO #PreWorkWaitStats
FROM sys.dm_os_wait_stats


WAITFOR DELAY '00:05:00'


SELECT wait_type, waiting_tasks_count, wait_time_ms
, max_wait_time_ms, signal_wait_time_ms
INTO #PostWorkWaitStats
FROM sys.dm_os_wait_stats


SELECT [object_name], [counter_name], [instance_name]
, [cntr_value], [cntr_type]
INTO #PostWorkOSSnapShot
FROM sys.dm_os_performance_counters


SELECT sql_handle, plan_handle, total_elapsed_time, total_worker_time
, total_logical_reads, total_logical_writes, total_clr_time
, execution_count, statement_start_offset, statement_end_offset
INTO #PostWorkQuerySnapShot
FROM sys.dm_exec_query_stats


SELECT index_group_handle, index_handle
, avg_total_user_cost, avg_user_impact, user_seeks, user_scans
INTO #PostWorkMissingIndexes
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle


IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[DeltaWorkQuerySnapShot]') AND type in (N'U'))
begin
SELECT
getdate() currentdatetime,
p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration]
, p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU]
, (p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) -
(p2.total_worker_time - ISNULL(p1.total_worker_time, 0)) AS [Time blocked]
, p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads]
, p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0) AS [Writes]
, p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time]
, p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions]
, SUBSTRING (qt.text,p2.statement_start_offset/2 + 1,
((CASE WHEN p2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE p2.statement_end_offset
END - p2.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
INTO [tempdb].[dbo].[DeltaWorkQuerySnapShot]
FROM #PreWorkQuerySnapShot p1
RIGHT OUTER JOIN
#PostWorkQuerySnapShot p2 ON p2.sql_handle =
ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =
ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =
ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
AND qt.text NOT LIKE '--ThisRoutineIdentifier%'
ORDER BY [Duration] DESC
end
ELSE
begin
    INSERT INTO [tempdb].[dbo].[DeltaWorkQuerySnapShot]
SELECT
getdate() currentdatetime,
p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration]
, p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU]
, (p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) -
(p2.total_worker_time - ISNULL(p1.total_worker_time, 0)) AS [Time blocked]
, p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads]
, p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0) AS [Writes]
, p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time]
, p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions]
, SUBSTRING (qt.text,p2.statement_start_offset/2 + 1,
((CASE WHEN p2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE p2.statement_end_offset
END - p2.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #PreWorkQuerySnapShot p1
RIGHT OUTER JOIN
#PostWorkQuerySnapShot p2 ON p2.sql_handle =
ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =
ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =
ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
AND qt.text NOT LIKE '--ThisRoutineIdentifier%'
ORDER BY [Duration] DESC
end


IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[DeltaWorkWaitStats]') AND type in (N'U'))
begin
SELECT
getdate() currentdatetime,
p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) AS wait_time_ms
, p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0) AS signal_wait_time_ms
, ((p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0))
- (p2.signal_wait_time_ms
- ISNULL(p1.signal_wait_time_ms, 0))) AS RealWait
, p2.wait_type
INTO [tempdb].[dbo].[DeltaWorkWaitStats]
FROM #PreWorkWaitStats p1
RIGHT OUTER JOIN
#PostWorkWaitStats p2 ON p2.wait_type = ISNULL(p1.wait_type, p2.wait_type)
WHERE p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) > 0
AND p2.wait_type NOT LIKE '%SLEEP%'
AND p2.wait_type != 'WAITFOR'
ORDER BY RealWait DESC
end
ELSE
begin
    INSERT INTO [tempdb].[dbo].[DeltaWorkWaitStats]
    SELECT
getdate() currentdatetime,
p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) AS wait_time_ms
, p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0) AS signal_wait_time_ms
, ((p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0))
- (p2.signal_wait_time_ms
- ISNULL(p1.signal_wait_time_ms, 0))) AS RealWait
, p2.wait_type

FROM #PreWorkWaitStats p1
RIGHT OUTER JOIN
#PostWorkWaitStats p2 ON p2.wait_type = ISNULL(p1.wait_type, p2.wait_type)
WHERE p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) > 0
AND p2.wait_type NOT LIKE '%SLEEP%'
AND p2.wait_type != 'WAITFOR'
ORDER BY RealWait DESC
end
IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[DeltaWorkMissingIndexes]') AND type in (N'U'))
begin
SELECT
   getdate() currentdatetime,
ROUND((p2.avg_total_user_cost - ISNULL(p1.avg_total_user_cost, 0))
* (p2.avg_user_impact - ISNULL(p1.avg_user_impact, 0)) *
((p2.user_seeks - ISNULL(p1.user_seeks, 0))
+ (p2.user_scans - ISNULL(p1.user_scans, 0))),0) AS [Total Cost]
, p2.avg_total_user_cost - ISNULL(p1.avg_total_user_cost, 0) AS avg_total_user_cost
, p2.avg_user_impact - ISNULL(p1.avg_user_impact, 0) AS avg_user_impact
, p2.user_seeks - ISNULL(p1.user_seeks, 0) AS user_seeks
, p2.user_scans - ISNULL(p1.user_scans, 0) AS user_scans
, d.statement AS TableName
, d.equality_columns
, d.inequality_columns
, d.included_columns
INTO [tempdb].[dbo].[DeltaWorkMissingIndexes]
FROM #PreWorkMissingIndexes p1
RIGHT OUTER JOIN
#PostWorkMissingIndexes p2 ON p2.index_group_handle =
ISNULL(p1.index_group_handle, p2.index_group_handle)
AND p2.index_handle =
ISNULL(p1.index_handle, p2.index_handle)
INNER JOIN sys.dm_db_missing_index_details d
ON p2.index_handle = d.index_handle
WHERE p2.avg_total_user_cost - ISNULL(p1.avg_total_user_cost, 0) > 0
OR p2.avg_user_impact - ISNULL(p1.avg_user_impact, 0) > 0
OR p2.user_seeks - ISNULL(p1.user_seeks, 0) > 0
OR p2.user_scans - ISNULL(p1.user_scans, 0) > 0
ORDER BY [Total Cost] DESC
end
ELSE
begin
  INSERT INTO [tempdb].[dbo].[DeltaWorkMissingIndexes]
  SELECT
        getdate() currentdatetime,
ROUND((p2.avg_total_user_cost - ISNULL(p1.avg_total_user_cost, 0))
* (p2.avg_user_impact - ISNULL(p1.avg_user_impact, 0)) *
((p2.user_seeks - ISNULL(p1.user_seeks, 0))
+ (p2.user_scans - ISNULL(p1.user_scans, 0))),0) AS [Total Cost]
, p2.avg_total_user_cost - ISNULL(p1.avg_total_user_cost, 0) AS avg_total_user_cost
, p2.avg_user_impact - ISNULL(p1.avg_user_impact, 0) AS avg_user_impact
, p2.user_seeks - ISNULL(p1.user_seeks, 0) AS user_seeks
, p2.user_scans - ISNULL(p1.user_scans, 0) AS user_scans
, d.statement AS TableName
, d.equality_columns
, d.inequality_columns
, d.included_columns

FROM #PreWorkMissingIndexes p1
RIGHT OUTER JOIN
#PostWorkMissingIndexes p2 ON p2.index_group_handle =
ISNULL(p1.index_group_handle, p2.index_group_handle)
AND p2.index_handle =
ISNULL(p1.index_handle, p2.index_handle)
INNER JOIN sys.dm_db_missing_index_details d
ON p2.index_handle = d.index_handle
WHERE p2.avg_total_user_cost - ISNULL(p1.avg_total_user_cost, 0) > 0
OR p2.avg_user_impact - ISNULL(p1.avg_user_impact, 0) > 0
OR p2.user_seeks - ISNULL(p1.user_seeks, 0) > 0
OR p2.user_scans - ISNULL(p1.user_scans, 0) > 0
ORDER BY [Total Cost] DESC
end


IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[DeltaWorkOSSnapShot]') AND type in (N'U'))
begin
SELECT
   getdate() currentdatetime,
p2.object_name, p2.counter_name, p2.instance_name
, ISNULL(p1.cntr_value, 0) AS InitialValue
, p2.cntr_value AS FinalValue
, p2.cntr_value - ISNULL(p1.cntr_value, 0) AS Change
, (p2.cntr_value - ISNULL(p1.cntr_value, 0)) * 100 / p1.cntr_value AS [% Change]
INTO [tempdb].[dbo].[DeltaWorkOSSnapShot]
FROM #PreWorkOSSnapShot p1
RIGHT OUTER JOIN
#PostWorkOSSnapShot p2 ON p2.object_name =
ISNULL(p1.object_name, p2.object_name)
AND p2.counter_name = ISNULL(p1.counter_name, p2.counter_name)
AND p2.instance_name = ISNULL(p1.instance_name, p2.instance_name)
WHERE p2.cntr_value - ISNULL(p1.cntr_value, 0) > 0
AND ISNULL(p1.cntr_value, 0) != 0
ORDER BY [% Change] DESC, Change DESC
end
ELSE
begin
  INSERT INTO [tempdb].[dbo].[DeltaWorkOSSnapShot]
    SELECT
        getdate() currentdatetime,
p2.object_name, p2.counter_name, p2.instance_name
, ISNULL(p1.cntr_value, 0) AS InitialValue
, p2.cntr_value AS FinalValue
, p2.cntr_value - ISNULL(p1.cntr_value, 0) AS Change
, (p2.cntr_value - ISNULL(p1.cntr_value, 0)) * 100 / p1.cntr_value AS [% Change]

FROM #PreWorkOSSnapShot p1
RIGHT OUTER JOIN
#PostWorkOSSnapShot p2 ON p2.object_name =
ISNULL(p1.object_name, p2.object_name)
AND p2.counter_name = ISNULL(p1.counter_name, p2.counter_name)
AND p2.instance_name = ISNULL(p1.instance_name, p2.instance_name)
WHERE p2.cntr_value - ISNULL(p1.cntr_value, 0) > 0
AND ISNULL(p1.cntr_value, 0) != 0
ORDER BY [% Change] DESC, Change DESC
end
go


DROP TABLE #PreWorkQuerySnapShot
DROP TABLE #PostWorkQuerySnapShot
DROP TABLE #PostWorkWaitStats
DROP TABLE #PreWorkWaitStats
DROP TABLE #PreWorkOSSnapShot
DROP TABLE #PostWorkOSSnapShot
DROP TABLE #PreWorkMissingIndexes
DROP TABLE #PostWorkMissingIndexes

Comments

Post a Comment

Popular posts from this blog

SSRS Report Design: Best Practices

Introduction Microsoft® SQL Server™ Reporting Services is a comprehensive reporting tool. In using Reporting Services, you can design and deliver a wide range of reports, from simple tables to online free-form reports with graphical items and interactive links. Reporting Services can render reports for a variety of client-viewing formats, including HTML, PDF, Excel, and image formats. The wide variety of options and choices can be problematic for report authors who must make numerous decisions when choosing how to lay out a report. This paper provides a few guidelines to make these decisions easier and answers common questions.
Read more

SSAS OLAP Design - Best Practices

Introduction This paper outlines the recommended best practices for designing OLAP databases in Microsoft SQL Server 2005 Analysis Services to better meet the functional and performance needs of users. Designing a database for Online Analytical Processing (OLAP) databases is very different from designing a database for Online Transaction Processing (OLTP) and requires a different approach for achieving optimal results. The best practices recommendations are grouped into the following sections: Data Source Design Best Practices Dimension Design Best Practices Cube Design Best Practices Partition Design Best Practices Aggregation Design Best Practices Within each section, these best practices are ordered roughly by priority, where priority is a combination of how important the best practice is and how often you must worry about it. Major topics not covered include best practices for the design of calculations and for server management and operations.  These two topics are s...
Read more

Enable Usage-Based Optimization in SSAS

Usage-Based Optimization is a fantastic feature in SSAS to improve the most frequently used MDX queries of any cube database. This feature is used to design aggregations based on actual user queries using Business Intelligence Development Studio. But when ever you try to design  Usage-Based Optimization  you might have seen all the options  disabled.  This uses the technique of creating aggregations on FREQUENTLY USED queries in the given time interval.  We can enable the options in Usage-Based Optimization wizard. Connect to SSAS using SSMS (Sql Server Management Studio). Right click on the server and select properties as shown below. set CreateQueryLogTable to  true. Set the value of  QueryLogConnectionString  to any database where you wish to store your queries. Set the value of  QueryLogSampling  to the appropriate value for your environment (the default value of 10 means every 10th query will be logged). The lowe...
Read more