The following sites have a very good pretty printers:

http://www.tsqltidy.com

• For whatever reason, sometimes this site “hangs” and doesn’t do anything.

http://www.dpriver.com/pp/sqlformat.htm

• This site actually sells a pretty printer that you can run locally, in addition to the above web based site.

Many of the apps I work with are dynamic query generators with anywhere from 20 to 80 tables in a join. It’s code by a machine, for a machine.

It will turn this incomprehensible string…

SELECT SDSOQS,SDLTTR,SDPRP2,SDADTM,SDQTYT,SDRSDT,SDSHPN,SDCORD,SDUOM4,SDPRP1,SDSRP1,SDSHAN,SDUOM2,SDPDDJ,SDOPDJ,SDKCOO,SDSFXO,SDLOCN,SDDELN,SDDRQJ,SDPSN,SDSO16,SDPOE,SDDRQT,SDAN8,SDFEA,SDPEND,SDSHCCIDLN,SDSOCN,SDSPATTN,SDTHGD,SDRKCO,SDOPTT,SDPMTN,SDCOMM,SDOORN,SDSRP3,SDVR02,SDDOC,SDLNID,SDURAT,SDCRCD,SDASN,SDRSDJ,SDAEXP,SDPRP5,SDPRP3,SDPEFJ,SDUORG,SDOCTO,SDTORG,SDSONE,SDPPDJ,SDDOCO,SDDCTO,SDCNDJ,SDSOBK,SDZON,SDSWMS,SDTHRP,SDUPRC,SDPDTT,SDMOT,SDUOM,SDRCTO,SDCRR,SDPSIG,SDDSC2,SDDGL,SDDCT,SDADDJ,SDRORN,SDDMCT,SDNXTR,SDSO12,SDTRDJ,SDEMCU,SDAAID,SDVR01,SDALLOC,SDLOTN,SDRFRV,SDDSC1,SDITM,SDCARS,SDSRP5,SDLNTY,SDLITM,SDPRIO,SDUNCS,SDRKIT,SDRLLN,SDRLIT,SDOGNO,SDMCU,SDKCO,SDTPC,SDSRP2,SDDVAN,SDKITID,SDANBY,SDNUMB,SDFRTH,SDAITM,SDFRMP,SDSQOR,SDPMTO,SDPRP4,SDPA8,SDSRP4,SDIVD,SDOKCO,SDFRGD,SDFUC,SDFUP,SDSO15 FROM PRODDTA.F4211 WHERE ((((((SDLTTR >= @P0  AND SDLTTR <= @P1 ) AND SDLITM LIKE @P2 ) AND SDNXTR < @P3 ) AND SDSOBK > @P4 ))) UNION SELECT SDSOQS,SDLTTR,SDPRP2,SDADTM,SDQTYT,SDRSDT,SDSHPN,SDCORD,SDUOM4,SDPRP1,SDSRP1,SDSHAN,SDUOM2,SDPDDJ,SDOPDJ,SDKCOO,SDSFXO,SDLOCN,SDDELN,SDDRQJ,SDPSN,SDSO16,SDPOE,SDDRQT,SDAN8,SDFEA,SDPEND,SDSHCCIDLN,SDSOCN,SDSPATTN,SDTHGD,SDRKCO,SDOPTT,SDPMTN,SDCOMM,SDOORN,SDSRP3,SDVR02,SDDOC,SDLNID,SDURAT,SDCRCD,SDASN,SDRSDJ,SDAEXP,SDPRP5,SDPRP3,SDPEFJ,SDUORG,SDOCTO,SDTORG,SDSONE,SDPPDJ,SDDOCO,SDDCTO,SDCNDJ,SDSOBK,SDZON,SDSWMS,SDTHRP,SDUPRC,SDPDTT,SDMOT,SDUOM,SDRCTO,SDCRR,SDPSIG,SDDSC2,SDDGL,SDDCT,SDADDJ,SDRORN,SDDMCT,SDNXTR,SDSO12,SDTRDJ,SDEMCU,SDAAID,SDVR01,SDALLOC,SDLOTN,SDRFRV,SDDSC1,SDITM,SDCARS,SDSRP5,SDLNTY,SDLITM,SDPRIO,SDUNCS,SDRKIT,SDRLLN,SDRLIT,SDOGNO,SDMCU,SDKCO,SDTPC,SDSRP2,SDDVAN,SDKITID,SDANBY,SDNUMB,SDFRTH,SDAITM,SDFRMP,SDSQOR,SDPMTO,SDPRP4,SDPA8,SDSRP4,SDIVD,SDOKCO,SDFRGD,SDFUC,SDFUP,SDSO15 FROM PRODDTA.F42119 WHERE ((((((SDLTTR >= @P5  AND SDLTTR <= @P6 ) AND SDLITM LIKE @P7 ) AND SDNXTR < @P8 ) AND SDSOBK > @P9 ))) ORDER BY 54 ASC  , 55 ASC  , 16 ASC  , 100 ASC  , 40 ASC

Into something you can actually read and work with:

SELECT   SDSOQS,
         SDLTTR,
         SDPRP2,
         SDADTM,
...
         SDFRGD,
         SDFUC,
         SDFUP,
         SDSO15
FROM     PRODDTA.F4211
WHERE    ((((((SDLTTR >= @P0
               AND SDLTTR <= @P1)
              AND SDLITM LIKE @P2)
             AND SDNXTR < @P3)
            AND SDSOBK > @P4)))
UNION
SELECT   SDSOQS,
         SDLTTR,
         SDPRP2,
         SDADTM,
         SDQTYT,
         SDRSDT,
...
         SDDCT,
         SDFUP,
         SDSO15
FROM     PRODDTA.F42119
WHERE    ((((((SDLTTR >= @P5
               AND SDLTTR <= @P6)
              AND SDLITM LIKE @P7)
             AND SDNXTR < @P8)
            AND SDSOBK > @P9)))
ORDER BY 54 ASC, 55 ASC, 16 ASC, 100 ASC, 40 ASC;

An enterprise grade SAS drive is spec’d at 3ms to 8ms response times.

Guys at Microsoft will tell you they want 10ms or less for everything. My goal is 10ms for the transaction log (LDF), and 20ms for the data files (MDF/NDF).

What happens when your are beyond 10ms on the t-log?

• The log buffer fills up,
• SQL Server sees the disk is being thrashed and takes it’s foot off the pedal because it can’t buffer anymore IO requests to the log,
• At this point, locks start getting held much longer,
• Severe blocking and deadlocks start to occur,
• Applications start to time out and go into retries, etc…
• The system stops being usable.

Errors like this start showing up in the ERRORLOG:

SQL Server has encountered x occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [Drive:\MSSQL\MSSQL.1\MSSQL\Data\xyz.mdf] in database [database].  The OS file handle is 0x00000000.  The offset of the latest long I/O is: 0x00000000000000

For more information on this:

http://blogs.msdn.com/b/sqlsakthi/archive/2011/02/09/troubleshooting-sql-server-i-o-requests-taking-longer-than-15-seconds-i-o-stalls-amp-disk-latency.aspx

The curious thing, CPU goes way down because no work is being done. This makes most sysadmins scratch their heads because they don’t realize where the bottleneck is.

Two ways of getting numbers:

PerfMon: Real Time

• Avg. Disk Sec/Read
• Avg. Disk Sec/Write
• Avg. Disk Sec/Transfer

Reading the PerfMon Output: .010 = 10ms, .050 = 50ms, 1.000 = 1 second, etc…

Here’s and example of 6,132ms (6 seconds) response times on a write operation (really bad).

DMV to see Virtual File Stats: Averages since system was started

SELECT
–virtual file latency
vReadLatency
= CASE WHEN num_of_reads = 0
THEN 0 ELSE (io_stall_read_ms/num_of_reads) END
, vWriteLatency
= CASE WHEN num_of_writes = 0
THEN 0 ELSE (io_stall_write_ms/num_of_writes) END
, vLatency
= CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE (io_stall/(num_of_reads + num_of_writes)) END
–avg bytes per IOP
, BytesperRead
= CASE WHEN num_of_reads = 0
THEN 0 ELSE (num_of_bytes_read/num_of_reads) END
, BytesperWrite
= CASE WHEN num_of_writes = 0
THEN 0 ELSE (num_of_bytes_written/num_of_writes) END
, BytesperTransfer
= CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE ((num_of_bytes_read+num_of_bytes_written)/(num_of_reads + num_of_writes)) END

