Index - CloudDB by mnadobnikCloudDB by mnadobnik

Mateusz Nadobnik

Czerwiec 15, 2018

Sprawdź, co robią Twoje indeksy – sys.dm_db_index_usage_stats

Bieganie, sport, który stał się bardzo popularny, nie ma, co się dziwić. Dostarcza sporą dawkę endorfin. Dzięki temu paradoksalnie mimo zmęczenia czujemy się pełni energii. To uczucie wielu ludzi nakręcą, biegają i częściej. Takie podejście może powodować pewne kłopoty bo więcej nie znaczy lepiej.

Pewnie czytasz i się zastanawiasz, co ma bieganie do baz danych? Bo raczej nie o bieganiu wpisu się spodziewasz. Odpowiadam, z bazami jest trochę jak z bieganiem. Przesadzając z ilością treningów możemy doprowadzić do gorszych rezultatów i samopoczucia, przesadzając z indeksami możemy doprowadzić do spadku wydajności. Żeby móc to jednak stwierdzić, niezbędne są dane, które trzeba zbierać i analizować.

Mateusz Nadobnik

Styczeń 17, 2017

Indeksy pokrywające

Na temat indeksów pokrywających napisano już wiele ale niekiedy warto zrobić sobie małą powtórkę wiedzy.

Tak więc, indeks pokrywając to nic innego jak pokrycie zapytania indeksem który zawiera wszystkie kolumny (w tym część tylko dołączona) z żądania. Przy takim podejściu unikamy operacji wyszukiwania w indeksie klastrowym bądź stercie. Jest to duża zaleta ale nie główna.

Taka metoda dostępu do danych jest efektywna przede wszystkim z względu na mniejszy rozmiar wiersza. Zakładając, że w indeksie klastrowym na jednej stronie jesteśmy wstanie upakować 80 wierszy (w uproszczonym ujęciu zakładając, że jeden wiersz to 100bajtów = 0,1kb * 80 = 8kb), to przy indeksie pokrywającym np. przy połowie mniejszej ilości kolumn otrzymamy 160 wierszy. Teoretyczne zapytanie pobierające 10000 wierszy musi przeskanować o połowę mniejszą ilość stron na poziomie liści indeksu nieklastrowego.

Mateusz Nadobnik

Grudzień 17, 2016

[PowerShell + SQL] Masowy zrzut T-SQL dla indeksów

W trakcie optymalizacji baz danych pod kątem indeksów należałoby się pozbyć nieużywanych, kosztowych bądź zduplikowanych indeksów (postaram się o tym napisać więcej w kolejnym wpisie) Przy tego typu zmianach niezbędne jest pozostawienie sobie możliwości wycofania zmian, ja to wykonuję poprzez zrzut skryptu T-SQL dla indeksu.

Przy większej ilości indeksów nie chciałbym robić tego pojedynczo, dlatego posługuję się skryptem których chciałbym się podzieli.

  <#
.Synopsis
    Umożliwia zrzut skryptu T-SQL indeksów z bazy danych
.DESCRIPTION
    Umożliwia zrzut skryptu T-SQL indeksów z bazy danych
.EXAMPLE
    Zrzut skryptu dla dwoch indeksow

    Get-CreateIndexScript -SqlServer SERWER-SQL -DatabaseName AdventureWorks2012 -IndexName ('IX_TransactionHistoryArchive_ProductID', 'IX_WorkOrderRouting_ProductID')
.EXAMPLE
   Zrzut skryptu dla dwoch indeksow i zapisanie do pliku 

   .\Get-CreateIndexScript.ps1 -SqlServer SERWER-SQL -DatabaseName AdventureWorks2012 -IndexName ('IX_TransactionHistoryArchive_ProductID', 'IX_WorkOrderRouting_ProductID') -Verbose | Out-File C:\file.sql
.EXAMPLE
   Zrzut skryptów dla indeksów pobranych z pliku płaskiego 

   Get-CreateIndexScript -SqlServer SERWER-SQL -DatabaseName AdventureWorks2012 -IndexName (Get-Content C:\Temp\indexes.txt) -Verbose | Out-File D:\Temp\
.LINK
   Author: Mateusz Nadobnik 
   Link: blog.mnadobnik.pl
   Date: 22.10.2016
   Version: 0.0.1.1
    
   Keywords: 
   Notes: 
   ChangeLog: 22.10.2016 - napisanie skryptu
              30.10.2016 - dodanie obługi błędów
#>
[CmdletBinding()]
param(
    #Address to SQL Server
    [Parameter(Mandatory=$true)]
        [string]$SqlServer,
    #Database Name
    [Parameter(Mandatory=$true)]
        [string]$DatabaseName,
    #Array with index names
    [Parameter(Mandatory=$true)]
        [array]$IndexName
    )

    Begin 
    {
        try 
        {
            [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
        }
        catch
        {
            Write-Warning $Error[0]
            return
        }

        $connectsqlserver = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServer
        $connectsqlserver.ConnectionContext.ApplicationName = "Powershell - Get-CreateIndexScript"
        $connectsqlserver.ConnectionContext.ConnectTimeout = 1

        Write-Verbose "Connect to server $SqlServer"
        if ($connectsqlserver.ConnectionContext.IsOpen -eq $false) 
        {
            $connectsqlserver.ConnectionContext.Connect()
        }

        $a = "'" + ($IndexName -join "','") + "'"
        $query = "Select i.name as index_name, t.name as table_name, SCHEMA_NAME(schema_id) as schema_name
        from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) s
        Join sys.indexes i 
        On i.object_id = s.object_id and i.index_id = s.index_id
        Inner join sys.tables t on i.object_id = t.object_id and t.object_id = s.object_id
        Where i.name is not null and i.name in  ($a)"

        Write-Verbose "Query  `n        $query"
        
        try
        {
            $db = $connectsqlserver.Databases.Item($DatabaseName)
            [array]$Indexes = ($db.ExecuteWithResults($query)).Tables[0]
        }
        catch
        {
            Write-Warning $Error[0]
            return
        }

        [string]$Script = "/******    Dump of index - $SqlServer    Script Date: $(Get-Date -Format "dd-MM-yyyy hh:mm:ss") ******/`nUSE [$DatabaseName]`nGO`n`n"
    }
    Process 
    {
        foreach ($row in $Indexes)
        {
            try
            {
                Write-Verbose "Get script for $($row.index_name)"
                $Script += "/****** Object:    $($row.index_name)    Script Date: $(Get-Date -Format "dd-MM-yyyy hh:mm:ss") ******/`n"
                $Script += $connectsqlserver.databases[$DatabaseName].Tables.Item($row.table_name,$row.schema_name).Indexes[$row.index_name].Script() 
                $Script += ";`nGO `n`n"
            }
            catch
            {
                Write-Warning $Error[0]
            }
        }
    }
    End {
        $connectsqlserver.ConnectionContext.Disconnect()
        return $Script 
    }

100

101

102

103

104

105

106

107

.Synopsis

Umożliwia zrzut skryptu T-SQL indeksów z bazy danych

.DESCRIPTION

Umożliwia zrzut skryptu T-SQL indeksów z bazy danych

.EXAMPLE

Zrzut skryptu dla dwoch indeksow

Get-CreateIndexScript -SqlServer SERWER-SQL -DatabaseName AdventureWorks2012 -IndexName ('IX_TransactionHistoryArchive_ProductID', 'IX_WorkOrderRouting_ProductID')

.EXAMPLE

Zrzut skryptu dla dwoch indeksow i zapisanie do pliku

.\Get-CreateIndexScript.ps1 -SqlServer SERWER-SQL -DatabaseName AdventureWorks2012 -IndexName ('IX_TransactionHistoryArchive_ProductID', 'IX_WorkOrderRouting_ProductID') -Verbose | Out-File C:\file.sql

.EXAMPLE

Zrzut skryptów dla indeksów pobranych z pliku płaskiego

Get-CreateIndexScript -SqlServer SERWER-SQL -DatabaseName AdventureWorks2012 -IndexName (Get-Content C:\Temp\indexes.txt) -Verbose | Out-File D:\Temp\

.LINK

Author: Mateusz Nadobnik

Link: blog.mnadobnik.pl

Date: 22.10.2016

Version: 0.0.1.1

Keywords:

Notes:

ChangeLog: 22.10.2016 - napisanie skryptu

30.10.2016 - dodanie obługi błędów

[CmdletBinding()]

param(

#Address to SQL Server

[Parameter(Mandatory=$true)]

[string]$SqlServer,

#Database Name

[Parameter(Mandatory=$true)]

[string]$DatabaseName,

#Array with index names

[Parameter(Mandatory=$true)]

[array]$IndexName

)

Begin

{

try

{

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

}

catch

{

Write-Warning $Error[0]

return

}

$connectsqlserver = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServer

$connectsqlserver.ConnectionContext.ApplicationName = "Powershell - Get-CreateIndexScript"

$connectsqlserver.ConnectionContext.ConnectTimeout = 1

Write-Verbose "Connect to server $SqlServer"

if ($connectsqlserver.ConnectionContext.IsOpen -eq $false)

{

$connectsqlserver.ConnectionContext.Connect()

}

$a = "'" + ($IndexName -join "','") + "'"

$query = "Select i.name as index_name, t.name as table_name, SCHEMA_NAME(schema_id) as schema_name

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

Join sys.indexes i

On i.object_id = s.object_id and i.index_id = s.index_id

Inner join sys.tables t on i.object_id = t.object_id and t.object_id = s.object_id

Where i.name is not null and i.name in ($a)"

Write-Verbose "Query `n $query"

try

{

$db = $connectsqlserver.Databases.Item($DatabaseName)

[array]$Indexes = ($db.ExecuteWithResults($query)).Tables[0]

}

catch

{

Write-Warning $Error[0]

return

}

[string]$Script = "/****** Dump of index - $SqlServer Script Date: $(Get-Date -Format "dd-MM-yyyy hh:mm:ss") ******/`nUSE [$DatabaseName]`nGO`n`n"

}

Process

{

foreach ($row in $Indexes)

{

try

{

Write-Verbose "Get script for $($row.index_name)"

$Script += "/****** Object: $($row.index_name) Script Date: $(Get-Date -Format "dd-MM-yyyy hh:mm:ss") ******/`n"

$Script += $connectsqlserver.databases[$DatabaseName].Tables.Item($row.table_name,$row.schema_name).Indexes[$row.index_name].Script()

$Script += ";`nGO `n`n"

}

catch

{

Write-Warning $Error[0]

}

End {

$connectsqlserver.ConnectionContext.Disconnect()

return $Script

}