SQL Performance

Its very usual that tomorrow you come to office and face a performance issue for your live application.

No doubt, sometime we get confused on what to check and what not to check….

Generally we just take on everything and start doing our analysis, which is not recommended.

eg. if there are missing indexes, there is no point to perform index maintenance again and again and reaching nowhere.

I have been searching for long time on various Performance Improvement Stages….and now I got it….Lets discuss….

It’s highly suggested to opt below three stages, step by step, if you ever face any Performance Issue.

Stage 1

–  Check for Blocking   [sp_who2]

–  Check for deadlock  [enable trace to read sql error log]

–  DBCC opentran [check for any long running transaction]

–  Check for Index Fragmentation [index_physical_stats, ALTER INDEX etc.]

–  Run Activity Monitor [Monitor CPU memory etc.]

–  DBCC [sqlperf, check for logspace and plan taking log backups]

–  DBCC [free some system and procedure cache]

Stage 2….. Go for this stage if you are through with Stage 1 and still issue persists.

–   Performance Monitor [cpu, memory, network, disk i/o, buffer cache etc.]

–  Update the Statistics [sp_updatestats]

–  Check for Missing Indexes.

–  Check actual execution plan for long running queries and have cost analysis. [select queries only]

–  Analyse select queries in query analyser for logical and physical reads [set statistics io on]

–  Capture Top High CPU queries and clear the SPIDs, if possible.

–  DBCC commands [check for memory usage]

–  DBCC Loginfo [Reduce the number of virtual logs file if its more than 50]

–  Check if you are doing recompilation during weekly maintenance [sp_recompile]

–  Check for database options like auto_updatestats and Auto shrink etc.

Stage 3 Finally these are the options to cover everything.

– SQL Profiler [select events and filter database, host etc.]

–  Tempdb [add new data files based on number of cpu]

– Move Heavily used indexes to different filegroups.

– Check for fill factor settings of your indexes.

– Check for MaxDOP setting [sp_configure]

– Virtual Memory / Paging configuration

– VAS (Virtual Address Space), 3G / PAE switches and AWE configuration [depends upon sql and os edition]

– Lock Pages in Memory [check if you really need to enable it]

– DBCC Checkdb [for integrity check in the database]

To ensure SQL server is performing well there are certain areas we should give attention as priority after analysing the performance bottlenecks.

These are :
Basic Performance Investigation
– General Concepts
– Fill Factor settings
– Index Fragmentation
– Max Degree of Paralism
– Filegroups and Data Files
– Tempdb configuration.
– Blocking and Deadlocks.
– Finding Missing Indexes, Better to use Covering Indexes.
– Apart from this I have also tried to cover below important topics.
– Basic Commands (DBCC & DMVs)
– Estimating Size of a Table

Basic Commands (SPROC, DBCC & DMVs)

Here are few SPs, Commands/views a SQL DBA must remember as they are used on day to day activities.

Important System Stored Procedures

– SP_attach_db & SP_detach_db
SP_addserver & SP_dropserver
– SP_Configure
– SP_Change_users_login
– SP_Changedbowner
– SP_clean_db_free_space
– SP_dbcmptlevel
– SP_databases
– SP_dboption
– SP_grantlogin & SP_grantdbaccess
– SP_Help & SP_Helptext
SP_Helpdb
– SP_Indexes
– SP_Logins
– SP_Lock
– SP_Monitor
– SP_Renamedb
SP_recompile
– SP_Statistics
– SP_Stored_procedures
– SP_Spaceused
– SP_Updatestats
– SP_Who2

– XP_fixeddrives
– XP_logininfo

DBCC (Database consistency Checks)

– DBCC SQLperf(logspace)
– DBCC CheckDB
– DBCC CheckDB with Physical_only
– DBCC Shrinkfile
– DBCC Loginfo
– DBCC Showcontig
– DBCC DBREINDEX
– DBCC Indexdefrag
– DBCC UpdateStatistics

Important DMVs (Dynamic Management Views)

– Sys.dm_db_index_usage_stats
– Sys.dm_db_index_operational_stats
– Sys.dm_db_index_physical_stats
– Sys.dm_db_missing_index_columns
– Sys.dm_db_missing_index_details
– Sys.dm_db_missing_index_group_stats
– Sys.dm_db_missing_index_groups
– Sys.dm_os_performance_counters
– Sys.dm_exec_requests

Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: