Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server Performance Checklist

08 June,2012 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer