SQL Performance Sample Queries

	-----------------------------------------------------------------------
	-- Collection of queries that helps in performance tuning
	--grant permission to user
	GRANT VIEW SERVER STATE TO [user_name]
	------------ based on http://msdn.microsoft.com/en-us/magazine/cc135978.aspx
	---------- Uncover hidden data to optimize application performance -----------------------
	-- * Number of missing indexes
	SELECT
	DatabaseName = DB_NAME(database_id)
	,[Number Indexes Missing] = count(*)
	FROM sys.dm_db_missing_index_details
	GROUP BY DB_NAME(database_id)
	ORDER BY 2 DESC;
	-- * top costly missing indexes
	SELECT TOP 10
	[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
	, avg_user_impact
	, TableName = statement
	, [EqualityUsage] = equality_columns
	, [InequalityUsage] = inequality_columns
	, [Include Cloumns] = included_columns
	, s.unique_compiles
	,s.last_user_seek
	,s.user_seeks
	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
	INNER JOIN sys.dm_db_missing_index_details d
	ON d.index_handle = g.index_handle
	ORDER BY [Total Cost] DESC;
	-- * top costly unused indexes (does not seem to work)
	-- Create required table structure only.
	-- Note: this SQL must be the same as in the Database loop given in the following step.
	SELECT TOP 1
	DatabaseName = DB_NAME()
	,TableName = OBJECT_NAME(s.[object_id])
	,IndexName = i.name
	,user_updates
	,system_updates
	-- Useful fields below:
	--, *
	INTO #TempUnusedIndexes
	FROM sys.dm_db_index_usage_stats s
	INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
	AND s.index_id = i.index_id
	WHERE s.database_id = DB_ID()
	AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
	AND user_seeks = 0
	AND user_scans = 0
	AND user_lookups = 0
	AND s.[object_id] = -999 -- Dummy value to get table structure.
	;
	-- Loop around all the databases on the server.
	EXEC sp_MSForEachDB 'USE [?];
	-- Table already exists.
	INSERT INTO #TempUnusedIndexes
	SELECT TOP 10
	DatabaseName = DB_NAME()
	,TableName = OBJECT_NAME(s.[object_id])
	,IndexName = i.name
	,user_updates
	,system_updates
	FROM sys.dm_db_index_usage_stats s
	INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
	AND s.index_id = i.index_id
	WHERE s.database_id = DB_ID()
	AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
	AND user_seeks = 0
	AND user_scans = 0
	AND user_lookups = 0
	AND i.name IS NOT NULL -- Ignore HEAP indexes.
	ORDER BY user_updates DESC
	;
	'
	-- Select records.
	SELECT TOP 10 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC
	-- Tidy up.
	DROP TABLE #TempUnusedIndexes
	-- * most costly used indexes
	-- Create required table structure only.
	-- Note: this SQL must be the same as in the Database loop given in the following step.
	SELECT TOP 1
	[Maintenance cost] = (user_updates + system_updates)
	,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
	,DatabaseName = DB_NAME()
	,TableName = OBJECT_NAME(s.[object_id])
	,IndexName = i.name
	INTO #TempMaintenanceCost
	FROM sys.dm_db_index_usage_stats s
	INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
	AND s.index_id = i.index_id
	WHERE s.database_id = DB_ID()
	AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
	AND (user_updates + system_updates) > 0 -- Only report on active rows.
	AND s.[object_id] = -999 -- Dummy value to get table structure.
	;
	-- Loop around all the databases on the server.
	EXEC sp_MSForEachDB 'USE [?];
	-- Table already exists.
	INSERT INTO #TempMaintenanceCost
	SELECT TOP 10
	[Maintenance cost] = (user_updates + system_updates)
	,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
	,DatabaseName = DB_NAME()
	,TableName = OBJECT_NAME(s.[object_id])
	,IndexName = i.name
	FROM sys.dm_db_index_usage_stats s
	INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
	AND s.index_id = i.index_id
	WHERE s.database_id = DB_ID()
	AND i.name IS NOT NULL -- Ignore HEAP indexes.
	AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
	AND (user_updates + system_updates) > 0 -- Only report on active rows.
	ORDER BY [Maintenance cost] DESC
	;
	'
	-- Select records.
	SELECT TOP 10 * FROM #TempMaintenanceCost
	ORDER BY [Maintenance cost] DESC
	-- Tidy up.
	DROP TABLE #TempMaintenanceCost
	-- * most fragmented indexes
	-- Create required table structure only.
	-- Note: this SQL must be the same as in the Database loop given in the -- following step.
	SELECT TOP 1
	DatbaseName = DB_NAME()
	,TableName = OBJECT_NAME(s.[object_id])
	,IndexName = i.name
	,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)
	INTO #TempFragmentation
	FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
	INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
	AND s.index_id = i.index_id
	WHERE s.[object_id] = -999 -- Dummy value just to get table structure.
	;
	-- Loop around all the databases on the server.
	EXEC sp_MSForEachDB 'USE [?];
	-- Table already exists.
	INSERT INTO #TempFragmentation
	SELECT TOP 10
	DatbaseName = DB_NAME()
	,TableName = OBJECT_NAME(s.[object_id])
	,IndexName = i.name
	,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)
	FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
	INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
	AND s.index_id = i.index_id
	WHERE s.database_id = DB_ID()
	AND i.name IS NOT NULL -- Ignore HEAP indexes.
	AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
	ORDER BY [Fragmentation %] DESC
	;
	'
	-- Select records.
	SELECT TOP 10 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC
	-- Tidy up.
	DROP TABLE #TempFragmentation
	-- * Find top server waits (CLR_AUTO_EVENT, CHECKPOINT_QUEUE, PAGEIOLATCH_SH, ...)
	SELECT TOP 10
	[Wait type] = wait_type,
	[Wait time (s)] = wait_time_ms / 1000,
	[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0
	/ SUM(wait_time_ms) OVER())
	FROM sys.dm_os_wait_stats
	WHERE wait_type NOT LIKE '%SLEEP%'
	ORDER BY wait_time_ms DESC;
	-- * Find top queries by average time blocked
	SELECT TOP 100
	[Average Time Blocked (s)] = (total_elapsed_time - total_worker_time) / (qs.execution_count * 1000000)
	,[Total Time Blocked (s)] = total_elapsed_time - total_worker_time / 1000000
	,[Execution count] = qs.execution_count
	-- quote text into CSV friendly format
	,[Individual Query] = '"' + SUBSTRING (qt.text,qs.statement_start_offset/2 + 1,
	(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)
	+ '"'
	-- quote text into CSV friendly format
	,[Parent Query] = '"' + qt.text + '"'
	,DatabaseName = DB_NAME(qt.dbid)
	FROM sys.dm_exec_query_stats qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
	ORDER BY [Average Time Blocked (s)] DESC;
	-- * Find most executed queries by number of executions (repetitions)
	SELECT TOP 10
	[Execution count] = execution_count
	,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2 + 1,
	(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)
	,[Parent Query] = qt.text
	,DatabaseName = DB_NAME(qt.dbid)
	FROM sys.dm_exec_query_stats qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
	ORDER BY [Execution count] DESC;
	-- * Find top queries by average CPU
	SELECT TOP 100
	[Average CPU (s)] = total_worker_time / (qs.execution_count * 1000000) --Unit: sec
	,[Total CPU (s)] = total_worker_time / 1000000
	,[Execution count] = qs.execution_count
	,qs.last_execution_time
	-- quote text into CSV friendly format
	,[Individual Query] = '"' + SUBSTRING (qt.text,qs.statement_start_offset/2 + 1,
	(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) + '"'
	-- quote text into CSV friendly format
	,[Parent Query] = '"' + qt.text + '"'
	,DatabaseName = DB_NAME(qt.dbid)
	FROM sys.dm_exec_query_stats qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
	where last_execution_time > '2012-03-10 7:00AM'
	ORDER BY [Average CPU (s)] DESC;
	-- * Find top queries by total CPU
	SELECT TOP 100
	[Average CPU (s)] = total_worker_time / (qs.execution_count * 1000000)
	,[Total CPU (s)] = total_worker_time / 1000000
	,[Execution count] = qs.execution_count
	-- quote text into CSV friendly format
	,[Individual Query] = '"' + SUBSTRING (qt.text,qs.statement_start_offset/2 + 1,
	(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) + '"'
	-- quote text into CSV friendly format
	,[Parent Query] = '"' + qt.text + '"'
	,DatabaseName = DB_NAME(qt.dbid)
	FROM sys.dm_exec_query_stats qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
	ORDER BY [Total CPU (s)] DESC;
	-- * list of all current traces
	select * from sys.traces
	-- * list all categories, classes and columns available to SQL trace
	/*
	Script: CH10 Trace View.sql
	Author: James Rowland-Jones
	Title: Professional SQL Server 2008: Internals and Performance Tuning
	*/
	SELECT cat.name AS category_name
	,ev.name AS event_name
	,col.name AS column_name
	,col.is_filterable AS column_is_filterable
	,col.max_size AS column_max_size
	,col.[type_name] AS column_type_name
	,sub.subclass_name AS subclass_name
	,sub.subclass_value AS subclass_value
	FROM sys.trace_categories cat
	JOIN sys.trace_events ev
	ON cat.category_id = ev.category_id
	JOIN sys.trace_event_bindings bi
	ON bi.trace_event_id = ev.trace_event_id
	JOIN sys.trace_columns col
	ON bi.trace_column_id = col.trace_column_id
	LEFT JOIN sys.trace_subclass_values sub
	ON sub.trace_column_id = bi.trace_column_id
	AND sub.trace_event_id = bi.trace_event_id
	ORDER BY cat.name
	,ev.name
	,col.name
	,sub.subclass_value
	-- * List definition of defatul SQL trace
	/*
	Script: CH10 Trace Definition.sql
	Author: James Rowland-Jones
	Title: Professional SQL Server 2008: Internals and Performance Tuning
	*/
	DECLARE @vTraceID INT;
	SET @vTraceID = 1;
	WITH filter AS
	( SELECT columnid
	,CASE logical_operator
	WHEN 0 THEN 'AND'
	WHEN 1 THEN 'OR'
	ELSE 'err'
	END AS logical_operator
	,CASE comparison_operator
	WHEN 0 THEN ' = '
	WHEN 1 THEN ' <> '
	WHEN 2 THEN ' > '
	WHEN 3 THEN ' < '
	WHEN 4 THEN ' >= '
	WHEN 5 THEN ' <= '
	WHEN 6 THEN ' LIKE '
	WHEN 7 THEN ' NOT LIKE '
	END AS comparison_operator
	,value
	FROM ::fn_trace_getfilterinfo(@vTraceID)
	)
	SELECT cat.name AS CategoryName
	,evt.name AS EventName
	,col.name AS ColumnName
	,STUFF ( ( SELECT ' | ' + child_fil.logical_operator
	+ child_fil.comparison_operator
	+ CAST(child_fil.value AS VARCHAR(MAX)
	)
	FROM filter child_fil
	WHERE parent_fil.columnid = child_fil.columnid
	FOR XML PATH ('')
	),1,1,''
	) AS ColFilters
	FROM fn_trace_geteventinfo(@vTraceID) evi
	JOIN sys.trace_events evt
	ON evi.eventid = evt.trace_event_id
	JOIN sys.trace_categories cat
	ON evt.category_id = cat.category_id
	JOIN sys.trace_columns col
	ON evi.columnid = col.trace_column_id
	LEFT JOIN filter parent_fil
	ON col.trace_column_id = parent_fil.columnid
	GROUP BY cat.name
	,evt.name
	,col.name
	,parent_fil.columnid
	ORDER BY cat.name
	,evt.name
	,col.name

view raw SQL hosted with ❤ by GitHub

ASP.NET Exception: "Session state has created a session id, but cannot save it ..."

Symptom:

• Only happens for new session
• Exception Message:
  A first chance exception of type 'System.Web.HttpException' occurred in System.Web.dll Additional information: Session state has created a session id, but cannot save it because the response was already flushed by the application.

•  Stack Trace: 

[HttpException (0x80004005): Session state has created a session id, but cannot save it because the response was already flushed by the application.]   System.Web.SessionState.SessionIDManager.SaveSessionID(HttpContext context, String id, Boolean& redirected, Boolean& cookieAdded) +3691007   System.Web.SessionState.SessionStateModule.DelayedGetSessionId() +199   System.Web.SessionState.SessionStateModule.ReleaseStateGetSessionID() +25   System.Web.SessionState.SessionStateModule.OnReleaseState(Object source, EventArgs eventArgs) +874   System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +80   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +270 

Cause:

• I was able to narrow down the cause of this problem to setting page's Buffer="false"
• Reasoning would be that without buffering, all content are pushed to client immediately, and somehow, ASP.NET decided to set cookie at a very late stage where it can no longer write to HTTP header to set SessionID cookie

Solution:
Set Buffer to true is not a solution for me, because I will need to flush content to deliver real-time progress information.
Based on some understanding, a solution quickly came up with some Google search. Simply add the following line to Global.asax.cs > Session_Start, the problem is resolved.
string sessionID = Session.SessionID;