December 28, 2010 at 5:10 pm
tfeuz (12/28/2010)
Just a second followup --I tried to include the columns you suggested. It turns out there were already in excess of 10 columns included.
The columns currently included matched what you had -- with the exception of STKey.
I tried to include that column and got an error "you are attempting to include a column that already belongs in the index key..... a column cannot paritipate in both.....
TF
Are you sure we're both talking about the same index? I would have expected SQL Server to pick the columns from the index in question, if those are already present. Maybe one of the execution plan gurus around here can explain it....
December 28, 2010 at 5:13 pm
Ohh... I forgot: We'd still need the actual execution plan, not the estimated. There might be a significant difference... And even if not: we'd have an idea regarding data size we're talking about as well as index statistics.
December 28, 2010 at 5:21 pm
You were right -- I looked at the wrong index.
Here is the updated plan after I made your suggested change
TF
December 28, 2010 at 5:39 pm
Now we have all the columns in the index we need.
But there's still a Key Lookup. The reason is to filter out rows accoring to the result of ReceivedMailboxAck = 0 and sentmailboxfile is null.
Edit: I missed to consider the Predicate section before. I'm sorry.
Two choices:
Use this condition to build a filtered index or add it as included columns.
Based on what I see so far I'd vote for the former.
Things to consider (among others):
- update frequency for ReceivedMailboxAck and sentmailboxfile,
- frequency the query in question is used
But like I said before: I think it's time to re-evaluate the indexing strategy for this table. Based on the results and with a few more explanations here and there (edit: from our side) you should be able to evaluate all related indexes in this query and maybe rethink the indexing strategy for your tables alltogether...
December 28, 2010 at 5:58 pm
So when you are going through this process do you basically pick apart each individual query and come up with an indexing strategy for that particular query?
It sounds like each query needs its own set of indexes rather than tying to come up wil a "one size fits all" methodology
December 28, 2010 at 6:19 pm
tfeuz (12/28/2010)
So when you are going through this process do you basically pick apart each individual query and come up with an indexing strategy for that particular query?It sounds like each query needs its own set of indexes rather than tying to come up wil a "one size fits all" methodology
Errmmm... *cough* :blush: I'm cheating... :blush:
I use Jesse Roberges script[/url] to list all missing indexes and the benefit an index would provide (considering the frequency it is used). Then I verify if there are queries that can benefit from a single ("merged") index. Edit: Sometimes I have one large index with many included columns and sometimes I have several smaller indices. It all depends. I don't think there's a "golden rule". Maybe except this one: Always create as many indices as required for the best support of SELECT statements by minimizing the effect on INSERT/UPDATE as well as storage consumption.
Sometimes I even decide not to create an index if it's either too wide or not used often enough or isn't as much helpful as I'd like. It's a case by case decision.
Another important part is the countermeasure: check for unused indexes (search this site for "unused index" and you'll find several very helpful scripts and threads).
This will help you to remove unneccessary workload from your system.
I also frequently run those sprocs to see if my indexing strategy still matches the queries.
Like I said before: it's a little more complicated than just trust the DTA... 😉
December 29, 2010 at 9:52 am
Lutz,
I tried running the script by Jesse. Keeping in mind we are using SQL 2008
When I run the code against the master db - it says it creates the SP fine. But when I execute it - I get nothing back. In addition the query analyzer underlines the SP name in red and tells me it does not know anything about it.
When I try running the code against the actual db I want to analyze I get:
Msg 4104, Level 16, State 1, Procedure Util_MissingIndexes, Line 89
The multi-part identifier "dm_db_missing_index_details.object_id" could not be bound.
Can you tell me where I am going wrong?
TF
December 29, 2010 at 10:38 am
tfeuz (12/29/2010)
Lutz,I tried running the script by Jesse. Keeping in mind we are using SQL 2008
When I run the code against the master db - it says it creates the SP fine. But when I execute it - I get nothing back. In addition the query analyzer underlines the SP name in red and tells me it does not know anything about it.
When I try running the code against the actual db I want to analyze I get:
Msg 4104, Level 16, State 1, Procedure Util_MissingIndexes, Line 89
The multi-part identifier "dm_db_missing_index_details.object_id" could not be bound.
Can you tell me where I am going wrong?
TF
You need to create the sproc in the db in question. Master shouldn't return any rows since master should not be used at all (usually).
You need to run the script in your actual DB.
Are you sure you're using a SS2K8 db? Please check the database properties-> options->compatibility level. I just tested it with my SS2K8R2 test installation and it runs just fine.
December 29, 2010 at 10:58 am
I think that is the issue -- it is set to SQL SERVER 2000 (80)
Should I change it to SQL SERVER 2008 (100)?
TF
I did not install the server --- it is a cloud machine from amazon -- so there may be some other wonky settings
December 29, 2010 at 11:18 am
tfeuz (12/29/2010)
I think that is the issue -- it is set to SQL SERVER 2000 (80)Should I change it to SQL SERVER 2008 (100)?
TF
I did not install the server --- it is a cloud machine from amazon -- so there may be some other wonky settings
It depends...
Do you have a recent (verified) backup? Are we talking about a test environment totally under your control or are there any other people involved? Are there any code snippets that won't work anymore in SS2K8 (like anything depending on an TOP 100 PERCENT ... ORDER BY used in a view definition)?
Would it be possible to create a new db with the right comp level and copy the data / restore from backup?
I don't know if just changing the comp level will be a valid approach or if you'd need to go through an upgrade scenario. Hard to tell from here... (and slightly off topic, too :-D)
December 29, 2010 at 11:21 am
Thanks, you answered my question 😉
December 29, 2010 at 11:43 am
tfeuz (12/29/2010)
Thanks, you answered my question 😉
Glad I could help 😛
Seriously: as long as you're on comp level 80 (aka SQL 2000), you won't benefit from some of the nice stuff they did in between (like filtered index, as mentioned before).
I guess you need to make a decision which version you'll use. I, personally would go to SS2K8, if possible.
Regarding the deadlock issue: we still might be able to help based on the table def including all index defs as mentioned before. (I hope I didn't open Pandora's box when asking for the sql version...).
December 29, 2010 at 11:45 am
I just assumed that when I asked for a 2008 database that is what I got. -- It should be easily changed.
As for the deadlocks -- I am now running my second test and so far so good.....
Thx!
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply