sql-server - resueltos - plan de ejecución y optimización de consultas sql server 2008 r2
¿Cuál es su enfoque para optimizar tablas grandes(+ 1M filas) en SQL Server? (9)
Estoy importando datos del mercado bursátil brasileño a una base de datos de SQL Server. En este momento tengo una tabla con información de precios de tres tipos de activos: acciones, opciones y forwards. Todavía estoy en datos de 2006 y la tabla tiene más de medio millón de registros. Tengo más 12 años de datos para importar, por lo que la tabla superará un millón de registros.
Ahora, mi primer enfoque para la optimización fue mantener los datos a un tamaño mínimo, por lo que reduje el tamaño de la fila a un promedio de 60 bytes, con las siguientes columnas:
[Stock] [int] NOT NULL [Date] [smalldatetime] NOT NULL [Open] [smallmoney] NOT NULL [High] [smallmoney] NOT NULL [Low] [smallmoney] NOT NULL [Close] [smallmoney] NOT NULL [Trades] [int] NOT NULL [Quantity] [bigint] NOT NULL [Volume] [money] NOT NULL
Ahora, el segundo enfoque para la optimización fue hacer un índice agrupado. En realidad, el índice principal se ajusta automáticamente y lo convertí en un índice compuesto con los campos Stock y Fecha. Esto es único, no puedo tener dos datos de cotización para la misma acción en el mismo día.
El índice agrupado asegura que las cotizaciones de la misma acción permanezcan juntas, y probablemente ordenadas por fecha . ¿Es esta segunda información verdadera?
En este momento, con medio millón de registros, tomará unos 200 ms seleccionar 700 citas de un activo específico. Creo que este número aumentará a medida que la mesa crezca.
Ahora, para un tercer enfoque, estoy pensando en dividir la tabla en tres tablas, cada una para un mercado específico (acciones, opciones y forwards). Esto probablemente reducirá el tamaño de la mesa en 1/3. Ahora, ¿ayudará este enfoque o no importa demasiado? En este momento, la mesa tiene 50mb de tamaño, por lo que puede caber completamente en la memoria RAM sin muchos problemas.
Otro enfoque sería usar la función de partición de SQL Server. No sé mucho al respecto, pero creo que normalmente se usa cuando las tablas son grandes y puede abarcar múltiples discos para reducir la latencia de E / S, ¿verdad? ¿Sería útil la partición en este caso? Creo que puedo dividir los valores más nuevos (últimos años) y los valores más antiguos en tablas diferentes. La probabilidad de buscar los datos más nuevos es mayor, y con una pequeña partición probablemente sea más rápida, ¿no?
¿Cuáles serían otros buenos enfoques para hacer esto lo más rápido posible? El uso principalmente seleccionado de la tabla será para buscar un rango específico de registros de un activo específico, como los últimos 3 meses del activo X. Habrá otros usos, pero este será el más común, ya que es posible que se ejecute en más de 3k usuarios concurrentemente.
El plan de ejecución muestra que está usando el índice agrupado bastante bien, pero olvidé un hecho extremadamente importante, ¡todavía estoy insertando datos! El inserto probablemente está bloqueando la mesa con demasiada frecuencia. Hay una manera en que podemos ver este cuello de botella?
El plan de ejecución no parece mostrar nada sobre problemas de bloqueo.
En este momento, estos datos solo son históricos, cuando el proceso de importación finaliza, las inserciones se detienen y son mucho menos frecuentes. Pero pronto tendré una tabla más grande para datos en tiempo real, que sufrirá este problema de inserción constante y será más grande que esta tabla. Entonces, cualquier enfoque para optimizar este tipo de situación es muy bienvenido.
Mencionó que su clave principal es un compuesto en (Stock, Date) y está en clúster. Esto significa que la tabla está organizada por Stock y luego por Fecha. Siempre que inserte una nueva fila, debe insertarla en el medio de la tabla, y esto puede hacer que las otras filas se desplacen a otras páginas (divisiones de página).
Recomendaría intentar invertir la clave principal en (Fecha, Stock) y agregar un índice no agrupado en Stock para facilitar las búsquedas rápidas de un Stock específico. Esto permitirá que las inserciones siempre sucedan al final de la tabla (suponiendo que esté insertando por orden de fecha), y no afectará el resto de la tabla, y menos posibilidades de divisiones de página.
Otra solución sería crear una tabla histórica para cada año y colocar todas estas tablas en una base de datos histórica, completar todas las entradas y luego crear los índices apropiados para ellas. Una vez que haya terminado con esto, no tendrá que tocarlos nunca más. ¿Por qué tendrías que seguir insertando datos? Para consultar todas esas tablas, simplemente "unirlas todas": p
La tabla de año actual debería ser muy diferente a esta tabla histórica. Por lo que entendí, ¿planeas insertar registros mientras viajas ?, planearía algo diferente, como hacer una inserción masiva o algo similar de vez en cuando a lo largo del día. Por supuesto, todo esto depende de lo que quieras hacer.
Los problemas aquí parecen estar en el diseño. Me gustaría un nuevo diseño. El que tienes ahora por lo que yo entiendo no es adecuado.
Trabajo para un distrito escolar y tenemos que rastrear la asistencia de cada estudiante. Es la forma en que ganamos nuestro dinero. Mi tabla que contiene la marca de asistencia diaria para cada alumno es actualmente de 38.9 millones de registros. Puedo sacar la asistencia de un solo estudiante muy rápidamente de esto. Mantenemos 4 índices (incluida la clave principal) en esta tabla. Nuestro índice agrupado es estudiante / fecha que mantiene todos los registros del alumno ordenados por eso. Hemos tomado un golpe en insertos a esta tabla con respecto a eso en el caso de que se inserte un antiguo registro para un estudiante, pero es un riesgo que vale la pena para nuestros propósitos.
Con respecto a la velocidad de selección, ciertamente aprovecharía el almacenamiento en caché en su circunstancia.
Un millón de discos realmente no es tan grande. Sin embargo, parece que lleva demasiado tiempo buscar: ¿está indexada la columna que estás buscando?
Como siempre, el primer puerto de escala debería ser el generador de perfiles de SQL y el planificador de consultas. Pregúntele a SQL Server qué hará con las consultas que le interesan. Creo que incluso puede pedirle que sugiera cambios, como índices adicionales.
No me gustaría empezar a particionar, etc. todavía. Como dices, debería estar cómodamente en la memoria en este momento, así que sospecho que tu problema probablemente sea un índice faltante.
Verifique su plan de ejecución en esa consulta primero. Asegúrese de que sus índices estén siendo utilizados. Encontré eso. Un millón de registros no es mucho. Para dar un poco de perspectiva, teníamos una tabla de inventario con 30 millones de filas y toda nuestra consulta, que unía toneladas de tablas y hacía muchos cálculos con menos de 200 MS. Descubrimos que en un servidor Quad Proc de 64 bits, podríamos tener registros significativamente más, por lo que nunca nos molestamos en la partición.
Puede usar SQL Profier para ver el plan de ejecución o simplemente ejecutar la consulta desde SQL Management Studio o Query Analyzer.
reevaluar los índices ... esa es la parte más importante, el tamaño de los datos en realidad no importa, pero lo hace, pero no por completo para la velocidad.
Mi recomendación es reconstruir los índices para esa tabla, hacer una compuesta para las columnas que más necesitarás. Ahora que solo tiene unos pocos registros, juegue con los diferentes índices; de lo contrario, será bastante molesto probar cosas nuevas una vez que tenga todos los datos históricos en la tabla.
Después de hacerlo, revise su consulta, haga que el evaluador del plan de consulta sea su amigo y verifique si el motor usa el índice correcto.
Acabo de leer tu última publicación, hay una cosa que no entiendo: ¿estás cuestionando la tabla mientras insertas datos? ¿al mismo tiempo?. ¿Para qué? Al insertar, ¿te refieres a uno registra cientos o miles? ¿Cómo te estás insertando? ¿uno a uno?
Pero una vez más la clave de esto son los índices, no te metas con particiones y otras cosas ... especialmente con un millón de registros, eso no es nada, tengo tablas con 150 millones de registros, y devolver 40k registros específicos toma el motor alrededor de 1500ms. .
En realidad, el índice principal se ajusta automáticamente y lo convertí en un índice compuesto con los campos Stock y Fecha. Esto es único, no puedo tener dos datos de cotización para la misma acción en el mismo día.
El índice agrupado asegura que las cotizaciones de la misma acción permanezcan juntas, y probablemente ordenadas por fecha. ¿Es esta segunda información verdadera?
Los índices en SQL Server siempre se ordenan por orden de columnas en índice. Por lo tanto, un índice en [stock, fecha] primero clasificará en stock, luego en stock en fecha. Un índice el [fecha, stock] primero clasificará la fecha, luego dentro de la fecha en stock.
Al realizar una consulta, siempre debe incluir la primera columna (s) de un índice en la parte DONDE, de lo contrario, el índice no se puede usar de manera eficiente.
Para su problema específico: si las consultas de rango de fechas son el uso más común, haga la clave principal el [fecha, stock], de modo que los datos se almacenarán secuencialmente por fecha en el disco y obtendrá el acceso más rápido. Desarrolle otros índices según sea necesario. Haga la reconstrucción de índice / actualización de estadísticas después de insertar muchos datos nuevos.
- Con 1 millón de registros, no consideraría esta una tabla particularmente grande que necesita técnicas de optimización inusuales como dividir la tabla, desnormalizar, etc. Pero esas decisiones vendrán cuando haya intentado todos los medios normales que no afectan su capacidad de usar técnicas de consulta estándar.
Ahora, el segundo enfoque para la optimización fue hacer un índice agrupado. En realidad, el índice principal se ajusta automáticamente y lo convertí en un índice compuesto con los campos Stock y Fecha. Esto es único, no puedo tener dos datos de cotización para la misma acción en el mismo día.
El índice agrupado asegura que las cotizaciones de la misma acción permanezcan juntas, y probablemente ordenadas por fecha. ¿Es esta segunda información verdadera?
Es lógicamente cierto: el índice agrupado define el orden lógico de los registros en el disco, que es lo único que debería preocuparle. SQL Server puede omitir la sobrecarga de ordenar dentro de un bloque físico, pero seguirá comportándose como si lo hiciera, por lo que no es significativo. La consulta de una acción probablemente sea de 1 o 2 lecturas de página en cualquier caso; y el optimizador no se beneficia mucho de los datos desordenados dentro de una página leída.
En este momento, con medio millón de registros, tomará unos 200 ms seleccionar 700 citas de un activo específico. Creo que este número aumentará a medida que la mesa crezca.
No necesariamente de manera significativa. No hay una relación lineal entre el tamaño de la tabla y la velocidad de la consulta. Generalmente hay muchas más consideraciones que son más importantes. No me preocuparía en el rango que describes. ¿Es esa la razón por la que estás preocupado? Me parece que 200 ms son geniales, lo suficiente como para llegar al punto donde se cargan sus tablas y puede comenzar a hacer pruebas realistas, y obtener una mejor idea del rendimiento de la vida real.
Ahora, para un tercer enfoque, estoy pensando en dividir la tabla en tres tablas, cada una para un mercado específico (acciones, opciones y forwards). Esto probablemente reducirá el tamaño de la mesa en 1/3. Ahora, ¿ayudará este enfoque o no importa demasiado? En este momento, la mesa tiene 50mb de tamaño, por lo que puede caber completamente en la memoria RAM sin muchos problemas.
¡No! Este tipo de optimización es tan prematuro que probablemente nazca muerto.
Otro enfoque sería usar la función de partición de SQL Server.
El mismo comentario. Podrá permanecer durante mucho tiempo en un diseño de esquema estrictamente lógico y completamente normalizado.
¿Cuáles serían otros buenos enfoques para hacer esto lo más rápido posible?
El mejor primer paso es la agrupación en stock. La velocidad de inserción no tiene ninguna importancia hasta que está viendo múltiples registros insertados por segundo: aquí no veo nada cerca de esa actividad. Esto debería acercarlo a la máxima eficiencia porque leerá de manera eficiente cada registro asociado con una acción, y ese parece ser su índice más común. Cualquier optimización adicional debe lograrse en función de las pruebas.