SQL Server Performance – Explained

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.


–  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.

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

--Check CPU & Memory usage

DECLARE @memory_usage FLOAT
, @cpu_usage FLOAT

SET @memory_usage = ( SELECT 1.0 - ( available_physical_memory_kb / ( total_physical_memory_kb * 1.0 ) ) memory_usage
FROM sys.dm_os_sys_memory

SET @cpu_usage = ( SELECT TOP ( 1 )
[CPU] / 100.0 AS [CPU_usage]
FROM ( SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [CPU]
FROM ( SELECT [timestamp]
, CONVERT(XML, record) AS [record]
FROM sys.dm_os_ring_buffers WITH ( NOLOCK )
AND record LIKE N'%<SystemHealth>%'
) AS x
) AS y
ORDER BY record_id DESC
SELECT @memory_usage [memory_usage]
, @cpu_usage [cpu_usage]

----Check Total and Target Memory (KB)
----Allocate more memory using sp_configure, in case target server memory is high
----The value in Page Life Expectancy shouldn't be less than 300 seconds

SELECT object_name, counter_name, cntr_value
 FROM sys.dm_os_performance_counters
 WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)','Page life expectancy');

–  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 Tempdb Speed!    Please refer Tempdb page on the top to learn more on this.

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

–  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.

    (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];

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

–  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. (then disable auto_shrink option)

STAGE 3 Finally these are the options to cover everything.

– 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.

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

– 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.



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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: