Tips to Improve Performance of Slow Running SQL Server
SQL Server Running Slow?
“While fetching records in SQL Server 2012 database, I am noticing that my database began running too slow from past two weeks, even some queries and processes ended with a timeout error. Speed is well for a moment and then everything becomes slow. CPU, disk seems normal. I am unable to check if there is blocked process as activity monitor returns timeout error. Why is this happening ? Why my SQL Server Database is running slow?”
This is one of the common performance issue in SQL Server, a user can face. Similarly, there are number of issues making the performance of SQL Server slow.
Users want quick response of retrieval of data from the database and developers make their best efforts to provide the data in the shortest time. There is no such way of defining, what is the good or bad performance of a query.
Lets benefit SQL query by optimizing the SQL Server and making its performance fast. I will share some quick tips to improve SQL Server performance. But first let us understand the possible reasons Why SQL Server running slow ?
Why SQL Server running slow? – Know The Reason
There are number of reasons why SQL Server running slow. Finding the cause of the performance bottleneck is vital. Through this, you will be able to solve the issue. Some of the biggest performance bottlenecks are as below:
Missing Indexes – Poor Disk performance & increase number of deadlock signify that you are having missing indexes.
You can easily determine missing indexes by DMVs, sys.dm_db_missing_index_details and sys.dm_db_missing_index_columns. Simultaneously, you can identify the indexes that need to be created to improve the performance.
Poor Designing of Indexes – Poorly designed database tables result in slow running queries that result in high execution plan.
You can easily identify the problem by querying the procedure cache. This is because the query execution plan remain in the cache as long as there is memory to store them.
Poor Designed Database Schema – This is one of the biggest killer of database schema. This can be seen while queries are running, when there is increase in number of input output request occur.
You can simply look for this evidence by using SQL Profiler. The profiler captures those queries who has the highest I/O load.
Less Storage in I/O Subsystem – To determine whether there is problem in storage performance or not, you can check ERRORLOG file. You will get slow I/O message in your Errorlog file, if the issue is related to storage I/O subsytem
You can look for I/O problem by running sys.dm_os_wait_stats DMV with code.
Slow Network, Small Buffer Pool, Wrong technology used are some of the reasons that degrades SQL Server performance. Resolving these problems might seem difficult but if you have the right tools, you can easily fix these performance issues.
Quick Tips to Improve SQL Query Performance
We have discussed various causes of slow running SQL Server. Implementing the following points will help you to improve the SQL Server performance
- Avoid writing a query with multiple joins that include outer joins, cross join, and other complex queries.
- Try to Avoid cursors. It makes the performance slow. If there is need to use cursors, then avoid dynamic cursors.
- Avoid * in SELECT statement. Specify the name of the column which you require.
- Delete unused indexes.
- Use Joins instead of subqueries.
- If possible, use UNION ALL instead of UNION.
- Avoid using Multi statement Table valued functions. They are more costly than inline TVFs.
- Positioning of index play an important role to improve the query performance. Selective columns should be placed leftmost turns out as a best practice.
- Avoid NULL in fixed length field. In case of requirement, use variable length field, it takes less space for NULL.
- Try to keep transaction small as sometimes the processing result in deadlocks.
- Use TRY CATCH to avoid deadlocks.
There are number of users/ DBAs who are annoyed by slow performance of SQL Server. The blog discusses the possible reasons Why SQL Server running slow? It also covers some quick tips to improve SQL server performance.