| Author |
Topic |
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
 Posted - 2007-05-22 : 13:09:15
|
| Anyone using the ONLINE=ON option on large DB's? We have a db of 5 GB and we are doing some load testing for SQL 2005. We are modifying the Index scripts for the upgrade. We will run a load with the ONLINE=ON option but just wanted to find out if anyone already is doing it on a similar scale db and has seen any issues? Also, we have auto-update stats off at the DB level. Does setting the ONLINE=ON require turning this auto-update stats to ON too? I didnt see anything to that effect in BOL, so was wondering.Thanks for any feedback.Dinakar NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-22 : 13:21:04
|
| 5GB is smallish. I'd say a large database starts around 50-100GB.When you say ONLINE=ON, are you referring to ALTER INDEX? If so, then yes we run it that way on our databases which are all 5GB or much larger.Why would you have auto-update stats off at the database level? They recommend having this turned on, plus auto-create, plus a job that does update stats.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-05-22 : 13:33:59
|
Yes, I was talking about Indexes  We had auto-update stats on initially when our db was small but we started having probs later. In fact, MS recommended we turn it off. Ours is an OLTP db and we have too many transactions per sec. We were seeing some IO related 17883 errors. We do update the stats manually during our weekly maintenance window. Most large systems I have seen or people working with large systems I know have it turned off. Dinakar NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-05-22 : 13:34:54
|
| Even our Create index scripts have STATS_NORECOMPUTE clause...Dinakar NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-22 : 13:39:36
|
| Do you have any articles about the auto-update stats? The information that I have comes from sql-server-performance.comTara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-22 : 14:21:39
|
| Thanks. I wish the articles would give more details as to how to determine if we should turn the option off. Perhaps we'd have to run SQL Profiler for lengthy amounts of time to determine when it runs and then check if queries became slower during that time.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-05-22 : 14:47:27
|
| AFAIK, there is some computation as to when the auto-updattion kicks in..like if the rows changed by 200 or 300 (or some number like that or some percentage based), SQL Server jumps in, attempts to lock the table and do an update stats which can interfere with regular transactions. Another Q/A I found:---Q I have noticed that at times when there is a heavy load on my server, some stored procedures' execution plans go awry. One of the first things I check when I notice timeouts is whether statistics are current for tables accessed by the stored procedure that times out.I have also noticed that although a table may have exceeded the threshold that causes auto statistics to update (20 percent of the rows plus 500 have changed), the update wasn't done and therefore I had to do it manually to resolve these timeouts. Could it be that SQL Server did not update the statistics because the server was too busy at the time?I'm using SQL Server 2000 and the database options for auto statistics update/create are turned on. The database is around 60GB in size and some of the tables involved have roughly 30 million rows.If I know which stored procedure inserts data into these tables, would it be acceptable to include "update statistics <table name>" in that stored procedure after I insert the rows? When I run the statistics update manually it takes one to two seconds.A Auto-updating of statistics is not throttled (auto-create is, however). But there are still plenty of reasons why an auto-update might fail. In a heavy load scenario it is most likely that the auto-update can't acquire the required locks. Then it would fail because auto-update never waits for locks; instead it fails immediately if it doesn't get the locks it needs.If you know the statistics entries you want to refresh and if the overhead of one to two seconds is acceptable, there is nothing wrong with a manual statistics refresh inside a stored procedure. However, the syntax you mentioned will refresh all statistics defined on this table, which might be prohibitively expensive.Also note that you cannot execute a manual statistics refresh inside an active user transaction. This will fail with error 226: command not allowed within multi-statement transaction.The auto stats trace event only logs successful auto-updates, but trace flag 8721 generates a bit more information. You get an entry in the error log for any update stats attempted (not just the successes). But auto-update stats is set oriented. The trace output will tell you how many stats entries on a table were refreshed (x >= 0), but it doesn't tell you how many and which ones failed. If the number is 0, you know something failed, but you don't know how many or which ones.-------------Dinakar NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
Online Index on large DB (> 4GB)