How can I get my query to change the Indexes it uses?
-
MS SQL Server 2000 SP3
I'm not the most knowledgable DBA, I've had to learn almost completely on my own, AND on a production server, because it's the only MS SQL Server I have access to.
Everything was fine.
Then I took down the production server for maintenance. Someone suggested that I re-index my tables because I was having some performance issues with a particularly large table (it didn't help that table btw), so I did re-index.
Now, Everything works wonderfully, except for the performance issue mentioned AND one other thing that is going horribly wrong.
Here is the table:
create table ABMcontactlink
(
classifier varchar(20) not null, /* Classification of contact. */
transmitter varchar(36) not null,
contact integer not null, /* Link to ABMcontact (detail) table */
primary key (classifier,transmitter,contact),
foreign key (contact) references ABMcontacts(identifier),
group_name varchar(20) null,
priority smallint null, /* Authorization level. */
type smallint null, /* Autoalarm or Manual */
last_modification_date datetime, /* Date/time record last touched */
last_modification_id varchar(40) /* Who last touched record */
)
go
create index IndexABMcontactlink on
ABMcontactlink(classifier,transmitter)
go
create index CandidateABMcontactlink on
ABMcontactlink(transmitter)
go
As you can see, I have the primary key, which creates a clustered index, PK_ABMContactlink_Some Number
Now, this is a very busy production database, and most quick short queries benefit more from CandidateABMContactlink than from the other two indexes.
Unfortunately, in this production system, and this table, seconds count ALOT, so when I have roughly 3000-4000 quereies an hour pulling information from this table, I personally beleive I need to keep CandidateABMContactlink, and I'm not willing to find out on a production server.
** Now to the Problem at Hand **
I have one query that kicks off about 7 times a day, used to take less than 1 minute before the re-index. NOW it takes 30 Minutes. And it drags the system to a crawl.
I did some looking into it, and this query is using CandidateABMContactlink, and it takes 30 minutes. If it uses PK_Abmcontactlink it finishes in under 45 seconds.
Most queries are simple, "Select Column_names from abmcontacts where identifier in (select contact from abmcontactlink where transmitter = 'XXXXXX')"
This one is:
select * from ABMcontacts where (
(last_modification_date >= '2006-04-28 04:40:03' and last_modification_date <= '2006-05-09 16:41:14')
and EXISTS(select contact from ABMcontactlink where contact = identifier
and EXISTS(select transmitter_id from ABMtransmitter where transmitter_id = transmitter and (dealer = 'XXXX'))))
or
(EXISTS(select contact from ABMcontactlink where
(last_modification_date >= '2006-04-28 04:40:03' and last_modification_date <= '2006-05-09 16:41:14')
and contact = identifier and EXISTS(select transmitter_id from ABMtransmitter where transmitter_id = transmitter and (dealer = 'XXXX'))))
I can't change the query, so how do I make it use the Index I want it to use without removing the index that it is using? (I know there are much better ways to write the above query, I'm not the culprit, if I could re-write it, I would)
-
You can try the 'WITH INDEX (index_id)' table option. More info can be found in BOL under search item 'FROM' clause (described).
Another option you might light to try is to use the 'WITH FORCEORDER' option. Simply append this string to the end of you T-SQL statement and see if it works for you.
-
I think the "With Index()" Option will help~
-
I would run it through the profiler and then run it through the index tuning wizard. You then may be able to see why it is using the wrong indexes. If the app or query is written properly and you have the proper keys and indexes you should not have to specify a with index option.
i went through a similiar scenario not to long ago, the profiler showed me that the app was hitting the wrong indexes and we had to re-apply an older index until the code could be changed to use the primary key instead of the index in the queries.
-
oops Also, did you update the statistics on these tables after reindexing?
If this is coming from a stored proc, I would also do a sp_recompile on the table as well.
-
Here's my 'short list' for tuning:
Round I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round III
Profiler
Query Hints
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Mike, that's what I did. Before I posted, I had already run a trace with profiler, found the problem query, determined the execution plan in Query Analyzer, and noticed it was using an Index that didn't "fit" what the query was looking for, in this case, the PK index was perfectly suited.
So I used the "With" clause to force it to use the PK Index and I was right, it took seconds instead of 30 minutes (all of which is in my original post).
Unfortunately, I can change the query in Query Analyzer to test, but I can't change it in the application, so "WITH" couldn't solve my problem, it helped in determining the solution though.
Anyway, after about 2 hours after posting, I an MS article on the Index Tuning Wizard, never used it before, and it solved the problem.
Said the best index to use was the PK Clustered Index, but strangely enough it then added a FOURTH index and used that one instead. Not sure why, but it accomplished the same thing. (Think it was an indexed view instead though, which kind of makes sense).
Thank you all for the replies, if I hadn't found the answer last night, I definitely would have found it this morning here
EDIT: Rudy, any idea how long that would take on a 4Gig DB?
And, no, I didn't update statistics after the re-index, So I'm sure all of my execution plans are off a little. I do have auto-update turned on though, so I assumed that would do the same thing, is that wrong?
-
My roughly 4GB (probably more) prod instance, takes about 10 minutes to run those tasks.
Tuning Wizard is a good tool, be careful with selecting to add indexed views though, as I have had issues where it added them to all kind of stuff and totally screwed perfromance. I never check that add indexed views box when i run it and only save them out to script.
I have also seen it recomending adding a index with every key in the table, which you really dont want. so I do not always trust it.
Having to add the fourth index is more than likely a result of the way the query is written in the code.
MM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply