That is really surprising. I would never had noticed that an index could cause such performance degradation!!! THANK YOU!!! I ALWAYS look to you for answers to the really tough ones. Glad you are here!!!!!!! KEEP UP THE GREAT WORK!
Have attended one of your sessions in Bangalore Microsoft TechEd few years back. Following you from then on blog. No one else explains these things better. Thanks a ton
Pinal, saw that presentation on PASS Summit. Which version of SQL Server you are using? 2016 does not show that behavior: Microsoft SQL Server 2016 (SP2-GDR) (KB4293802) - 13.0.5081.1 (X64) Jul 20 2018 22:12:40 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 8.1 Enterprise 6.3 (Build 9600: ) (Hypervisor)
Pinal Dave You are exploiting a rare scenario and attempting to invoke fear for those that don’t know that this can occur so they will hire you as identified by the quote from you below "we can discuss during the consultation or during training". Another person is asking you how to identify missing indexes and you tell him to subscribe to some site and you will hand out 3 free scripts when you could have just replied back informing he/she to use the built-in DMV sys.dm_db_index_usage_stats. I have seen some of your videos and liked them, but this is a step a little too far in my opinion. 99.99999% of the time your performance problem will NOT be from an index but from an overwhelmed subsystem resource, mis/non-configured server settings, mis/non-configured database settings, missing indexes, your query and yes the poor architecture a db may have which can help to induce performance issues.
Hi Richard, Thanks for your comment. I appreciate your comment and I totally agree there are lots of things beyond the index. During SQL Server consultancy, along with the index, you need to look at lots of different things - just to name few - Server/Instance Level Configuration Check I/O distribution Analysis SQL Server Resource Wait Stats Analysis TempDB Review Database Files (MDF, NDF) and Log File Inspection Log Reviews (Windows Event Logs, SQL Server Error Logs and Agent Logs) DBCC Best Practices Implementations When you subscribe to get free scripts you get the complete script which also generates the drop script of the unused index. However, for advanced users, you can use sys.dm_db_index_usage_stats as well. Here is the link for my consultancy, where we discuss indexes and lot more content: blog.sqlauthority.com/comprehensive-database-performance-health-check/ All the advanced users do not need any consultants, as they can just do all of them myself. However, those who are busy can consider hiring others to do the task for them. Again, truly appreciate you taking time to leave a comment. Thank you!
Can you pls suggest on below: Table A with cols ID, Name, Message There are two non clustered indexes 1. Index1 with index_keys as Name,Message 2. Index2 with index_keys as Message,Name These are duplicate indexes but column order is different. Is this ok or probable reason of performance issues?
Hi Pinal here the performance gets degraded when you create two indexes in the order of A, B and B, A. Are they not covering index ? Should B, A be used ? What happens if I add another index of the order C, D ? Just a query,
If you show the execution plan as XML you will probably see that the index is ACTUALLY being used. The statistics of the new index are being used to determine the execution plan. I agree in a detrimental way. but the index is being used.
Great point. However, what I wanted to stress in this video was also the point that when we create an index, it can also reduce the performance of SELECT statement which people usually do not think. Many people just leave their indexes as it is and have no idea how they impact other queries and particularly SELECT queries. This is just one of the tip, there are so many misconceptions out there and lots of people need to learn about them, otherwise, they will spend countless hours in doing performance debugging.
I was not able to reproduce this behavior on SQL2019. I was using AdventureWorks2017. As long as the first index is there is behaves nicely even if the second one exists. I tried compatibility levels 130, 140 and 150. Are we sure what you present is true? Does it depend on the database?
@@PinalDaveSQLAuthority Yes, it works on AdventureWorks2014, but I it doesn't seem to work on the AdventureWorks2017 version of the database.What would cause the behavior to change?
@@curtisbrowne2710 did you take into account that the behaviour depends on index id? So it matters what order the indexes are created in? dba.stackexchange.com/questions/259780/creating-an-index-that-is-not-used-by-a-select-query-reduces-performance-of-th/259853#259853
Great question, I really wish the explanation was that simple. It is pretty long and I usually cover that in my training. However, for the simple version, you can watch some of my free videos here and I explain it there. blog.sqlauthority.com/free-learning-videos/
I spent over 10 minutes watching this video but you didn't say why the select was slower with an unused index. So this video is not really very helpful. Are you hoping we will pay you to tell us that or are you expecting us to google to find out why?
Great question my friend. The topic was to show that Index reduces the performance of SELECT queries and not to go into the details about why as it would a very long deeper conversation which we can discuss during the consultation or during training. I hope from this video I was clear that just like Insert, Update and Delete, an Index can slow down a SELECT statement as well. Let me know if that was not clear. ... and thank you for watching this one!
you've just happened to come across a bug in the qry optimizer. It picked a poor plan! I would say this is more rare than you think. Not impossible of course, but probably not to lose sleep over. If you have adequate IO/CPU headroom, and your server is humming along performing well, don't waste your time looking for these obscure problems
Hi there, honestly no. This is what I have been seeing at many of my clients from the last 10 years. During consultation, we have found many cases which are similar to this one and also this is not a bug but rather expected behavior.
@@PinalDaveSQLAuthority if you're looking at high resource consuming queries, you should detect this and fix it any way.. but I disagree, it's a bug when it comes to query optimization. It's a failure on the optimizer.. I'm sure David DeWitt would agree
@@lookingforronfalter let us agree to disagree. However, as I mentioned Indwx Tuning is not I start my day with... There are many more stuff you need to do it before it, and I am sure you agree with that part.
This is very wrong way to present this problem. If I have to tune this query that first thing will he getting rid of lazy spool. By using cte or temp table . Creating index is not even a solution in this. But I really fan of your blog. Thank you for posting this.
Thanks for watching the video. Please note that the goal of the video was not to tune the query but to demonstrate that index can reduce the performance of the index. I hope you got that message, please. Once you agree to the point we can focus on alternatives to fix the queries. Additionally, the first index solves the problem as well. It is just an addition index, which is not used is creating a problem. Besides CTE or temp table and indexes there are few more tricks also there where we can get query Performance without changing the code too... In some future video, I will cover it. Thank you again for watching the video, I think we both agreed that indexes are not good so we are the same page.
@@PinalDaveSQLAuthority Now when I see it, I agree with you. It is specially for DBA who just think index is solution to every thing. Thank you keep posting :)
why on earth anyone wants average on productid . Query is reading same table twice with a sub query in where clause . can be easily written with over clause and partition by . Best Practices of writing query not followed
Hi All, Those who want free scripts to identify unused indexes, can register here: go.sqlauthority.com and it will send you script immediately.
Please sign up now.
That is really surprising. I would never had noticed that an index could cause such performance degradation!!! THANK YOU!!! I ALWAYS look to you for answers to the really tough ones. Glad you are here!!!!!!! KEEP UP THE GREAT WORK!
Appreciate your kind note
Power. No one explains Tsql as clear and interesting as you ❤
Thanks!
Have attended one of your sessions in Bangalore Microsoft TechEd few years back. Following you from then on blog. No one else explains these things better. Thanks a ton
Thank you so much!
There's my teacher, right there. Thanks, sir!
Thanks!
Pinal, saw that presentation on PASS Summit. Which version of SQL Server you are using?
2016 does not show that behavior:
Microsoft SQL Server 2016 (SP2-GDR) (KB4293802) - 13.0.5081.1 (X64) Jul 20 2018 22:12:40
Copyright (c) Microsoft Corporation Developer Edition (64-bit)
on Windows 8.1 Enterprise 6.3 (Build 9600: ) (Hypervisor)
I used SQL Server 2017 for this demo and SQL Server 2019 for PASS.
@@PinalDaveSQLAuthority Thanks. I can see it now
Hi Pinal! Please use temporary table instead of subquery in your scenario. Thanks!
Noted!
Pinal Dave You are exploiting a rare scenario and attempting to invoke fear for those that don’t know that this can occur so they will hire you as identified by the quote from you below "we can discuss during the consultation or during training". Another person is asking you how to identify missing indexes and you tell him to subscribe to some site and you will hand out 3 free scripts when you could have just replied back informing he/she to use the built-in DMV sys.dm_db_index_usage_stats. I have seen some of your videos and liked them, but this is a step a little too far in my opinion. 99.99999% of the time your performance problem will NOT be from an index but from an overwhelmed subsystem resource, mis/non-configured server settings, mis/non-configured database settings, missing indexes, your query and yes the poor architecture a db may have which can help to induce performance issues.
Hi Richard,
Thanks for your comment. I appreciate your comment and I totally agree there are lots of things beyond the index. During SQL Server consultancy, along with the index, you need to look at lots of different things - just to name few -
Server/Instance Level Configuration Check
I/O distribution Analysis
SQL Server Resource Wait Stats Analysis
TempDB Review
Database Files (MDF, NDF) and Log File Inspection
Log Reviews (Windows Event Logs, SQL Server Error Logs and Agent Logs)
DBCC Best Practices Implementations
When you subscribe to get free scripts you get the complete script which also generates the drop script of the unused index. However, for advanced users, you can use sys.dm_db_index_usage_stats as well.
Here is the link for my consultancy, where we discuss indexes and lot more content: blog.sqlauthority.com/comprehensive-database-performance-health-check/
All the advanced users do not need any consultants, as they can just do all of them myself. However, those who are busy can consider hiring others to do the task for them.
Again, truly appreciate you taking time to leave a comment. Thank you!
did we can retrieve the JSON data as early as possible from the table we have 30 columns
Sorry, I do not understand your question
You are a Saint! Thanks a lot!
You are very kind.
Can you pls suggest on below:
Table A with cols ID, Name, Message
There are two non clustered indexes
1. Index1 with index_keys as Name,Message
2. Index2 with index_keys as Message,Name
These are duplicate indexes but column order is different.
Is this ok or probable reason of performance issues?
I do not think they are duplicate. They are unique.
are u provide training of sql basic to advance
All videos online.
@@PinalDaveSQLAuthority paid cource. And fees?
@@Ganeshay-09 It is free. Just watch all the videos on UA-cam.
Hi Pinal here the performance gets degraded when you create two indexes in the order of A, B and B, A. Are they not covering index ? Should B, A be used ? What happens if I add another index of the order C, D ? Just a query,
Your question is very valid and it should be investigated. I will have to try myself.
have you tried to use OPTION (RECOMPILE) at the bottom? how would it affect this behavior?
In longer demo we do experiment with that... Absolutely no difference in results you are seeing.
The subject of the Video should be, a Un used INDEX Reduces Performance of SELECT Queries
Thank you so much. Good point
Very nice explanation sir. I request you to please provide a explanation video for understanding deadlock graph.
Sure....
Hey Pinal, if scan 20% and lookeup 10% update 70%.Is it unused index? Yes/No ? then how
It is difficult to answer without looking at the all the indexes.
I would have love to see the video including a cluster index and then creating a non cluster index would have shown some different results.
Sure will focus on them.
How to reset indexes sir,
Sorry, I do not understand.
@@PinalDaveSQLAuthority I create indexes long back then how can I reset the indexes. Is it requires? If requires how to reset existing indexes
Cool thanks
You bet
I think it is more how you define your index(es) than anything else that impacts the performances.
That is correct point
Just as a new index affects the performance of existing queries, so an existing index can affect future queries
That is very correct assessment. Thank you so much sir
If you show the execution plan as XML you will probably see that the index is ACTUALLY being used. The statistics of the new index are being used to determine the execution plan. I agree in a detrimental way. but the index is being used.
Great point. However, what I wanted to stress in this video was also the point that when we create an index, it can also reduce the performance of SELECT statement which people usually do not think. Many people just leave their indexes as it is and have no idea how they impact other queries and particularly SELECT queries.
This is just one of the tip, there are so many misconceptions out there and lots of people need to learn about them, otherwise, they will spend countless hours in doing performance debugging.
I was not able to reproduce this behavior on SQL2019. I was using AdventureWorks2017. As long as the first index is there is behaves nicely even if the second one exists. I tried compatibility levels 130, 140 and 150. Are we sure what you present is true? Does it depend on the database?
I tried it and it works in SQL Server 2019 as well just like the other one.
@@PinalDaveSQLAuthority Yes, it works on AdventureWorks2014, but I it doesn't seem to work on the AdventureWorks2017 version of the database.What would cause the behavior to change?
@@curtisbrowne2710 I tried with 2017 with different compatibility and it works too. I am really not sure.
@@curtisbrowne2710 did you take into account that the behaviour depends on index id? So it matters what order the indexes are created in? dba.stackexchange.com/questions/259780/creating-an-index-that-is-not-used-by-a-select-query-reduces-performance-of-th/259853#259853
how do i find unused indices in the database?
Chintan, You can go to go.sqlauthority.com and subscribe there and in the response, you will get three free performance tuning scripts.
sys.dm_db_index_usage_stats
Sir your voice is very good...
Thank you for your kind words.
Hi
Why did this happen?
Is there any explanation?
Great question, I really wish the explanation was that simple. It is pretty long and I usually cover that in my training. However, for the simple version, you can watch some of my free videos here and I explain it there. blog.sqlauthority.com/free-learning-videos/
explanation here dba.stackexchange.com/questions/259780/creating-an-index-that-is-not-used-by-a-select-query-reduces-performance-of-th/259853#259853
Wow! Didn't knew this happens but I'm only a manager :-)
Glas you liked the video...thanks for watching.
I spent over 10 minutes watching this video but you didn't say why the select was slower with an unused index. So this video is not really very helpful.
Are you hoping we will pay you to tell us that or are you expecting us to google to find out why?
Great question my friend. The topic was to show that Index reduces the performance of SELECT queries and not to go into the details about why as it would a very long deeper conversation which we can discuss during the consultation or during training.
I hope from this video I was clear that just like Insert, Update and Delete, an Index can slow down a SELECT statement as well. Let me know if that was not clear.
... and thank you for watching this one!
Explanation here dba.stackexchange.com/questions/259780/creating-an-index-that-is-not-used-by-a-select-query-reduces-performance-of-th/259853#259853
find top 1-3 queries that are executed the most
I have blogged about them at stackoverflow.com
you've just happened to come across a bug in the qry optimizer. It picked a poor plan! I would say this is more rare than you think. Not impossible of course, but probably not to lose sleep over. If you have adequate IO/CPU headroom, and your server is humming along performing well, don't waste your time looking for these obscure problems
Hi there, honestly no. This is what I have been seeing at many of my clients from the last 10 years. During consultation, we have found many cases which are similar to this one and also this is not a bug but rather expected behavior.
@@PinalDaveSQLAuthority if you're looking at high resource consuming queries, you should detect this and fix it any way.. but I disagree, it's a bug when it comes to query optimization. It's a failure on the optimizer.. I'm sure David DeWitt would agree
@@lookingforronfalter let us agree to disagree. However, as I mentioned Indwx Tuning is not I start my day with... There are many more stuff you need to do it before it, and I am sure you agree with that part.
I think I have to pay that 1:1 session for my managers. If they don't listen to me at least they will listen to you :|
Thank you Francesco, it will be always a pleasure.
lol... that's a lot of forward statement my friend. :D
So true!
This is very wrong way to present this problem. If I have to tune this query that first thing will he getting rid of lazy spool. By using cte or temp table . Creating index is not even a solution in this. But I really fan of your blog. Thank you for posting this.
Thanks for watching the video. Please note that the goal of the video was not to tune the query but to demonstrate that index can reduce the performance of the index. I hope you got that message, please.
Once you agree to the point we can focus on alternatives to fix the queries. Additionally, the first index solves the problem as well. It is just an addition index, which is not used is creating a problem.
Besides CTE or temp table and indexes there are few more tricks also there where we can get query Performance without changing the code too... In some future video, I will cover it.
Thank you again for watching the video, I think we both agreed that indexes are not good so we are the same page.
@@PinalDaveSQLAuthority Now when I see it, I agree with you. It is specially for DBA who just think index is solution to every thing. Thank you keep posting :)
why on earth anyone wants average on productid . Query is reading same table twice with a sub query in where clause . can be easily written with over clause and partition by . Best Practices of writing query not followed
This was the example query.