| Author |
Topic |
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
 Posted - 2009-12-12 : 22:02:08
|
| I have found the Fragmentation for which I have defragmented later again on checking, I found some Index keys with Fragmentation over 80 Percent along with some other Primary Key based as well. I was not perturbed with PK's based average fragmentation but I could not understood why even after defragmenting some indexes show still fragmentation over 80 Percent.Users didnt complain about the data accessibility, should I ignore this average fragment?Is it necessary to do Reindex process after successful Defragmentation?Is is necessary to do Shrink after Defrag and reindex?While Doing Defragmentation, how to maintain the LDF size as it decrease and goes low disk size? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-12 : 22:50:36
|
| You need to read up on defragmenting (REORGANIZE) and rebuilding (REBUILD) as they do similar but different things. You do not run them both right after each other. Do not shrink after defrag or reindex! In fact never shrink in production unless absolutely necessary for disk space reasons or if you deleted a large amount of data and want to reclaim space. To maintain the transaction, perform frequent tlog backups. We backup ours every 15 minutes.Depending upon your database design, tables can quickly get fragmented. Fragmented isn't necessarily a cause for performance issues though, so don't get too concerned about it. How big is the table?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." |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-12-13 : 04:58:52
|
| How big are the indexes that don't show a reduction in fragmentation (how many pages)?--Gail ShawSQL Server MVP |
|
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2009-12-13 : 16:23:31
|
| These are some of the rows that were returned when I executed for knowing the Fragmentation.TableName TableIndexName avg_frag_% rowsPITGRAY SE_PITGRAY_IDX0 99.97668454 366667PITGRAY SE_PITGRAY_IDX0 99.97668454 366667PITGRAY SE_PITGRAY_IDX0 99.97668454 366667PITGRAY SE_PITGRAY_IDX0 99.97668454 366667PITGRAY SE_PITGRAY_IDX0 100 366667PITGRAY SE_PITGRAY_IDX0 100 366667PITGRAY SE_PITGRAY_IDX0 100 366667PITGRAY SE_PITGRAY_IDX0 100 366667I have just ignored some other similar rows which depict for TableIndexName Suffix as 'PK' between 70 - 100 Percent. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-12-13 : 17:54:11
|
| What query did you use to get that? The same index appears to be there several times.--Gail ShawSQL Server MVP |
|
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2009-12-13 : 18:47:07
|
| I Have queried using sys.dm_db_index_physical_stats and sys.indexes DMVs to find the fragmentation.I Use this query prior to defragment and later as well.I get most of them got defragmented but still some stubborn Indexes still possess with fragmentation.As Tara told earlier, I use Transaction log backup for every 15 minutes and get full backup during after hours.Several big tables have around 1 or 2 GB and another having 8 GB.Please advise. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2009-12-13 : 20:17:13
|
| Thanks Tara, I did added to the query and this time I have only SUFFIX as 'IDX' was reported and no 'PK' at the end amongst the results.What exactly happened is we are trying to defragment later want to shrink the MDF and LDF.It got it later the Reindex process did ate again the space(I Knew reindex will generate Transaction Log) but it got the same space before I did the Reindex.As you suggested I should NOT do Reindex after Defrag, However, is it correct if I not do reindex which was defragmented and leave it once the defragmentation has been accomplished.Earlier when we have the space swallowing problem identified with 100 percent fragmentation then I followed with Defrag + reindex which worked out for us. But not this time, unable to understand between 2 scenarios.What would be the correct methodology for fragmentation to defragmentation?Thanks for your time. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-13 : 23:41:48
|
| Well defrag+reindex is different than reindex+defrag. Defrag doesn't fix everything, it does much less work than the reindex. Do not shrink after you've run either as you are going to create heavy fragmentation. This was mentioned to you by Gail in your other thread not too long ago. I only run the reindex. I reindex everything every night that is over 50% fragmented.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." |
|
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2009-12-14 : 17:40:20
|
Even Reindex will cause a Heavy transaction registered onto my LDF.I am still unable to contemplate between Defrag + Reindex OR Reindex + Defrag.My Aim is to reduce the Fragmentation below 50 percent which is not working with either of the combination. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2009-12-14 : 23:08:12
|
| Thanks Tara.I got this DB Diagram for one of the most populated table which has more than 2 Million Rows.RecordNum Varchar25citeID Numeric(10,0)Delite Numeric(10,0)UpdaterSNO Numeric(10,0)DateCr8 DatetimeTimeCr8 DatetimeUpdateTime DatetimeUpdateDate DatetimeLocalID Numeric(10,0)Suburbs Varchar51Town Varchar51Maintown Varchar51StateAddress Varchar51PostalCode Varchar51Mlocality Numeric(10,0)Dispensary Varchar11 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2009-12-14 : 23:23:27
|
| There are Clustered Indexes on the Concatenated Primary Key and couple of Non-Clustered Indexes and the fragmentation on them is around 99 percent most of them, Every day there would be around couple of 100's of DML operations altogether.Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2009-12-15 : 23:57:50
|
| Some tables (Heavily populated) in the databases were Sequential and some tables (Less Populated) were at random of Clustered index nature.Some tables with PK as the suffix were having 100 percent too.Very much Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2009-12-16 : 00:14:45
|
| Fill Factor 80 PERCENT.But will it be ok with the kind of output Above as I submitted.Since Defragmentation is not removed overall and reindex however done, but after that the Fragmentation still appearing giving wrong connotations.Thanks |
|
|
|
|
Fragment, reindex