In this article we share some practical tips on troubleshooting slow procedures in SQL Server
SQL Server is an application that is capable of a lot, but most of the features in the application are such that they require an updated hardware, broadly speaking – sufficient resources are required. If there is insufficient space, and you are attempting to perform heavy operations using the application, you will be troubled by slow performance. Lack of storage space is one of the very basic and mainstream reasons why this would be happening, there can be multiple other reasons leading to slow procedures in the application. Given below is a list of ways or tips you can use to troubleshoot the cause of slow procedures in SQL Server.
- Storage of Execution Plan – One of the biggest reasons why you are facing slow procedures in SQL Server is probably because your Execution plan is stored in the cache. To find out if it is in the cache, you need to search it there and see if it exists in the top 10 appearing plans. You might or might not choose to save this plan.
- Collect Working Parameters – For running the stored procedures you should have a set of working parameters readily available. Sometimes you might be able to get it from the end user, and sometimes you won’t. For those times, you will have to switch to more challenging ways of finding suitable parameters.
- Find Out the Speed of Your Parameters – The parameters you select should be of differing speeds. You should have ones that are fast, slow as well as those having varying speeds.
Look for Writes by the Stored Procedures – You can check if there are any inserts, deletes etc in the query. If you come across any such thing, you can always tune it in developments, and also look for varying performance.
- Similarity between the Writes Performed – First you need to find out if the stored procedure is performing any kinds of writes, if it is, you then have to check for similarities between those performed earlier and those being performed right now. If the same rows are being updated every time, it will take the same amount of time, however if new rows are being added or deleted, the amount of time taken will differ.
- The Speed of Stored Procedures – You need to find out how does the stored procedure run in Production and Development. Is it slow in one and fast in another? Is it slow in both? What is the count of CPU reads in both environments? And what are the differences in these counts? You will have to make changes in the configuration of environments on the basis of answers you get to these questions.
The situations described above are a few different ways using which you can know the reason of slow procedures in SQL Server. SQL Server is an advanced yet complex application, with lots of features to use from, sometimes a user might unintentionally make changes leading to slow performance, which can be detected by following the above mentioned procedures. One must also note that an overall slow SQL Server application may have other contributing causes such as a corrupted SQL Server mdf file.
Author Introduction:
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair accdb db and sql recovery software products. For more information visit www.datanumen.com