SQL SERVER – 3 Questions: An Index Reduces Performance of SELECT Queries

Earlier last week I have published a blog which received very much positive reviews. Lots of people had no idea that even index can also reduce the performance of SELECT queries. I have received a lot of follow up questions and comments via blogs, youtube and particularly on Twitter. There were few very nasty emails too but just so you know, I welcome everything and I have done my best to answer all the communication received.

However, before you continue reading this blog post, please read the blog post here and watch the following video first about select queries.

Here are the top 3 questions which I have received in the response of the video and their answer.

Question 1: Why did you create an index if that was not a great idea?

Answer 1:  The whole intention of the video was to demonstrate that indexes are not a good solution to this problem. There are many different ways to improve the performance of the query.  I believe indexes should be used as the last resources when there is no other option left to improve the performance of the queries.

Well, the reason, I created the index is to prove that indexes are not a great way to start doing performance tuning.

Question 2: What are the other ways to improve the query performance besides indexes?

Answer 2: Again, let me repeat, I no way say that indexes are a way to improve the performance of the query. They are just big distractions. Here is what I do at my customer place before we even talk about indexes:

• Server Level Configuration Check (8 Point Checks)
• Database Level Configuration Check (6 Point Checks)
• I/O Distribution and Latency Analysis (4 Point Checks)
• Data and Log File Inspections (3 Point Checks)
• TempDB Analysis and Review (2 Point Checks)
• Windows Configuration Check (3 Point Checks)
• DBCC Best Practices (2 Point Checks)
• SQL Server Wait Statistics Analysis and Strategy (5 Point Checks)
• Error Log Review (3 Point Checks)
• Miscellaneous Performance Checks (3 Point Checks)

Once I go through above various checks, we start looking at the indexes and queries. However, my experience is that most of the time, clients gain multifold performance while focusing on the above items only. Many of my clients often do not want me to the next section where I talk about indexes and queries as they have overachieved their performance goals.

If you want me to teach you how you can do SQL Server Performance Tuning of your Server, just drop me an email and we can do a mix of training / consulting where I can teach you everything you need to learn about tuning your server. If you are interested in starting your own consulting business, I will be happy to help you teach you how you can also get business.

Question 3: How to identify indexes which are not used for any queries?

Answer 3: It is very simple. Here is the unused indexes script which is built on the top of the sys.dm_db_index_usage_stats DMV. You can run against your database and identify which indexes are not used and after doing proper testing consider them to drop. Here is another script to identify missing indexes script. However, do not create every index this query suggests as often this script gives you misleading information too. Please remember indexes have a very strong impact on SELECT queries and also on Insert, Update and Delete queries.

There are few additional performance tuning scripts as well, I suggest you register at https://go.sqlauthority.com and it will automatically send you an email whenever I publish new information.

Reference: Pinal Dave (https://blog.sqlauthority.com)