Geek Sync | How to Catch SQL Server Performance Troublemakers

SQL Server applications can be huge and complex. Finding performance problems from such a huge application is often very difficult. There are many different performance troublemakers in SQL Server, and as a DBA, it is your responsibility to catch them. Once you catch performance troublemakers, you can decide how to resolve them.

Join IDERA and Pinal Dave on Wednesday, Jan 10 at 11 AM CT as he demonstrates simple tricks to identify SQL Server Performance Troublemakers. Pinal’s presentation will be focused on finding CPU intensive queries, I/O optimization with index tuning, identifying and resolving performance bottlenecks with wait stats, and more. At the end of the session, attendees will have the understanding and the scripts to identify their own performance troublemakers.

You can view Pinal's slides here. You can view the Geek Sync recording here.

You can view Pinal's scripts below.

31-Wait Stats (1).sql
-- ------------------------------------------------------
-- Tip 33: Wait Statistics
-- (c) http://blog.sqlauthority.com
-- Additional resources - http://bit.ly/WaitStats 
-- ------------------------------------------------------
/*
Script to Clear Wait Types
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
GO
*/
-- SQL Wait Stats and Queies
-- (C) Pinal Dave (http://blog.sqlauthority.com) 2016
-- Send query result to pinal@sqlauthority.com for quick feedback on resource bottlenecks
SELECT	wait_type AS Wait_Type, 
wait_time_ms / 1000.0 AS Wait_Time_Seconds,
waiting_tasks_count AS Waiting_Tasks_Count,
-- CAST((wait_time_ms / 1000.0)/waiting_tasks_count AS decimal(10,4)) AS AVG_Waiting_Tasks_Count,
wait_time_ms * 100.0 / SUM(wait_time_ms) OVER() AS Percentage_WaitTime
--,waiting_tasks_count * 100.0 / SUM(waiting_tasks_count) OVER() AS Percentage_Count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN 
(N'BROKER_EVENTHANDLER',
N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP',
N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER',
N'CHECKPOINT_QUEUE',
N'CHKPT',
N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT',
N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT',
N'DBMIRROR_DBM_MUTEX',
N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE',
N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL',
N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC',
N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT',
N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL',
N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT',
N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK',
N'HADR_WORK_QUEUE',
N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE',
N'MEMORY_ALLOCATION_EXT',
N'ONDEMAND_TASK_QUEUE',
N'PREEMPTIVE_HADR_LEASE_MECHANISM',
N'PREEMPTIVE_OS_FLUSHFILEBUFFERS',
N'PREEMPTIVE_OS_AUTHENTICATIONOPS',
N'PREEMPTIVE_OS_AUTHORIZATIONOPS',
N'PREEMPTIVE_OS_COMOPS',
N'PREEMPTIVE_OS_CREATEFILE',
N'PREEMPTIVE_OS_CRYPTOPS',
N'PREEMPTIVE_OS_DEVICEOPS',
N'PREEMPTIVE_OS_FILEOPS',
N'PREEMPTIVE_OS_GENERICOPS',
N'PREEMPTIVE_OS_LIBRARYOPS',
N'PREEMPTIVE_OS_PIPEOPS',
N'PREEMPTIVE_OS_QUERYREGISTRY',
N'PREEMPTIVE_OS_VERIFYTRUST',
N'PREEMPTIVE_OS_WAITFORSINGLEOBJECT',
N'PREEMPTIVE_OS_WRITEFILEGATHER',
N'PREEMPTIVE_SP_SERVER_DIAGNOSTICS',
N'PREEMPTIVE_XE_GETTARGETSTATE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
N'QDS_ASYNC_QUEUE',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_SHUTDOWN_QUEUE',
N'REDO_THREAD_PENDING_WORK',
N'REQUEST_FOR_DEADLOCK_SEARCH',
N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK',
N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', 
N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', 
N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', 
N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', 
N'SLEEP_TASK',
N'SP_SERVER_DIAGNOSTICS_SLEEP',
N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES',
N'UCS_SESSION_REGISTRATION',
N'WAIT_FOR_RESULTS',
N'WAIT_XTP_CKPT_CLOSE',
N'WAIT_XTP_HOST_WAIT',
N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_RECOVERY',
N'WAITFOR',
N'WAITFOR_TASKSHUTDOWN',
N'XE_TIMER_EVENT',
N'XE_DISPATCHER_WAIT'
) AND wait_time_ms >= 1
ORDER BY Wait_Time_Seconds DESC
-- ORDER BY Waiting_Tasks_Count DESC

21-Cardinality Estimator (1).sql
-- ------------------------------------------------------
-- Tip 23: Cardinality Estimation
-- (c) http://blog.sqlauthority.com
-- ------------------------------------------------------

USE AdventureWorks2014
GO

-- CTRL + M
-- Old Cardinality Estimation 
-- SQL Server 2012
ALTER DATABASE AdventureWorks2014 
SET COMPATIBILITY_LEVEL = 110
GO
EXEC [dbo].[uspGetManagerEmployees] 44
GO

-- NEW Cardinality Estimation 
-- SQL Server 2014
ALTER DATABASE AdventureWorks2014 
SET COMPATIBILITY_LEVEL = 120
GO
EXEC [dbo].[uspGetManagerEmployees] 44
GO


-- NEW Cardinality Estimation 
-- SQL Server 2016
ALTER DATABASE AdventureWorks2014 
SET COMPATIBILITY_LEVEL = 130
GO
EXEC [dbo].[uspGetManagerEmployees] 44
GO

11-Statistics s (1).sql
-- ------------------------------------------------------
-- Tip 13: Statistics Off/On
-- (c) http://blog.sqlauthority.com
-- ------------------------------------------------------

------ Statistics ON
USE [master]
GO
ALTER DATABASE StatusON SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT
GO
ALTER DATABASE StatusON SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT
GO
------ Statistics OFF
USE [master]
GO
ALTER DATABASE StatusOFF SET AUTO_CREATE_STATISTICS OFF WITH NO_WAIT
GO
ALTER DATABASE StatusOFF SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT
GO
--