Total Pageviews

Tuesday, January 28, 2014

Database optimization - the missing step

Does your intelligence or your magical database optimization scripts really optimize the database? The traditional wisdom is to optimize indexes and statistics, but how about optimizing a HEAP (a table without clustered index) by removing fragmentation in your database?

Database optimization, more precisely Index optimization is one of the major tasks every DBA performs on a regular basis regardless the size of the database. Based on daily data changes and fragmentation threshold, DBA decides to optimize indexes such as REBUILD, RE-ORGANIZE.

Free automated script:
A lot of automated index optimization scripts are freely available to help improve your database performance. But if you carefully review these index optimization statements, you will discover that those scripts cautiously avoid optimizing HEAP. If so, then your database is not fully optimized.

Generally, if a HEAP table is part of the OLTP system, then it is highly recommended that it should be clustered. However, there are business logic, reasons and mistakes as well to not create clustered Index on a table and keep the table as a HEAP. As a result, executing queries against these tables become very resource intensive.

Starting form SQL 2008, a HEAP can be optimized as well with the “ALTER TABLE <xxxxx> REBUILD” option. Therefore, to optimize a HEAP we no longer need to create a clustered index and dropping it afterward.

Key points to remember:
1.      If the HEAP contains non-clustered indexes, all indexes will be rebuilt (dropped and recreated).
2.      Fill Factor can’t be set on a HEAP.
3.      All or a specific partition can be optimized.
4.      Data compression (PAGE or ROW level) can be set or changed.

Script to check HEAP fragmentation:
select  o.name ,
        ips.index_type_desc ,
        ips.avg_fragmentation_in_percent ,
        ips.record_count ,
        ips.page_count ,
        ips.compressed_page_count
from    sys.dm_db_index_physical_stats(db_id(), null, null, null, 'DETAILED') ips
        join sys.objects o on o.object_id = ips.object_id
where   ips.index_id = 0
        and ips.avg_fragmentation_in_percent > 0
order by ips.avg_fragmentation_in_percent desc;

T-SQL Statement example to optimize HEAP:
ALTER TABLE tblLarge REBUILD
ALTER TABLE tblLarge REBUILD with (MAXDOP = 4)
ALTER TABLE tblLarge REBUILD WITH (DATA_COMPRESSION = PAGE)
ALTER TABLE tblLarge REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = NONE)
ALTER TABLE tblLarge REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) )

Comments:
You should not rely on only index optimization; make sure that you are taking care of HEAP as well. In my experience, I have seen many DBAs - even SQL experts – overlooking this area.

Learn More:
A SQL Server DBA myth a day: (29/30) fixing heap fragmentation

ALTER TABLE (Transact-SQL)

Monday, January 6, 2014

A PowerShell Script to monitor enterprise wide disk space usage

The folks who are responsible for administering thousands of Windows Servers all know that monitoring disk space is crucial for application performance. When there are numerous Windows Servers, such as thousands, it becomes more difficult to know what is going on where; and thus putting an effective monitoring tool in place is absolutely necessary for proactive monitoring purposes.

I recently faced a challenge to collect disk space usage about more than 300 (SQL Servers) out of 2000 Windows Servers across the enterprise and send the collected information by e-mail. To complete the task, the output should be easy to understand and the server should contain a brief description so that we can quickly identify the purpose of the server. Following is a desirable output format.

Figure#1: Sample disk usage html report:

To achieve this goal, I have developed a custom PowerShell Script and scheduled it to run every 6 hours. It investigates all Windows Servers listed in a CSV file through Job. To schedule this script, I have used SQL Server Agent since I felt more comfortable with it and because it is easy to implement. However, Windows Schedule Task can also be used to do the same functions.

PowerShell Script:
Download the original Script: http://bit.ly/1cZNScb

#set-executionpolicy unrestricted

#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# Send email to all DBA
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

function Send-EmailToDBA {
 param(
        [Parameter(Mandatory=$true)][string]$emailBody,
        [Parameter(Mandatory=$true)][string]$emailSubject
    )

    $EmailFrom = "noreply@myCompanyorg"
    $EmailTo = "abcxyz@myCompanyorg, abc@myCompanyorg, xyz@myCompanyorg"

    $SMTPServer = "smtpServer.org"

    $mailer = new-object Net.Mail.SMTPclient($smtpserver)
    $msg = new-object Net.Mail.MailMessage($EmailFrom,$EmailTo,$EmailSubject,$Emailbody)
    $msg.IsBodyHTML = $true
    $mailer.send($msg)
} # end of function


#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# Get-DiskInfo
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

function Get-DiskInfo {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$True,ValueFromPipeline=$True)]
        [string[]]$FileFullPath = 'i:\ServerList\servers.txt',
        
        [Parameter(Mandatory=$True,ValueFromPipeline=$True)]
        [decimal]$DiskThreshold = 10
    )
    
    BEGIN {}
    PROCESS {
        $SHBServers = (import-csv $FileFullPath -Header Server, Description)
        foreach ($computer in $SHBServers) {           

            $Server = $($Computer.Server).split("\")[0]
            # $disks =Get-WMIObject -ComputerName $computer.server Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}
            $disks =Get-WMIObject -ComputerName $Server Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}
           
            foreach ($disk in $disks ) {

               if ($disks.count -ge 1) {           
                   $Used= $disk.freespace / $disk.size * 100
                   $result =  @{'Server'=$computer.server;
                              'Server Description'=$computer.description;
                              'Volume'=$disk.VolumeName;
                              'Drive'=$disk.name;
                              'Size (gb)'="{0:n2}" -f ($disk.size / 1gb);
                              'Used (gb)'="{0:n2}" -f (($disk.size - $disk.freespace) / 1gb);
                              'Free (gb)'="{0:n2}" -f ($disk.freespace / 1gb);
                              '% free'="{0:n2}" -f ($disk.freespace / $disk.size * 100)}                         
                             

                   $obj = New-Object -TypeName PSObject -Property $result
                   if ($Used -lt $Diskthreshold){  
                        Write-Output $obj }
               }
            }
        }
    }
    END {}
} # end of function

#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# Script to generate disk usage report
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
$Today = [string]::Format( "{0:dd-MM-yyyy}", [datetime]::Now.Date )
$ReportFileName = "i:\Sarjen\Report\DiskUsage_$Today.html"

# Custom HTML Report Formatting
$head = @"
        <style>
            BODY{font-family: Arial; font-size: 8pt;}
            H1{font-size: 16px;}
            H2{font-size: 14px;}
            H3{font-size: 12px;}
            TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse; background-color:#D5EDFA}
            TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #94D4F7;}
            TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}        
        </style>
"@

    #define an array for html fragments
    $fragments=@()

    # Set free disk space threshold below in percent (default at 10%)
    [decimal]$thresholdspace = 20

    #this is the graph character
    [string]$g=[char]9608 

    # call the main function
    $Disks = Get-DiskInfo `
                -ErrorAction SilentlyContinue `
                -FileFullPath ("i:\Sarjen\SQLServers.txt") `
                -DiskThreshold $thresholdspace
           
    #create an html fragment
    $html= $Disks|select @{name="Server";expression={$_.Server}},
                  @{name="Server Description";expression={$_."Server Description"}},
                  @{name="Drive";expression={$_.Drive}},
                  @{name="Volume";expression={$_.Volume}},
                  @{name="Size (gb)" ;expression={($_."size (gb)")}},
                  @{name="Used (gb)";expression={$_."used (gb)"}},
                  @{name="Free (gb)";expression ={$_."free (gb)"}},
                  @{name="% free";expression ={$_."% free"}},           
                  @{name="Disk usage";expression={
                        $UsedPer= (($_."Size (gb)" - $_."Free (gb)")/$_."Size (gb)")*100
                        $UsedGraph=$g * ($UsedPer/4)
                        $FreeGraph=$g* ((100-$UsedPer)/4)
                        #using place holders for the < and > characters
                         "xopenFont color=Redxclose{0}xopen/FontxclosexopenFont Color=Greenxclose{1}xopen/fontxclose" -f $usedGraph,$FreeGraph }}`
        | sort-Object {[decimal]$_."% free"} `
        | ConvertTo-HTML -fragment
    
    #replace the tag place holders.
    $html=$html -replace "xopen","<"
    $html=$html -replace "xclose",">"
    
    #add to fragments
    $Fragments+=$html         

    #write the result to a file
    ConvertTo-Html -head $head -body $fragments `
     | Out-File $ReportFileName
    
      # Open the html file
      # ii $ReportFileName

    $Emailbody= (Get-Content $ReportFileName ) | out-string
    $EmailSubject = "Disk usage report - Drives less than $thresholdspace% free space"

    Send-EmailToDBA -EmailBody $Emailbody -EmailSubject $EmailSubject                          


Figure#2: CSV file for Server list with description: 

Figure#3: SQL Agent Job to run the PowerShell Script:

Pre-requisites:
To collect disk information from multiple servers, we need the following rights or privileges:

1.      WMI access to the target server.
2.      Privileges to run PowerShell Script in the source server. Run the following command:

set-executionpolicy unrestricted

3.      Have PowerShell version 2.0 or above.

Script Explanation:
1.      The PowerShell script reads the Server name (figure#2) from a CSV file. This CSV file contains the Server name and description.
2.      The Script will check the percentage of free disk space, for example 10% or 20%, etc.
3.      The output will be preserved in HTML format.
4.      An e-mail notification will be sent and the email body will contain the HTML table.

Running the Script:
The script can be run manually or through a Windows or SQL Agent Job. Figure#3 is an example of how to run it by utilizing SQL Agent Job.

Conclusion:
I am running this script against 200+ productions Windows Server and it took around 2 minutes to complete. Although this script does its job, there is room for improvement, such as adding error handling and logging. So if you enhance this script and improve its functionality, then I request that you share your modified script with me.