Measurement of sproc query performance:
===========================================
## Tools to use:
===============
– Include actual query plan in ssms
– Look for NCI/CI Index scan
– Look for Key Lookups
– Use Index usage stats to figure out which index is being used..
– Make covering indexes based to reduce Key Lookups..(Select or where clauses go into covering)
– Careful with this as covering too many columns could increase index size (Index prop > Fragmentation > Pages)
– Run 2 versions of the DB for comparison. (old and new)
## Comparing improvements:
===============
– Compare query plans – run both queries as a batch and compare query plans.
dbold.qry
dbnew.qry
– Compare Duration/Cpu/Read/write by – Run SQL Profiler to compare both query runs (
run dbold.qry
run dbnew.qry
See diff rows in profiler and their Duration/Cpu/Read/write
## Perf Improvement Tips:
===============
1. Some sprocs have multiple params and hence a complex where condition, simplify with If clauses..
Where (@p1 is null OR tbl1.P1 = @p1) AND (@p2 is null OR tbl1.P2 = @p2) etc.. (index not utilised)
Simplify the sproc with If clauses:
If(@P1 is Not null)
<query> where tbl1.P1 = @p1
If(@P2 is Not null)
<query> where tbl1.P2 = @p2
This way the QO (Query Optimiser) can know which index to utilize.
— Using DTA (DB engine tuning wizard)
– Save the sql script(s) in a .sql file (say workload_singleQuery.sql or workload_multipleQueries.sql)
– The sql should have “Use <DBToOptimise>” as first statement
– Run DTA > Sart New Session
– General tab > Workload – File: = workload_singleQuery.sql (or multi query file)
– Database dropdown – select <DBToOptimise>
– Select db and tables to tune: Tick <DBToOptimise> (or select specific tables)
– Tuning Options tab > tick (1) Non-Clustered Indexes (2) No partitioning (3) Keep all existing PDS
Useful tools/read:
=====================
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2012/05/10/query-cost-relative-to-the-batch-lt-gt-query-cost-relative-to-batch.aspx
http://www.red-gate.com/products/dba/sql-monitor/resources/articles/monitor-sql-server-io