May 4, 2003 at 8:46 pm
We have a table in our DB that seems to get its statistics outdated rather quickly causing our queries to take minutes to complete.
The table in question contains about 3.5 million rows with 40 odd columns and we've noticed that in some cases its only taking about 5000 row modifications (which we do in about 5 hours) to get the stats out of date (I presume they are outdated since updating stats on the table returns the response times back to the normal 2-3 seconds). We've created a job that runs every 4 hrs to update the stats so it's not a huge problem but isn't 5000 mods fairly small? Doco that I've seen says that SQL would only auto update the stats at 20% of the no. of rows in the table, approx 700,000.
One other thing - the query in question is in a stored proc and uses index hints? I'm not a developer but I've heard hints are bad except in exceptional circumstances. Could this be related to the outdated stats?
Thanks for any replies
Nigel Longmire
May 4, 2003 at 10:45 pm
Can you post the query / hints?
May 4, 2003 at 10:54 pm
Here's the query straight from profiler for those interested
INSERT INTO @ClaimLines
SELECT DISTINCT R.ClaimNo, R.ClaimLineNo, Amount = -1 * (R.FundBenefit + IsNull(R.MediBenefit, 0) - IsNull(R.ComiAmt, 0)), Refund = CONVERT(MONEY, NULL), CL.PayeeClass
FROM @ClaimLines CL
JOIN dbo.ClaimLines CL0 (INDEX = XPKClaimLines)
ON CL.ClaimNo = CL0.ClaimNo
AND CL.ClaimLineNo = CL0.ClaimLineNo
JOIN dbo.ClaimLines R (INDEX = XIF463ClaimLines) -- shouldn't need this hint, but SQLServer will not use this index otherwise!
ON R.ReprocessClaimNo = CL.ClaimNo
AND R.ReprocessClaimLineNo = CL.ClaimLineNo
JOIN dbo.Payments P
ON R.CurrentPaymentNo = P.PaymentNo
LEFT JOIN @ClaimLines CL2
ON R.ClaimNo = CL2.ClaimNo
AND R.ClaimLineNo = CL2.ClaimLineNo
WHERE CL2.ClaimNo IS NULL
AND P.PaidDate IS NOT NULL
AND CL0.RevCanDate IS NULL
May 5, 2003 at 11:43 am
SQL Server uses a sampling of the data to determine if the stats are outdated.
Index hints should be avoided, but there are times when they are very useful. I assume that if you don't use one of those hints the execution time is much longer.
I assume that you have walked through the query's execution plan to make sure that SQL Server is using the indexes. Look for any table scans, etc.
Bonne chance.
Patrick
Quand on parle du loup, on en voit la queue
May 5, 2003 at 12:21 pm
What do your indexes look like? How many col, clustered, fill factor? Maybe it only takes a couple of inserts to cause page splitting on your indexes. Enough page splitting would cause the stats to be out dated,
John Zacharkan
John Zacharkan
May 5, 2003 at 12:29 pm
if you have clustered indexes with not efficient fillfactor or without a fillfactor, that might also be the reason for slow running of your queries. As you had mentioned the update stats run every 4 hours. How much time does the update stats job take. If it not much maybe you can try to have it run every 2 hours (though its not a very efficient method). Run DBCC SHOWCONTIG to see the fragmentation levels and based on that you can decide what course of action to be taken. Hope this helps.
Good Luck.
May 5, 2003 at 2:05 pm
Another penny, move the conditions from the WHERE clause into the ON clause. The IS NULL wont' be movable, I think.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply