May 13, 2024 at 5:01 pm
Phil Parkin - I have been going with the idea (aka assumption) that similar is what is being referred to here as otherwise there would be no need to merge the two Indexes for as you pointed out they would be exactly the same. As for the script Scott Pletcher mentioned I was equally intrigued to see what they had thus far created (and still hopeful that they will share it) as well as the aim for their final script.
May 13, 2024 at 6:12 pm
ScottPletcher wrote:You don't actually have to drop both indexes. You would, for example, often drop one index and modify the other one.
I've started on code to do this process but have never finished it. I still do this mostly by hand, although of course I do have a proc that will generate the code for existing index so it's easy to modify it.
Seems contrary to the purpose of combining indexes. You still end up with 2 indexes instead of 1.
No, you do a CREATE INDEX ... WITH ( .., DROP_EXISTING = ON, ... ) to modify the remaining index, not create a new one.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 13, 2024 at 9:29 pm
Jeff Moden wrote:ScottPletcher wrote:You don't actually have to drop both indexes. You would, for example, often drop one index and modify the other one.
I've started on code to do this process but have never finished it. I still do this mostly by hand, although of course I do have a proc that will generate the code for existing index so it's easy to modify it.
Seems contrary to the purpose of combining indexes. You still end up with 2 indexes instead of 1.
No, you do a CREATE INDEX ... WITH ( .., DROP_EXISTING = ON, ... ) to modify the remaining index, not create a new one.
Heh... ok... You win. Technically, though, you're not modifying the index, though. You are still dropping it and replacing it.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2024 at 9:43 pm
Jeff Moden based on what ScottPletcher stated I do not see how one would end up with 2 Indexes as he did state you Drop one of the Indexes and modify the other which to me means you had 2 Indexes you drop one leaving with 1 Index and then you modify that remaining Index to accomodate for whatever extras are needed. So perhaps you misread what was posted, if not please explain?
There is no "modify" for indexes. As he pointed out, there is a "CREATE WITH DROP_EXISTING = ON".
The reason for me being a bit pedantic here is for newbies (based on a lot of the hooie I've seen people post on LinkedIn). If a newbie does manage to get an interview and they say "well, just modify the index", it could cost them because, like I said, there's no "modify" when it comes to the columns being used.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2024 at 10:33 pm
Really Jeff Moden that would be interesting to read (or see) -- your proof in a presentation -- as I find that a rather interesting stance and would greatly like to see that (as I am sure others would be as well). I might be good at SQL but I can guarantee you there are always things that I can learn about it and would love to do just that.
You can make your own proof. Create a very wide Clustered Table with a few columns and a single wide CHAR() to simulate a whole bunch of other columns. You need something wide enough to get the number of rows per page down to, say, 1 to 3.
Then, write a query that will use the Clustered Index to find the beginning of a range scan and aggregate just a couple of columns to make a report. Measure the run time for the report.
Then, make an NCI that includes the same key as the CI and INCLUDEs that include the columns that you need (aka, a "Covering Index".) Measure the report performance with that in place and see why, even though the key of the NCI is identical to the CI, why you wouldn't want to get rid of the NCI. The identically keyed NCI will blow the doors off the CI.
I'd forgotten that the presentation the proof is in is most about how to keep in-row LOBs from fragmenting the heck out of your CIs but, if you're really interested, here's the link to the presentation.
https://www.youtube.com/watch?v=IiI4n5aFg5U
At Timestamp 38:08, I show the results of a run to do some aggregation from a wide CI (just 1 row per page in most cases) and it continues thru a demo of what an NCI that has the exact same key as the CI (and a couple of includes) does. Of course, you already know what's going to happen because this is a "Covering Index" that just happens to have the same key as the CI. That part of the demo ends at 49:21.
And, please forgive the "slow speak" in that 'tube. It was the first time I presented this and I had also pulled an all-night at work the night before. I didn't have much left in me.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2024 at 10:42 pm
Okay Jeff Moden that is a great point.
Although being knowledgeable about Indexes I might off-handedly state that I would simply modify the Index even though that technically means I delete it and add it back. However, I am sure the rest of my quite extensive knowledge on SQL would cover for that as a non-precise methodology but more of a conceptual methodology. Of course, if they then asked me what I meant by modifying it would say delete it and add it back.
Also thank you greatly for that information and presentation, I am sure everyone will greatly appreciate it as well.
May 14, 2024 at 1:13 am
n/a
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 14, 2024 at 6:29 am
I hope your applications do not use table hints to force the use of a given index.
Also doublecheck the indexes are enabled !
( maybe disabled for 99% of the time and rebuilt to serve a specific operation and afterward get disabled again )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 23, 2024 at 6:29 pm
I found this which seem to be the solution i'm looking for:
finding and eliminating duplicate and overlapping indexes.
https://www.sqlservercentral.com/articles/finding-and-eliminating-duplicate-or-overlapping-indexes-1
May 23, 2024 at 7:40 pm
Great EM2 so that simply shows you how to find duplicates and overlapping indexes which was not your question. Further their final solution was simply deleting the offending Indexes that you have visually examine which were the solutions provided to you here.
However in some cases dropping an overlap is not as straight forward since you often need to drop both Indexes and add a new one that merges the non-overlapping elements if possible which was also outlined within the solutions provided here.
Lastly if all you needed was a method to find them, which again was not what you asked, then you should have requested that as most of us here already know to to find such things. I am only stating this so that perhaps the next time you pose a question you pose the question you are actually looking for an answer to.
May 23, 2024 at 7:55 pm
@dennis-jensen I whole-heartedly agree to all you have stated. Thank you!
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply