| Author |
Topic |
|
terry.stevens
Starting Member
6 Posts |
 Posted - 2008-12-01 : 13:47:49
|
| Hi all - I hope it's appropriate to ask this question here; I need help and I'm at my wits' end. A couple of months ago, we upgraded a SQL Server 2000 instance to a SQL Server 2005 Enterprise Edition on a two-node active/passive cluster.Since that time, we've had a lot of trouble out of one application that uses stored procedures to query five tables (four in one database, one in another). Ordinarily, this query works fine. Since we upgraded, however, we've been getting strange timeouts on this application.The indexes had been defragmented and the statistics were updated on the Saturday beforehand (the problem typically appears on Monday mornings, though not exclusively). When the issue occurred, I checked the index fragmentation - in one case, the indexes were about 20% fragmented, but usually they are not fragmented at all.Still, in the early going, I attempted to defrag the indexes; no luck. Rebuilding the indexes resolved the issue, though, which led me to suspect the statistics.Sure enough, updating the statistics on one of the tables resolves the issue.Flash forward a bit. I had created a job to update the statistics on these tables daily, at 4am. Even still, we'd still see the error later in the day, usually around 9-10am. What I need to do is figure out what the root problem is and then develop a plan to fix it. Currently I'm suspecting that it's an issue with the 2005 version of Auto Stats, or some such. But I'd appreciate ANY help anyone can give, as this problem has caused a ton of headaches. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-01 : 13:56:41
|
| 1) Rebuilding index will update statistics as well so you don't need to do that.2)Is your AUTO stats DB option turned on?3) Do you have heavy DML operation in morning?4)You can't defrag Heap tables.5) sp_updatestats will update only needed one6) What edition? 32-bit or 64-bit? |
 |
|
|
terry.stevens
Starting Member
6 Posts |
Posted - 2008-12-01 : 14:04:37
|
| Thanks for the reply, sodeep!1) Right, but that's how I discovered it was an issue with the statistics - since the rebuild resolved the issue and the defrag did not2) Yes, it is on - I'm considering modifying the auto statistics to use the async option3) Having difficulty tracking this one down, as we require permission to run a SQL trace. It doesn't appear there is that much going on; the table in question only has about 300k rows4) not an issue5) gotcha5) 32-bit |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-01 : 17:19:27
|
| Can you compare execution plan of SP before updating stats and after stats are update? Save and check exactly what difference do you see? If there is difference then you have problem with cache or parameter sniffing. |
|
|
|
terry.stevens
Starting Member
6 Posts |
Posted - 2008-12-02 : 13:49:36
|
| The issue occurred this morning. I saved the execution plans immediately before and after I updated the statistics and it and both plans are identical. |
|
|
|
terry.stevens
Starting Member
6 Posts |
Posted - 2008-12-02 : 14:42:43
|
| My working theory now is that we need to enable asynchronous auto statistics updates. That seems to be the likely culprit. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
terry.stevens
Starting Member
6 Posts |
Posted - 2008-12-04 : 09:11:59
|
| Thanks, tikizer - I'm working to get approval to implement the async auto statistics now. I'll update if and when we discover how it works out. |
|
|
|
terry.stevens
Starting Member
6 Posts |
Posted - 2009-10-05 : 09:31:05
|
| Well, sorry to bump old threads, but we continue to see this issue. I've enabled auto update statistics, and we still have the problems. Even with the auto statistics update, we have to update statistics manually. Even so, we sometimes experience the issue again within two hours, sometimes within an hour of the last statistics update.Anybody have any insight? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Statistics issue in SQL Server 2005