Post reply

Index Fragmentation

  • December 27, 2016 at 8:52 am

    #338982

    Hello Friends,

    I Hope everyone is doing Well.

    Requesting you to please help me on how get a notification mail alert for if the fragmentation is more than 30% using power shell script and in table format.

    Anyone can help with that one or if any one have the script can you please provide with me..

    Thanks in advance!!

    SQL server DBA

  • December 28, 2016 at 4:40 am

    #1919835

    Anyone can help me on how get a notification mail alert for if the fragmentation is more than 30% using power shell script and in table format.

    Thank you in advance for your attention to this matter.

    Regards,

    DBA

    SQL server DBA

  • December 28, 2016 at 4:48 am

    #1919836

    Which bit do you need help with - checking the fragmentation or sending the e-mail? Use the dm_db_index_physical_stats function to get fragmentation levels. Use sp_send_dbmail to send the e-mail.

    John

  • December 28, 2016 at 5:04 am

    #1919837

    Thank you John for your quick reply 🙂 ...Yes checking the fragmentation for all SQL server instances from Monitoring server if fragmentation is more than 30%,i believe its possible from power shell script ..need a notification alert l to the DBA team for necessary action ...

    FYI...please find the below article for your reference,i have partially completed but i'm not getting the output..

    if anyone have the script in handy,please provide me as soon as possible.

    https://www.simple-talk.com/sql/database-administration/exceptional-powershell-dba-pt-3-collation-and-fragmentation/

    Thanks in advance for your help...

    SQL server DBA

  • December 28, 2016 at 9:43 am

    #1919898

    Database admin(DBA) (12/28/2016)

    Thank you John for your quick reply 🙂 ...Yes checking the fragmentation for all SQL server instances from Monitoring server if fragmentation is more than 30%,i believe its possible from power shell script ..need a notification alert l to the DBA team for necessary action ...

    FYI...please find the below article for your reference,i have partially completed but i'm not getting the output..

    if anyone have the script in handy,please provide me as soon as possible.

    https://www.simple-talk.com/sql/database-administration/exceptional-powershell-dba-pt-3-collation-and-fragmentation/

    Thanks in advance for your help...

    For what it's worth, I'm a firm believer in NOT centralizing such things. Checking for fragmentation is both disk and CPU intensive and if some bloody external process jumps in the middle of a critical batch file run on one of my servers, there will be a serious talk in the woodshed with someone and high velocity pork chops launched at point blank range will be a prime feature of the talk.

    Further, such centralized "features" form a single point of failure and external dependencies. I believe that each server should be autonomous. Other wise, what is the purpose of decentralizing servers to begin with.

    YES! I do believe in the ability to push such autonomous code to multiple servers to make life easier for the DBA but to make servers externally dependent is just the wrong thing to do even for something as supposed simple as checking for fragmentation.

    BTW, remember that the optimizer does NOT check for fragmentation and it makes absolutely no difference to single row lookups by the GUI or elsewhere. IMHO, defragmentation should only be used to recover large amounts of space for memory or disk. It actually doesn't help performance as much as people think. Instead, you should check for "average percentage of page used" and then be very careful of destroying the "natural" fill factors formed by everyday usage. If you defragment an index with the wrong fill factor, you can and will create a huge amount of blocking caused by page splits or waste even more space because the fill factor applies to the whole index or table (CI) rather than just the active parts of the table.

    Just so you know, I followed some advice from Brent Ozar and haven't rebuilt any index on my big production box since 17 Jan 2016 and performance has only gotten better both for batch processing and multi-row GUI queries and most of the indexes have stayed at 90% page space used with only a few dipping into the 80s.

    If you really want to ensure performance, make sure your stats stay up to date. Many people think that defragmentation was the key to a performance improvement because they also forgot that rebuilding an index automatically updates stats and most index maintenance routines include a stats rebuild for indexes that were only reorganized.

    --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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

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