Sql Server Index Bakımında Cursor Kullanımı

Tablolarımız üzerinde bulunan indexler zamanla çalışan sorgular neticesinde bozulmalara uğramaktadır. Bu indexlerin belirli periyotlarla rebuild veya reorganize edilmesi gerekir. %5 ile %30 arası bozulmalarda reorganize %30 üzeri bozulmalarda ise rebuild işlemi yapılması Microsoft'un meşhur deyimi ile Best Practicedir.

Yukarıdada bahsettiğim gibi oluşturduğumuz indexlerde veri eklenmesi silinmesi gibi işlemler oldukça indexlerimiz pageleri arasında boşluklar oluşacak ve btree veri yapımızın dengesi bozulacaktır. Bu nedenle düzenli olarak bozulan indexlerimizi bulup bozulma oranlarına göre rebuild veya reorganize etmemiz gerekir.

Aşağıdaki DMV kullanılarak indexlerideki bozulma oranlarını görebiliriz.

SELECT TOP 200

DB_NAME() AS databaseName

,OBJECT_SCHEMA_NAME(s.object_id) AS SchemaName

, OBJECT_NAME(s.[object_id]) As TableName

,i.name As IndexName

,ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %]

FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null) s

INNER JOIN sys.indexes i on s.[object_id] =i.[object_id]

and s.index_id=i.index_id

INNER JOIN sys.indexes o on i.object_id=o.object_id

WHERE s.database_id=DB_ID()

and i.name is not null

and OBJECTPROPERTY(s.[object_id],'IsMsShipped')=0

order by [Fragmentation %] desc

Bu bozulma oranlarını gördükten sonra çeşitli yöntemlerle index bakımı yapabiliriz. Aşağıdaki scriptte cursor kullanarak index bakımını çok kolaylıkla yapabiliriz.

DECLARE @Database VARCHAR(255)  

DECLARE @Table VARCHAR(255) 

DECLARE @cmd NVARCHAR(500) 

DECLARE @fillfactor INT

SET @fillfactor = 30

DECLARE DatabaseCursor CURSOR FOR

SELECT name FROM master.dbo.sysdatabases  

WHERE name  IN ('SAROT_OTEL')  

ORDER BY 1 

OPEN DatabaseCursor 

FETCH NEXT FROM DatabaseCursor INTO @Database

WHILE @@FETCH_STATUS = 0 

BEGIN

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +

  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES

  WHERE table_type = ''BASE TABLE''' 

   -- create table cursor 

   EXEC (@cmd) 

   OPEN TableCursor  

   FETCH NEXT FROM TableCursor INTO @Table 

   WHILE @@FETCH_STATUS = 0  

   BEGIN 

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)

       BEGIN

           -- SQL 2005 or higher command

           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

           EXEC (@cmd)

       END

       ELSE

       BEGIN

          -- SQL 2000 command

          DBCC DBREINDEX(@Table,' ',@fillfactor) 

       END

       FETCH NEXT FROM TableCursor INTO @Table 

   END 

   CLOSE TableCursor  

   DEALLOCATE TableCursor 

   FETCH NEXT FROM DatabaseCursor INTO @Database

END

CLOSE DatabaseCursor  

DEALLOCATE DatabaseCursor

PowerShell Script Dili Kullanarak SQL Server Yedekleme

Bildiğiniz gibi SQL Server Express kullanıcıları için backupları otomatikleştirmek çok büyük problemdir. Bugün sizlere bu backupları otomatikleştirecek bir powershell uygulamasından bahsetmek istiyorum.

Bildiğiniz gibi DBAler için bir çok işlemi otomatikleştirmek çok önemlidir. Aşağıdaki PowerShell scripti değiştirerek sadece Backup işlemlerini değil bir çok işlemi otomatik hale getirebiliriz. Bunlara örnek olarak upgrade işlemi, hotfix veya service pack işlemleri olabilir.

Öncelikle powershell scriptimizi yazıyoruz. Bunun için Windows PowerShell ISE kullanabiliriz. Sonra bu dosyayı kaydediyoruz.

param(

    $serverName,

    $backupDirectory,

    $daysToStoreBackups

)

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $serverName

$dbs = $server.Databases

foreach ($database in $dbs | where { $_.IsSystemObject -eq $False })

{

    $dbName = $database.Name

    $timestamp = Get-Date -format yyyy-MM-dd-HHmmss

    $targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + ".bak"

    $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")

    $smoBackup.Action = "Database"

    $smoBackup.BackupSetDescription = "Full Backup of " + $dbName

    $smoBackup.BackupSetName = $dbName + " Backup"

    $smoBackup.Database = $dbName

    $smoBackup.MediaDescription = "Disk"

    $smoBackup.Devices.AddDevice($targetPath, "File")

    $smoBackup.SqlBackup($server)

    "backed up $dbName ($serverName) to $targetPath"

}

Copy-Item C:\Backup\Backups\* Z:\SQLYEDEKLEME

Get-ChildItem "$backupDirectory\*.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreBackups)} |% {Remove-Item $_ -force }

"removed all previous backups older than $daysToStoreBackups days"

Dosyamız dışarıdan 3 tane parametre alacak. Çalıştıracağımız zaman 3 tane parametre gönderiyoruz. Bunlar ServerName adını verdiğimiz SQLSERVER Instance Name, Backup alacağımız Directory ve Backupları saklama günümüz.

powershell C:\Backup\Tools\Backup.ps1 -serverName "SQLSERVER" -backupDirectory "C:\Backup\Backups" -daysToStoreBackups 2 >> C:\Backup\Logs\%date%.log

Ben scripte ayrıca yedekleri diğer bir storage kopyaladığım için  Copy-Item scriptinide ekledim.