A Glance on SQL Server Performance Tunning

Here is a brief on what I use.

1. Tools to use

perfmon, sql profiler, sql trace, SSMS, DBCC and system views, functions and procedures.

Use perfmon to check the overall SQL Server performance status.

Use Profiler and SQL Trace to identify costly queries.
Use Execution Plan to find out poor performance part in an execution.
Use DBCC sqlperf(waitstats)  to find waitings. Use DBCC to find other potential perf issues.

Some tables or functions for SQL2000:

syslockinfo, sysprocesses, sysperinfo, ::fn_virtualfilestats

Here is an example of using ::fn_virtualfilestats to check the I/O statistics on database files.

 select     DbId
    ,FileId
    ,TimeStamp
    ,NumberReads
    ,NumberWrites
    ,BytesRead
    ,BytesWritten
    ,IoStallMS
from ::fn_VirtualFileStats
    (dbid,fileid)
 --IoStallMS<= 20 ms for log
)

2. Identifying costly queries, build the performance baseline.

--check the parsing time and execution time. this is not as important as statistics io
set statistics time on
--check mainly the logical reads. scan count also counts, but less important.
set statistics io on


use profiler to capture a workload and check the CPU, duration, reads and writes

3. Analyze the execution plan on the costly queries

When you check the execution plan, try to find these the first:
(higher percentages, fat bars, scans, etc..)
-- high number of percentage of cost
-- thicker data flow lines
-- bookmark lookup( key lookup for clustered index and RID lookup on non-clustered index are its another name. address it by creating covering index)
-- scan operations
-- hash joining

The execution plan can be extracted directly from DMV if sql server is 2005 or above.

select qs.*,s.text,qp.query_plan from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) s cross apply sys.dm_exec_query_plan(qs.plan_handle) qp where s.text like '%xxxx%'

clicking on the link of query plan to display the query plan in SSMS.

set showplan_xml on, then the execution is to display the execution plan. not really executing the statement

set statistics xml on, then it executes the statement and return the xml execution plan. it's the same as turning on the include actual execution plan in SSMS.

4. Tune the statements and further check their costs, comparing with baseline built in step one.