April 12, 2010 at 12:50 pm
I've got a stored proc that performs a query that, among other things, does approximately 20 different joins on the same table via the clustered index. In most instances that I've run the proc against, I get sub-second response and the query uses a clustered index seek on this one particular table with a million rows in it.
In one instance it chooses to do a clustered index scan for no reason that I can discern. Unfortunately, that one instance is on a very annoyed client site.
The indexes were rebuilt last night, are not fragmented and the statistics are up to date. Additionally, I've forced a recompile of the proc a couple of times.
Are there any other scenarios in which it might choose to do a scan instead of a seek that anyone can think of?
The query is a nightmare with 95 some odd joins not counting the 20 some odd sub-query joins but the primary difference in the query plans between the one's that run quickly and the one that runs slow is the clustered index scan against this one table.
"Beliefs" get in the way of learning.
April 12, 2010 at 12:55 pm
Robert Frasca (4/12/2010)
The indexes were rebuilt last night, are not fragmented and the statistics are up to date. Additionally, I've forced a recompile of the proc a couple of times.
Did that fix the query in that one instance?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 12, 2010 at 1:11 pm
No, the problem continues to exist only on the one server. It works fine everywhere else I've tried it including on our dedicated volume testing environment where the data sets are even larger than for this client. I'm starting to think that the problem exists elsewhere in the query but this is how it's manifesting itself.
I have some index additions that I've been testing on some of the other tables touched by the query and my thought was that tweaking another table might stimulate the optimizer into trying a different path. However, my management is hesitant to put these index changes into the field for fear something odd, like this, will happen. Since I can't re-create the problem I can't say as I blame them. Unfortunately, I'm at a loss as to what to try next.
"Beliefs" get in the way of learning.
April 12, 2010 at 3:36 pm
Are you sure that you have exactly same schema on this database and others? It maybe that lack of an index on other table triggers scan on the big one. Is it possible that you have some implicit conversion somewhere?
We had also an interesting case that removing a column from the SELECT clause sped up the query from 4.5 minutes to 9 seconds. The column was a case statement returning some strings depending on other, shorter string values taken from one of the tables in the query. I suppose the poor performance was caused by memory reallocations.
...and your only reply is slàinte mhath
April 12, 2010 at 8:42 pm
Are all instances running the same version of SQL server (same patch level)?
I have seen where the patch level has an impact on query performance - very similar description as you have provided.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 13, 2010 at 6:49 am
Piotr.Rodak (4/12/2010)
Are you sure that you have exactly same schema on this database and others? It maybe that lack of an index on other table triggers scan on the big one. Is it possible that you have some implicit conversion somewhere?We had also an interesting case that removing a column from the SELECT clause sped up the query from 4.5 minutes to 9 seconds. The column was a case statement returning some strings depending on other, shorter string values taken from one of the tables in the query. I suppose the poor performance was caused by memory reallocations.
Theoretically, I'm sure of that. It's the latest GA release of our product. That being said, I have my suspicions that an error might have occurred during the upgrade and wasn't caught. I will try to get a copy of their database and compare schemas if I don't resolve this soon.
"Beliefs" get in the way of learning.
April 13, 2010 at 6:54 am
CirquedeSQLeil (4/12/2010)
Are all instances running the same version of SQL server (same patch level)?I have seen where the patch level has an impact on query performance - very similar description as you have provided.
Yes, they're all running on 9.0.4035. I've actually brought some of our clients up to sp 3, cu 6 to resolve some issues but, in this case, all of the machines I've tested on were 9.0.4035. (We have clients still running SQL Server 2000, most on SQL Server 2005 and a few on SQL Server 2008 so it's quite a mix.)
"Beliefs" get in the way of learning.
April 13, 2010 at 12:20 pm
Can you post query plans that we can look at (actuals in *.sqlplan preferred)? There probably a statistics/rowcount difference that is leading it astray.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 13, 2010 at 12:37 pm
Well, I just learned that it apparently WAS a statistics issue. The local dba ran an UPDATE STATISTICS TableA WITH FULLSCAN and the problem went away. He specifically said that a "vanilla" UPDATE STATISTICS without the fullscan qualifier didn't work. Since the index statistics had already been rebuilt with the indexes earlier in the night I didn't think statistics would be a problem. There aren't any "custom" column indexes on that (or any other) table. I guess I'm going to need to rethink my maintenance plans to include fullscan stat updates. I'm not particularly happy about it but until I can figure out the underlying cause, I'm stuck.
I'm still not sure I understand why it's happening on their site and no one elses though. Probably some data anomalies that don't exist on the other sites.
"Beliefs" get in the way of learning.
April 13, 2010 at 5:01 pm
Thanks for updating us on the situation.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply