April 1, 2010 at 3:55 pm
Also, have you checked the index usage DMV to see if all of those 20 indexes are actually being used?? Have you compared their create scripts to see if they are overlapping and maybe you can eliminate a few without any effect on system performance (actually it could go up)?? These things are very simple and will take all of 10 minutes of your time.
That in itself is somewhat of an overstatement. I would wager that a good percentage of people working with SQL server do not have the knowledge required to easily do this. (Not you in particular Kramaswamy, just throwing it out there) 10 minutes of your time might be several hours of someone else's, especially if they're cautious and do some research before making changes. Even if your server has been running for 6 months, you have the potential to drop indexes that might only be used by year end processes. And while you could make the argument that those should be built and point and time and not maintained throughout the entire year for a once a year process, it doesn't change the fact that you might be creating a problem down the road that you have to deal with.
There's also the other possibility of it being a database created by a third party vendor. I deal with this constantly. Anything I do to their tables has the potential to be overwritten when they patch. My own SP in my own database does not run this risk.
It's all situational. While your method might be superior if you were implementing it, there are other factors that always come into play. Like it or not, I've had to settle for using a loop or two on occassion. But sometimes it simply takes longer... and if it's not something that is going to see a lot of use, 'Good enough' unfortunately is a realistic compromise I have to make.
[Edit] Ack, long day, SQL tag instead of Quote tag. Bleh.
April 1, 2010 at 4:08 pm
Garadin (4/1/2010)
Also, have you checked the index usage DMV to see if all of those 20 indexes are actually being used?? Have you compared their create scripts to see if they are overlapping and maybe you can eliminate a few without any effect on system performance (actually it could go up)?? These things are very simple and will take all of 10 minutes of your time.
It's all situational. While your method might be superior if you were implementing it, there are other factors that always come into play. Like it or not, I've had to settle for using a loop or two on occassion. But sometimes it simply takes longer... and if it's not something that is going to see a lot of use, 'Good enough' unfortunately is a realistic compromise I have to make.
I have to agree with the situational comment. There are many limiting factors in an environment. Some are political and others are knowledge based. However, learning how to fine tune is an essential aspect that must be learned. It may take 2 hours today, but after some practice it may just be a 10 minute exercise.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2010 at 5:14 pm
I only mentioned the index cleanup investigation because the OP stated concern about having so many indexes on the table.
I disagree with your statement that not many can do index comparison. Virtually every SQL Server user I have ever met could open SSMS, navigate to a table and it's indexes, right click and generate a script. do that for each index. eyeball the output looking for identical columns. It really is that simple. Obviously there are scripts to help with this, such as sp_helpindex2 to be found online, but they aren't required. 🙂
As for the index usage DMV, there is sample code in BOL, with straight-forward explainations of outputs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 2, 2010 at 5:53 am
TheSQLGuru (4/1/2010)
I only mentioned the index cleanup investigation because the OP stated concern about having so many indexes on the table.I disagree with your statement that not many can do index comparison. Virtually every SQL Server user I have ever met could open SSMS, navigate to a table and it's indexes, right click and generate a script. do that for each index. eyeball the output looking for identical columns. It really is that simple. Obviously there are scripts to help with this, such as sp_helpindex2 to be found online, but they aren't required. 🙂
As for the index usage DMV, there is sample code in BOL, with straight-forward explainations of outputs.
I tend to agree with just about everything you have said in this thread.
Others make valid points, but the fact remains that a technique based on copying data to tempdb to index it will never be optimal, serves to perpetuate bad practice, and only makes a bad situation worse.
Hack a temporary solution if lives depend on it, but be sure to clean it up ASAP. None of the ideas presented (identifying duplicate indexes, checking index usage) are at all complex, and Books Online is an excellent resource.
If all else fails, hire a consultant, and/or arrange some training 😉 😀
April 2, 2010 at 6:13 am
TheSQLGuru (3/26/2010)
Will you be iteratively (and by iteratively I mean 5+ perhaps) hitting the temp table?
I have found in some cases that taking off a copy to a temp table leads to huge performance gains when data in the temp table is accessed more than once. It's a technique I use sometimes, but, as many of you pointed out, should be applied to cut down times for long running complex reports.
-- Gianluca Sartori
April 2, 2010 at 8:31 am
Gianluca Sartori (4/2/2010)
TheSQLGuru (3/26/2010)
Will you be iteratively (and by iteratively I mean 5+ perhaps) hitting the temp table?I have found in some cases that taking off a copy to a temp table leads to huge performance gains when data in the temp table is accessed more than once. It's a technique I use sometimes, but, as many of you pointed out, should be applied to cut down times for long running complex reports.
Which brings us full circle on the It Depends Train.
I have seen the same benefit of using a temp table for long running processes such as Gianluca described. It really boils down to testing it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 8:38 am
Having read through most of the replies in this thread, I feel like I need to make something clear.
I'm more than capable of going through the indices on the table and cleaning them up. In fact, in a perfect world, I'd be completely re-doing the table, such that the production side of it was indexed only on the fields that were necessary for the entering and editing of information within it. A mirror table would handle all the operations that were needed for data retrieval.
But that's just not possible right now. There are simply not enough resources at our disposal to be able to devote the time to managing something like that. We've already taken steps to clean the indices up in a simple manner, IE removing duplicates, etc... The ones that remain, yeah we could kill them, but then we'd have to investigate to see if some key process somewhere along the line has suddenly become unusable because of the lack of it.
It's not simply a manner of being able to take all the stuff, write it on a board, and then cross things off. Especially when this is for just a simple report, it's far too time intensive to do anything else. The temp table, while definitely not a long-term fix, is more than sufficient for the current needs.
April 2, 2010 at 9:15 am
kramaswamy (4/2/2010)
Having read through most of the replies in this thread, I feel like I need to make something clear.
You asked for feedback in your original post and, as far as I can tell, you have received quite a lot.
That counts as success in my book, whether you agree with the comments you received or not. 🙂
April 2, 2010 at 9:24 am
Oh don't get me wrong - I'm far from complaining. I just wanted to clarify my situation, and perhaps add somewhat to the discussion.
Sometimes I read comments on these forums, and others, where people are recommending very elegant solutions to problems, however, they don't always take in to account the amount of time that would be required to correctly implement the solutions.
On the flip side, I also frequently see criticisms of solutions that are inelegant - however, the commentors are not always aware of the restrictions that are frequently in place, both in terms of time and resources.
April 2, 2010 at 9:37 am
I can appreciate your point of view. Thanks for sharing.
April 2, 2010 at 9:50 am
kramaswamy (4/2/2010)
Oh don't get me wrong - I'm far from complaining. I just wanted to clarify my situation, and perhaps add somewhat to the discussion.Sometimes I read comments on these forums, and others, where people are recommending very elegant solutions to problems, however, they don't always take in to account the amount of time that would be required to correctly implement the solutions.
On the flip side, I also frequently see criticisms of solutions that are inelegant - however, the commentors are not always aware of the restrictions that are frequently in place, both in terms of time and resources.
I see that point of view. Hopefully there are sufficient responses to cover both ends of the spectrum for this situation.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 10:09 pm
Kramaswamy,
I sincerely hope that you didn't take any of my comments as belittling your competence. Nothing of the sort was intended. My last post was intended to focus on the different perspective of someone whose primary mission is to improve performance of existing systems versus that of someone who is balancing any number of conflicting objectives, deadlines, and priorities about new stuff to get into production. If I worded it poorly and gave offense, I heartily apologize.
SQLGuru,
I don't feel you are heartless or out of touch with real-world demands either. I readily listen quite respectfully to your views. I just feel that there are some situations where the need to build something fast can outweigh the need to build it to perform optimally. Will doing everything quick-and-dirty eventually lead to serious problems? Absolutely. All things in moderation.
Sincerely,
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 2, 2010 at 10:32 pm
Sometimes I read comments on these forums, and others, where people are recommending very elegant solutions to problems, however, they don't always take in to account the amount of time that would be required to correctly implement the solutions.
I for one am glad that elegant solutions are recommended. It makes us aware that such solutions exist, when we need them in the future.
On the flip side, I also frequently see criticisms of solutions that are inelegant - however, the commentors are not always aware of the restrictions that are frequently in place, both in terms of time and resources.
Nobody in an online forum can be aware of all the variables. Everyone has to exercise their independent judgment. One of the great things about the SSC forums are the debates. Again it can make us aware (or remind us) of considerations that we might not have thought of otherwise
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 3, 2010 at 12:51 am
The Dixie Flatline (4/2/2010)
All things in moderation.
Except moderation?
April 3, 2010 at 11:14 pm
Including moderation.
To heck with Russell's Paradox.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply