January 24, 2005 at 4:25 pm
We have a 15 gig database. Two tables in the database each have about 400,000 rows and are frequently joined together in queries. They're fatties with a bunch of varchar data. Occasionally, SQL Server seems to get "confused" about one of these tables. A complicated query which joins those two tables (among others), will suddenly take hundreds of times longer than it normally does. We're talking from a few seconds to several minutes. The estimated query plan will look perfectly normal, but the actual execution plan will show a clustered index look-up of a particular table returned 2 billion rows-- when the table only has about 400,000 rows (let alone how few are relevant to the clustered index seek). The big fat line coming from the table in the plan is quickly winnowed as it "realizes" its mistake. This issue can sometimes be "fixed" by rebuilding the indexes on the table. Sometimes, changing from LEFT JOINs to INNER JOINs will also make it go away. In any case the actual rows returned never changes-- and never seems to be wrong. It just seems to get "confused" and internally generates a bunch of bogus rows which it then weeds out again. It does appear to be a computational issue in some sense because the CPU spikes during these queries-- it doesn't seem to be reading the data from the disk over and over anyway.
This problem will crop up again even after its resolved with an index rebuild, but seemingly randomly. I can also post/email a screenshot of this section of the query plan for "proof" of sorts, so y'all don't think I'm nuts.
We run SQL 7 and have service pack 4 on Windows 2000 Server. We run normal DB maintenance, index rebuilds, and stats recalcs on a nightly and/or weekly basis.
Has anyone heard of this sort of thing? Any ideas?
Thanks for your time.
January 24, 2005 at 4:36 pm
You might want to try DBCC UPDATEUSAGE and sp_updatestats.
In addition you might want to run a DBCC CHECKTABLE and or DBCC CHECKDB to see if their may be any reports of errors.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply