Monday, May 4, 2020

Sql Server Performance Tuning Scripts in just 2 steps

Step 1: Index and Statistics

1) Missing Index Scripts

Performance is often associated with Indexes. If your database is missing many indexes, you may want to execute a script from the following blog and create necessary missing indexes for your database tables. 
Missing Index Script

2) Unused Index Scripts

If you have unused indexes in your database, they will do more harm than helping your database. It is a good idea to remove them from your database. You may want to execute a script from the following blog and remove unnecessary indexes from your database tables. 
Unused Index Script

3) Find Statistics of Whole Database

Database statistics help SQL Server Engine to make appropriate decisions to use indexes. It is critical to understand the health of your statistics and script mentioned in the blog post displays all the details about your database statistics. 
Statistics Detail Script

Step 2: SQL Wait Statistics

If you want to improve performance of your SQL Server, it is critical to understand where is the performance bottleneck. I have written an entire month long series on the SQL Wait Statistics. With the help of SQL Wait Statistics, we can identify the performance bottleneck of the SQL Server. 
Identify SQL Wait Statistics

Note: Please try this on your development environment first before you execute them on your production database. Always take database backup before you change anything.

No comments:

Post a Comment