4 W's of Production DBA

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

Application
Database Design
Microsoft SQL Server
Operating System
Hardware

Now you got answer of Who

2. Resolve Performance bottleneck:

SQL Coding Issues:
Excessive Scanning
Poor use of indexes/missing Indexes
Out of Date Table Statistics
‘Where’ clause not limiting record set enough
Excessive Recompiles
Long blocking durations (multi-connections)

Where:
Limit number of Columns
Avoid <>, OR, and not
Indexed fields
Avoid calculated columns
Avoid functions
Avoid redundant criteria

Joins
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)


Merge Join
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.

Hash Join
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

Hardware Issues:

Typical CPU scaling issues
Symptoms:

Plan compilation and recompilations
Plan reuse < 90% is bad
Parallel queries
Parallel wait type cxpacket > 10% of total waits
High runnable tasks or sos_scheduler_yield waits

Causes:

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:

Symptoms:
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


Causes:

Aggravated by Big IOs such as table scans (bad query plans)
Non covering indexes
Sharing of storage backend – combine OLTP and DW workloads
TempDB bottleneck
Too few spindles, HBA’s

Typical Blocking Issues

Symptoms:

High average row lock or latch waits
Will show up in
sp_configure “blocked process threshold” and Profiler “Blocked process

Report”
Top wait statistics are LCK_x. See sys.dm_os_wait_stats.

Causes:
Higher isolation levels
Index contention
Lock escalation
Slow I/O
Sequence number problem

Typical Memory Issues
Symptoms:

Page life expectancy < 300 secs
SQL Cache hit ratio < 99%
Lazy writes/sec constantly active
Out of memory errors

Causes:

Too many large scans (I/O)
Bad query plans
External (other process) pressure

Temporary Tables

Temporary Tables

# tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When we close this session, the table will be automatically dropped. They are backed by physical disk and are even logged into the transaction log. They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT, and DELETE statements.



CREATE TABLE #TempTable (
Id int,
Desc char(30) )

select name
from tempdb..sysobjects
where name like '#TempTable%'

name
------------------------------------------------------------------------------------
#TempTable_________________________ . . .________00000000001D

Temporary tables act like physical tables in many ways. You can create indexes and statistics on temporary tables. You can also apply Data Definition Language (DDL) statements against temporary tables to add primary keys, DEFAULTs . You can also add and drop columns from temporary tables.


ALTER TABLE #TempTable ADD CONSTRAINT [DF_Desc] DEFAULT ('Test') FOR [Desc]

ALTER TABLE #TempTable add PID int DEFAULT (10) not null

ALTER TABLE #TempTable ADD PRIMARY KEY CLUSTERED ([PID])
GO

CREATE nonclustered index Ix_desc on #TempTable([Desc])



SQL Server stores the object with a some type of unique number appended on the end of the name. It does all this for you automatically. You just have to refer to #TempTable in your code.

If two different users both create a #TempTable table each will have their own copy of it. The exact same code will run properly on both connections. Any temporary table created inside a stored procedure is automatically dropped when the stored procedure finishes executing. If stored procedure P1 creates a temporary table and calls stored procedure P2, then P2 will be able to use the temporary table that P1 created. It's generally considered good coding practice to explicitly drop every temporary table you create. If you are running scripts through SQL Server Management Studio or Query Analyzer the temporary tables are kept until you explicitly drop them or until you close the session.

Local temporary tables are visible only in the current session... ... Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE.

Scopes/Sessions are an interesting thing in sql server.
They follow the rule of inheritance, same as transactions:
A child has all parents' "objects" plus it's own.
So what this means is that you can access a #temp table created
in the parent process from the child of that parent,
but not vice versa.
Any dynamic SQL execution with either exec() or sp_executeSQL is ran in
a child session/scope of the current one.

To check this, pl run below set of queries.

Set1:

exec('select * into #TempTable2 from #TempTable; select * from #TempTable2')
select * from #TempTable2
drop table #TempTable2

Set2:

select * into #TempTable2 from #TempTable
exec('select * from #TempTable2')
select * from #TempTable2
drop table #TempTable2

You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table.

Global Temporary Tables

You can also create global temporary tables. These are named with two pound signs. For example, ##TempTable is a global temporary table. Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.


Table variables are similar to temporary tables except with more flexibility and they always stay in memory.Table variables don't need to be dropped when you are done with them.