| Author |
Topic |
|
pdset
Constraint Violating Yak Guru
310 Posts |
 Posted - 2010-12-05 : 15:58:57
|
| Hello Patrons,Knowing that fragmenation causes as a result of the user transactions and other impediments.But,Will the Weekly Reindex job comprises any indirect Fragmentation as a result of the reindex?I do have my weekly reindex job comprises the following aspects:RECOMPILE STORED PROCEDURES;UPDATE STATISTICS;REINDEX on all tables.CHECKDB on the database.Thanks for your time. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-05 : 17:15:23
|
| I don't think you need RECOMPILE STORED PROCEDURES - should happen automatically when you reindex / update statistics.But if you do I would do if afterwardsMy sequence would be REINDEX on all tables (I would only do fragmented indexes)UPDATE STATISTICS (only non-index stats required - beware if updating statistics on tables which ahve just been rebuilt if you are not using FULL SCAN)RECOMPILE STORED PROCEDURES; (optional)CHECKDB on the database.Also: Restore backup to another machine, and do CHECKDB on that machine. If you do this then it is not necessary to also do CHECKDB on the main database - which can save some time on busy/large production servers / databases |
 |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-12-06 : 20:17:42
|
| Thanks for the Reply.However, Can I know how to negotiate in Reindex only those Fragmented indexes through the Script. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-12-09 : 20:25:51
|
| Thanks Tkizer. I have done with some exceptions as there won't be any results go beyond 100 rows of its execution output, though I have increased. This is due to I do have more than 100 tables.Any flip to get all the results to its maximum.Thanks again. |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-12-09 : 20:27:56
|
| Hi Tkizer,Thanks for it. But I dont want Defragmenting everytime but only other than Defragmentation which is like Reindex.Can this script be used for such purpose as well.Thanks for your explanation and time. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-12-10 : 00:05:53
|
| I want to clarify what you haven't understood.Your script exclusive for defragmenting / reindex based either rebuild or reorganize as you claim, which is good.My interest here if I use script then I need not do everytime (on Weekly-Basis)defragmenting and so on.....So I want to know whether your script NOT doing Defragmenting but only REINDEX on weekly basis.Then still if I want at 'some juncture' to do Defragmenting will be good.However, you have sounded this script is multi-faceted and I will try.Perhaps this is much clear. Many thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-10 : 02:29:48
|
| Tara's script has parameters so you can control whether you want Re-index or De-fragment, and you can also set the amount of fragmentation - so only badly fragmented files will be processed. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-12-13 : 23:21:29
|
Thanks TKizer for your overall support though I have executed on preliminary basis and to my astonishment I have seen the Largely populated tables still shows some fragmentation and I was not sure whether the Defragmentation has worked out or not.Since all other tables got defragmented but the Large tables with almost 70 - 100 percent (before and after Defragmentation) was still existing.On looking for answers what went wrong - I have got some answers that you have mentioned any fragmentation which has 1000 rows or less should be ignored. But in my case it is not so, (bottomline users didn't complain of slowness for which fragmentation was one of the reasons).Do I need to understand the defragmentation has worked.If so, What other measures need to look into.Thanks for elaborating for my cause perhaps it is most of DBA's still look for similar answers like this. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-12-13 : 23:54:53
|
| Fragmentation is normal on small tables. On those that you've noticed fragmentation exists after you've rebuilt the indexes, how many pages are there in those indexes? Row counts don't really matter. It's page count that matters. It's around 1,000 pages that matters, but Paul Randal indicates it might be as high as 10,000 pages.Also, you may have a system with heavy fragmentation due to improper clustered index. On the table with the problem what's the clustered index (data type and columns, please).Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
Evidence for Fragmentation