Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


High Fragmentation Index ...


High Fragmentation Index ...

Author
Message
WhiteLotus
WhiteLotus
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 877
OK Cool .. I will just drop it Smile

Thanks so much !!
WhiteLotus
WhiteLotus
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 877
Btw I have another issue after running the rebuild/reorganize index script ( once in 2 days ) ...the log files grows very fast ..we just added new harddisk a few days ago but now it is running out space again ... we also have doing the log transaction back up but it seems doesnt help much ...

Pls kindly advice Smile
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47204 Visits: 44367
SQLisAwE5OmE (7/13/2014)
If the table is very small, table scan is sometimes faster than seek...so, that's why the fragmentation is getting high again.


Fragmentation has nothing to do with scans (other than slowing them down if they're from disk), so I don't know what you're trying to say here.

Analysing whether or not an index is useless does not involve looking at the table size, unless the table is under a page (8k) in size. 12000 rows is well over that. Analysing whether or not an index is useful involves finding the queries which use that index, testing them with the index and without and seeing whether the performance change is acceptable.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47204 Visits: 44367
murnilim9 (7/13/2014)
Btw I have another issue after running the rebuild/reorganize index script ( once in 2 days ) ...the log files grows very fast ..we just added new harddisk a few days ago but now it is running out space again ... we also have doing the log transaction back up but it seems doesnt help much ...


Please see the thread you started specifically for this question - http://www.sqlservercentral.com/Forums/Topic1592060-391-1.aspx


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLisAwE5OmE
SQLisAwE5OmE
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 3062
Thanks Gail for correcting...I'm way off then...sorry.

Regards,
SQLisAwe5oMe.
WhiteLotus
WhiteLotus
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 877
hmm I have dropped the index anyway (moreover, I noticed the statistic of the unused index is quite convincing )...
btw how do we find out which stored proc that use that index ? I really have no idea about it because we have a lot of SP ...

Pls kindly response

Thanks a lot ...
Shaun Finnegan
Shaun Finnegan
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 470
murnilim9 (7/10/2014)
Hi All,

At the moment I am tuning the indexes with high fragmentation ..I created a maintenance plan for rebuild /reorganize ( set the logic for fragmentation above 50% then I will do rebuild , otherwise I will reorganize ... fill factor = 80 . I also filtered by the number of page count.
noticed there is 1 index which is fragmented very quickly starting from 2 am until 7 am .
I did rebuild / reorganize every 2 am ( after log backup at 12 am)
I believe after rebuild it will become 0% but after 3 hours it will become 80% I guess...

I check the unused index data ( using the script ) and for that index i got this data :

User_Seek : 0
User_Scans : 16
User_lookup : 0
User_updates : 1.128.932

I wonder that I should just drop this index or keep maintain it with rebuild it AGAIN after a few hours later ...

Please kindly advice...thanks heaps

Cheers,
Me



Hi, Just posted this in the 2012 section but same applies. Ask your self the below question before you start setting up Index rebuild maintenance plans. DBA's have noted in the past that this can be one of the main causes of bring a SQL server offline.

Before you start reorg'ing or rebuilding your indexes, ask yourself:

Are we actually having any performance issues with the database? What have your users said?
What is the profile of the data in the table? How many inserts / deletes / updates?
When were the statistics last updated on the tables?
If rebuilding, when can you do this? Controlled outage? Online or Offline rebuild? How will this affect the disk space on the server?

Have a read of these two articles before you proceed as it requires a bit more thought other than which option you should choose.

http://blogs.msdn.com/b/psssql/archive/2012/09/05/how-it-works-online-index-rebuild-can-cause-increased-fragmentation.aspx
http://www.mssqltips.com/sqlservertip/2361/rebuilding-sql-server-indexes-using-the-online-option/

cheers,

Shaun

SQL Server Best Practices Supporter
WhiteLotus
WhiteLotus
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 877
oh thanks so much for your links..very useful information !
i am performing offline index since the edition of my SQL is Standard which will not support online index.

I noticed the duration of performing rebuild/reorganize is around 20 - 30 seconds for 1 database ( I have just tested 1 database ) ..To be honest , I was a bit worried of table lock but I guess it was a very short time ..so I think it will be alright ....

any thought ?

Thanks!
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17576 Visits: 32260
murnilim9 (7/14/2014)
hmm I have dropped the index anyway (moreover, I noticed the statistic of the unused index is quite convincing )...
btw how do we find out which stored proc that use that index ? I really have no idea about it because we have a lot of SP ...

Pls kindly response

Thanks a lot ...


There is no way to correlate between the missing index information and a particular query. Instead, you can query the plan cache to look at missing index information in the execution plans. I have a query posted on my blog that will get you started there.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
WhiteLotus
WhiteLotus
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 877
Thanks a lot on your response !


Btw I performed rebuild indexes on certain databases ..maybe around 22 indexes need to be rebuilt and it failed and showed an error :

/*********************************************..." failed with the following error: "Transaction (Process ID 87) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

at the moment there are still 10 indexes that need to be rebuilt...

I am thinking to drop some unused indexes and filter by page_count which is above 300

Any idea about this issue ?

Thank you


Cheers
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search