Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2006-01-05 : 21:11:16
|
| I got a surprise this week. I ran a procedure which added a new customer ID. One table in particular got populated with about 10,000 rows of customer assignments.Thought I'd check out the results and logged into the web-based admin interface. To my complete surprise, the pages were loading in 20 seconds, not the usual (less than 1 sec).Sensing database trouble, I doned my cape and flew into diagnostic mode. The execution plan on the reporting procs was FINE, yet the query crawled in QA.On a lark, I tried logging in as another customer, the report ran in under a second. Heyyyyyy, wait a minute. |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-06 : 05:30:37
|
| Was the database being extended to make space for the 10,000 rows? That takes surprisingly long on our boxes, particularly if the Extension is set to the default 10% and the database is now up in the Gigabytes ...Kristen |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2006-01-06 : 08:13:06
|
| My take is that the "new" value for the 10,000 rows wasn't reflected in the statistics. After updating the statistics on the table, the procedure ran in under 1 second as it did for all other clients.I'm more than a little surprised about this. For the short term, I've added an UPDATE STATISTICS command after the line that does the insert. Seems to me that SQL ought to update statistics automatically after an insert, maybe not.Has anyone had a similar experience? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-06 : 09:26:12
|
| The Sprocs that reference the table are going to use their old, boring, cached query plan though, aren't they? ... until they get recompiled or some other influence sorts it out (query plan pushed out of memory, stop/start SQL <He!He!He!>)Kristen |
|
|
|
Sharky
Starting Member
23 Posts |
Posted - 2006-01-06 : 10:00:11
|
| Hi GuysWe recently had a similar problem. Query was running unsually long and we couldn't explain it. Updated stats on the table and presto back to normal. My colleague posted this on another forum: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=12219Seems like updateing stats manually on high transactional tables might be a good idea, even with auto update stats on. Any input would be appreciated.Leigh |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2006-01-06 : 11:10:27
|
| Strange: AUTO UPDATE STATISTICS is enabled on this database.Also strange, the execution plan was excellent, and identical for either client ID, but one ran in 20 seconds until STATS were UPDATED (manually).Go figure. |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-01-06 : 13:25:42
|
| SQL Server will not automatically update the statistics until the number of modifications on the table hit about 30% of the total number of rows in the table (That is stretching my memory, though). There is an article on MS about it, I will post it in a few secs. In the meantime, you can watch the auto-statistics update event in profiler to see how often auto-update gets kicked off. Normally, it should not be much in a day.EDIT: Ahh. I knew I had it about somewhere:http://support.microsoft.com/kb/195565/EN-US/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-06 : 15:30:59
|
| FWIW we update Stats on all tables every day - using FULL SCAN just to be sure. (Mind you, our databases are < 5GB)Kristen |
|
|
|
|
|
|
|
|
Update Stats. Update Stats! UPDATE STATS!