Post reply

Fragmentation

  • February 26, 2013 at 2:44 am

    #394257

    Performance Slow.. how do i check the fragmentations, i mean which DB/tables to be reindexed :w00t:

    ************************************
    Every Dog has a Tail !!!!! :-D

  • February 26, 2013 at 2:51 am

    #1591078

    First find fragmentation this query helps you

    select a.*,b.AverageFragmentation from

    (

    SELECT

    tbl.name AS [Table_Name],

    tbl.object_id,

    i.name AS [Name],

    i.index_id,

    CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered],

    CAST(case when i.type=3 then 1 else 0 end AS bit) AS [IsXmlIndex],

    CAST(case when i.type=4 then 1 else 0 end AS bit) AS [IsSpatialIndex]

    FROM

    sys.tables AS tbl

    INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)

    )a

    inner join

    (

    SELECT

    tbl.object_id,

    i.index_id,

    fi.avg_fragmentation_in_percent AS [AverageFragmentation]

    FROM

    sys.tables AS tbl

    INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)

    INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS fi ON fi.object_id=CAST(i.object_id AS int) AND fi.index_id=CAST(i.index_id AS int)

    )b

    on a.object_id=b.object_id and a.index_id=b.index_id

    which ever having more fragmentation do reindex on that table

  • February 26, 2013 at 3:45 am

    #1591099

    Thanks BriPan 🙂

    One more query.. I am asked to Build a maintenance plan, Can u tell me the sequence in the belwo points

    1) Full Backup + Log Backup

    2) ReBuild Index

    3) Truncate Log

    4) Update Stats.. (Weekly)

    5) Archieving of data (Weekly)

    Please tell the sequence.. also tell any new things to include in this package?????

    So that the maintenecae Plan will be upto date & covering everything which is required..

    ************************************
    Every Dog has a Tail !!!!! :-D

  • February 26, 2013 at 4:04 am

    #1591111

    This is ok to me still at the end all depend on ur project requirement and DB requirement,u need to modify as per that only.

  • February 26, 2013 at 4:10 am

    #1591113

    But Truncate Log after rebuild index is valid???

    Truncate Query - (backup Log XYZ with truncate_only)

    Is this fine or should i execute some other query?? do u have any query?

    ************************************
    Every Dog has a Tail !!!!! :-D

  • February 26, 2013 at 5:08 am

    #1591130

    Don't truncate your log (not that you can in SQL 2008).

    Please read through this - Managing Transaction Logs[/url]

    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

  • February 26, 2013 at 5:15 am

    #1591135

    Thanks Gail 🙂

    But On weekly basis the Log gets Full & then application stops running or the DB runs very slow ... how to tackle this now...

    ************************************
    Every Dog has a Tail !!!!! :-D

  • February 26, 2013 at 5:22 am

    #1591137

    Scenario

    First Full backup was only taken... no Log backup was there.. hence the Log sie exceeds to maximum... upto 2GB+ :w00t:

    And so they use to Truncate log..

    Please assist what to do now in order to stream line this process...

    i mean to keep the Log size minimum..

    ************************************
    Every Dog has a Tail !!!!! :-D

  • February 26, 2013 at 5:31 am

    #1591140

    runal_jagtap (2/26/2013)

    Thanks Gail 🙂

    But On weekly basis the Log gets Full & then application stops running or the DB runs very slow ... how to tackle this now...

    Please assist what to do now in order to stream line this process...

    i mean to keep the Log size minimum..

    GilaMonster (2/26/2013)

    Please read through this - Managing Transaction Logs[/url]

    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

  • February 26, 2013 at 5:39 am

    #1591142

    No log backup means

    "My data is of no value to the business, it doesn't matter if I lose all changes since the last full backup". If this is true, set database into simple recovery mode.

    If, however, losing up to a day's worth of changes is going to cause disruption, make your users very unhappy and potentially lose people their jobs, which is more often the case - then you should backup the logs on a regular basis. If the business can cope ok with losing 15 minutes worth of data for example, backup the logs every 15 minutes. Somewhere Secure. NOT on the same spindles, using the same controller as the ones your data and log files are on.

    Then test restoring them to your DR kit regularly

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

Viewing 10 posts - 1 through 9 (of 9 total)

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