BEGIN -- Do not lock anything, and do not get held up by any locks. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- What SQL Statements Are Currently Running? SELECT [Spid] = session_Id , ecid , [Database] = DB_NAME(sp.dbid) , [User] = nt_username , [Status] = er.status , [Wait] = wait_type , [Individual Query] = SUBSTRING (qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2) ,[Parent Query] = qt.text , Program = program_name , Hostname , nt_domain , start_time FROM sys.dm_exec_requests er INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt WHERE session_Id > 50 -- Ignore system spids. AND session_Id NOT IN (@@SPID) -- Ignore this current statement. ORDER BY 1, 2END

Whats Blocking?

Shows what queries currently blocking/being blocked

BEGIN -- Do not lock anything, and do not get held up by any locks. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- If there are blocked processes... IF EXISTS(SELECT 1 FROM sys.sysprocesses WHERE blocked != 0) BEGIN -- Identify the root-blocking spid(s) SELECT distinct t1.spid AS [Root blocking spids] , t1.[loginame] AS [Owner] --, master.dbo.dba_GetSQLForSpid(t1.spid) AS -- 'SQL Text' , t1.[cpu] , t1.[physical_io] , DatabaseName = DB_NAME(t1.[dbid]) , t1.[program_name] , t1.[hostname] , t1.[status] , t1.[cmd] , t1.[blocked] , t1.[ecid] FROM sys.sysprocesses t1, sys.sysprocesses t2 WHERE t1.spid = t2.blocked AND t1.ecid = t2.ecid AND t1.blocked = 0 ORDER BY t1.spid, t1.ecid -- Identify the spids being blocked. SELECT t2.spid AS 'Blocked spid' , t2.blocked AS 'Blocked By' , t2.[loginame] AS [Owner] --, master.dbo.dba_GetSQLForSpid(t2.spid) AS -- 'SQL Text' , t2.[cpu] , t2.[physical_io] , DatabaseName = DB_NAME(t2.[dbid]) , t2.[program_name] , t2.[hostname] , t2.[status] , t2.[cmd] , t2.ecid FROM sys.sysprocesses t1, sys.sysprocesses t2 WHERE t1.spid = t2.blocked AND t1.ecid = t2.ecid ORDER BY t2.blocked, t2.spid, t2.ecid END ELSE -- No blocked processes. PRINT 'No processes blocked.' ENDGO

BAU Tasks

Disk Space Issues?

Check freespace overall, and drill into details

--Database MountPoint Details SELECT DISTINCT d.name AS DBName ,                volume_mount_point AS [Volume_MountPoint],        ((total_bytes/1024)/1024)/1024 AS TotalSpaceGB ,        ((total_bytes-available_bytes)/1024)/1024/1024 AS UsedSpaceGB ,        ((available_bytes/1024)/1024)/1024 AS AvailableSpaceGB ,        (((available_bytes/1024)/1024)/1024*100)        /(((total_bytes/1024)/1024)/1024) AS [Free%]        FROM sys.master_files AS f        CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)        INNER JOIN sys.databases d ON f.database_id = d.database_idWHERE d.NAME = DB_NAME(DB_ID())
--Database Files By MountPoint DetailsSELECT (SELECT cob FROM dbo.appdate) [Date], d.name AS DBName , fg.name AS FileGroup,           f.name AS FileName ,        (CONVERT(BIGINT,df.size)*8/1024)/1024 as FileSizeGB,        ((CONVERT(BIGINT,df.size)*8/1024) - (FILEPROPERTY(df.name, 'SpaceUsed') * 8/1024))/1024 As FileFreeSpaceGB,          volume_mount_point [Volume_MountPoint],        ((total_bytes / 1024 ) / 1024 ) / 1024 AS TotalSpaceGB ,        ((available_bytes / 1024 ) / 1024 ) / 1024 AS AvailableSpaceGB ,        (((available_bytes / 1024 ) / 1024 ) / 1024 * 100 )        /(((total_bytes / 1024 ) / 1024 ) / 1024 ) AS [Free%]FROM sys.master_files AS f        CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)        INNER JOIN sys.databases d ON f.database_id = d.database_id        INNER JOIN sys.database_files df ON f.name = df.name        INNER JOIN sys.filegroups fg ON df.data_space_id = fg.data_space_idWHERE d.NAME = DB_NAME(DB_ID())ORDER BY 7,5 DESC
--Database Objects By Filegroup DetailsSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT   (SELECT cob FROM dbo.appdate) [Date], FILEGROUP_NAME(a.data_space_id) AS FileGroupName , (SELECT SCHEMA_NAME(schema_id) FROM sys.objects z WHERE z.object_id = p.object_id) [SchemaName],        OBJECT_NAME(p.object_id) AS TableName ,        i.name AS ClusteredIndexName ,        (a.total_pages/128)/1024 AS TotalTableSizeGB ,        (a.used_pages/128)/1024 AS UsedSizeGB ,        (a.data_pages/128)/1024 AS DataSizeGBFROM sys.allocation_units AS aINNER JOIN sys.partitions AS p ON a.container_id = CASE                                                      WHEN a.type IN (1, 3 )                                                      THEN p.hobt_id                                                     ELSE p.partition_id                                                      END                                                     LEFT JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id--WHERE FILEGROUP_NAME(a.data_space_id) = 'Data'ORDER BY 6 DESC

Shrink DB Files

Incremental shrink of db files

--Shrink files by 1GB DECLARE @FileName VARCHAR(50) = '', @ShrinkSizeMb INT = 1024, @Debug BIT = 0
DECLARE @usedspace INT = 0 , @currentspace INT = 0, @freespace INT = 0, @targetspace INT = 0 SELECT @usedspace = size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0, @currentspace = size/128.0 FROM sys.database_files WHERE name = @FileName SET @freespace = @currentspace - @usedspace PRINT 'Current Space: ' + CONVERT(VARCHAR,@currentspace) PRINT 'Used Space: ' + CONVERT(VARCHAR,@usedspace) PRINT 'Free Space: ' + CONVERT(VARCHAR,@freespace) WHILE @freespace > @ShrinkSizeMb BEGIN SET @targetspace = @currentspace - @ShrinkSizeMb PRINT 'Shrink ' + @FileName + ' by ' + CONVERT(VARCHAR,@ShrinkSizeMb/1024) + 'GB' DECLARE @xSQL VARCHAR(MAX) SET @xSQL = 'DBCC SHRINKFILE ('''+ @FileName + ''',' + CONVERT(VARCHAR,@targetspace) + ')' PRINT @xSQL IF @debug = 0 BEGIN PRINT 'Current Space: ' + CONVERT(VARCHAR,@currentspace) PRINT 'Used Space: ' + CONVERT(VARCHAR,@usedspace) PRINT 'Free Space: ' + CONVERT(VARCHAR,@freespace) PRINT 'Shrink - Started: @FileName=' + @FileName + ', @ShrinkSizeMb=' + CONVERT(VARCHAR,@ShrinkSizeMb) + ', @TargetSpace=' + CONVERT(VARCHAR,@targetspace) EXEC (@xSQL) PRINT 'Shrink - Started: @FileName=' + @FileName + ', @ShrinkSizeMb=' + CONVERT(VARCHAR,@ShrinkSizeMb) + ', @TargetSpace=' + CONVERT(VARCHAR,@targetspace) END SELECT @usedspace = size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0, @currentspace = size/128.0 FROM sys.database_files WHERE name = @FileName SET @freespace = @currentspace - @usedspace END PRINT @FileName + ' Shrink completed Successfully'

PERFORMANCE TUNING

Whats Running (Detailed)

SELECT r.session_id, r.request_id as session_request_id, --r.connection_id, r.status, s.host_name, c.client_net_address, CASE WHEN s.login_name = s.original_login_name THEN s.login_name ELSE s.login_name + ' (' + s.original_login_name + ')' END as login_name, s.program_name, db_name(r.database_id) as database_name, r.command, --r.sql_handle, --r.statement_start_offset, --r.statement_end_offset, --r.plan_handle, substring(st.text,r.statement_start_offset/2 ,(CASE WHEN r.statement_end_offset = -1 THEN len(convert(nvarchar(max), st.text)) * 2 ELSE r.statement_end_offset END - r.statement_start_offset)/2) as statement, st.text as query_text, qp.query_plan as xml_query_plan, r.start_time, r.total_elapsed_time as total_elapsed_time_ms, r.cpu_time as cpu_time_ms, r.wait_type as current_wait_type, r.wait_resource as current_wait_resource, r.wait_time as current_wait_time_ms, r.last_wait_type, r.blocking_session_id, r.reads, r.writes, r.logical_reads, r.row_count, r.prev_error, r.nest_level, r.granted_query_memory, r.executing_managed_code, r.transaction_id, r.open_transaction_count, r.open_resultset_count, r.scheduler_id --r.user_id, --r.percent_complete, --r.estimated_completion_time, -- This is an internal, MS only. --r.task_address, --r.text_size, --r.language, --r.date_format, --r.date_first, --r.quoted_identifier, --r.arithabort, --r.ansi_null_dflt_on, --r.ansi_defaults, --r.ansi_warnings, --r.ansi_padding, --r.ansi_nulls, --r.concat_null_yields_null, --CASE r.transaction_isolation_level -- WHEN 0 THEN 'Unspecified' -- WHEN 1 THEN 'ReadUncomitted' -- WHEN 2 THEN 'ReadCommitted' -- WHEN 3 THEN 'Repeatable' -- WHEN 4 THEN 'Serializable' -- WHEN 5 THEN 'Snapshot' -- ELSE CAST(r.transaction_isolation_level AS VARCHAR(32)) --END as transaction_isolation_level_name, --r.lock_timeout, --r.deadlock_priority, --r.context_info,FROM sys.dm_exec_requests(NOLOCK) r LEFT OUTER JOIN sys.dm_exec_sessions(NOLOCK) s on s.session_id = r.session_id LEFT OUTER JOIN sys.dm_exec_connections(NOLOCK) c on c.connection_id = r.connection_id OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qpWHERE --r.status NOT IN ('background','sleeping') AND c.session_id <> @SPID/*--KEY WAIT RESOURCESELECT o.name AS TableName, i.name AS IndexName,SCHEMA_NAME(o.schema_id) AS SchemaNameFROM sys.partitions p JOIN sys.objects o ON p.OBJECT_ID = o.OBJECT_ID JOIN sys.indexes i ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id WHERE p.hobt_id = 72057594040811520--PAGE WAIT RESOURCESELECT DB_NAME(68)DBCC traceon (3604)GODBCC page (68, 1, 492478) --Database_id,file_id,page_id --OBJECT WAIT RESOURCESELECT DB_NAME(5)SELECT OBJECT_NAME(981083477) --OBJECT: 5:981083477:20 --OBJECT: 5:981083477:0 --OBJECT: 5:981083477:26 SELECT * FROM sys.dm_exec_query_memory_grants*/

3 Ways I like to Tune SQL

1) Query Tuning

Look at Actual Execution Plan (if possible otherwise estimated)-- Is there a missing index?

Look at where clause, joins.. (Is there an index on those columns)

Look at selected column.. (can these be included on index to create covering)

Look at tables involved.. sp_help.. understand table/index structure(PK/Clustered/HEAP?)

Look at sys.indexes (anything similar to tweak.. any hypothetical indexes? --BAD)

Define potential indexes

Test by both running forcing with hint (old vs new) and check Overall Batch %

Compare both using statistics io

2) WorkLoad Tuning

DTA - Plan Cache Analysis

DTA - Query from SSMS

3.1) DMV Tuning - System DMVs

Wait Stats - dm_os_wait_stats (Clear Waitstats - DBCC SQLPERF(Waitstats,Clear)

Perfmon Stats - dm_os_performance_counters (Tuning Insert Statements - How many page splits?)

Index Stats - dm_db_index_usage_stats

3.2) Query DMVs

sys.dm_exec_requests

Missing Indexes

--Missing indexes in Current Database Select user_seeks * avg_total_user_cost * (avg_user_impact*0.01) [IndexAdvantage], migs.last_user_seek, mid.statement, mid.equality_columns, mid.inequality_columns, mid.included_columns, migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact FROM sys.dm_db_missing_index_group_stats as migs INNER JOIN sys.dm_db_missing_index_groups as mig ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details as mid ON mig.index_handle = mid.index_handle WHERE mid.database_id = DB_ID() ORDER BY 1 DESC

3.2) Index DMVs

sys.dm_db_index_usages_stats

sys.dm_io_virtual_file_stats

Useful Tips

CHECKPOINT - Clears the dirty pages out of the buffer cache (move them out to the IO subsystem)

Clear out 2 main areas of sql server memory reservation

a) DBCC FreeProcCache - Clear Procedure Cache and remove all plans (whole instance)

b) DBCC FlushProcinDB(dbid) - Drop specific plan

c) DBCC DropCleanBuffers - Clear out the data buffers **Don't do this in Production**

SET STATISTICS TIME ON -Elapsed Time and IO Time

SET STATISTICS IO ON -Scans, Logical Reads (Memory), Physical Reads (IO Disk)

SET SHOWPLAN / EXECUTION PLANS -

Wait Stats

Show me Wait Stats

Cumulative Waits on instance to Date Top X Waits for server instance This is since the last restart or having statistics manually cleared --Clearing Stats on Server DBCC SQLPERF('sys.dm_os_wait_stats',clear) --Clean Cache DBCC FREEPROCCACHE --Clean Buffers DBCC DROPCLEANBUFFERS

WITH WaitStatsAS ( SELECT [wait_type], [waiting_tasks_count] ,[wait_time_ms]/1000. AS [wait_time_secs] ,100.*[wait_time_ms]/SUM([wait_time_ms])OVER() AS [pct] ,ROW_NUMBER()OVER(ORDER BY [wait_time_ms] DESC) AS [Rnum] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN ('BROKER_EVENTHANDLER', 'BROKER_RECIEVE_WAITFOR', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH', 'BROKER_TRANSMITTER' ,'CHECKPOINT_QUEUE', 'CHKPT', 'CLR_AUTO_EVENT','CLR_MANUAL_EVENT','CLR_SEMAPHORE', 'DISPATCHER_QUEUE_SEMAPHORE' ,'FT_IFTS_SCHEDULER_IDLE_WAIT', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP', 'LAZYWRITER_SLEEP' ,'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'RESOURCE_QUEUE', 'SERVER_IDLE_CHECK' ,'SLEEP', 'SLEEP_BPOOL_FLUSH', 'SLEEP_DBSTARTUP', 'SLEEP_DCOMSTARTUP', 'SLEEPMSDBSTARTUP' ,'SLEEP_SYSTEMTASK', 'SLEEP_TASK', 'SLEEP_TEMPDBSTARTUP', 'SNI_HTTP_ACCEPT', 'SQLTRACE_BUFFER_FLUSH' ,'WAITFOR_TASKSHUTDOWN' ,'XE_DISPATCHER_JOIN', 'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT' -----Newly Background Wait types added in 2012 ,'DIRTY_PAGE_POLL', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'SP_SERVER_DIAGNOSTICS_SLEEP' ,'SLEEP_MASTERDBREADY', 'FFT_RECOVERY', 'PWAIT_ALL_COMPONENTS_INITIALIZED' ) AND [wait_type] NOT LIKE 'PREEMPTIVE_%' )SELECT WS_1.waiting_tasks_count ,WS_1.wait_type AS [wait_type] ,CAST(WS_1.[wait_time_secs] AS DECIMAL(12,2)) AS [wait_type_secs] ,CAST(WS_1.pct AS DECIMAL(12,2)) AS [Pct] ,CAST(SUM(WS_2.pct) AS DECIMAL(12,2)) AS [RunPct]FROM WaitStats AS WS_1 INNER JOIN WaitStats AS WS_2 ON WS_2.Rnum <= WS_1.RnumGROUP BY WS_1.Rnum ,WS_1.waiting_tasks_count ,WS_1.wait_type ,WS_1.wait_time_secs ,WS_1.pctHAVING SUM(WS_2.pct) - WS_1.pct < 95 OPTION (RECOMPILE); --pct threshold

Glen Berry Version: Isolate top waits for server instance since last restart or statistics clear

WITH Waits AS(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rnFROM sys.dm_os_wait_statsWHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'))SELECT W1.wait_type, CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,CAST(W1.pct AS DECIMAL(12, 2)) AS pct,CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pctFROM Waits AS W1INNER JOIN Waits AS W2ON W2.rn <= W1.rnGROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pctHAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

Wait Stats - Explanation

ASYNC_IO_COMPLETION

Occurs when a task is waiting for I/Os to finish

IO_COMPLETION

Occurs while waiting for I/O operations to complete.

This wait type generally represents non-data page I/Os.

Data page I/O completion waits appear as PAGEIOLATCH_* waits

PAGEIOLATCH_SH

Occurs when a task is waiting on a latch for a buffer that is in an I/O request.

The latch request is in Shared mode.

Long waits may indicate problems with the disk subsystem.

PAGEIOLATCH_EX

Occurs when a task is waiting on a latch for a buffer that is in an I/O request.

The latch request is in Exclusive mode.

Long waits may indicate problems with the disk subsystem.

WRITELOG

Occurs while waiting for a log flush to complete.

Common operations that cause log flushes are checkpoints and transaction commits.

PAGELATCH_EX

Occurs when a task is waiting on a latch for a buffer that is not in an I/O request.

The latch request is in Exclusive mode.

BACKUPIO

Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data.

Execution Plans - Red Flags

-Table Scans (Bad)

-Clustered Index Scan (Bad)

-Lookups

-Spools (Something getting buffered out to TempDB)

-Parallelism -CXPACKET wait symptom of underlying issue

-Range Scan can be good

-Estimated estimated plan vs Actual estimated plan (Differences mean very bad statistical info / Parameter Sniffing)

-Physical Reads (why not in buffer) - Lack of Memory

-Missing Indexes / Don't have Statistics / Auto Generated Statistics

-Hash Joins / Merge Joins - do bunch of sorts in TempDB

-Implicit Data Conversions

-Cursors: http://bit.ly/AB-cursors --> Set Based / Windows functions fastest - Ok for Running Totals (better than self joins)

Can improve by using LOCAL FAST_FORWARD

-Page Life Expectancy (relative)

-NOT IN / LEFT OUTER JOIN is null --> NOT EXISTS is better -> EXCEPT new functionality (adds distinct sort operator can add performance issue)

-IN / EXISTS / APPLY / JOIN /CTE --> EXISTS uses alot less IO as can sort circuit.

-UNION ALL faster as UNION does distinct sort

7) Optimising for SELECT instead of DML

-Make sure you have a full / representable workload when adding an index

-Cache data needed for report.

-Cost to achieve Benefit

-Keys and Constraints added when doing DML which do not appear at all when doing SELECTs (Triggers included)

8) Unwanted Recompiles

IN memory -> No -> Load MetaData -> Compile - > Optimize - > Execute IN memory -> Yes -> Execute Causes

-OPTION RECOMPILE -SP_RECOMPILE

-Dont use at procedure level (only use at statement level)

-Plan aged out of memory

-Interleaved DDL and DML

-Big Changes since last execution

--Schema changes to objects in underlying code

--New/Updated index statistics

--sp_configure will flush plan cache (true for max server memory)

- myths 9) Kitchen Sink Procedure -Many optional parameters to satify a variety of search conditions

-Dynamic SQL is often the best route here

-Especially if optimize for ad hoc workloads is enabled

-(Stored a stub/fingerprint in plan cached) -sp_executesql ATsql, N'ATCustomerID INT, ATOrderDate DATE', ATCustomerID, ATOrderDate -sp_executesql

vs EXEC (sp_ promotes bteeer plan re-use, encourages strongly typed parameters instead of concat string)

-can use recompile in dynamic sql

10) Comma-Delimited parameters

-String splitting expensive (even using CLR)

-Table-valued parameters are typically a better approach

11) Implicit Conversions

-SQL Server has to do alot of extra work /scans when conversion operations are assumed by SQL programmer

-Happens al lthe time with data types you'd think wouldnt need it.. (between date types and character types)

-http://bit.ly/15bDRRA -http://bit.ly/13io1f (Type precedence call also have impact)

Ian Stirks Column Mismatch Utility

- http://www.sqlservercentral.com/articles/Administration/65138/ Jonathan Kehayias plan cache analyzer

- http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx

Jonathan Kehayias index scan study

- http://www.sqlperformance.com/2013/04/t-sql-queries/implicit-conversion-costs

12) Tempoarary Structures

Temp Table Temp Variable Stored in? Tempdb

Tempdb Statistics Yes No

(1 row) Indexes/Keys Yes 1UK/PK only Truncate Yes No Recompiles Yes No Parallelism Yes No Metadata Overhead Low Lowest Lock Overhead Normal Lowest 13) UDFs

-Most scalar and multi-statement table-valued UDFs can be evaluated for each row -Move the function to be inline. -Be very cautious using (make sure not being invoked for every row in the result set)

Development Snippets

Error Handling

Try Catch              BEGIN TRYBEGIN TRANSACTION        --Do WorkEND TRANSACTION
BEGIN CATCH    IF (XACT_STATE()) IN (1,-1)         ROLLBACK TRAN
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE() ;    DECLARE @ErrorSeverity INT = ERROR_SEVERITY() ;    DECLARE @ErrorState INT = ERROR_STATE() ;        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) ;
END CATCH

No Sleep MOUSE MOVER

               Opt("GuiOnEventMode",1)Opt("TrayOnEventMode",1)Opt("TrayMenuMode",1); no default tray menuitems#NoTrayIcon#include
Global $infolabelGlobal $sleepTimeGlobal $pause = 1
;---------------Tray event values----------------
Global $TRAY_EVENT_SHOWICON = -3Global $TRAY_EVENT_HIDEICON = -4Global $TRAY_EVENT_FLASHICON = -5Global $TRAY_EVENT_NOFLASHICON = -6Global $TRAY_EVENT_PRIMARYDOWN = -7Global $TRAY_EVENT_PRIMARYUP = -8Global $TRAY_EVENT_SECONDARYDOWN = -9Global $TRAY_EVENT_SECONDARYUP = -10Global $TRAY_EVENT_MOUSEOVER = -11Global $TRAY_EVENT_MOUSEOUT = -12Global $TRAY_EVENT_PRIMARYDOUBLE = -13Global $TRAY_EVENT_SECONDARYDOUBLE = -14    ;---------------Build UI----------------TraySetClick(16)
$exititem = TrayCreateItem("Exit")TrayItemSetOnEvent(-1,"ExitEvent")
TraySetIcon(@AutoItExe)
TraySetState()TraySetToolTip("Why work when you can be awesome!")
;---------------Set initial variables----------------
$lastpos = MouseGetPos()$PreviousSetting = RegRead("HKEY_CURRENT_USER\SOFTWARE\KillSaver\", "Timeout")If $PreviousSetting <> "" Then    $sleepTime = $PreviousSettingElse    $sleepTime = 1000 * 60 * 1;1 minutes = 1000 * 60 * 1EndIf

;---------------Main loop----------------
While 1    $currentpos = MouseGetPos()   ;Check if current position is the same as the last position, if true move the mouse to the right.    if $lastpos[0] = $currentpos[0] AND $lastpos[1] = $currentpos[1] then        MouseMove($currentpos[0] + 50, $currentpos[1], 4)               ;Check to see if the mouse has actually moved, move the mouse to the left if not.        $newpos = MouseGetPos()        if $lastpos[0] = $newpos[0] AND $lastpos[1] = $newpos[1] then            MouseMove($currentpos[0] - 50, $currentpos[1], 4)        endif               ;Move back to original position so the mouse is in the same place they left it.        MouseMove($currentpos[0], $currentpos[1], 4)    EndIF   ;MsgBox(48,"",$sleepTime)    Sleep($sleepTime)    $lastpos = $currentposWEnd
Exit
;---------------Functions----------------
;Function for exiting the appFunc ExitEvent()    ExitEndFunc
;Close last GUI windowFunc OnClose()    GUIDelete()EndFunc

WITH xmlnamespaces(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT DEQP.query_plan,

DEST.text,

RO.X.value('@PhysicalOp', 'nvarchar(50)') as PhysicalOp,

RO.X.value('@LogicalOp', 'nvarchar(50)') as LogicalOp,

RO.X.value('@EstimatedTotalSubtreeCost', 'float') as EstimatedTotalSubtreeCost

FROM sys.dm_exec_cached_plans AS DECP

CROSS APPLY sys.dm_exec_query_plan(DECP.plan_handle) AS DEQP

CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) as DEST

CROSS APPLY DEQP.query_plan.nodes('//RelOp[Hash/ProbeResidual]') as RO(X);

Service Broker

--Check the availability of the queues

SET TRANSACTION isolation level READ uncommitted

SELECT t1.NAME AS [Service_Name],

t3.NAME AS [Schema_Name],

t2.NAME AS [Queue_Name],

CASE

WHEN t4.state IS NULL THEN 'Not available'

ELSE t4.state

END AS [Queue_State],

CASE

WHEN t4.tasks_waiting IS NULL THEN '--'

ELSE CONVERT(VARCHAR, t4.tasks_waiting)

END AS tasks_waiting,

CASE

WHEN t4.last_activated_time IS NULL THEN '--'

ELSE CONVERT(VARCHAR, t4.last_activated_time)

END AS last_activated_time,

CASE

WHEN t4.last_empty_rowset_time IS NULL THEN '--'

ELSE CONVERT(VARCHAR, t4.last_empty_rowset_time)

END AS last_empty_rowset_time,

(SELECT Count(*)

FROM sys.transmission_queue t6

WHERE ( t6.from_service_name = t1.NAME )) AS [Tran_Message_Count]

FROM sys.services t1

INNER JOIN sys.service_queues t2

ON ( t1.service_queue_id = t2.object_id )

INNER JOIN sys.schemas t3

ON ( t2.schema_id = t3.schema_id )

LEFT OUTER JOIN sys.dm_broker_queue_monitors t4

ON ( t2.object_id = t4.queue_id

AND t4.database_id = Db_id() )

INNER JOIN sys.databases t5

ON ( t5.database_id = Db_id() )

SQL Agent

SELECT [sJOB].[job_id] AS [JobID], [sJOB].[name] AS [JobName], CASE WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL ELSE Cast(Cast([sJOBH].[run_date] AS CHAR(8)) + ' ' + Stuff( Stuff(RIGHT('000000' + Cast([sJOBH].[run_time] AS VARCHAR(6 )), 6), 3, 0 , ':'), 6, 0, ':') AS DATETIME) END AS [LastRunDateTime], CASE [sJOBH].[run_status] WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'Running' -- In Progress END AS [LastRunStatus], Stuff(Stuff(RIGHT('000000' + Cast([sJOBH].[run_duration] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)], [sJOBH].[message] AS [LastRunStatusMessage], CASE [sJOBSCH].[nextrundate] WHEN 0 THEN NULL ELSE Cast(Cast([sJOBSCH].[nextrundate] AS CHAR(8)) + ' ' + Stuff( Stuff(RIGHT('000000' + Cast([sJOBSCH].[nextruntime] AS VARCHAR(6)) , 6), 3, 0, ':'), 6, 0, ':') AS DATETIME) END AS [NextRunDateTime]FROM [msdb].[dbo].[sysjobs] AS [sJOB] LEFT JOIN (SELECT [job_id], Min([next_run_date]) AS [NextRunDate], Min([next_run_time]) AS [NextRunTime] FROM [msdb].[dbo].[sysjobschedules] GROUP BY [job_id]) AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id] LEFT JOIN (SELECT [job_id], [run_date], [run_time], [run_status], [run_duration], [message], Row_number() OVER ( partition BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC ) AS RowNumber FROM [msdb].[dbo].[sysjobhistory] WHERE [step_id] = 0) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id] AND [sJOBH].[rownumber] = 1WHERE 1 = 1 AND CASE [sJOBH].[run_status] WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'Running' -- In Progress END = 'Failed'ORDER BY [jobname]

SELECT [sJOB].[job_id] AS [JobID],

[sJOB].[name] AS [JobName],

CASE

WHEN [sJOBH].[run_date] IS NULL

OR [sJOBH].[run_time] IS NULL THEN NULL

ELSE Cast(Cast([sJOBH].[run_date] AS CHAR(8)) + ' '

+ Stuff( Stuff(RIGHT('000000' + Cast([sJOBH].[run_time] AS

VARCHAR(6

)), 6)

, 3,

0, ':'), 6, 0, ':') AS DATETIME)

END AS [LastRunDateTime],

CASE [sJOBH].[run_status]

WHEN 0 THEN 'Failed'

WHEN 1 THEN 'Succeeded'

WHEN 2 THEN 'Retry'

WHEN 3 THEN 'Canceled'

WHEN 4 THEN 'Running' -- In Progress

END AS [LastRunStatus],

Stuff(Stuff(RIGHT('000000'

+ Cast([sJOBH].[run_duration] AS VARCHAR(6)), 6), 3, 0,

':'),

6, 0, ':') AS [LastRunDuration (HH:MM:SS)],

[sJOBH].[message] AS [LastRunStatusMessage],

CASE [sJOBSCH].[nextrundate]

WHEN 0 THEN NULL

ELSE Cast(Cast([sJOBSCH].[nextrundate] AS CHAR(8))

+ ' '

+ Stuff( Stuff(RIGHT('000000' + Cast([sJOBSCH].[nextruntime]

AS

VARCHAR(6))

, 6),

3, 0, ':'), 6, 0, ':') AS DATETIME)

END AS [NextRunDateTime]

FROM [msdb].[dbo].[sysjobs] AS [sJOB]

LEFT JOIN (SELECT [job_id],

Min([next_run_date]) AS [NextRunDate],

Min([next_run_time]) AS [NextRunTime]

FROM [msdb].[dbo].[sysjobschedules]

GROUP BY [job_id]) AS [sJOBSCH]

ON [sJOB].[job_id] = [sJOBSCH].[job_id]

LEFT JOIN (SELECT [job_id],

[run_date],

[run_time],

[run_status],

[run_duration],

[message],

Row_number()

OVER (

partition BY [job_id]

ORDER BY [run_date] DESC, [run_time] DESC ) AS

RowNumber

FROM [msdb].[dbo].[sysjobhistory]

WHERE [step_id] = 0) AS [sJOBH]

ON [sJOB].[job_id] = [sJOBH].[job_id]

AND [sJOBH].[rownumber] = 1

WHERE 1 = 1

AND CASE [sJOBH].[run_status]

WHEN 0 THEN 'Failed'

WHEN 1 THEN 'Succeeded'

WHEN 2 THEN 'Retry'

WHEN 3 THEN 'Canceled'

WHEN 4 THEN 'Running' -- In Progress

END = 'Failed'

ORDER BY [jobname]

Disk/FILE Latency

SELECT DB_NAME(vfs.database_id) AS database_name, vfs.database_id, io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_latency, io_stall_write_ms / NULLIF(num_of_writes,0) AS avg_write_latency, io_stall_write_ms / NULLIF(num_of_reads + num_of_writes,0) as avg_total_latency, num_of_bytes_read / NULLIF(num_of_reads,0) AS avg_bytes_per_read , num_of_bytes_written / NULLIF(num_of_writes,0) AS avg_bytes_per_write , vfs.io_stall , vfs.num_of_reads , vfs.num_of_bytes_read , vfs.io_stall_read_ms , vfs.num_of_writes , vfs.num_of_writes , vfs.num_of_bytes_written , vfs.io_stall_write_ms , size_on_disk_bytes /1024 /1024. AS size_on_disk_mbytes , physical_nameFROM sys.dm_io_virtual_file_stats(NULL,NULL) vfs JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_idORDER BY avg_total_latency desc