, LEFT(mf.physical_name,2) as Drive
, DB_NAME(vfs.database_id) as DB
–, mf.name AS FileName
, vfs.*
, mf.physical_name
FROM sys.dm_io_virtual_file_stats(NULL,NULL) as vfs
JOIN sys.master_files as mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
–WHERE mf.type_desc = ‘LOG’ — log files
–WHERE DB_NAME(vfs.database_id) IN (‘tpcc’,’tpcc2′)
ORDER BY vLatency DESC
— ORDER BY vReadLatency DESC
— ORDER BY vWriteLatency DESC

The following is a sample output showing that the Read Latency is 6ms and the Write Latency is 255ms with the Average Latency (R+W) is 145ms.

vReadLatency         vWriteLatency        vLatency             Drive DB         physical_name
-------------------- -------------------- -------------------- ----- ---------- -----------------------------------------------------
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb12.ndf
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb11.ndf
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb10.ndf
6                    254                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb5.ndf
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb4.ndf
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb3.ndf
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb2.ndf
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb13.ndf
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb14.ndf

(10 row(s) affected)

The following chart from Microsoft (http://technet.microsoft.com/en-us/library/cc966412.aspx) shows what they expect:

From Jimmy May @ Microsoft.

http://blogs.msdn.com/b/jimmymay/archive/2008/10/30/drum-roll-please-the-debut-of-the-sql-dmv-all-stars-dream-team.aspx

• Expensive Queries (CPU, reads, frequency, etc.)
• Wait Stats
• Virtual File Stats (including calculations for virtual file latency)
• Plan Cache
• Blocking (real-time)

Script Download Here:

DMV_All-Stars_v20150114

–DMV_All-Stars.sql
–Jimmy May 
–A.C.E. Performance Team
–jimmymay@microsoft.com
–aspiringgeek@live.com
–http://blogs.msdn.com/jimmymay
–Table of Contents
–1. expensive queries
–2. wait stats
–3. virtual file stats (& virtual file latency)
–4. plan cache interrogation
–5. real-time blockers
–<<<<<<<<<<—————————————————————–>>>>>>>>>>–
–Weasel Clause: This script is provided “AS IS” with no warranties, and confers no rights.
—  Use of included script samples are subject to the terms specified at
—  http://www.microsoft.com/info/cpyright.htm
–<<<<<<<<<<—————————————————————–>>>>>>>>>>–

–1. expensive queries
–text *and* statement
–usage: modify WHERE & ORDER BY clauses to suit circumstances
SELECT –TOP 25
— the following four columns are NULL for ad hoc and prepared batches
DB_Name(qp.dbid) as dbname , qp.dbid , qp.objectid , qp.number
–, qp.query_plan –the query plan can be *very* useful; enable if desired
, qt.text
, SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END
– qs.statement_start_offset)/2) + 1) as statement_text
, qs.creation_time , qs.last_execution_time , qs.execution_count
, qs.total_worker_time    / qs.execution_count as avg_worker_time
, qs.total_physical_reads / qs.execution_count as avg_physical_reads
, qs.total_logical_reads  / qs.execution_count as avg_logical_reads
, qs.total_logical_writes / qs.execution_count as avg_logical_writes
, qs.total_elapsed_time   / qs.execution_count as avg_elapsed_time
, qs.total_clr_time       / qs.execution_count as avg_clr_time
, qs.total_worker_time , qs.last_worker_time , qs.min_worker_time , qs.max_worker_time
, qs.total_physical_reads , qs.last_physical_reads , qs.min_physical_reads , qs.max_physical_reads
, qs.total_logical_reads , qs.last_logical_reads , qs.min_logical_reads , qs.max_logical_reads
, qs.total_logical_writes , qs.last_logical_writes , qs.min_logical_writes , qs.max_logical_writes
, qs.total_elapsed_time , qs.last_elapsed_time , qs.min_elapsed_time , qs.max_elapsed_time
, qs.total_clr_time , qs.last_clr_time , qs.min_clr_time , qs.max_clr_time
–, qs.sql_handle , qs.statement_start_offset , qs.statement_end_offset
, qs.plan_generation_num
, qp.encrypted
, qp.query_plan
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
–WHERE last_execution_time >= ‘20130264’
–ORDER BY qs.execution_count      DESC  –Frequency
ORDER BY qs.total_worker_time    DESC  –CPU
–ORDER BY qs.total_elapsed_time   DESC  –Durn
–ORDER BY qs.total_logical_reads  DESC  –Reads
–ORDER BY qs.total_logical_writes DESC  –Writes
–ORDER BY qs.total_physical_reads DESC  –PhysicalReads
–ORDER BY avg_worker_time         DESC  –AvgCPU
–ORDER BY avg_elapsed_time        DESC  –AvgDurn
–ORDER BY avg_logical_reads       DESC  –AvgReads
–ORDER BY avg_logical_writes      DESC  –AvgWrites
–ORDER BY avg_physical_reads      DESC  –AvgPhysicalReads

–sample WHERE clauses
–WHERE last_execution_time > ‘20070507 15:00′
–WHERE execution_count = 1
—  WHERE SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
—    ((CASE statement_end_offset
—        WHEN -1 THEN DATALENGTH(qt.text)
—        ELSE qs.statement_end_offset END
—            – qs.statement_start_offset)/2) + 1)
—      LIKE ‘%MyText%’

–<<<<<<<<<<—————————————————————–>>>>>>>>>>–
–2. wait stats
–DBCC sqlperf(waitstats)
–DBCC sqlperf(‘sys.dm_os_wait_stats’,CLEAR)  –re-initialize waitstats
SELECT * , (wait_time_ms – signal_wait_time_ms) as resource_wait_time_ms
, signal_wait_time_per_wait
= CASE WHEN waiting_tasks_count = 0
THEN 0 ELSE (signal_wait_time_ms/waiting_tasks_count) END
, resource_wait_time_per_wait
= CASE WHEN waiting_tasks_count = 0
THEN 0 ELSE ((wait_time_ms – signal_wait_time_ms)/waiting_tasks_count) END
FROM sys.dm_os_wait_stats
ORDER BY resource_wait_time_ms DESC
–ORDER BY wait_time_ms DESC
–ORDER BY signal_wait_time_ms DESC
–ORDER BY waiting_tasks_count DESC
–ORDER BY max_wait_time_ms DESC

–adapted from Paul Randal
–Wait statistics, or please tell me where it hurts
–http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts
;WITH Waits AS
(SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms – signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
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_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’KSOURCE_WAKEUP’,                  N’LAZYWRITER_SLEEP’,          N’LOGMGR_QUEUE’,                    N’ONDEMAND_TASK_QUEUE’,
N’PWAIT_ALL_COMPONENTS_INITIALIZED’,                              N’QDS_PERSIST_TASK_MAIN_LOOP_SLEEP’,
N’QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP’,                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’SLEEP_TEMPDBSTARTUP’,             N’SNI_HTTP_ACCEPT’,
N’SP_SERVER_DIAGNOSTICS_SLEEP’,     N’SQLTRACE_BUFFER_FLUSH’,     N’SQLTRACE_INCREMENTAL_FLUSH_SLEEP’,
N’SQLTRACE_WAIT_ENTRIES’,           N’WAIT_FOR_RESULTS’,          N’WAITFOR’,                         N’WAITFOR_TASKSHUTDOWN’,
N’WAIT_XTP_HOST_WAIT’,              N’WAIT_XTP_OFFLINE_CKPT_NEW_LOG’,
N’WAIT_XTP_CKPT_CLOSE’,             N’XE_DISPATCHER_JOIN’,        N’XE_DISPATCHER_WAIT’,              N’XE_TIMER_EVENT’
)
)
SELECT TOP 10
W1.wait_type AS WaitType,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_Sec,
CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_Sec,
CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_Sec,
W1.WaitCount AS WaitCount,
CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_Sec,
CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_Sec,
CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_Sec
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) – W1.Percentage < 99.9; — percentage threshold

GO

–<<<<<<<<<<—————————————————————–>>>>>>>>>>–
–3. virtual file stats
SELECT
–virtual file latency
vReadLatency
= CASE WHEN num_of_reads = 0
THEN 0 ELSE (io_stall_read_ms/num_of_reads) END
, vWriteLatency
= CASE WHEN num_of_writes = 0
THEN 0 ELSE (io_stall_write_ms/num_of_writes) END
, vLatency
= CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE (io_stall/(num_of_reads + num_of_writes)) END
–avg bytes per IOP
, BytesperRead
= CASE WHEN num_of_reads = 0
THEN 0 ELSE (num_of_bytes_read/num_of_reads) END
, BytesperWrite
= CASE WHEN num_of_writes = 0
THEN 0 ELSE (num_of_bytes_written/num_of_writes) END
, BytesperTransfer
= CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE ((num_of_bytes_read+num_of_bytes_written)/(num_of_reads + num_of_writes)) END

, LEFT(mf.physical_name,2) as Drive
, DB_NAME(vfs.database_id) as DB
–, mf.name AS FileName
, vfs.*
, mf.physical_name
FROM sys.dm_io_virtual_file_stats(NULL,NULL) as vfs
JOIN sys.master_files as mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
–WHERE mf.type_desc = ‘LOG’ — log files
–WHERE DB_NAME(vfs.database_id) IN (‘tpcc’,’tpcc2′)
ORDER BY vLatency DESC
— ORDER BY vReadLatency DESC
— ORDER BY vWriteLatency DESC
–<<<<<<<<<<—————————————————————–>>>>>>>>>>–
–4. plan cache interrogation
— note: sys.dm_exec_cached_plans is diminutive version of syscacheobjects
— no dbid, setopts
— we want reusable code, absence of ad hoc SQL
— we want relatively few rows with low usecounts
–2000
SELECT cacheobjtype , objtype , usecounts , pagesused , dbid , sql
FROM master.dbo.syscacheobjects
WHERE cacheobjtype = ‘Compiled Plan’
ORDER BY usecounts DESC
–ORDER BY sql
–2005
SELECT c.cacheobjtype , c.objtype , c.usecounts , c.size_in_bytes , t.dbid , t.text
FROM sys.dm_exec_cached_plans as c
CROSS APPLY sys.dm_exec_sql_text(plan_handle) as t
WHERE c.cacheobjtype = ‘Compiled Plan’
ORDER BY c.usecounts DESC
–ORDER BY t.text

–<<<<<<<<<<—————————————————————–>>>>>>>>>>–
–5. real-time blockers
–Report Blocker and Waiter SQL Statements
–http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/trans/sql05vb044.mspx?mfr=true
— SQLCAT BPT
SELECT
t1.resource_type as lock_type
, db_name(resource_database_id) as DB
, t1.resource_associated_entity_id as blkd_obj
, t1.request_mode as lock_req          — lock requested
, t1.request_session_id as waiter_sid– spid of waiter
, t2.wait_duration_ms as waittime
, (SELECT text FROM sys.dm_exec_requests as r  — get sql for waiter
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
WHERE r.session_id = t1.request_session_id) as waiter_batch
, (SELECT SUBSTRING(qt.text , r.statement_start_offset/2
, (CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE r.statement_end_offset END – r.statement_start_offset)/2)
FROM sys.dm_exec_requests as r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as qt
WHERE r.session_id = t1.request_session_id) as waiter_stmt    — this is the statement executing right now
, t2.blocking_session_id as blocker_sid — spid of blocker
, (SELECT text FROM sys.sysprocesses as p       — get sql for blocker
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
WHERE p.spid = t2.blocking_session_id) as blocker_stmt
FROM sys.dm_tran_locks as t1
JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address

Always trying to figure out “if” and “how much” my indexes are being used.

This posting has the information:

http://www.mssqltips.com/sqlservertip/1239/how-to-get-index-usage-information-in-sql-server/

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

Conversely, which indexes are being UPDATED but NEVER used, not mirrored, and is not disabled already (since the system has been up):

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
         inner join sys.objects as o 
            on i.object_id = o.object_id     
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 and
user_updates > 100 and
is_disabled <> 1 and
(USER_SEEKS + USER_SCANS + USER_LOOKUPS) = 0
 AND o.is_ms_shipped = 0

When I apply my custom indexes or missing indexes, I put _fmcb_ in the name of the index. This way, I can run the below query and extract all of them out for a separate build script.

http://social.technet.microsoft.com/wiki/contents/articles/19598.how-to-generate-index-creation-scripts-for-all-tables-in-a-database-using-t-sql.aspx

