Diario de SQL Server

Crear los índices que se necesitan... (1/2)

Escrito por qwalgrande 19-09-2010 en General. Comentarios (1)

Es my difícil dar con la medida precisa de los índices que se deben crear, debido a que cuando se diseñan las aplicaciones, es raro dar con el comportamiento típico que tendrá luego el usuario en su día a día. Así, uno pronostica qué búsquedas se harán y prepara los índices para ello. Acertar es algo muy diferente.

 

Lo ideal sería que el usuario usara la aplicación y después pudiera optimizarse el conjunto de consultas más significativo. Justamente eso es lo que nos proporcionan las DMVs que dan acceso a estadísticas de uso. Es decir, se pueden crear la índices que serán usados con certeza, sabiendo además cuánto mejorarán esas consultas, y cómo de frecuente será su uso.

 

La información se puede consultar de muchas formas, esta es la consulta que yo empleo:

 

select

db_name(d.database_id) as DB,

object_name(d.object_id, d.database_id) tabla,

s.avg_user_impact, s.user_seeks,

d.equality_columns, d.inequality_columns, d.included_columns,

p.row_count, l.num_esperas, l.ms_esperas, s.last_user_seek,

create_index = replace('create nonclustered index IX_' +

object_name(d.object_id, d.database_id) +'_A# on ' +

object_name(d.object_id, d.database_id) + ' (' +

isnull(d.equality_columns + ',', '') + isnull(d.inequality_columns, '') + ') ' +

isnull('include (' + d.included_columns + ')', '') +

' with(online = on)', ',)', ')')

from

sys.dm_db_missing_index_details d left join

sys.dm_db_missing_index_groups g on d.index_handle =g.index_handle left join

sys.dm_db_missing_index_group_stats s on g.index_group_handle = s.group_handle left

oin

sys.dm_db_partition_stats p on d.object_id = p.object_id and p.index_id < 2 left join

(select

database_id,

object_id,

row_number() over (partition by database_id

order by sum(page_io_latch_wait_in_ms) desc) as row_number,

sum(page_io_latch_wait_count) as num_esperas,

sum(page_io_latch_wait_in_ms) as ms_esperas,

sum(range_scan_count) as range_scans,

sum(singleton_lookup_count) as index_lookups

from sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)

where page_io_latch_wait_count > 0

group by database_id, object_id) l

on d.object_id = l.object_id and d.database_id = l.database_id

where

d.database_id = db_id()

and s.last_user_seek > dateadd(dd, -7, getdate())

order by --floor(s.avg_user_impact) desc,

s.user_seeks desc

 

Desde luego, no se pueden crear todos los índices que se recogen con esta consulta sin más. Hay que evaluarlos con los conocimientos que se poseen de la base de datos, del entorno, del tamaño de los campos, etc. Ojo, hay que asegurarse también de que no exista ya el índice sugerido.

 

Un par de detalles. El primero, la información estadística se elimina cuando se reinicia el servidor. Por lo que es muy importante dejar pasar un tiempo prudencial antes de ponerse a examinar y explotar estos datos. El segundo, cuando se crea un índice, se elimina toda la información relativa a la tabla en la que el índice se crea. Es mejor examinar todos los índices que se sugieren a la vez, puede que se puedan unificar en uno o crear dos, depende, pero si se crea uno, toda la información se elimina.

 

La pega puede ser pasarse. En ese caso, puedes recurrir al (2/2) cuando esté disponible y eliminar los índices que nunca fueron usados.

 

Concatenar valores en una misma consulta

Escrito por qwalgrande 05-09-2010 en General. Comentarios (3)

Es muy frecuente tener que construir un campo que concatener los valores de una columna para varios registros. Una forma clásica de realizar esta acción (es decir, que funciona también para versiones antes de SQL Server 2005) es concatenar en una variable:

 

declare @Cadena varchar(8000)

select @Cadena = ''

 

select @Cadena = @Cadena + ',' + COLUMN_NAME

from INFORMATION_SCHEMA.COLUMNS

where TABLE_NAME = 'Tabla1'

ORDER BY ORDINAL_POSITION

select STUFF(@Cadena, 1, 1, '') as CadenaCampos

Desde SQL Server 2005, se puede solventar ese problema en una sóla consulta, haciendo uso de FOR XML PATH, lo que permite una mejor integración en consultas más complejas:

 

select STUFF(

(SELECT CAST(',' AS varchar(MAX)) + COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

where TABLE_NAME = 'Tabla1'

ORDER BY ORDINAL_POSITION

FOR XML PATH('')

), 1, 1, '') as CadenaCampos

 

Un ejemplo de esa integración sería el siguiente:

 

Select Table_Name, STUFF(

(SELECT CAST(',' AS varchar(MAX)) + C.COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS C

where C.TABLE_NAME = T.TABLE_NAME

ORDER BY ORDINAL_POSITION

FOR XML PATH('')

), 1, 1, '') as CadenaCampos

from INFORMATION_SCHEMA.TABLES T

 

En esta línea se pueden hacer otras cosas más elaboradas, como por ejemplo, construir una sentencia pivot dinámica:

 

http://social.msdn.microsoft.com/Forums/es-ES/sqlserveres/thread/d5544169-7068-4796-88ce-b6eafd0f0b8e