October 11, 2010 at 1:00 pm
my problem is right there,when i clean the buffer on both server the first run take 1.5min in both of them.
but on the second run without the buffer clean the new server run the query in 1 sec while the old server run it 1.5 min again like he did in the first place(and i'm not clearing the buffer).
October 11, 2010 at 3:30 pm
Apologies , ive lead on quite a bad steer here.
I originally read your query as
where ltrim(rtrim(Colname)) like @param
its not its
where colname like ltrim(rtrim(@param).
I am assuming that you have an index on PARTNAME , is that fair ?
Im not 100% sure how sqlserver estimates rows using LIKE but i will research that.
In the mean time though you could use a plan guide using the literal value and apply that.
October 11, 2010 at 4:37 pm
Don't use the dbcc commands unless you really need to
Thats going to change your plan every time.
Run through sp_executesql and don't do the dbcc freeproccache and rerun it takes less time bcoz only for the first time it generates plan as it is a storeproc.
dbcc freeproccache is a plancache.
I guess helpful!
October 12, 2010 at 8:04 am
Your problem is caused by how SQL server estimates the number of rows for a statement like
select ... from PART where PARTNAME LIKE @p
In the posted execution plan you can see that the estimated number of rows for the clustered index scan is 955199 while the actual rows is 9.
This bad estimate means that SQL server selects a table scan instead of an index seek with its corresponding bookmark lookup. It is trying to avoid making over 900k bookmark lookups.
There are two things you can do to fix the problem:
1) Make sure the index on PARTNAME is covering by including all columns used in the query in the index.
2) Use a plan guide with OPTIMIZE FOR to make sure SQL server creates a query plan optimized for a typical value.
Creating a covering index is very easy. The main drawback is that the index will take up a lot of space and it will also slow down updates and inserts to the table somewhat.
Creating a plan guide is more complex. You can read more about plan guides at:
http://msdn.microsoft.com/en-us/library/ms190417.aspx
http://msdn.microsoft.com/en-us/library/ms188255.aspx
Of course there are also drawbacks with plan guides. Fer example you might have to create several different plan guides to cover different variations of the slow statements, and you might have to update your plan guides when the vendor releasees a new version of the application.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply