SQL Performance

This is quite normal that sometime SQL Server / Application connecting to SQL database stop responding or start responding slow.

As a DBA we sometime get confused and start resolving the issue without following the recommended steps that one should follow while this issue occurs.

When application gets STOPPED suddenly, steps are…

1. Check Network Connectivity through ping using command prompt.
2. Check Drive Space.
3. Check “SQL Agent Service” is running, if not just restart from SSMS.
4. Go to “Services” ( through run>services.msc ) and check if SQL Server Services are up and running.
5. Then Go to Query Analyzer and execute ‘SP_Helpdb‘ and check the related database status.
6. Check SQL Server Error Logs for Space Issue. Use DBCC sqlperf(logspace) in case log file is full.
7. If Drive has space, still there is space issue, 1. try increasing the Data file Size Limit/threshold. 2. Try adding another data/log file in the Drive that has enough space. Refer this article.
8. If the database is secondary/mirrored/replicated DB, (In case any high availability feature is implemented), we should check for its related monitors to ensure its running fine.

Please suggest for improvement on this….

In case of SLOWNESS, steps are…

The first thing that one should check is Blocking/Deadlock, the steps are as follows.

1. Checking for Blocking through SQL string ‘SP_who2 active’.
2. DBCC opentran [check for any long running transaction quickly]
3. Checking SQL Error Logs for Deadlock. Refer this article.
4. Run Activity Monitor [Monitor CPU memory etc.] or Check for CPU Utilization from task manager and find out which SPID taking high CPU.
5. Use Update Stats for related database. Make sure “Auto Update Stats” & “Auto Shrink” is set to OFF from database options.
6. Use script to find Index Fragmentation followed by running DBCC commands, Refer this article.
7. Check for long running procedures / queries, followed by fetching the execution plan and applying missing indexes if in case most of the cost goes to Index Scan. Refer this article.
8. Check if you are doing recompilation during weekly maintenance [sp_recompile].
9. DBCC Loginfo [Reduce the number of virtual logs if its more than 50, for the related database].
10. Analyse SELECT queries in query analyser for logical and physical reads [set statistics io on]
11. Use this query is to analyze the buffer pool, the amount of space being taken up and how much of that space is empty space.

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 

12. USE “DBCC Checkdb” to find consistency errors and use DBCC to free system and procedure cache BUT make sure to run this during off business hours.

13. If the database is secondary/mirrored/replicated DB, (In case any high availability feature is implemented), we should check for its related monitors to ensure its running fine.

14. PerfMon :- We can check for Basic OS and SQL Server performance counters. The counters are….

• Buffer Cache Hit Ratio.
• Page Life Expectancy.
• Memory Available vs Target.
• Disk IO read/write.
• CPU Utilization etc.
• Paging File
• SQL Statistics
  • Refer this article for complete understanding on its Normal Threshold Values and how to measure etc.

15. SQL Server Configuration (Memory, CPU, MaxDOP, TempDB, Index Fill Factor, Database Options eg. Recovery Models as per requirements).

Refer this article for SQL Configuration best practices.

16. Lock Pages in Memory [check if you really need to enable it]. Refer this article.

17. Last but this is not the end, We can also plan to create separate filegroup with new data file and move heavily used tables or its indexes into it, as each data file has its own NT thread.

Overall, we can configure the SQL Server to improve the performance and achieve significant performance impact.

Thanks with Regards

Amit Vaid

Note:- We will welcome your opinion/suggestions to improve this article, write to us anytime.