locked
Need a powershell script for SQL index Fragmentation RRS feed

  • Question

  • Hello Friends,

    I Hope everyone is doing Well.

    Requesting you to please help me on how get a notification mail alert 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 
    Wednesday, December 28, 2016 11:58 AM

Answers

  • Hi SQLDBA421,

    I cannot seem to find any SQL Server alerts regarding index fragmentation, so I would assume the ideal solution is to configure database mail on each instance and scheduling a SQL Agent job to send the query result when it’s not empty. If it’s not possible, does a PowerShell script that gather index fragmentation information mets your requirement? If so, you could take a look at the script below:

    $ServerName = 'HOSTNAME
    $InstanceName = 'DEFAULT'
    
    $DBNameList =  invoke-Sqlcmd -query "                 
    select HOST_NAME() AS [HOSTNAME], @@SERVICENAME AS [INSTANCENAME],name AS [DBName] 
    from sys.databases 
    where state = 0 and name not in ('master','tempdb','model','msdb');
    " -serverinstance $ServerName
    $DBNameList
    
    $Results = @()
    
    foreach($Name in $DBNameList)
    {
    $NameDB = $Name.DBName
    $NameHN = $Name.HOSTNAME
    $Results += Invoke-Sqlcmd -Query "
    select HOST_NAME() AS [HOSTNAME], @@SERVICENAME AS [INSTANCENAME], DB_NAME(database_id) AS [DBName], SI.name AS [IndexName], SI.object_id, SDI.index_type_desc AS [IndexType], SDI.avg_fragmentation_in_percent AS [FragmentPercentage]
    from sys.dm_db_index_physical_stats (db_id(),NULL,NULL,NULL,NULL) SDI
    inner join sys.indexes SI
    on SDI.object_id = SI.object_id
    where SDI.avg_fragmentation_in_percent >30
    order by SDI.avg_fragmentation_in_percent DESC
    " -Database $NameDB -ServerInstance $NameHN ##\$InstanceName
    }
    
    $Results
    Notice that there are few parts code I haven’t handled yet:
    1. Fetch Hostname/instance name from file.
    2. Insert results back into a table.
    3. Handling the difference between named instance and default instance.

    On the other hand, if you have SCOM server in your environment, you could take a look into this blog.

    If you have any other questions, please let me know.

    Regards,
    Lin


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, December 29, 2016 6:57 AM
  • Thank you Phil for your quick reply...Please see my comments inline below..

    Are you meaning index fragmentation? Do you want to trigger an email alert when just one index is fragmented in excess of 30% ?  -->Yes Just we need a notification email alert from our monitoring server if the index fragmentation is more than 30% for all DBs servers but  we can't run those script each and every server,,so that reason i would like to develop these script using powershell script ..i mean we can mentioned like servers.txt..

    Thanks in advance your help...

    Let me first tell you fragmentation is either for heap or for clustered index so if you are looking for alert for all indexes which are fragmented > 30 % I think in long run it would create a problem with lot of emails. 

    I don't know powershell but I can create a script which would get output from sys.dm_db_index_physical_stats dump that output in temp table and see what all indexes  fragmentation > 30 % and then shoot an alert but believe me this is really not useful. Instead why not weekly create a maintenance plan which would rebuild and reorganize all indexes. I have a small a script here


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, December 29, 2016 5:16 AM

All replies

  • Hi,

    refer following index maintenance script

    http://sqlblog.com/blogs/allen_white/archive/2010/11/13/scripting-index-maintenance-with-powershell.aspx

    index rebuild script

    https://sethusrinivasan.wordpress.com/2012/02/14/index-rebuild-on-large-database-sql-agent-powershell-job/

     email alert in powershell


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.


    • Edited by AV111 Wednesday, December 28, 2016 1:26 PM email
    Wednesday, December 28, 2016 1:23 PM
  • Are you meaning index fragmentation? Do you want to trigger an email alert when just one index is fragmented in excess of 30% ?

    The scripts that I've seen which gather average index fragmentation metrics are very resource intensive, so it would be risky to run such a thing on a regular schedule to check average index fragmentation for purposes of triggering an alert, especially on a Production system.

    Thanks,


    Phil Streiff, MCDBA, MCITP, MCSA

    Wednesday, December 28, 2016 1:27 PM
  • Thank you Phil for your quick reply...Please see my comments inline below..

    Are you meaning index fragmentation? Do you want to trigger an email alert when just one index is fragmented in excess of 30% ?  -->Yes Just we need a notification email alert from our monitoring server if the index fragmentation is more than 30% for all DBs servers but  we can't run those script each and every server,,so that reason i would like to develop these script using powershell script ..i mean we can mentioned like servers.txt..

    Thanks in advance your help...

    Wednesday, December 28, 2016 1:47 PM
  • Thank you Vishe for your quick help..but i need all servers not only individual DB or server..I'm planning to implement these fragmentation script from our monitoring server if the fragmentation in excess of 30%..requesting you to please help me on this..

    Thanks in adavnce for your help :)

    Wednesday, December 28, 2016 1:50 PM
  • Thank you Phil for your quick reply...Please see my comments inline below..

    Are you meaning index fragmentation? Do you want to trigger an email alert when just one index is fragmented in excess of 30% ?  -->Yes Just we need a notification email alert from our monitoring server if the index fragmentation is more than 30% for all DBs servers but  we can't run those script each and every server,,so that reason i would like to develop these script using powershell script ..i mean we can mentioned like servers.txt..

    Thanks in advance your help...

    Let me first tell you fragmentation is either for heap or for clustered index so if you are looking for alert for all indexes which are fragmented > 30 % I think in long run it would create a problem with lot of emails. 

    I don't know powershell but I can create a script which would get output from sys.dm_db_index_physical_stats dump that output in temp table and see what all indexes  fragmentation > 30 % and then shoot an alert but believe me this is really not useful. Instead why not weekly create a maintenance plan which would rebuild and reorganize all indexes. I have a small a script here


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, December 29, 2016 5:16 AM
  • Hi SQLDBA421,

    I cannot seem to find any SQL Server alerts regarding index fragmentation, so I would assume the ideal solution is to configure database mail on each instance and scheduling a SQL Agent job to send the query result when it’s not empty. If it’s not possible, does a PowerShell script that gather index fragmentation information mets your requirement? If so, you could take a look at the script below:

    $ServerName = 'HOSTNAME
    $InstanceName = 'DEFAULT'
    
    $DBNameList =  invoke-Sqlcmd -query "                 
    select HOST_NAME() AS [HOSTNAME], @@SERVICENAME AS [INSTANCENAME],name AS [DBName] 
    from sys.databases 
    where state = 0 and name not in ('master','tempdb','model','msdb');
    " -serverinstance $ServerName
    $DBNameList
    
    $Results = @()
    
    foreach($Name in $DBNameList)
    {
    $NameDB = $Name.DBName
    $NameHN = $Name.HOSTNAME
    $Results += Invoke-Sqlcmd -Query "
    select HOST_NAME() AS [HOSTNAME], @@SERVICENAME AS [INSTANCENAME], DB_NAME(database_id) AS [DBName], SI.name AS [IndexName], SI.object_id, SDI.index_type_desc AS [IndexType], SDI.avg_fragmentation_in_percent AS [FragmentPercentage]
    from sys.dm_db_index_physical_stats (db_id(),NULL,NULL,NULL,NULL) SDI
    inner join sys.indexes SI
    on SDI.object_id = SI.object_id
    where SDI.avg_fragmentation_in_percent >30
    order by SDI.avg_fragmentation_in_percent DESC
    " -Database $NameDB -ServerInstance $NameHN ##\$InstanceName
    }
    
    $Results
    Notice that there are few parts code I haven’t handled yet:
    1. Fetch Hostname/instance name from file.
    2. Insert results back into a table.
    3. Handling the difference between named instance and default instance.

    On the other hand, if you have SCOM server in your environment, you could take a look into this blog.

    If you have any other questions, please let me know.

    Regards,
    Lin


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, December 29, 2016 6:57 AM
  • Thank you shashank for your quick response,But that is our requirement.

    Please note our maintenance plan jobs are in place and its running fine since if the fragmentation is more than 30% need to check manually on the servers .

    To avoid this,i would like to implement a Powershell script to find the Fragmentation details greater than 30% for a list of servers and   the results needs to be exported in excel ,we need this results over the email..Please help..

    Thanks in advance!!


    Monday, January 9, 2017 6:29 AM
  • Thank you so much lin for your guidance and help.

    its very helpful, but Need Powershell script to find the Fragmentation details greater than 30% for a list of servers and the results needs to be exported in excel. We need thi sresults over the email.Pls help

    I found the script from below  link and I have modified as per my requirement but i'm getting the errors

    https://vox.veritas.com/t5/Downloads/SQL-Database-Fragmentation-Level-Email-Reporting/ta-p/811611

    Can you please have a look into this.

    Thanks in advance!!


    Monday, January 9, 2017 6:33 AM
  • You can use an undocumented sp to run thrue the all databases on the server

    PS Create a main table with all needed columns (outout of that DBCC) 

    INSERT INTO tbl EXEC sp_MSForeachDB "Use ? DBCC SHOWCONTIG WITH TABLERESULTS"


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, January 9, 2017 6:38 AM
    Answerer