Close Menu



Perfomance Tuning of SQL-Server

Before You Proceed we recommend you to
read our Disclaimer below

Please test the script on your testing or Dev (development) environment's which are expendable before executing or implementing these scripts over production or realtime environment. Also get the Aprovalfrom your DBA before executing these scripts

The information contained in this website is for general information, Educational and Practice purposes only. The information is provided by and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk.

In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website.

Through this website you are able to link to other websites which are not under the control of [business name]. We have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them.

Every effort is made to keep the website up and running smoothly. However, [business name] takes no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control.

Performance issues That can be the result of Improper Query Design

Sometimes you might come up with situations where users start complaining about slowness in the Application and the application owner may conclude that its the database which is causing the slowness. The issue could be related to the application or could be because of the database or even could be because of the network. However, upon confirmation that it’s because of the Database. We have to work with application or developer's team to sort out where the problem from the database lies.

Finding the root cause of the Problem
Mostly the problem lies with a specific stored procedure or could be the entire server being slow

a)If the issue is the server being slow then we need to check the server for slowness. (we will discuss that as a separate topic for now)

b)if the issue is related to a specific query (ex:- a report running slow or a report never loads) then the report should be associated with a Stored procedure or a function used in the query which is causing the slowness.

Important note:- if the issue exists only at a specific point of time then it could be a network issue or a Database issue (database running heavy procedures’ at that point of time and causing overload on the CPU or heavy I/O. And sometimes they may even be locking tables which is causing the wait or delay). If that’s the case we need to try to avoid the heavy transactions at that point of time (depending on your environment and requirements and priorities).

Sticking to the Basic’s

Comparing runtimes: Compare the query runtime and make a note of it on Testing and Dev environments. We need to make sure that our dev and prod are identical and simulate a Realtime environment by a database refresh (if required).
Important note:- Make sure that u run a query on production only if there are SELCTS involved in the query. AVOID Using any DML Statements on prod.
If the prod run time is slower than the expected runtimes (ask your Developer how much should be the runtime). Then start troubleshooting the Query.

Checking for indexing. When we look at the query plan the query plan should have the least possible scan’s than seeks (I would recommend a zero scan’s). and if there is a scan running in the query plan, then an index needs to be created on the column where the scan is running

(mostly after there where clause in the query). The beauty of SQL-Server is that Sequel shows us the query plan with the index which needs to be created. The query plan also gives us the SQL Code for the creation of the index.

If there are indexes which exist and still if the runtime is slow Then there could definitely a problem in the query.

Finetuning the query: Fine-tuning a query is an art and not a science. Even though we can find some ground rules of do's and don’ts in a query it’s always a trial and error techniques which finetunes a query.

Now we need to start examining the query. Check if there are any functions present in the query

Case1:- If functions are present.

Test the function execution time. By supplying parameters if required. Try to avoid functions and use Pre-Calculated computed columns and try removing the function if possible.

Case2:- If Cursors are present.

Check for Cursors in the query. Cursors are a curse for performance. Cursors cause the query to run very slowly and needs to be removed. Cursors can be replaced by Temporary tables.And accordingly, we need to use alternatives in the query depending on the situation.

In real life scenario, there could be something else other than a Cursor or a function which can be present in the query which is causing the slowness. The point to be noted in both the cases is that we made minor changes in the query making sure that the output remained the same. The point I want to emphasize is the even we altered the query the output should not change but the perfomance. Every query is different so the secret of Tuning a query depends on different situations. No-matter what the scenario is there will always be an alternative which way of running the query without effecting the output but increasing the performance.

 Finding the best alternative way of running a query without changes in the output is Query tuning. Most of the times we might need to experiment different ways of running the query with the same output ending up with an alternative with the best peromance.