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
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.
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,
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!
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.
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 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
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 :)
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!
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
