SQL Server Performance

No doubt, sometime we get confused on what to check and what not to check when we get to know any performance issue….
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]
-  Check for Index Fragmentation [index_physical_stats, ALTER INDEX etc.]
-  DBCC opentran [check for any long running transaction]
-  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].
-  Enable AUTO_UPDATE_STATISTICS_ASYNC option as well if you do not perform weekly update statistics and database’s auto_update_statistics option is enabled.
The Query Optimizer can execute your queries without waiting for updated statistics when AUTO_UPDATE_STATISTICS_ASYNC option is used/enabled. Query Optimizer will go ahead and build an execution plan with the out-of-date statistics and then update statistics in the background to help the next query’s performance.
It is recommended NOT to use this when your queries can wait until the statistics are updated.
-  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 specially buffer pool]
This query is to analyze the buffer pool, the amount of space being taken up and how much of that space is empty space. This may take enough time to run based on how much memory you have, simple … You can then plan to clear system or buffer cache.

SELECT
    (CASE WHEN ([database_id] = 32767)
        THEN N'Resource Database'
        ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
    COUNT (*) * 8 / 1024 AS [MBUsed],
    SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id];
GO

-  DBCC Loginfo [Reduce the number of virtual logs file if its more than 50]
-  The data and log file should not grow frequently, the initial data and log files should have enough space and set atleast 1024mb / 1gb of auto_growth.
-  Do NOT shrink your database or the data file at all, this results fragmentation in your data.
(I am in a short of disk space and have to reclaim space quickly at any cost, is there any work around….?)
Yes, Here it is
Even if you have to shrink DB by any reason to reclaim the space to disk, ensure you ONLY reclaim unallocated space by shrinkfile. [use sp_spaceused]
-  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]
Note: I am always looking for valuable feedback and suggessions to improve these three stages. Let me know if you have anything to add or modify here.
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