sql-server - nomenclatura - indices xml sql server

Rendimiento de índices no agrupados en montones frente a índices agrupados (2)

Este Libro Blanco de 2007 compara el rendimiento de las declaraciones de selección / inserción / eliminación / actualización y selección de rango individuales en una tabla organizada como un índice agrupado en comparación con la tabla organizada como un montón con un índice no agrupado en las mismas columnas clave que el CI mesa.

En general, la opción de índice agrupado se desempeñó mejor en las pruebas, ya que solo hay una estructura que mantener y no es necesario realizar búsquedas de marcadores.

Un caso potencialmente interesante no cubierto por el documento hubiera sido una comparación entre un índice no agrupado en un montón frente a un índice no agrupado en un índice agrupado. En ese caso, habría esperado que el montón incluso tuviera un mejor rendimiento ya que una vez a nivel NCI, SQL Server tiene un RID para seguir directamente en lugar de tener que atravesar el índice agrupado.

¿Alguien está al tanto de las pruebas formales similares que se han llevado a cabo en esta área y, de ser así, cuáles fueron los resultados?

Como explica Kimberly Tripp, la reina de la indexación, en su publicación del blog El debate sobre el índice agrupado continúa ... , tener una clave de agrupación en una tabla de base de datos acelera bastante todas las operaciones, no solo SELECT .

Los SELECT son generalmente más lentos en un montón en comparación con una tabla agrupada, siempre que elija una buena clave de agrupación, algo así como una INT IDENTITY . Si utiliza una clave de agrupamiento realmente mala, como un GUID o una clave compuesta con muchos componentes de longitud variable, entonces, pero solo entonces, un montón puede ser más rápido. Pero en ese caso, realmente necesita limpiar el diseño de su base de datos en primer lugar ...

Por lo tanto, en general, no creo que haya ningún punto en un montón: elija una clave de agrupamiento buena y útil y debería beneficiarse en todos los aspectos.

Para verificar su solicitud creé 2 tablas siguiendo este esquema:

7.9 millones de registros que representan información de saldos.
Un campo de identidad de 1 a 7,9 millones.
un campo numérico que agrupa los registros en aproximadamente 500k grupos.

La primera tabla llamada heap obtuvo un índice no agrupado en el group campos. La segunda tabla llamada clust obtuvo un índice agrupado en el campo secuencial denominado key y un índice no agrupado en el group campos

Las pruebas se llevaron a cabo en un procesador I5 M540 con 2 núcleos hiperprocesados, memoria de 4 Gb y Windows 7 de 64 bits.

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Actualización del 9 de marzo de 2011 : realicé un segundo punto de referencia más extenso al ejecutar el siguiente código .net y la duración del registro, la CPU, las lecturas, las escrituras y las cuentas de filas en el Perfil del servidor Sql. (El texto de comando utilizado se mencionará en los resultados).
NOTA: la CPU y la duración se expresan en milisegundos
1000 consultas
Las consultas de CPU cero se eliminan de los resultados.
0 filas afectadas son eliminadas de los resultados.

int[] idList = new int[] { 6816588, 7086702, 6498815 ... }; // 1000 values here. using (var conn = new SqlConnection(@"Data Source=myserver;Initial Catalog=mydb;Integrated Security=SSPI;")) { conn.Open(); using (var cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = "select * from heap where common_key between @id and @id+1000"; cmd.Parameters.Add("@id", SqlDbType.Int); cmd.Prepare(); foreach (int id in idList) { cmd.Parameters[0].Value = id; using (var reader = cmd.ExecuteReader()) { int count = 0; while (reader.Read()) { count++; } Console.WriteLine(String.Format("key: {0} => {1} rows", id, count)); } } } }

Fin de la actualización el 9 de marzo de 2011 .

Rendimiento SELECT

Para verificar los números de rendimiento realicé las siguientes consultas una vez en la tabla de almacenamiento dinámico y una vez en la tabla de clust:

select * from heap/clust where group between 5678910 and 5679410 select * from heap/clust where group between 6234567 and 6234967 select * from heap/clust where group between 6455429 and 6455729 select * from heap/clust where group between 6655429 and 6655729 select * from heap/clust where group between 6955429 and 6955729 select * from heap/clust where group between 7195542 and 7155729

Los resultados de este punto de referencia son para el heap :

rows reads CPU Elapsed ----- ----- ----- -------- 1503 1510 31ms 309ms 401 405 15ms 283ms 2700 2709 0ms 472ms 0 3 0ms 30ms 2953 2962 32ms 257ms 0 0 0ms 0ms

Actualización del 9 de marzo de 2011 : cmd.CommandText = "select * from heap where group between @id and @id+1000";
721 filas tienen> 0 CPU y afectan a más de 0 filas

Counter Minimum Maximum Average Weighted --------- ------- ---------- ------- --------- RowCounts 1001 69788 6368 - Cpu 15 374 37 0.00754 Reads 1069 91459 7682 1.20155 Writes 0 0 0 0.00000 Duration 0.3716 282.4850 10.3672 0.00180

Fin de la actualización el 9 de marzo de 2011 .

Para la tabla de clust los resultados son:

rows reads CPU Elapsed ----- ----- ----- -------- 1503 4827 31ms 327ms 401 1241 0ms 242ms 2700 8372 0ms 410ms 0 3 0ms 0ms 2953 9060 47ms 213ms 0 0 0ms 0ms

Actualización del 9 de marzo de 2011 : cmd.CommandText = "select * from clust where group between @id and @id+1000";
721 filas tienen> 0 CPU y afectan a más de 0 filas

Counter Minimum Maximum Average Weighted --------- ------- ---------- ------- --------- RowCounts 1001 69788 6056 - Cpu 15 468 38 0.00782 Reads 3194 227018 20457 3.37618 Writes 0 0 0 0.0 Duration 0.3949 159.6223 11.5699 0.00214

Fin de la actualización el 9 de marzo de 2011 .

SELECT WITH JOIN performance

cmd.CommandText = "select * from heap/clust h join keys k on h.group = k.group where h.group between @id and @id+1000";

Los resultados de este punto de referencia son para el heap :

873 filas tienen> 0 CPU y afectan a más de 0 filas

Counter Minimum Maximum Average Weighted --------- ------- ---------- ------- --------- RowCounts 1009 4170 1683 - Cpu 15 47 18 0.01175 Reads 2145 5518 2867 1.79246 Writes 0 0 0 0.00000 Duration 0.8215 131.9583 1.9095 0.00123

Los resultados de este punto de referencia son para el clust :

865 filas tienen> 0 CPU y afectan a más de 0 filas

Counter Minimum Maximum Average Weighted --------- ------- ---------- ------- --------- RowCounts 1000 4143 1685 - Cpu 15 47 18 0.01193 Reads 5320 18690 8237 4.97813 Writes 0 0 0 0.00000 Duration 0.9699 20.3217 1.7934 0.00109

ACTUALIZAR el rendimiento

El segundo lote de consultas son las declaraciones de actualización:

update heap/clust set amount = amount + 0 where group between 5678910 and 5679410 update heap/clust set amount = amount + 0 where group between 6234567 and 6234967 update heap/clust set amount = amount + 0 where group between 6455429 and 6455729 update heap/clust set amount = amount + 0 where group between 6655429 and 6655729 update heap/clust set amount = amount + 0 where group between 6955429 and 6955729 update heap/clust set amount = amount + 0 where group between 7195542 and 7155729

Los resultados de este punto de referencia para el heap :

rows reads CPU Elapsed ----- ----- ----- -------- 1503 3013 31ms 175ms 401 806 0ms 22ms 2700 5409 47ms 100ms 0 3 0ms 0ms 2953 5915 31ms 88ms 0 0 0ms 0ms

Actualización el 9 de marzo de 2011 : cmd.CommandText = "update heap set amount = amount + @id where group between @id and @id+1000";
811 filas tienen> 0 CPU y afectan a más de 0 filas

Counter Minimum Maximum Average Weighted --------- ------- ---------- ------- --------- RowCounts 1001 69788 5598 811 Cpu 15 873 56 0.01199 Reads 2080 167593 11809 2.11217 Writes 0 1687 121 0.02170 Duration 0.6705 514.5347 17.2041 0.00344

Fin de la actualización el 9 de marzo de 2011 .

Los resultados de este benchmark para el clust :

rows reads CPU Elapsed ----- ----- ----- -------- 1503 9126 16ms 35ms 401 2444 0ms 4ms 2700 16385 31ms 54ms 0 3 0ms 0ms 2953 17919 31ms 35ms 0 0 0ms 0ms

Actualización del 9 de marzo de 2011 : cmd.CommandText = "update clust set amount = amount + @id where group between @id and @id+1000";
853 filas tienen> 0 CPU y afectan a más de 0 filas

Counter Minimum Maximum Average Weighted --------- ------- ---------- ------- --------- RowCounts 1001 69788 5420 - Cpu 15 594 50 0.01073 Reads 6226 432237 33597 6.20450 Writes 0 1730 110 0.01971 Duration 0.9134 193.7685 8.2919 0.00155

Fin de la actualización el 9 de marzo de 2011 .

Eliminar puntos de referencia

El tercer lote de consultas que ejecuté son las declaraciones de eliminación

delete heap/clust where group between 5678910 and 5679410 delete heap/clust where group between 6234567 and 6234967 delete heap/clust where group between 6455429 and 6455729 delete heap/clust where group between 6655429 and 6655729 delete heap/clust where group between 6955429 and 6955729 delete heap/clust where group between 7195542 and 7155729

El resultado de este punto de referencia para el heap :

rows reads CPU Elapsed ----- ----- ----- -------- 1503 10630 62ms 179ms 401 2838 0ms 26ms 2700 19077 47ms 87ms 0 4 0ms 0ms 2953 20865 62ms 196ms 0 4 0ms 9ms

