Execution Plans
-
Please help. 2 identical queries on identical databases (one restored from a backup of the other) on different servers. One returns instantaneously the other takes over a minute. All other queries run fine on both servers. The slow query is doing a clustered index scan, the quick one is only doing an index seek.
-
Hi pbb001,
quote:
Please help. 2 identical queries on identical databases (one restored from a backup of the other) on different servers. One returns instantaneously the other takes over a minute. All other queries run fine on both servers. The slow query is doing a clustered index scan, the quick one is only doing an index seek.now what is your question?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Frank.
Apologies for this but I'm on a very steep learning curve at the moment and this is my first entry on this site.
The question is why doesn't this query execute in the same way when it's run against identical databases (especially as this problem is unique to this one query which is simply doing a select against one particular column in a table)?
-
quote:
The question is why doesn't this query execute in the same way when it's run against identical databases (especially as this problem is unique to this one query which is simply doing a select against one particular column in a table)?have you verified this by looking at the execution plans?
I think there are a lot of things to take into consideration.
- table structure
- indexes
- # of rows in table
- ...
Wait a moment! Just reread you post.
A SELECT <one_field> FROM table?
Can you post the DDL and the SQL statement?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Here is the statement:
SELECT cheque_amount FROM attributes
WHERE cheque_amount = '100.00'
AND object_class = 'cheque'
And here are the 2 execution plans:
Server 1 - slow query
Execution Tree
--------------
Parallelism(Gather Streams)
|--Clustered Index Scan(OBJECT:([DBNAME].[dbo].[ATTRIBUTES].[ATTRIBUTE_ID]), WHERE:([ATTRIBUTES].[CHEQUE_AMOUNT]='100.00' AND [ATTRIBUTES].[OBJECT_CLASS]='cheque'))
Server 2 - fast query
Execution Tree
--------------
Parallelism(Gather Streams)
|--Filter(WHERE:([ATTRIBUTES].[CHEQUE_AMOUNT]='100.00'))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([DBNAME].[dbo].[ATTRIBUTES]))
|--Index Seek(OBJECT:([DBNAME].[dbo].[ATTRIBUTES].[idxCheque]), SEEK:([ATTRIBUTES].[OBJECT_CLASS]='cheque') ORDERED FORWARD)
The problem lies with the cheque_amount column but this column isn't an index key for any of the indexes.
There are 1.8 million rows in the table and both the coulmns in this query are varchar.
-
If I remember right, a non-clustered index is better than a clustered for queries where
a. the index has a good selectivity ( + 90 or 95%, I think)
b. the range for data is quite narrow
This might be your case.
Does cheque_amount need to be varchar?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
If I remember right, a non-clustered index is better than a clustered for queries wherea. the index has a good selectivity ( + 90 or 95%, I think)
b. the range for data is quite narrow
This might be your case.
Does cheque_amount need to be varchar?
Frank
Not sure, if this really helps you.
Sorry, brain dead wednesday here!
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Ok. Thanks for your help anyway. What I don't understand is why SQL Server should decide to use different execution plans for such a simple query against 2 identical databases (both with identical indexes).
-
Assuming that the table, data, indexes, and SQL Server versions are identical then the most likely culprit for the difference in execution plans is the statistics.
Look at when the Stats were last updated. Chances are that when you created the new table that the Statistics were updated. The Query Optimizer uses the Statistics to assist in deciding the execution plan.
Problem I see for you is that the newer table, the one most likely with the current Statistics, is running slower. You may have to force an index hint as a last resort.
Try this query...
SELECT i.name,
STATS_DATE(i.id,i.indid)
FROM sysobjects o,
sysindexes i
WHERE o.name = 'attributes' -- table name
AND o.id = i.id
GO
"Keep Your Stick On the Ice" ..Red Green -
quote:
Ok. Thanks for your help anyway. What I don't understand is why SQL Server should decide to use different execution plans for such a simple query against 2 identical databases (both with identical indexes).well, welcome to the club!
Sometimes the Optimizer has a mind of its own and is hard, if at all, to convince
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I'm with jeffwe. I suggest doing an update statistics on both tables, then re-trying the execution plans.
Cheers,
- Mark
Cheers,
- Mark -
pbb001,
There is just one thing I cannot understand.
You tell us :
quote:
The problem lies with the cheque_amount column but this column isn't an index key for any of the indexes.There are 1.8 million rows in the table and both the coulmns in this query are varchar.
If you have no index on cheque_amount AND the object_class columns is very probably not selective at all (I guess you should have 3-4 distinct values in this columns like cheque, card, cash...)
So by having 1.8 millions of rows and having and index just on object_class normally you MUST do a table scan (if you have a clustered index, which is your case, a clustered index scan)
In your place based on this query I would define the idxCheque index like that: object_class, cheque_amount hoping that at least the amounts are selectives.
But if everybody is paying $100.00 by cheque you can forget you index. It will use a table scan.
Bye
Gabor
Bye
Gabor -
Not sure whether this has been verified, but are both SQL Servers at the same service pack level?
The query optimizer has had minor tweaks between SPs, especially where parallel queries are concerned. You could also try removing parallelism from the equation by using the optimizer hint MAXDOP 1 for this query.
Use this to check the SP level of each server:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
(Or the old-fashioned way!)
SELECT @@version
-
I would recommend doing a Index Rebuild and a statistics FULLSCAN of indexes. Without seeing the execution plan, I would be interested in the "Estimated Row Count." Is the estimated row count different between the queries? If so, then the Statistics may be out of date and/or the indexes weren't sampled enough to come up with good statistics.
Just my two cents.
Greg
-
Lots of good info in all of your posts. Sometimes these threads go astray, but everyone has some good points.
I wish I had my formula for the Optimizer with me today. I may be off in left field with this one, but I believe the number of pages in the table is used in the calculation as well. When comparing the differences you may want to run a DBCC SHOWCONTIG WITH ALL_INDEXES, TABLERESULTS and check row count, pages, density, etc.
I'm still guessing the statistics have something to do with the difference, but hard to tell from here.
pbb001, why don't you run these commands in both databases and compare what you have.
DBCC SHOW_STATISTICS ( attributes)
DBCC SHOW_STATISTICS ( attributes, idxCheque)
Something tells me the second command will produce different results.
"Keep Your Stick On the Ice" ..Red Green
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply