<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
Fragmentation size
14 posts, Page 1 of 2
1
2
»»
Fragmentation size
Rate Topic
Display Mode
Topic Options
Author
Message
zi
zi
Posted Wednesday, December 26, 2012 12:42 PM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, April 26, 2013 11:23 AM
Points: 76,
Visits: 188
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
Sean Lange
Sean Lange
Posted Wednesday, December 26, 2012 12:51 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641,
Visits: 8,273
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
Post #1400340
GilaMonster
GilaMonster
Posted Wednesday, December 26, 2012 12:53 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 1:00 PM
Points: 37,744,
Visits: 30,024
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
zi
zi
Posted Wednesday, December 26, 2012 1:19 PM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, April 26, 2013 11:23 AM
Points: 76,
Visits: 188
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
GilaMonster
GilaMonster
Posted Wednesday, December 26, 2012 1:34 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 1:00 PM
Points: 37,744,
Visits: 30,024
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
zi
zi
Posted Wednesday, December 26, 2012 1:42 PM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, April 26, 2013 11:23 AM
Points: 76,
Visits: 188
Thank you very much
Post #1400364
Jeff Moden
Jeff Moden
Posted Wednesday, December 26, 2012 3:53 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 32,928,
Visits: 26,815
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1400402
peterdru401
peterdru401
Posted Friday, December 28, 2012 3:39 AM
Grasshopper
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
zi
zi
Posted Sunday, December 30, 2012 9:57 AM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, April 26, 2013 11:23 AM
Points: 76,
Visits: 188
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
Lynn Pettis
Lynn Pettis
Posted Sunday, December 30, 2012 10:50 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 21,633,
Visits: 27,490
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 »
14 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