Actualización del 9 de marzo de 2011 : cmd.CommandText = "delete heap where group between @id and @id+1000";
724 filas tienen> 0 CPU y afectan a más de 0 filas

Counter Minimum Maximum Average Weighted --------- ------- ---------- ------- --------- RowCounts 192 69788 4781 - Cpu 15 499 45 0.01247 Reads 841 307958 20987 4.37880 Writes 2 1819 127 0.02648 Duration 0.3775 1534.3383 17.2412 0.00349

Fin de la actualización el 9 de marzo de 2011 .

El resultado de este punto de referencia para el clust :

rows reads CPU Elapsed ----- ----- ----- -------- 1503 9228 16ms 55ms 401 3681 0ms 50ms 2700 24644 46ms 79ms 0 3 0ms 0ms 2953 26955 47ms 92ms 0 3 0ms 0ms

Actualización el 9 de marzo de 2011 :
cmd.CommandText = "delete clust where group between @id and @id+1000";

751 filas tienen> 0 CPU y afectan a más de 0 filas

Counter Minimum Maximum Average Weighted --------- ------- ---------- ------- --------- RowCounts 144 69788 4648 - Cpu 15 764 56 0.01538 Reads 989 458467 30207 6.48490 Writes 2 1830 127 0.02694 Duration 0.2938 2512.1968 24.3714 0.00555

Fin de la actualización el 9 de marzo de 2011 .

Inserte puntos de referencia

La última parte del índice de referencia es la ejecución de las instrucciones de inserción.

insertar en el montón / clust (...) valores (...), (...), (...), (...), (...), (...)

El resultado de este punto de referencia para el heap :

rows reads CPU Elapsed ----- ----- ----- -------- 6 38 0ms 31ms

Actualización el 9 de marzo de 2011 :

string str = @"insert into heap (group, currency, year, period, domain_id, mtdAmount, mtdAmount, ytdAmount, amount, ytd_restated, restated, auditDate, auditUser) values"; for (int x = 0; x < 999; x++) { str += string.Format(@"(@id + {0}, ''EUR'', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, ''test''), ", x); } str += string.Format(@"(@id, ''CAD'', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, ''test'') ", 1000); cmd.CommandText = str;

912 declaraciones tienen> 0 CPU

Counter Minimum Maximum Average Weighted --------- ------- ---------- ------- --------- RowCounts 1000 1000 1000 - Cpu 15 2138 25 0.02500 Reads 5212 7069 6328 6.32837 Writes 16 34 22 0.02222 Duration 1.6336 293.2132 4.4009 0.00440

Fin de la actualización el 9 de marzo de 2011 .

El resultado de este benchmark para el clust :

rows reads CPU Elapsed ----- ----- ----- -------- 6 50 0ms 18ms

Actualización el 9 de marzo de 2011 :

string str = @"insert into clust (group, currency, year, period, domain_id, mtdAmount, mtdAmount, ytdAmount, amount, ytd_restated, restated, auditDate, auditUser) values"; for (int x = 0; x < 999; x++) { str += string.Format(@"(@id + {0}, ''EUR'', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, ''test''), ", x); } str += string.Format(@"(@id, ''CAD'', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, ''test'') ", 1000); cmd.CommandText = str;

946 declaraciones tienen> 0 CPU

Counter Minimum Maximum Average Weighted --------- ------- ---------- ------- --------- RowCounts 1000 1000 1000 - Cpu 15 2403 21 0.02157 Reads 6810 8997 8412 8.41223 Writes 16 25 19 0.01942 Duration 1.5375 268.2571 6.1463 0.00614

Fin de la actualización el 9 de marzo de 2011 .

Conclusiones

Aunque hay más lecturas lógicas en curso cuando se accede a la tabla con el índice agrupado y no agrupado (mientras se usa el índice no agrupado), los resultados de rendimiento son:

Las instrucciones SELECT son comparables
Las instrucciones de ACTUALIZACIÓN son más rápidas con un índice agrupado en su lugar
Las instrucciones DELETE son más rápidas con un índice agrupado en su lugar
Las instrucciones INSERT son más rápidas con un índice agrupado en su lugar

Por supuesto, mi índice de referencia era muy limitado en un tipo específico de tabla y con un conjunto muy limitado de consultas, pero creo que en base a esta información ya podemos comenzar diciendo que casi siempre es mejor crear un índice agrupado en su tabla.

Actualización el 9 de marzo de 2011 :

Como podemos ver en los resultados agregados, las conclusiones sobre las pruebas limitadas no fueron correctas en todos los casos.

Los resultados ahora indican que las únicas declaraciones que se benefician del índice agrupado son las declaraciones de actualización. Las otras afirmaciones son aproximadamente un 30% más lentas en la tabla con índice agrupado.

Algunos gráficos adicionales donde graficé la duración ponderada por consulta para heap vs clust.

Como se puede ver, el perfil de rendimiento de las instrucciones de inserción es bastante interesante. Los picos son causados por unos pocos puntos de datos que tardan mucho más en completarse.

Fin de la actualización el 9 de marzo de 2011 .