| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2007-04-25 : 13:29:42
|
| Hi,I've deleted about 3-4 million rows from one of my tables as the data was old and no longer needed. The problem is that now queries are runnning extra slow. I am in the process of running taras isp_ALTER_INDEX however its taking quite a long time and seems to be slowing things down even further while its running as expected. (It's been running 4 hours already, I have stopped it and will rerun it a slower traffic period for the db server)Just wondering if I have the right approach here or if anyone else has any suggestions. Thanks for your help! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-25 : 13:33:06
|
| Yes isp_ALTER_INDEX will slow things down while it is running. It is quite intensive and should be run at night only.You may try running update stats on the problematic tables. You probably have bad statistics since you deleted so much data. How big is your database? Even on my databases that are close 20GB, isp_ALTER_INDEX completes in about an hour (IIRC that is). Are you using DETAILED or SAMPLED? I only use SAMPLED on largish databases.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-25 : 13:33:43
|
| you can do an update stats as a quick fix and run the reindex at night when there is little/no load.************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2007-04-25 : 13:41:29
|
| is there a quick way to run update stats across my whole DB?I'm googling but only seeing per table per index which could take me a long time.Tara the DB is 12 GB in sizethx again!! :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-25 : 13:45:40
|
| "is there a quick way to run update stats across my whole DB?"Does sp_updatestats still exist in SQL 2005 ?Kristen |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2007-04-25 : 13:47:44
|
| I just ran "UPDATE STATISTICS tblmessage"It's running on a 5 million row table (of which I deleted a few hundred thousand). My next table I want to run it on is larger with 40% deletion. It's taken 4 minutes so far I have no idea what sort of ride I am in for here. Any info much appreciated ! |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-25 : 13:50:28
|
| update statistics tblmessage will use the default sampling size of 10%. You might want to monitor the performance closely and if you still have issues with the table, you might want to do an UPDATE STATISTICS tblmessage WITH FULLSCAN. It does take a few minutes since its an online/non-interfering operation.************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-25 : 13:53:47
|
| I should mention that my isp_ALTER_INDEX stored procedure is not working correctly on one of my production instances. It just hangs on one table and blocks all access to it. We can't even kill the process to release the locks. We have to restart SQL Server to fix it. It has happened twice now. But we are also having the same problem with my update stats procedure too, so I don't think the issue is with my code. I haven't had time to debug it yet though.I've got both stored procedures running on lots of production instances, but we are having this locking problem on one instance. We do not have sp2 installed in production yet.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2007-04-25 : 14:32:04
|
thanks for the updates Tara, I'll keep an eye out to make sure thats the problem but as of now I don't have evidence that it is.I'm still running my query UPDATE STATISTICS tblmessage WITH FULLSCAN Its a 5 million row table, we have been running for 35 minutes already. Does something sound wrong or should I just let this process complete by giving it all the time it wants?Thanks! |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-25 : 14:36:49
|
| just let it finish. How long it takes to finish depends on the load on your SQL Server.************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-25 : 14:38:25
|
| Give it time to complete. A fullscan on that size table will take a while. And no we can't estimate the time it will take to run.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2007-04-25 : 15:59:12
|
| Running 2 hours now, I guess we are in for the long haul :) I went straight to the WITH FULLSCAN option for this table.The next table I will try will be double the size with way more deletes on the table. Do you think it would be a better idea to try it without FULLSCAN on ?Also if I update statistics with a SAMPLE, then later WITH FULLSCAN, will the first statistic update contribute to the FULLSCAN or is it just like starting from scratch again?Thanks very much once again! :) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-25 : 16:05:00
|
| I always use 25% in production. I can't afford to impact my databases for as long as the FULLSCAN takes. But since you updated so much data, you probably need to take the time to do the scan.SAMPLE to FULLSCAN is like starting from scratch.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2007-04-25 : 16:34:56
|
| awesome tara, thanks very much for the informative posts :)FULLSCAN completed at 2 hours 15 mins.. Now for the main table, I think i've got it from herecheers,mike |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-29 : 12:27:08
|
| I think 2 hours 15 minutes is a long time for a 5 million row table. Have you got a lot of indexes on t hat table? Are some of them possibly redundant?Might be an idea to Defrag that table before updating stats (if you have deleted lots of rows). Or just REINDEX it if your have a maintenance window.Kristen |
|
|
|
|
sql slowed to a crawl after deleting mass rows