mantenimiento - Cuándo crear un nuevo índice SQL Server?

mantenimiento de indices sql server

Question

Obviamente (creo), la creación de un índice en una columna BIT es innecesaria. Sin embargo, si tenía una columna que necesita buscar en la que cada valor es probablemente único, como BlogPost o StreetAddress o algo así, entonces un índice parece apropiado (de nuevo, creo).

Pero, ¿cuál es el límite? ¿Qué sucede si espera 10.000 filas y tendrá alrededor de 20 valores únicos entre ellas? ¿Debería crearse un índice?

Gracias por adelantado.

Answer 1

no es necesario crear un índice en una columna BIT .

Estarás sorprendido.

He tenido que crear un índice que implique una columna de bit para una consulta como:

SELECT foo.Name FROM foo WHERE foo.Active = 1

Sin embargo, había alrededor de 300,000 filas en la tabla.

Answer 2

Aquí hay buenas respuestas ya publicadas ... Solo agrego mis dos centavos ... Ejecute el DMV de Missing Index y vea si la tabla que mencionó está listada como candidata para crear un nuevo índice y ver la definición del índice.

De ¿Está usando SQL''s Missing Index DMVs?

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
  ''CREATE INDEX [missing_index_'' + CONVERT (varchar, mig.index_group_handle) + ''_'' + CONVERT (varchar, mid.index_handle)
  + ''_'' + LEFT (PARSENAME(mid.statement, 1), 32) + '']''
  + '' ON '' + mid.statement
  + '' ('' + ISNULL (mid.equality_columns,'''')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN '','' ELSE '''' END
    + ISNULL (mid.inequality_columns, '''')
  + '')''
  + ISNULL ('' INCLUDE ('' + mid.included_columns + '')'', '''') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Answer 3

En la columna que sugiere, sería lógico crear una tabla de referencia o búsqueda de los datos para evitar la redundancia de datos. Esto haría que su columna sea una clave externa, apuntando hacia el PK de la nueva tabla de búsqueda.

Todas las columnas de clave externa deben indexarse.

De lo contrario, evitaría colocar un índice en condiciones normales en dicha columna.

Answer 4

James golpeó el clavo en la cabeza. Añadiré que incluso una columna de bit podría beneficiarse de un índice según cómo esté utilizando la tabla. Por ejemplo, si necesita contar el número de filas que tienen un 1 muchas veces a lo largo del día, un índice podría ser útil. Los índices no siempre tratan de encontrar un solo registro; también se pueden usar para agregaciones.

Answer 5

La mejor respuesta a esto es perfilar sus consultas y ver si el índice mejora sus consultas. La dificultad para responder esto es que es casi imposible generalizar el comportamiento del optimizador de consultas.

Dicho esto, una regla empírica es si su selectividad es 10% o menos en una consulta dada en una tabla, entonces lo más probable es que se beneficie de un índice. Por lo tanto, en su ejemplo, puede beneficiarse de un índice si sus valores están distribuidos uniformemente. Sin embargo, teniendo en cuenta que su tabla es pequeña, su impulso de rendimiento puede ser insignificante.

Esta no es una regla difícil ya que hay muchos factores que pueden cambiar el 10%, incluido el uso de un tipo agrupado u otro tipo de índice, el tamaño de las filas, si algunas columnas no están en línea, estructura de consulta, etc.

También tenga en cuenta que hay una penalización de rendimiento significativa para insertar en una tabla con un índice. Si esta tabla se actualiza o anexa con frecuencia, el aumento de velocidad del índice puede ser negado por las inserciones y actualizaciones más lentas.

Consulte el artículo de MSDN sobre Tablescan vs acceso de índice .

Editar: como han señalado otros, su consulta puede beneficiarse de un índice si está realizando consultas de agregación, como contar el número de veces que aparece un valor particular. También puede beneficiarse si ordena con frecuencia en una columna en particular.

Answer 6

Los índices con cardinalidad baja son muy problemáticos. Si solo hay varios valores posibles, SQL Server casi siempre index-scans, sin importar las proporciones.

Ejemplo: He tenido una tabla con un campo de Estado que solo aceptaba los valores "A", "N" y "R" (para Activo, Nuevo y Jubilado). Normalmente se acercaba a una condición en la que el 95% "R", 4 +% fueron "A", y algunos fueron "N". SELECCIONAR DONDE estado = ''N'' sería escaneo de tabla, no importa qué.

PERO - hay un nuevo tipo de índice llamado índice filtrado, que finalmente maneja esta condición. También es útil cuando quiere excluir registros con valores NULL.

Answer 7

También debe examinar cuidadosamente sus índices si comienza a experimentar interbloqueos entre las consultas, generalmente entre un SELECT y un INSERT / UPDATE. Un índice mal elegido puede contribuir a puntos muertos, ya que no puede tener un índice en absoluto. Consulte este artículo de la base de conocimientos para obtener información adicional. Generalmente, agregar un índice o modificar sus columnas incluidas ayudará a resolver dichos bloqueos. Asegúrese de examinar el plan de consultas de las consultas afectadas.

Answer 8

Una de las mejores maneras es utilizar las vistas mvp en SQL Server i sugest. No reinicie su servidor por una semana y luego ejecute esta consulta:

USE master; 
Go
SELECT d.database_id,
  d.object_id,
  d.index_handle,
  d.equality_columns,
  d.inequality_columns,
  d.included_columns,
  d.statement AS fully_qualified_object,
  gs.*
FROM   sys.dm_db_missing_index_groups g
JOIN   sys.dm_db_missing_index_group_stats gs ON   gs.group_handle = g.index_group_handle
JOIN   sys.dm_db_missing_index_details d ON   g.index_handle = d.index_handle

Go

SELECT mig.index_group_handle,
  mid.index_handle,
  migs.avg_total_user_cost AS AvgTotalUserCostThatCouldbeReduced,
  migs.avg_user_impact AS AvgPercentageBenefit,
  ''CREATE INDEX missing_index_'' + CONVERT (varchar, mig.index_group_handle)
  + ''_'' + CONVERT (varchar, mid.index_handle)
  + '' ON '' + mid.statement
  + '' ('' + ISNULL (mid.equality_columns,'''')
  + CASE
         WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns
         IS NOT NULL THEN '',''
         ELSE ''''
    END
    + ISNULL (mid.inequality_columns, '''')
  + '')''
  + ISNULL ('' INCLUDE ('' + mid.included_columns + '')'', '''') AS  create_index_statement
FROM sys.dm_db_missing_index_groups mig 
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle =  mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle =  mid.index_handle
Order By migs.avg_user_impact Desc

luego verifica tus tablas y crea el índice requerido.

Answer 9

Yo diría que todo depende de cómo se usa la tabla y los requisitos generales del sistema. Por ejemplo, si es parte de un JOIN grande y el padre es una gran tabla de tipo de informe, entonces querrá que el índice esté seguro. Si es relativamente pequeño en comparación con las otras tablas en la base de datos, y está muy insertado y raramente leído, entonces es probable que no se desee un índice.

Pero el alcance de la operación entre toda la base de datos frente a los recursos disponibles que se asignarán es el factor clave de decisión. Es la forma en que se comporta esta tabla + índice posible en todo el sistema en comparación con todas sus otras tablas y sus requisitos. Si no se tiene en cuenta el panorama general, se podría matar a todo el sistema tratando de aplicar alguna regla arbitraria simplemente por el simple hecho de aplicar una regla arbitraria.