December 21, 2012 at 12:44 am
stryk (12/21/2012)
But they ran the Index Rebuild on that table which seems to help a lot! So, for now we will schedule this maintenance daily ...
No, don't. Run an Update Statistics on that table daily.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2012 at 12:48 am
We already have a complete statistic update up running every day ...
Jörg A. Stryk
MVP - MS Dynamics NAV
December 21, 2012 at 12:57 am
stryk (12/21/2012)
We already have a complete statistic update up running every day ...
Fullscan or sampled?
Index rebuilds seldom fix performance problems. The stats updates they do however fix many problem
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2012 at 2:35 am
We do a Full Scan - using the MP - every weekend (because we have plenty of time to do this); every day we just do a sp_updatestats (because we don't have the time for a full run) plus a sp_createstats 'indexonlx' ...
But in case of this special table I wonder how these stats should get "outdated"- the affected "Customer" table is just master data, so once a record was inserted (sequentially, ordered by that "No_") there are not many changes ... for the same reason IMHO "fragmentation" should not be a problem here ... especially not on the "Clustered Index" ...
It seems like this query was fired from a NAV GUI (so called "Form") and it was declaring a "Fast Forward Cursor" to load all that data ... I don't no for sure how big that table currently is, so I could guess "memory pressure" might be the problem ... but if that's the case: why does this problem not happen ALL the times? It's still the same 16GB of RAM (SQL Server Max. 14GB) ...
The more I think about it, the more I have to scratch my head ...
I'm afraid we are stalled here now, since my customer just went to X-Mas vacation.
I almost hope this problem returns :Whistling:, because I'm really curious about the QEP and to find out what happens here ...
Jörg A. Stryk
MVP - MS Dynamics NAV
December 21, 2012 at 3:42 am
stryk (12/21/2012)
We do a Full Scan - using the MP - every weekend (because we have plenty of time to do this); every day we just do a sp_updatestats (because we don't have the time for a full run) plus a sp_createstats 'indexonlx' ...
So full scan on the weekend, sampled during the week. I would suggest this table gets a full scan daily.
If an index rebuild fixed the problem, 99% chance it's either stale stats or a bad exec plan getting into cache somehow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2012 at 4:16 am
OK, I'll try that! Thanks for the advice!
Jörg A. Stryk
MVP - MS Dynamics NAV
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply