Top 10 Index T-SQL che gli amministratori di database di SQL Server deve sapere

Amministratori di database di SQL Server sapere che gli indici nei database sono molto simili agli indici nelle biblioteche.Un indice in un database è una struttura associata a una tabella o vista che accelera il recupero delle righe della tabella o vista.

Questo articolo elenca i top indicizzato T-SQL che sono utili per gli amministratori di database di SQL Server.Il T-SQL trattati in questo articolo sono classificati in tre categorie: definizione dell'indice o Crea, query - Query informazioni sugli indici relativi e manutenzione.

Definizione - Crea indice

1.Indice cluster

Indici cluster memorizzare i dati di righe in modo ordinato nella tabella in base alla loro valori fondamentali.Un solo indice cluster possono essere creati per ogni tabella, in quanto le righe di dati stessi possono solo essere ordinati in un unico ordine.

Un indice cluster può essere creato durante la creazione di vincoli come chiave primaria su una tabella esistente.Esempio:

ALTER TABLE [MyAddress] 
ADD CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED 
(
 [AddressID] ASC
) ON [PRIMARY]
GO

Un indice cluster possono anche essere creati su una colonna senza clausola di vincoli legati.Esempio:

CREATE INDEX CLUSTERED [MyAddress_id_CIX] ON [MyAddress1] 
(
 [ID] ASC
) ON [PRIMARY]
GO

2.Non indice cluster

In generale, gli indici non cluster vengono creati per migliorare le prestazioni delle query di uso frequente non coperte dal indice cluster.In un indice non cluster, l'ordine logico dell'indice non corrisponde l'ordine fisico delle righe memorizzate su disco.

Un indice non cluster può essere creato su una tabella esistente che copre le colonne non coperti da indice cluster.Esempio:

CREATE UNIQUE INDEX NONCLUSTERED 
[NIX_col5_col2_col3_col4_col6] 
ON [MyAddress] 
(
 [AddressLine1] ASC,
 [AddressLine2] ASC,
 [Città] ASC,
 [StateProvinceID] ASC,
 [CAP] ASC
) ON [PRIMARY]
GO

Un indice non cluster possono anche essere creati durante la creazione di vincoli sulla tabella esistente.Esempio:

ALTER TABLE [MyAddressType] 
ADD CONSTRAINT [DEFF_MyAddressType_ModifiedDate]  
DEFAULT (GETDATE ()) PER [ModifiedDate]
GO

3.XML Index

Un indice XML può essere creato su una colonna XML e la tabella deve avere un indice cluster sulla chiave primaria.L'indice XML può essere primaria o secondaria.

Un indice XML primario può essere creato come illustrato di seguito:

CREATE INDEX PRIMARIA idx_xCol_MyTable XML MyTable (xCol)

Un indice XML secondario può essere creato come illustrato di seguito:

CREATE TABLE MyTable (Col1 tasto INT PRIMARY XmlCol XML)
GO
- Creazione di indice primario.
CREATE XML INDEX PRIMARIA PIdx_MyTable_XmlCol 
SU T (XmlCol)
GO
- Creazione di indici secondari (PATH, VALUE, PROPRIETÀ).
CREATE XML INDEX PIdx_MyTable_XmlCol_PATH ON MyTable (XmlCol)
Utilizzo di XML INDEX PIdx_MyTable_XmlCol
PER PERCORSO
GO
CREATE XML INDEX PIdx_MyTable_XmlCol_VALUE SU T (XmlCol)
Utilizzo di XML INDEX PIdx_MyTable_XmlCol
PER VALORE
GO

4.Indice spaziale

SQL Server 2008 ha fornito un particolare tipo di colonna chiamata una colonna spaziale, che è una colonna della tabella che contiene i dati di un tipo di dati spaziali, come la geometria o geografia.

Un indice spaziale può essere creato utilizzando la seguente sintassi:

CREATE TABLE MySpatialTable (id chiave primaria int, geometria geometry_col);
CREATE SIndx_MySpatialTable_geometry_col1 SPATIAL INDEX 
   ON MySpatialTable (geometry_col)
   CON (BOUNDING_BOX = (0, 0, 500, 200));

Query dei metadati relativi Indice

5.Trova tutti gli indici

La seguente interrogazione può essere usato per interrogare tutte le tabelle, colonne e indici sul database corrente:

SELECT OBJECT_SCHEMA_NAME (BaseT. [object_id], DB_ID ()) AS [Schema],  
  BaseT. [Nome] AS [nome_tabella], I. [nome] AS [nome_indice], AC. [Nome] AS [nome_colonna],  
  I. [type_desc]
DA sys. [Tabelle] AS BaseT  
  INNER JOIN sys. [Indici] Ho ON BaseT. [Object_id] = I. [object_id]  
  INNER JOIN sys. [Index_columns] IC ON I. [object_id] = IC. [Object_id] 
  INNER JOIN sys. [All_columns] AC ON BaseT. [Object_id] = AC. [Object_id] e IC. [Column_id] = AC. [Column_id] 
DOVE BaseT. [Is_ms_shipped] = 0 e I. [type_desc] <> 'HEAP' 
ORDER BY BaseT. [Nome], I. [index_id], IC. [Key_ordinal]

6.Frammentazione

La seguente interrogazione può essere utilizzato per trovare la frammentazione indice su tutte le tabelle nel database corrente:

Object_name SELECT (IPS.object_id) AS [TableName], 
   SI.name AS [IndexName], 
   IPS.Index_type_desc, 
   IPS.avg_fragmentation_in_percent, 
   IPS.avg_fragment_size_in_pages, 
   IPS.avg_page_space_used_in_percent, 
   IPS.record_count, 
   IPS.ghost_record_count,
   IPS.fragment_count, 
   IPS.avg_fragment_size_in_pages
DA sys.dm_db_index_physical_stats (DB_ID (DB_NAME ()), NULL, NULL, NULL, 'DETTAGLIATE') IPS
   JOIN sys.tables ST CON (nolock) = ON IPS.object_id ST.object_id
   JOIN sys.indexes SI CON (nolock) = ON IPS.object_id SI.object_id E IPS.index_id = SI.index_id
DOVE ST.is_ms_shipped = 0
ordine di IPS.avg_fragment_size_in_pages disc

7.Missing indice

SQL Server registra gli indici che si pensa che si dovrebbe creare che aiuteranno a migliorare le prestazioni delle query.La lista seguente query tutti gli indici mancanti.

SELECT sys.objects.name
(* Avg_total_user_cost avg_user_impact) * (+ user_seeks user_scans) AS Impact
, 'CREATE ix_IndexName indice non cluster' + sys.objects.name COLLATE database_default + '(' + IsNull (mid.equality_columns,'') + CASO QUANDO mid.inequality_columns IS NULL 
                ALLORA''  
    CASO QUANDO mid.equality_columns ELSE IS NULL 
                    ALLORA''  
        ELSE END ',' + mid.inequality_columns FINE + ')' + CASO QUANDO mid.included_columns IS NULL 
                ALLORA''  
    ELSE 'CONTENERE (' + mid.included_columns + ')' + FINE ';' COME CreateIndexStatement
, Mid.equality_columns
, Mid.inequality_columns
, Mid.included_columns 
    COME DA sys.dm_db_missing_index_group_stats RPI 
            INNER JOIN sys.dm_db_missing_index_groups COME ON mig migs.group_handle = mig.index_group_handle 
            INNER JOIN sys.dm_db_missing_index_details COME ON metà mig.index_handle = mid.index_handle E mid.database_id = DB_ID () 
            INNER JOIN sys.objects CON (nolock) = ON mid.OBJECT_ID sys.objects.OBJECT_ID 
    WHERE (migs.group_handle IN 
        ( 
        SELECT TOP (500) group_handle 
            DA sys.dm_db_missing_index_group_stats CON (NOLOCK) 
            ORDER BY (avg_total_user_cost avg_user_impact *) * (+ user_seeks user_scans) DESC))  
        E OBJECTPROPERTY (sys.objects.OBJECT_ID, 'isusertable') = 1 
    ORDER BY DESC 2, 3 DESC 

8.Indice inutilizzate

La seguente dichiarazione elenca tutti gli indici che non sono stati utilizzati.Ciò genera anche l'istruzione DROP INDEX che può tornare utile quando si eliminano gli indici.

SELECT o.name, nomeindice = i.name, i.index_id   
, Si legge = user_seeks + + user_scans user_lookups   
, Scrive = user_updates   
, Righe = (SELECT SUM (p.rows) DA DOVE p.index_id sys.partitions p = s.index_id E s.object_id = p.object_id)
, CASE
 QUANDO s.user_updates <1 THEN 100
 ELSE 1.00 * (s.user_seeks + + s.user_scans s.user_lookups) / s.user_updates
  FINE COME reads_per_write
, 'DROP INDEX' + QUOTENAME (i.name) 
+ 'ON' + QUOTENAME (c.name) + '.'+ QUOTENAME (OBJECT_NAME (s.object_id)) come 'dichiarazione drop'
DA sys.dm_db_index_usage_stats s  
INNER JOIN i sys.indexes ON i.index_id = s.index_id E s.object_id = i.object_id   
INNER JOIN sys.objects o su s.object_id = o.object_id
INNER JOIN c sys.schemas su o.schema_id = c.schema_id
DOVE OBJECTPROPERTY (s.object_id, 'IsUserTable') = 1
E s.database_id = DB_ID ()   
E i.type_desc = 'cluster'
E i.is_primary_key = 0
E i.is_unique_constraint = 0
E (SELECT SUM (p.rows) DA DOVE p.index_id sys.partitions p = s.index_id E s.object_id = p.object_id)> 10000
ORDER BY legge

Indice Maintainenance

9.Ricostruisci indice

Quando un indice viene frammentato, richiede deframmentazione.Deframmentazione può essere fatto utilizzando la clausola di ricostruire quando alterare una tabella.Questo comando è equivalente a DBREINDEX DBCC nelle versioni di SQL Server prima del 2005.Il comando che possono essere utilizzate per ricostruire l'indice è la seguente:

USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
RICOSTRUZIONE;
GO

Se TUTTI non è specificato nel ricostruire, non sarà ricostruire un indice non cluster.

10.REORGANIZE indice

Specifica che il livello foglia dell'indice sarà riorganizzato.Il REORGANIZE dichiarazione viene sempre eseguita in linea.Questo comando è equivalente a DBCC INDEXDEFRAG nelle versioni di SQL Server prima del 2005.

USE AdventureWorks2008R2;
GO
ALTER PK_ProductPhoto_ProductPhotoID INDEX ON Production.ProductPhoto
REORGANIZE;
GO

Corso SQL Server - Corso Hyper-V -Corso Windows Server –
Certificazione Microsoft