SELECT  ' CREATE ' +
 CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  +
 I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +
 I.name  + ' ON '  +
 Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +
 KeyColumns + ' )  ' +
 ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +
 ISNULL(' WHERE  '+I.Filter_definition,'') + ' WITH ( MAXDOP=0, ' +
 CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ','  +
 'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ','  +
 -- default value
 'SORT_IN_TEMPDB = OFF '  + ','  +
 CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ','  +
 CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ','  +
 -- default value
 ' DROP_EXISTING = ON '  + ','  +
 -- default value
 ' ONLINE = OFF '  + ','  +
 CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ','  +
 CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END  + ' ) ON [' +
 DS.name + ' ] '  [CreateIndexScript]
 FROM sys.indexes I
 JOIN sys.tables T ON T.Object_id = I.Object_id
 JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid
 JOIN (SELECT * FROM (
 SELECT IC2.object_id , IC2.index_id ,
 STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END
 FROM sys.index_columns IC1
 JOIN Sys.columns C
 ON C.object_id = IC1.object_id
 AND C.column_id = IC1.column_id
 AND IC1.is_included_column = 0
 WHERE IC1.object_id = IC2.object_id
 AND IC1.index_id = IC2.index_id
 GROUP BY IC1.object_id,C.name,index_id
 ORDER BY MAX(IC1.key_ordinal)
 FOR XML PATH('')), 1, 2, '') KeyColumns
 FROM sys.index_columns IC2
 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
 GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4
 ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id
 JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id
 JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id
 JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id
 LEFT JOIN (SELECT * FROM (
 SELECT IC2.object_id , IC2.index_id ,
 STUFF((SELECT ' , ' + C.name
 FROM sys.index_columns IC1
 JOIN Sys.columns C
 ON C.object_id = IC1.object_id
 AND C.column_id = IC1.column_id
 AND IC1.is_included_column = 1
 WHERE IC1.object_id = IC2.object_id
 AND IC1.index_id = IC2.index_id
 GROUP BY IC1.object_id,C.name,index_id
 FOR XML PATH('')), 1, 2, '') IncludedColumns
 FROM sys.index_columns IC2
 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
 GROUP BY IC2.object_id ,IC2.index_id) tmp1
 WHERE IncludedColumns IS NOT NULL ) tmp2
 ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id
 WHERE i.name like '%fmcb%'
 --I.is_primary_key = 0 AND I.is_unique_constraint = 0
 --AND I.Object_id = object_id('Person.Address') --Comment for all tables
 -- AND I.name = 'IX_Address_PostalCode' --comment for all indexes

Problems happen when you have fragmentation in the t-log. You can find this by running DBCC LOGINFO. This usually happens when autogrow kicks in and the database is set at the defaults… which are very small.

In the ERRORLOG, you start to see this:

Database x has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

More info here:

http://blogs.msdn.com/b/saponsqlserver/archive/2012/02/22/too-many-virtual-log-files-vlfs-can-cause-slow-database-recovery.aspx

http://support.microsoft.com/kb/2653893

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

To get rid of it (from the above link):

If you have more than 50, I would recommend fixing it and adjusting your autogrowth so that it doesn’t occur as fequently. To get rid of all of the execessive VLFs, follow these easy steps to shrink off the fragmented chunk and add a new, clean chunk to your transaction log:

1. Wait for an inactive time of day (ideally, it would be best to put the database into single user mode first) and then clear all transaction log activity through a regular transaction log backup. If you’re using the simple recovery model then you don’t need to do a log backup… Instead, just clear the transaction log by running a checkpoint.

 BACKUP LOG databasename TO devicename

2. Shrink the log to as small a size as possible (truncateonly)

DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)

NOTE: if you don’t know the logical filename of your transaction log use sp_helpfile to list all of your database files.

3. Alter the database to modify the transaction log file to the appropriate size – in one step

ALTER DATABASE databasename
MODIFY FILE
(
NAME = transactionloglogicalfilename
, SIZE = newtotalsize
)

Quick way to find free space in SQL Server data files.

This helps find skewed and lumpy data distribution.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82359

select
 a.FILEID,
 [FILE_SIZE_MB] =
 convert(decimal(12,2),round(a.size/128.000,2)),
 [SPACE_USED_MB] =
 convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
 [FREE_SPACE_MB] =
 convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
 NAME = left(a.NAME,15),
 FILENAME = left(a.FILENAME,30)
 from
 dbo.sysfiles a