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 Maintenance

1) No Temp DB Refresh

sometimes temp DB needs to be refreshed because it may get full or sometimes the temp DB may be slow. Situations like these demand a restart of the SQL Server for the temp DB to be emptied out. Hence there should always be a maintenance plan in place where the server needs a restart depending on your situation.

2) No Index analysis weekly

Weekly analysis of index fragmentation and statistics update is a good practice than  waiting for the performance issues to pop in and then the DBA checking for fragmentation.

3) Non Monitoring Index Usage.

There is always a constant need for index usage monitoring. Indexes which are being used the most and the least define what indexes need to remain and what needs to be removed. Because We never want unused indexes sitting there on the Server. Hence there is a need for constant index monitoring. The DMV sys.dm_db_index_operational_stats() can be efficiently used for this purpose.

4) Timely Index rebuild or reorganize (depending on requirements).

Microsoft says that “no need for us to perform the re-indexing of each and every index in
the tables of the database. If the fragmentation level of a particular index is
less than 30% then we can simply re-organize it, if it is greater than 30%
then we should elect for the re-building of the index.” Hence Depending on this its advisable if a rebuild is needed or Re-Organize is needed.

5) Non Configured Memory.

SQL- Server utilizes. The max memory if not defined a specific amount to use. Problems could occur from this situation and the heights of this issue could eventually turnout that the OS might start deprivation of memory. Please refer to SQL-Server Memory Tuning

6) Before you Implement Changes.

Testing. This is basic But I still wanted to emphasize because there are soo many DBA’s who implement changes as a Quick-fix for SLA Reason’s and then recommend a Change Req and proceed accordingly. This can sometimes become a disaster.

7) Patches, Bug-Fixes and Security Updates:

Every update let it be a Patch Bug-Fix, Security Update. Or anything has to be thoroughly analyzed by reading the associated KB Article from Microsoft . Microsoft KB is currently the largest known Knowledge Base on the internet. Use the KB before you make an update. It may take a few minutes to read though however it will help you in saving a disaster. Test the update on your testing and dev with Realtime data. Also make sure while the update is being installed the instance is offline. If you are installing on a cluster make sure u install on the passive node and make the passive to active and then install on the previous active and now passive node according to your environment.

8) I/O Restriction.

Its Recommended that we implement I/O restriction (possible from 2012versions) by the help of resource manager thereby creating a resource pool and define a I/O for the resource pool  and Add a specific database to it hence restricting the I/O. when one DB is restricted at usage levels of the I/O the mission critical databases will have more I/O thereby creating a free-flow of operations.

9) Health monitoring by analyzing space and file growth

Data accumulates fast hence there should be a constant process of monitoring and analyzing space and file growth constantly on a regular basis. This check will help in a constant performance than a sudden bottleneck .It is always being Proactive than reactive.

10) No to Shrinking Data Files:

Shrinking cause heavy Fragmentation which is not good for any RDBMS system hence will become a bottleneck. it causes extra IO operations.

11) Constantly monitoring I/O.

I/O also should be constantly monitored and this has to become a standard practice to see what’s going on with the I/O on our sever just to be safe than sorry later on practice.


Continued to Next Page ------>> Improper Query Tuning