June 9, 2009 at 10:24 am
Background data on the scenario:
TABLE DETAILS
Say you inherit maintenance / optimization of another groups SQL / Internal apps and there's a particular table in an important database that has stats showing Average User Costs of 30,75,111,296,577,and 1494 with a number of smaller ones (1-5 etc). (among other things)
Currently there is only 1 clustered index on the table. Current index space is 1.2MB.
There is currently a little over 1million rows, within just under 500MB for this table. The table is a mix of IDs and (mostly) nvarchar fields (50-255) with a few dates and various mixed in. (I know, we're planning on fixing the data structure later but we have to crawl before we run...I inherited this :P).
In other words users have to wait 30 seconds, 2-5 minutes and under heavy thrash they leave and come back 30 minutes later and are sometimes still waiting on the data to return.
NOTE: There are other tables in the same database where the indexing and tuning options are much simpler and will be implemented to help alleviate some of the wait times I mentioned earlier, but this table has the worst stats of all of them.
SERVER
The server is currently low on RAM but we're about to upgrade that to remove that from the equation (we're planning on applying the new index(es) AFTER the various hardware upgrades).
PROBLEM
The problem is that there are a number of lookup queries in different internal applications that all use this table, along with an unplanned batch insert process that sometimes occurs while the server is thrashing under a mixed heavy load to give results to the various internal applications.
So there are about 7 equality columns that are mixed and matched in order and use with the lookup queries, and about an average of 3-20 columns would need to be covered if they're going to be satisfied within the new indexes.
Question:
Is it better to create 1 non-clustered index with the equality columns ordered in the most optimal way for MOST use cases and covering the bulk of the ~20 required columns to satisfy the queries...
OR
Is it better to create more than 1 specialized [meaning equality column ordered] indexes only covering their individual subset of columns?
To go the specialized route I'd be creating 6-7 indexes, each covering a range of 3-20 columns to cover most use cases.
Also, this table gets inserted into in batches...sometimes while thrashing under heavy load, and that's one reason why I'm hesitant to create so many indexes...as I'm afraid the extra overhead of many indexes can make the thrashing problems worse when inserting.
Thanks! I wasn't sure which way to go on this one... 🙂
June 9, 2009 at 11:08 am
Personally, if it were me, I'd create indexes to support the two or three most run queries, and then later re-evaluate to see if more were needed.
My standard technique:
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2009 at 11:41 am
I'd go with specialized indexes. The gain from those should much more than offset the loss of maintaining them.
Like Gail said, pick the most common queries, add covering indexes for them, test, go from there.
If you want more specific advice, we'd need to see the table structure, the most common queries, and the execution plans for those. Provide that, and we should be able to help tune the thing quite nicely.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2009 at 11:51 am
I tend to agree with Gail. Saving 0.01s on a query run 1000 times an hour can be more important than saving 12 minutes on one run once a day. Find out what's run often, what you can improve, pick a few indexes and then retest/ repeat to improve performance. There is a point where more indexes cause issues with inserts/updates, so be sure you watch for issues there.
June 9, 2009 at 12:20 pm
From experience I've found that fixing the 3 top performance problems in the DB often provides an incredible improvement in overall response and performance
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2009 at 12:39 pm
GilaMonster (6/9/2009)
From experience I've found that fixing the 3 top performance problems in the DB often provides an incredible improvement in overall response and performance
Ditto. It's a ripple effect.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2009 at 1:27 pm
Thanks guys!
I'll just do the top 3-4 indexes and only cover the columns of the smaller queries (that way I can be sure I won't hit any issues with the overhead) and then see where we're at.
We're in a code freeze right now (no changes to ANYTHING :P), but it's about to get opened back up within the next week or so (hopefully), so I'm just getting ready.
We actually operate in code freeze (obviously except critical patches that even then require buyoff from all the stakeholders) and only get periodic windows where we can make changes...so I was going to try to apply as many indexes as I could at once (within reason), but I see why that's not a great idea. 🙂
June 9, 2009 at 1:37 pm
I'd want to see the queries that are having a problem first. Good indexes can't fix bad queries.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 9, 2009 at 5:06 pm
too true, but I don't own the apps (at this point), just the SQL server tuning / maintenance stuff (itself). This project is an "extra" one I'm helping out on at this point.
They did wrap much of the (unnecessary) complexity into views and some stored procedures, but much of the original SQL is embedded within various helper apps that are scattered about which I don't have any influence on yet. 1/3 to 1/2 of the tables aren't normalized correctly for what they use the data for, so I plan on fixing that if I get that far before it's taken off of my plate. 🙂
June 9, 2009 at 10:25 pm
You can use Profiler to capture the queries being executed by the application.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 10, 2009 at 9:01 am
I could get into that, and then get stonewalled with the "freeze" (aka "don't change *anything*"). There's just too many people that have to buyoff on *every* change.
Right now we're just doing low hanging fruit (using the info from dm_db_missing_index* to identify key tables that need indexes) to remove as much latency where (simply and easily) possible. Hence the Hardware upgrade.
I'm on a good roll...already made a couple of changes that people are noticing (the positive effects) so I should have more buyoff this next time around. After these indexes I'm going to push for refactoring the primary stored procedures so we have a more systematic and efficient drop in replacement for the inconsistent ad-doc queries used in the various apps (when we get to that).
At least the table complexity is *mostly* hid from the apps by views.
On that note: is it *really* worth creating indexes on views (performance wise)? I've read that it is a good strategy, but I was targeting the lowest part of the stack first (tables) before trying to make adjustments higher. Should I do both at the same time?
The goal here isn't really to make it perform as fast as possible, but first to make as few changes as possible to remove delay and enhance efficiency, and then setup a plan to redo what needs it.
June 10, 2009 at 9:29 am
chadowens777 (6/10/2009)
Right now we're just doing low hanging fruit (using the info from dm_db_missing_index* to identify key tables that need indexes) to remove as much latency where (simply and easily) possible.
Careful. The suggestions in there are just that, suggestions. The logic that goes into producing those is far less than what even DTA does, the suggestions are often too wide, don't take into account other indexes that exist or other suggestions made. Use it as a starting point, but don't just apply the indexes that it suggests.
The goal here isn't really to make it perform as fast as possible, but first to make as few changes as possible to remove delay and enhance efficiency, and then setup a plan to redo what needs it.
Seriously, follow the method in those articles I posted. That's what I do when I'm doing performance tuning work for clients.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2009 at 10:29 am
Yeah, I'm starting with about 8-9 indexes across the whole system, which *should* impact about 30-40% of the activity (conservative estimate), which *hopefully* will make the rest of it faster (having the current load thrashing reduced).
The suggestions (from dm_db_missing_index*) taken together are currently around 25 indexes (if you ignore all the low frequency stuff).
Thank you for the warning, I will take it to heart... and I'll look at your tuning method again (I briefly went over it because I'm trying to limit how much time I spend on this).
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply