SQL Server Performance Checklist

I receive calls – usually with stressed and panicked individuals – who require an immediate SQL Server performance adrenaline hit.

The approach is to resolve the symptoms as fast as possible. Every minute counts – and quite often techniques are used that are not best practise.

 For these situations I have a standard general procedure – which based on findings could lead to other  approaches.

 1) Run a series of performance DMV queries in peak period.

 2) SQL Server Performance Silver Bullets

 a) Statistics out of date

b) AntiVirus scanning database files

c) Assign more memory to SQL Server - max server memory (MB)

d) ScreenSavers – I have seen servers with very CPU intensive screensavers!

e) Missing Indexes

f) High Fragmentation Levels - SQL Index Fragmentation and sys.dm_db_index_physical_stats

g) High Levels of Blocking \Deadlocking

 There are plenty more silver bullets. Remember , what I’m focused is the symptom – there’s plenty of time later for root cause analysis

 3) Server RAM – Use as much as possible. Is it configured correctly?SQL Server memory configuration management

 4) Top Wait Events – What are they?Top 5 SQL Server DMV for Index Analysis

 5) Identify Large Table Scans – can  indexes replace the scans

 6) Check frequent recompilation of execution plans

 7) Poor execution plans – parameter sniffing

 8) Configuration of log and tempdb files

 9) Run performance DMVs again. Keep monitoring and see if problems appears again

Related Posts

SQL Server Performance Killers

SQL Server Performance ,memory pressure and memory usage

SQL Server – Memory: Pages sec, memory pressure and thrashing