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
