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 12»»

Fragmentation size Expand / Collapse
Author
Message
Posted Wednesday, December 26, 2012 12:42 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:17 AM
Points: 90, Visits: 239
Dear Friends

1-1-How to know the fragmentation size for whole database not only for certain table, to decide if it exexceeds 30% to re-build index or re-organise.

2-what is the relationship between re-build or re-organise index by log file.

Thanks lot
Post #1400334
Posted Wednesday, December 26, 2012 12:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:32 PM
Points: 13,302, Visits: 12,168
zi (12/26/2012)
Dear Friends

1-1-How to know the fragmentation size for whole database not only for certain table, to decide if it exexceeds 30% to re-build index or re-organise.

2-what is the relationship between re-build or re-organise index by log file.

Thanks lot


1) Fragmentation for the database as whole doesn't make sense in this context. You need to look at each index individually.

2) I don't understand your question here. What do you mean?

You should probably do a google search for "sql server rebuild vs reorganize index" and be prepared to spend some time reading.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1400340
Posted Wednesday, December 26, 2012 12:53 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 42,822, Visits: 35,953
zi (12/26/2012)
1-1-How to know the fragmentation size for whole database not only for certain table, to decide if it exexceeds 30% to re-build index or re-organise.


Databases don't have fragmentation. Indexes do. Talking about the fragmentation level of an entire DB is completely meaningless.



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 #1400341
Posted Wednesday, December 26, 2012 1:19 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:17 AM
Points: 90, Visits: 239
Thanks for replying

- what if I have a huge database = 'Tables' , how to know that I need to re-organise or to re-build indexes

-why when re-organise or re-build indexes make log file bigger.

Thanks lot
Post #1400358
Posted Wednesday, December 26, 2012 1:34 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 42,822, Visits: 35,953
zi (12/26/2012)
- what if I have a huge database = 'Tables' , how to know that I need to re-organise or to re-build indexes


Then you look at the fragmentation of the indexes and decide which to rebuild. I recommend you use something like Ola's scripts
http://ola.hallengren.com/Versions.html

-why when re-organise or re-build indexes make log file bigger.


Because all modifications to the DB are logged and both index rebuilds and reorganise operations do a substantial amount of modifications



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 #1400362
Posted Wednesday, December 26, 2012 1:42 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:17 AM
Points: 90, Visits: 239


Thank you very much
Post #1400364
Posted Wednesday, December 26, 2012 3:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:11 AM
Points: 36,995, Visits: 31,516
zi (12/26/2012)
Dear Friends

1-1-How to know the fragmentation size for whole database not only for certain table, to decide if it exexceeds 30% to re-build index or re-organise.

2-what is the relationship between re-build or re-organise index by log file.

Thanks lot


These are actually interview questions.... important interview questions that every DBA should know by heart. The fact that you're posting these questions means you really need to spend some time practicing being a DBA before you answer another ad for the position. And, no... I'm not trying to be mean here. Call it "tough love" for someone looking for a job as a DBA. Buy yourself a copy of the SQL Server Develop Edition, install it on your laptop or home computer, and hit the books.

You might also want to learn how to Google for such answers instead of relying on the kindness of others who might not actually know the answers to your questions.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1400402
Posted Friday, December 28, 2012 3:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 8:34 AM
Points: 23, Visits: 128
Here is an excellent difference between rebuilding the indexes & reorganising the indexes

Eg.
Index Rebuild : This process drops the existing Index and Recreates the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO

Index Reorganize : This process physically reorganizes the leaf nodes of the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO

Note: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.
Post #1400857
Posted Sunday, December 30, 2012 9:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:17 AM
Points: 90, Visits: 239
Thanks for replying

Do you have a script to know the fragmentation size for the whole database not only table by table.

Thanks lot
Post #1401258
Posted Sunday, December 30, 2012 10:50 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:50 PM
Points: 23,299, Visits: 32,039
zi (12/30/2012)
Thanks for replying

Do you have a script to know the fragmentation size for the whole database not only table by table.

Thanks lot


To repeat:

GilaMonster (12/26/2012)
zi (12/26/2012)
1-1-How to know the fragmentation size for whole database not only for certain table, to decide if it exexceeds 30% to re-build index or re-organise.


Databases don't have fragmentation. Indexes do. Talking about the fragmentation level of an entire DB is completely meaningless.





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1401262
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse