December 14, 2011 at 7:10 am
OK, I need to make some recommendations for improvement, and ran a missing index script, and it came up with the following suggestion. However, I'm getting pushback, as I'll explain below. This missing index indicates it has a 99% avg user impact/improvement. How reliable is this DMV is the first question.
CREATE INDEX [missing_index_xxx_xxx_ATable] ON [SERVER].[DBO].[ATable] ([AtID]) INCLUDE ([ID], [Active], [Details], [UserID], [TimeStamp], [Request], [Record], [ExecutionTime], [SessionIdentifier])
Given the above, looks like a broad covering index based on the usage of queries. But given that some of the columns here are LOB's does this make sense as a proper recommendation? A large portion and size of this TB table, about 1/2 is LOB, and saying no point to build the above index. It was suggested just to do an index on ATable, Timestamp desc.
As I understand it, a NC idx can include LOB values as included cols where data storage is exactly the same for data records.
Does any one have thoughts with this?
TIA
December 14, 2011 at 7:19 am
December 14, 2011 at 7:23 am
Thx, being at a client with strict firewall rules (no youtube), could you possibly summarize, or point to a text/html link 🙂 Thanks!
December 14, 2011 at 7:26 am
Sure, if you had listened to those videos you wouldn't ask the question. :hehe: (how's that for summary).
There are 3 hours of video for a reason. This is not something we can do over forums.
As for this specific issue, I think you're going backwards into this. Performance is not about shotgun approach to see what sticks, not in TB db anyways.
Go back to the videos and then this to find your real pain queries :
December 14, 2011 at 7:34 am
Wow - 3hours of video? Come on, I asked an opinion on a specific question. Why are you suggesting I sit to watch 3hours of video? Thx
December 14, 2011 at 7:41 am
RSP (12/14/2011)
Wow - 3hours of video? Come on, I asked an opinion on a specific question. Why are you suggesting I sit to watch 3hours of video? Thx
There's a plethora of reasons for it!
Your question is far from simple. It takes concise people books to tell you all the reasons why <not to> do what you think you should<n't> be doing.
If you had watched the videos you'd know what to do and what not to do on your own and why, not just what some goof on the internets is telling you.
P.S. the 2nd set of links should be accessible now and will help you a lot as well.
All links from 1 MCMs / 2 mvps.
December 14, 2011 at 7:41 am
LOB's? No, that index doesn't make that much sense.
There are a couple of fundamental problems with missing index recommendations. First, they're based on the stats available to them, so if the stats are skewed or out of date, the recommendations are going to be weak. Second, there is no direct correlation between the missing index DMVs and actual queries. Maybe that index will save you tons of time for the query that called it, but that query gets called once a week and only takes 60 seconds to complete. Who cares. You need to know what you're tuning.
Best way to be sure about the recommendations though is to test them. Simply evaluating them based on whether they "make sense" or not is at best a guess.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 14, 2011 at 8:01 am
Thanks, Grant! This makes sense, and no I don't consider you a goof from the internet, as the previous poster suggested 😉
<<Maybe that index will save you tons of time for the query that called it, but that query gets called once a week and only takes 60 seconds to complete>> True indeed, but I think its much more frequent.
The correlation part is tricky, but I did see a recurring top query and most executed from a sem-reliable sample (as the server was rebooted a week ago - so about a week)
I'll dig a little deeper here. THX
December 14, 2011 at 8:06 am
RSP (12/14/2011)
Thanks, Grant! This makes sense, and no I don't consider you a goof from the internet, as the previous poster suggested 😉<<Maybe that index will save you tons of time for the query that called it, but that query gets called once a week and only takes 60 seconds to complete>> True indeed, but I think its much more frequent.
The correlation part is tricky, but I did see a recurring top query and most executed from a sem-reliable sample (as the server was rebooted a week ago - so about a week)
I'll dig a little deeper here. THX
Ah, but I am a goof from the internet. Just a goof with a track record of being right about 50.00000000000000001% of the time (might be exagerating there). Ninja was pointing you at some good info.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 14, 2011 at 8:10 am
My reference to goof of the internet was for me, or anybody else for that matter.
Even with MVPS I retest their claims to make sure
#1 I understand what they mean and can use them correctly
#2 They are actually correct. Everybody makes mistakes, no exceptions.
The point is that I'M responsible for what I do in my systems. I can't go back to my client and tell them to sue the dude on the net that told me to do this. I must be accountable.
So as much as I know I'm right in this index question of yours, there's nothing like YOU becoming that sure (and with reason) about yourself.
Hence watch the videos when you get a chance. 😛
December 14, 2011 at 8:10 am
Grant Fritchey (12/14/2011)
RSP (12/14/2011)
Thanks, Grant! This makes sense, and no I don't consider you a goof from the internet, as the previous poster suggested 😉<<Maybe that index will save you tons of time for the query that called it, but that query gets called once a week and only takes 60 seconds to complete>> True indeed, but I think its much more frequent.
The correlation part is tricky, but I did see a recurring top query and most executed from a sem-reliable sample (as the server was rebooted a week ago - so about a week)
I'll dig a little deeper here. THX
Ah, but I am a goof from the internet. Just a goof with a track record of being right about 50.00000000000000001% of the time (might be exagerating there). Ninja was pointing you at some good info.
He wears a kilt (in public, on a stage while speaking at sql events). 'nough said :w00t:.
December 14, 2011 at 8:16 am
I was of course joking, and it is quite funny how Grant immediately replied after the "goof" comment. I am sure there is great value to the videos, just not what I expected. I mean, I can point you to a great sysinternals book, that if you read cover-to-cover, I'm sure you'll find an answer 🙂
Thx!
December 14, 2011 at 8:21 am
RSP (12/14/2011)
I was of course joking, and it is quite funny how Grant immediately replied after the "goof" comment. I am sure there is great value to the videos, just not what I expected. I mean, I can point you to a great sysinternals book, that if you read cover-to-cover, I'm sure you'll find an answer 🙂Thx!
A book takes a month to digest. Those can be watched back to back to back at night or on a saturday (quite funny as well as informative).
There are 3 levels, beginner, intermediate and advanced.
You need the first 2 (even if light "reading") to really understand what's really going on in the 3rd.
Alternatively I could find you 4-5 good books on that topic. :hehe:
December 14, 2011 at 9:00 am
RSP (12/14/2011)
This missing index indicates it has a 99% avg user impact/improvement. How reliable is this DMV is the first question.
Hi Robert,
It helps to know a little bit about where these recommendations come from, and what the numbers mean. The first thing to say is that the missing index DMV information will generally be of a much lower quality than recommendations that come out of a properly configured DTA (Database Tuning Advisor) session. DTA has a broader range of tools open to it, and can base its recommendations on a representative workload, and can take its time over it.
On the other hand, the missing index DMVs are populated directly by the query optimizer during cost-based optimization of a single batch, if it is convenient to do so. During the index analysis and matching phase, the optimizer is looking to match predicates from the query to existing indexes. The predicates aren't necessarily the same as those found in the SQL text, since previous stages like parsing and simplification will have transformed the written requirement into an internal form that makes it easier to reason about the options, and takes into account things like constraints, foreign key relationships, the domain of possible values for a column and so on.
So, during index matching, the optimizer often has a list of seek equality/inequality predicates, as well as other columns needed for the query (but not part of any filter). When looking to match these requirements against available indexes, it may notice that an 'ideal' index (in terms of the seek and include columns just mentioned) does not exist. If so, it can perform a quick calculation to roughly estimate the plan cost benefit such an index might have had for the current query, at the current early stage of optimization. This whole process is completely opportunistic; it's a handy by-product that may arise out of normal optimization, provided as a convenience for DBAs. Whether missing index information is generated or not depends highly on the particular plan, estimated cost, and the code path the optimizer follows when performing index analysis and matching.
In your particular case, it seems the optimizer saw a query with a particularly high estimated cost, that contained an equality predicate on the AtID column. The same query also either used or returned the remaining INCLUDEd columns somewhere along the line. This suggests that the current database either does not have an index seekable on AtID, or that the existing index does not include *all* the columns needed for the query. The missing index feature will often 'suggest' indexes that just add a few INCLUDE columns to an existing index definition, simply to avoid a lookup operator in the plan.
Given the above, looks like a broad covering index based on the usage of queries. But given that some of the columns here are LOB's does this make sense as a proper recommendation? A large portion and size of this TB table, about 1/2 is LOB, and saying no point to build the above index. It was suggested just to do an index on ATable, Timestamp desc.
It's highly likely that the specific index suggested is not what you need. It would be wrong of me to say that it is never worth creating an index (particularly a filtered one) that includes LOB columns, but you would need to have a very clear justification for doing so. As I mentioned previously, running a representative workload through DTA can produce much higher-quality recommendations that are then worth evaluating in detail. This is very often a much better starting point than even the best human DBAs can manage.
The contents of the missing index DMVs require a much larger pinch of salt: I view them as vague clues that the optimizer would have liked something to be different, but it's a long road from there to nailing down an index that it would make sense to apply to a live system. The DMVs can be educational in terms of learning to think a bit like the optimizer - but I would caution against taking them at all literally. If you often see many similar entries in the DMVs (perhaps just differing in included columns) that may be a stronger hint that a potentially useful index is missing.
Paul
December 14, 2011 at 10:11 am
In the missing index report there is a column called 'user_seeks' from the view sys.dm_db_missing_index_group_stats.
I have seen significant improvement only if the number of user_seeks is also high. Check that column to ensure that the index being recommended will have higher number of user_seeks.
As previously suggested I do not create those indexes that might have high % improvement but low user_seeks.
Blog
http://saveadba.blogspot.com/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply