Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

High Fragmentation Index ... Expand / Collapse
Author
Message
Posted Sunday, July 13, 2014 9:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 12:38 AM
Points: 75, Visits: 166
OK Cool .. I will just drop it :)

Thanks so much !!
Post #1592047
Posted Sunday, July 13, 2014 10:34 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 12:38 AM
Points: 75, Visits: 166
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 :)
Post #1592052
Posted Monday, July 14, 2014 12:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 42,774, Visits: 35,871
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 2008, MVP
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

Post #1592070
Posted Monday, July 14, 2014 1:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 42,774, Visits: 35,871
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 2008, MVP
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

Post #1592073
Posted Monday, July 14, 2014 5:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 6:40 PM
Points: 399, Visits: 2,458
Thanks Gail for correcting...I'm way off then...sorry.

Regards,
SQLisAwe5oMe.
Post #1592149
Posted Monday, July 14, 2014 6:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 12:38 AM
Points: 75, Visits: 166
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 ...
Post #1592386
Posted Monday, July 14, 2014 7:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 6:28 PM
Points: 24, Visits: 252
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
Post #1592390
Posted Monday, July 14, 2014 9:44 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 12:38 AM
Points: 75, Visits: 166
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!
Post #1592401
Posted Tuesday, July 15, 2014 1:11 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:11 PM
Points: 15,646, Visits: 28,027
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1592424
Posted Wednesday, July 16, 2014 9:24 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 12:38 AM
Points: 75, Visits: 166
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
Post #1593365
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse