Why My server is hung?
A SQL Report outage. Why did this happen?
Who is responsible for outage?
What steps you will do to resolve it ASAP?
What steps you need to do so that it should not happen again?
When issue will resolved?
Most of the production DBAs daily face one of these 4 W's Why,What,Who,When.
Below is a simple guide to tackle these 4 W's :
1. Identify Factors Affecting Performance
Microsoft SQL Server
Now you got answer of Who
2. Resolve Performance bottleneck:
SQL Coding Issues:
Poor use of indexes/missing Indexes
Out of Date Table Statistics
‘Where’ clause not limiting record set enough
Long blocking durations (multi-connections)
Limit number of Columns
Avoid <>, OR, and not
Avoid calculated columns
Avoid redundant criteria
Avoid Join Hints
Always Join on indexed columns
Use selectivity from table with fewest rows
Avoid Bookmark lookups
Nested Loop Join
Common When One Table is VerySmall (Candidate outer table) and Other is Large and Indexed on Join Columns (Candidate inner table)
Method for Joins with Sorted Inputs, Usually Inputs are Sequenced on Join Key
Common When Both Tables are Indexed on join key ,Common When Join Sequence Needs to be Preserved on Output.
Join of Disproportionately Sized Tables
Does Not Require Tables to beProcessed in Join Column Sequence
Does Not Produce Ordered Result
Highly Applicable to Ad-hoc Queries
Indexes Can’t be Anticipated
Typical CPU scaling issues
Plan compilation and recompilations
Plan reuse < 90% is bad
Parallel wait type cxpacket > 10% of total waits
High runnable tasks or sos_scheduler_yield waits
Queries not parameterized
Inefficient Query plan
Not enough stored procedures
MAXDOP is not set to 1
Statistics not updated
Table scan, range scan
SET option changes within SP
Typical IO Scaling Issues:
High average disk seconds per read (> 10 msec) and write (> 2 msec)
for spindle based devices
Top 2 values for wait stats are one of - ASYNCH_IO_COMPLETION,
IO_COMPLETION, LOGMGR, WRITELOG, PAGEIOLATCH_x
Aggravated by Big IOs such as table scans (bad query plans)
Non covering indexes
Sharing of storage backend – combine OLTP and DW workloads
Too few spindles, HBA’s
Typical Blocking Issues
High average row lock or latch waits
Will show up in
sp_configure “blocked process threshold” and Profiler “Blocked process
Top wait statistics are LCK_x. See sys.dm_os_wait_stats.
Higher isolation levels
Sequence number problem
Typical Memory Issues
Page life expectancy < 300 secs
SQL Cache hit ratio < 99%
Lazy writes/sec constantly active
Out of memory errors
Too many large scans (I/O)
Bad query plans
External (other process) pressure