Can an Index reduce the performance of SELECT Queries?
Let me make this question a bit more complete –
Can an Index reduce the performance of SELECT Queries even though that index is not used anywhere?
If your answer is NO, well, continue reading.
I have created this video specially for you! Every single place where I go for SQL Server Performance Tuning Consultancy, I always have a conversation with my customer’s about indexes.
Whenever I say that Indexes reduces the performance of your Insert, Update and Delete, I have always got happy nodes about the same. However, as soon as I mentioned that the same index can be dangerous for your SELECT statement, I always see a surprised look on their face.
Finally, I have decided to create a video on the same subject. Please go ahead and watch the video where I demonstrate an index even though not used for a query can reduce the performance of the SELECT statement.
Top 5 Follow Questions on SELECT Queries
Here are the top 5 questions I have received from everyone who has watched my demonstration in detail:
- Why an index is reducing the performance of the SELECT statement?
- How to create an optimal index which is best for your workload?
- How to rank various indexes in terms of their impact on queries?
- What should you do to maintain optimal performance for your server?
- What are the other such pitfalls?
Business Secrets
After receiving many such questions, I have decided to create an in-depth course, which answers not only the above questions but also teaches my business secrets of SQL Server Performance Tuning. If you believe indexes are the performance tuning secret weapon, you will realize in my course that there are many more things to consider over indexes when it is about SQL Server Performance Tuning.
Build Your Methodology
I personally believe that if you want to become a good SQL Server Performance Tuning consultant, you can just follow my footsteps in the beginning and help your customers with their server’s performance. Once you are comfortable with my methodology, you can develop your system on the top my way of tuning system and build your own SQL Server Performance Tuning Methodology.
Call to Action
If you have missed attending my SQL Server Performance Tuning Practical Workshop for EVERYONE, you still have a chance to watch the recorded class. Here is the link to read more about the class and link to register. Remember, if you register before June 2nd, 2019. You also qualify for Buy One Get One deal where you also get 1 free hour SQL Server Performance Tuning Consultation.
Please note that the buy one and get one offer is only available for the next 7 days.
Reference: Pinal Dave (https://blog.sqlauthority.com)
9 Comments. Leave new
I had attended the class earlier you actually demystify this concept in detail. I think that was my best learning entire this year. I am creating indexes better since I took your class. You were awesome.
I just bought this recording. Thanks for making it available for my co-workers.
This demonstration was epic. I have never thought about it – you are doing great for community.
I wish I can attend the workshop but it is bit expensive for me, however, I am very curious what you would be covering there. I guess, I will start saving.
Any changes with compatibility level and/or cardinality estimator will cause different behaviour?
I have seen issues with compatibility level in SQL server. Our company has some Databases at SQL Server 2012 compatibility level, that, if upgraded to latest level for 2016 perfomance on some queries is degraded. This is a databases for a CRM system. This of course is not on all queries, most queries do actually benefit from the compatibility level update.
Great information. It looks like makes the difference having more indexes only when the sub query return more records. Don’t see any differences with the below query which returns less records.
SELECT SalesOrderDetailID,OrderQty from
[Sales].[SalesOrderDetail] sod –WITH (INDEX(IX_FirstTry))
WHERE SalesOrderID = (SELECT AVG(SalesOrderID) from
[Sales].[SalesOrderDetail] sod1 –WITH (INDEX(IX_FirstTry))
WHERE sod.ProductID=sod1.ProductID
GROUP BY ProductID)
GO
I just swapped the columns.
You are great knowledge and faithful person.
You may have a typo on the page here: “I have missed attending my SQL Server Performance Tuning Practical Workshop for EVERYONE, you still have a chance to watch the recorded class.”
seems to need an ‘If you’ not an ‘I’ at the beginning,
Good stuff though.
Thank you – I agree and I corrected.
I noticed in your demonstration that the two indexes use the same fields but in a different order. So my question is this: If the unused index has no fields in common with the actively used index, will it still negatively impact the performance?