| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2009-04-03 : 15:03:16
|
| Hi,I have just installed Taras new wonderful version of ISP_ALTER_INDEX that includes a history table. I am posting my tables results in hopes of learning anything about my setup. How does this data look ? I guess it really depends on what the data is. Anyways just posting this for general feedback, I'm wondering if I should be changing the parameters I pass to the SPROC. Currently I am running it everynight with the following params, perhaps I could benefit by changing some of these values?EXEC isp_ALTER_INDEX @dbName = 'dbname', @statsMode = 'SAMPLED', @defragType = 'REORGANIZE',--'REBUILD', @minFragPercent = 10, @maxFragPercent = 100, @minRowCount = 1000, @logHistory = 1Altho it may be difficult to suggest anything with this limited data, I thought I would give it a chance. Any suggestions greatly appreciated !Btw: one thing, it doesnt seem to be going thru all the indexes, this is my first night letting it run. Does it skip indexes in some cases ? Date / PercentFragmented2009-04-03 01:56:27.927 51.872009-04-03 02:02:10.217 21.582009-04-02 01:08:18.080 43.022009-04-03 01:09:06.640 36.682009-04-02 01:08:17.103 21.962009-04-03 01:08:56.710 20.662009-04-02 01:07:38.903 12.592009-04-03 01:07:57.513 12.462009-04-02 01:05:47.840 16.112009-04-03 01:06:11.190 15.212009-04-02 01:03:01.750 36.842009-04-03 01:03:17.507 36.842009-04-03 01:56:37.390 45.822009-04-03 01:56:27.400 19.852009-04-02 01:03:58.380 13.072009-04-03 01:04:38.413 16.182009-04-03 02:00:25.867 81.732009-04-03 02:01:21.827 15.252009-04-03 02:00:44.177 10.432009-04-03 02:00:54.083 10.602009-04-03 01:57:51.353 89.912009-04-03 01:58:47.753 45.292009-04-03 01:59:58.807 70.82 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-04-03 : 15:14:21
|
| The only indexes it should be skipping are those that fall outside of the input parameters. If you are still seeing skips, let me know so that I can fix it.Eventually I will modify it to look at number of pages rather than the row counts. According to a Microsoft engineer, indexes can't be defragmented at about 1000 pages or less. I didn't have this information when I originally wrote this, so that's why I included the row count option as row counts are related to the number of pages. I recommend that you use REBUILD instead of REORGANIZE, especially if you have a non-peak period to run it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-04-03 : 15:28:29
|
| Hey Tara,I do have a non peak period to run it, altho its always active. I will run with REBUILD and hope performance is still OK, and report back to you :)It does seem to be skipping some indexes, but I will confirm with you again after I look into a bit more as I am not 100% sure.Thanks again! :)Mike123 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-04-03 : 15:33:36
|
| If it is indeed skipping, send me the output of the DMV that I am using towards the top of the stored procedure. Show me the output before and after isp_ALTER_INDEX runs. You can send the data directly to me via the email option in the forums or on my blog. Here's the query:SELECT * FROM sys.dm_db_index_physical_stats (DB_ID('YourDbName'), NULL, NULL, NULL, 'SAMPLED')WHERE avg_fragmentation_in_percent > 10 AND index_id > 0ORDER BY ObjectIdTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-04-03 : 20:43:21
|
| Hey Tara,It definatley missed some indexes, I will let it run for a second time tonight at 1am, and let you know what happens. Please allow me a couple days to collect the data.Thanks again! :)mike123 |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-04-04 : 13:17:41
|
| Hey Tara,I think I've encountered an infinite loop while executing this SPROC... my server is hanging with 95% CPU utilization and there is no I/O. Its been going for 5 hours and I'm not sure waht to do .I think I am going to force a shutdown of sql server...Previously I thought indexes where building, but now I dont think so, due to there being no I/OAny ideas ?Thanks very much!Mike123 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-04-04 : 14:14:02
|
| Run SQL Profiler to see what's going on. How big is your database? By the way, I'm running it on hundreds of databases already, most are mission critical and some have SLA's at 99.999%. We've never encountered what you are experiencing. The biggest database that I have it running against right now is 200GB.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-04-06 : 18:10:05
|
| Hi Tara,I ended up rebooting the system, and everything came back ok, which leaves me a little puzzled as to what was going on. My database is only 30GB.I've reverted back to your old version for the time being. I will get my indexes as defragmented as possible before attempting it again, and I'll try it on REORGANIZE instead of REBUILD for a few days and see if that helps...... not sure what else to try but any recommendations appreciated !Thanks again,mike123 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-04-07 : 01:08:43
|
| Hey Tara, I will try your recommendations and keep you posted! In the meantime, what statsmode do you think I should be using ? DETAILED will give me the best final index tuning ?Thanks!mike123 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-04-07 : 14:44:37
|
| Hey Tara,I have been using SAMPLED too. Will DETAILED do a better job ? If it does take longer, that could be ok, as long as it doesnt slow the system down too bad. How much longer are we talking ? Not sure if you can toss out any approximations.Much appreciated!Thanks again,mike123 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-04-07 : 15:17:23
|
DETAILED doesn't necessarily do a better job. It just depends. Here is the BOL documentation on the three different scanning modes that you can pass to sys.dm_db_index_physical_stats.quote:
The LIMITED mode is the fastest and scans the smallest number of pages. It scans all pages for a heap, but only the parent-level pages for an index, which are the pages above the leaf-level. The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap. If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED. The DETAILED mode scans all pages and returns all statistics.
I can't estimate how much longer DETAILED will take over SAMPLED.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-04-07 : 17:19:07
|
| Hey Tara,I'd like to test and try it tonight during off peak hours. Do you think DETAILED will slow my system or anything like that ? I am guessing that it doesnt cause any locking etc like a REBUILD would. thanks again :)mike123 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-04-08 : 01:03:46
|
| Hey Tara,Appreciate all the help here. Whats the difference between running REORGANIZE with @minFragPercent = 1, VS running REBUILD ? Thanks again!mike123 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
ISP_ALTER_INDEX