Multiple Queries-Please Help

  • 1.Which Rebuilding of index takes time

    Clustered Or Non Clustered --And WHY ?

    2.When SQL SERVER Backup is Taken ,how to Check that the Back up Done is fine ..

    (Without restoring )

    3.I am Clear with the Point that INSERT\DELETE can causes the FRAGMENTATION.

    But How Does Update Affects can be cause of Fragmentation..It not even release any Space of Acquire any Space.Please Explain.

    4.Logshipping Scenerio-

    For any Activity on Production Server Can Anytime we Disabled the Three JOBS (Backup Copy and Restore)Simultaneously .And Post updating the Secondary Server till the latest Log Backup ,if run the THree Jobs again.Does it Affect anything?

    What would be the Affect..Please Advise.

  • Jai-SQL DBA (6/12/2012)


    1.Which Rebuilding of index takes time

    Clustered Or Non Clustered --And WHY ?

    2.When SQL SERVER Backup is Taken ,how to Check that the Back up Done is fine ..

    (Without restoring )

    3.I am Clear with the Point that INSERT\DELETE can causes the FRAGMENTATION.

    But How Does Update Affects can be cause of Fragmentation..It not even release any Space of Acquire any Space.Please Explain.

    4.Logshipping Scenerio-

    For any Activity on Production Server Can Anytime we Disabled the Three JOBS (Backup Copy and Restore)Simultaneously .And Post updating the Secondary Server till the latest Log Backup ,if run the THree Jobs again.Does it Affect anything?

    What would be the Affect..Please Advise.

    1) Both take time as rebuild requires sorting of the keys.

    2) Only sure way to verify backup file is good is do to a restore.

    3) If rows contain variable length records it is possible during an update that the record will not fit where it originally resided on a page do to an increase in the length of variable length data.

    4) I will have to defer this to someone more knowledgeable on Log Shipping.

  • Point 4,

    Out of the three jobs, you would only need to stop the backup job as the copy and restore wont do anything should the backup not be in the folder to copy.

    This will affect your RPO so you will need to ensure that you re-enable the backup job to bring your RPO back inline with your SLA's.

    When you run the jobs it will just act as a normal Logshipping backup/copy/restore run so the affect is minimal, it just depends on how big the log backup is to how long it takes for it to complete.

    You will not be able to modify the data in the secondary database at any time during a logshipping setup, unless you issue a RESTORE WITH RECOVERY, which will require you to re-setup Logshipping.

  • 3.I am Clear with the Point that INSERT\DELETE can causes the FRAGMENTATION.

    But How Does Update Affects can be cause of Fragmentation..It not even release any Space of Acquire any Space.Please Explain..

    3) If rows contain variable length records it is possible during an update that the record will not fit where it originally resided on a page do to an increase in the length of variable length data.

    I would also assume that it can cause fragmentation if you are changing the value of a field(s) in an indexed column that will change the sort position of that row.

    Jared
    CE - Microsoft

  • 3. Deletes don't cause fragmentation.

    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 (6/12/2012)


    3. Deletes don't cause fragmentation.

    I thought that deletes were a major cause of internal fragmentation, though not necessarily external fragmentation.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (6/12/2012)


    GilaMonster (6/12/2012)


    3. Deletes don't cause fragmentation.

    I thought that deletes were a major cause of internal fragmentation, though not necessarily external fragmentation.

    Yes, I second that deleting also cause fragmentation. Can you please explain a little bit more as to what's the main difference between internal & external fragmentation?

    Regards,

    TA

  • SueTons (6/12/2012)


    opc.three (6/12/2012)


    GilaMonster (6/12/2012)


    3. Deletes don't cause fragmentation.

    I thought that deletes were a major cause of internal fragmentation, though not necessarily external fragmentation.

    Yes, I second that deleting also cause fragmentation. Can you please explain a little bit more as to what's the main difference between internal & external fragmentation?

    Regards,

    TA

    If you second it, you must understand it too. Please tell us your thoughts.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (6/12/2012)


    SueTons (6/12/2012)


    opc.three (6/12/2012)


    GilaMonster (6/12/2012)


    3. Deletes don't cause fragmentation.

    I thought that deletes were a major cause of internal fragmentation, though not necessarily external fragmentation.

    Yes, I second that deleting also cause fragmentation. Can you please explain a little bit more as to what's the main difference between internal & external fragmentation?

    Regards,

    TA

    If you second it, you must understand it too. Please tell us your thoughts.

    Hi, I know that deleting a row can cause fragmentation since it creates free space within a page, also inserting can cause fragmentation due to page splits. A row cannot span into two pages from my understanding. All of these are internal fragmentation I believe.....so, what do you mean by external fragmentation?....that's where I am little confused.

    Regards,

    TA

  • opc.three (6/12/2012)


    GilaMonster (6/12/2012)


    3. Deletes don't cause fragmentation.

    I thought that deletes were a major cause of internal fragmentation, though not necessarily external fragmentation.

    For the direct mechanics of the delete internally, this is an excellent blog article to start with:

    http://blogs.msdn.com/b/askjay/archive/2011/01/28/how-does-sql-server-perform-deletes-in-my-table.aspx

    [EDIT] It's only polite to warn you about that link... if you've never gone looking at DBCC PAGE, you're about to enter the rabbit hole. [/EDIT]

    However, Orlando, just think it through. Let's look at a fake page set:

    Page 1:

    Row 1

    Row 2

    Row 3

    Page 2:

    Row 4

    Row 5

    Row 7

    Page 3:

    Row 8

    Row 9

    Row 10

    So, we insert row 6, and only fitting 3 rows into our fake pages...

    1: 1/2/3

    2:4/5 (PAGE SPLIT)

    3: 8/9/10

    4: 6/7

    Yes, I'm taking a bit of liberty with the mechanics to illustrate the point, before anyone drops me over the whipping stool...

    So, we've fragmented. Also, we've got dead space on two of the pages, we're not fully up to fillfactor. We expect this until we reindex/etc, anything that defrags and refills the pages.

    However, using that structure, deleting a record doesn't change fragmentation. Let's remove Row 9.

    1: 1/2/3

    2: 4/5

    3: 8/10

    4: 6/7

    The Deletion can't affect fragmentation, as it doesn't affect page placement of any other row. On its own page, sure, it moves the slot around during the ghost cleanup, but the page isn't in any particular physical order anyway. It uses pointers on its own page for out of order entries within its own range in the clustered index (heaps too, ish, kinda). The rows on a particular page are not guaranteed to be in the order of the clustered index, just that all the rows on that page are within a range subset of the index.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • SueTons (6/12/2012)


    Hi, I know that deleting a row can cause fragmentation since it creates free space within a page, also inserting can cause fragmentation due to page splits. A row cannot span into two pages from my understanding. All of these are internal fragmentation I believe.....so, what do you mean by external fragmentation?....that's where I am little confused.

    That is not fragmentation. Fragmentation is out of order internal pages for contiguous access. Empty space on a page isn't fragmentation. If anything, it helps to AVOID fragmentation, because page splits won't occur if there's room to include larger updates or inserts.

    My personal definitions:

    Internal (logical) fragmentation is when your MDF file is contiguous on disk but your data is non-contiguous internally. External Fragmentation is when your actual file is in non-contiguous space on the physical drive itself. Internal Fragmentation is dealt with via SQL Tools. External Fragmentation is discussed with your SAN admins or via external tools.

    Both slow you down.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Evil Kraig F (6/12/2012)


    SueTons (6/12/2012)


    Hi, I know that deleting a row can cause fragmentation since it creates free space within a page, also inserting can cause fragmentation due to page splits. A row cannot span into two pages from my understanding. All of these are internal fragmentation I believe.....so, what do you mean by external fragmentation?....that's where I am little confused.

    That is not fragmentation. Fragmentation is out of order internal pages for contiguous access. Empty space on a page isn't fragmentation. If anything, it helps to AVOID fragmentation, because page splits won't occur if there's room to include larger updates or inserts.

    My personal definitions:

    Internal (logical) fragmentation is when your MDF file is contiguous on disk but your data is non-contiguous internally. External Fragmentation is when your actual file is in non-contiguous space on the physical drive itself. Internal Fragmentation is dealt with via SQL Tools. External Fragmentation is discussed with your SAN admins or via external tools.

    Both slow you down.

    Hmmm.....I am not sure if I agree with you 100% about deleting a row does not create internal fragmentation. Please read through this article from SqlServerCentral.

    "What is Index Fragmentation? Over time, as indexes experience INSERTs, UPDATEs, and

    DELETEs, a normal process called index fragmentation occurs in databases"

    http://bradmcgehee.com/wp-content/uploads/presentations/SSQ202--How%20to%20Defragment%20Indexes%20for%20Peak%20Performance.pdf

    Regards,

    Tony

    Regards,
    SQLisAwe5oMe.

  • Evil Kraig F (6/12/2012)


    SueTons (6/12/2012)


    Hi, I know that deleting a row can cause fragmentation since it creates free space within a page, also inserting can cause fragmentation due to page splits. A row cannot span into two pages from my understanding. All of these are internal fragmentation I believe.....so, what do you mean by external fragmentation?....that's where I am little confused.

    That is not fragmentation. Fragmentation is out of order internal pages for contiguous access. Empty space on a page isn't fragmentation. If anything, it helps to AVOID fragmentation, because page splits won't occur if there's room to include larger updates or inserts.

    My personal definitions:

    Internal (logical) fragmentation is when your MDF file is contiguous on disk but your data is non-contiguous internally. External Fragmentation is when your actual file is in non-contiguous space on the physical drive itself. Internal Fragmentation is dealt with via SQL Tools. External Fragmentation is discussed with your SAN admins or via external tools.

    Both slow you down.

    I appreciate your response. I do not doubt your logic, I doubt the premise on which it is built. I think empty space in a page created by a delete qualifies as internal fragmentation as it relates to the index. This can be measured by examining the avg_page_space_used_in_percent column in sys.dm_db_index_physical_stats in comparison to the index's fill factor.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Ok...

    Index (Logical) Fragmentation (also sometimes called external fragmentation, though the term is used for other things too) is the result of inserts and updates (row-widening updates) that result in pages of an index not being in their physical order. This is caused by page splits, hence can only happen when data is added to a page and does not fit.

    Low page density (called internal fragmentation in many places, I prefer not to use that term as it's prone to misunderstandings, multiple definitions and because it has nothing to do with fragments of anything) is when pages are fairly empty on average. Can the the result of a too-low fill factor. Can be the result of page splits (so inserts or row-widening updates), can be the result of deletes or row-reducing updates.

    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
  • Evil Kraig F (6/12/2012)


    SueTons (6/12/2012)


    Hi, I know that deleting a row can cause fragmentation since it creates free space within a page, also inserting can cause fragmentation due to page splits. A row cannot span into two pages from my understanding. All of these are internal fragmentation I believe.....so, what do you mean by external fragmentation?....that's where I am little confused.

    That is not fragmentation. Fragmentation is out of order internal pages for contiguous access. Empty space on a page isn't fragmentation. If anything, it helps to AVOID fragmentation, because page splits won't occur if there's room to include larger updates or inserts.

    My personal definitions:

    Internal (logical) fragmentation is when your MDF file is contiguous on disk but your data is non-contiguous internally. External Fragmentation is when your actual file is in non-contiguous space on the physical drive itself. Internal Fragmentation is dealt with via SQL Tools. External Fragmentation is discussed with your SAN admins or via external tools.

    Both slow you down.

    From my quick research... Internal fragmentation is directly related to page fullness. External fragmentation is when the logical order of the pages does not match the physical order of the pages. So depending on how you define the simple word "fragmentation," deletes may or may not cause it.

    Source 1: http://blog.sqlauthority.com/2010/01/12/sql-server-fragmentation-detect-fragmentation-and-eliminate-fragmentation/

    Source 2: http://www.sqlservercentral.com/blogs/practicalsqldba/2012/04/05/sql-server-index-fragmentation-understanding-fragmentation/

    I'm sure there is something in Kalen Delaney's SQL Server Internals book. I'll take a look when I get to work to cite another source or prove myself wrong.

    EDIT: No need to check, Gail posted 🙂

    Jared
    CE - Microsoft

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply