Optimizando Desempeño de SQL Server 2008

Objetivos

• Implementar el regulador de recursos.
• Utilice el Asistente para la optimización de motor de base.
• Recoger datos de rendimiento mediante el uso de vistas de administración dinámica (DMV).
• Utilice Performance Studio.

 Contenido

• Lección 1: Uso del Asistente para la optimización de motor de base de datos
• Lección 2: Trabajo con el regulador de recursos
• Lección 3: Uso de las vistas de administración dinámica y funciones
• Lección 4: Trabajar con el almacén de datos de rendimiento 

Database Engine Tuning Advisor

 Que es?

• El Database Engine Tuning Advisor (DTA) está diseñado para evaluar sus consultas en las reglas del optimizador de consultas para hacer sugerencias para mejorar el rendimiento.
• DTA trabaja en conjunto con la salida de Traza de SQL.
• Las recomendaciones que DTA pueden hacer son:
  
  • La adición de índices
  • Dejar caer los índices
  • tablas de particiones
  • Almacenamiento alinear tablas
• Los únicos eventos que son ciderados por el  DTA son:
  
  • RPC: Starting
  • RPC: Completed
  • SQL: A partir de lotes
  • SQL: Lote Completo

El análisis se lleva a cabo con cuatro pasos:

1. Generar una carga de trabajo para el análisis
2. Comience DTA y conectarse a un servidor que ejecuta SQL Server que contiene una base de datos para analizar la carga de trabajo en contra.
3. Seleccione la carga de trabajo a utilizar
4. Especifique las opciones de optimización

Mejores Practicas

AUTOMATIZACIÓN DE ANÁLISIS

• Se puede interactuar con el dta.exe línea de comandos.
• Se puede configurar un rastreo mediante código, que puede ser ejecutado desde un trabajo del Agente SQL Server
• El trabajo puede importar un archivo de traza en una tabla una vez que el rastro se ha completado.
• DTA puede utilizar una tabla como fuente de carga de trabajo, puede crear un paso de trabajo para iniciar un análisis DTA ejecutar en los datos de seguimiento que acaba de importar.

Opciones de Tuning

• Las limitaciones de tiempo y acciones en línea
  
  • limitar el tiempo de optimización.
  • limitar el espacio consumido
• Las estructuras existentes en la base de datos
  
  • DTA hace recomendaciones vistas indexadas e indices.
  • El ajuste más común es recomendar índices solamente.
• Opciones de particiones
  
  • Todas las recomendaciones se sugieren utilizando particionado completo o estrategias de particionado alineado.
• Para mantener las estructuras existentes en la base de datos
  
  • Permite definir si las recomendaciones que considere índice existente y las estructuras de particiones de la base de datos o si las estructuras existentes se pueden eliminar como parte de las recomendaciones.

Trabajar con el regulador de recursos

Que es?

El regulador de recursos le permite limitar la CPU y la memoria asignada, o utilizados por una conexión específica o grupo de usuarios

Componentes

• Las agrupaciones de recursos.
• Grupos de cargas de trabajo.
• Funciones de clasificación.

• Clasificación se produce en el momento se crea una conexión.
• Puede limitar los recursos puestos a disposición de un grupo de trabajo.
• No se puede poner limitaciones a las operaciones internas de SQL Server.

Funciones de clasificacion

• Una función de clasificador es una función que se crea en la base de datos master.
• Sólo una función de clasificador puede estar activo al regulador de recursos a la vez.
• El valor que se devuelve es el nombre del grupo de carga de trabajo que el período de sesiones se clasifican en.
• Cada grupo de recursos puede ser configurado con un valor mínimo, pero el total de los valores mínimos en todos los grupos de recursos no puede exceder de 100.

Implementacion

Se implementa el regulador de recursos mediante los siguientes pasos:

1. Habilitar regulador de recursos.
2. Cree una o más agrupaciones de recursos.
3. Cree uno o más grupos de cargas de trabajo.
4. Asociar cada grupo de carga de trabajo para un grupo de recursos.
5. Crear y probar una función clasificadora.
6. Asocie la función de clasificador del regulador de recursos.

 Uso de las vistas de administración dinámica y funciones

 Que es?

Proporcionar la infraestructura de instrumentación que permite a los administradores de bases de datos para recuperar información del sistema, así como supervisar, diagnosticar y corregir problemas.

Categorias

DMV se almacenan en el esquema SYS y se pueden agrupar en categorías amplias, varias docenas.

El DMV más comunes que se utilizan para recopilar estadísticas de base de datos son:

• sys.dm_db_index_usage_stats
• sys.dm_db_index_operational_stats
• sys.dm_db_index_physical_stats
• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_details

sys.dm_db_index

• usage_stats (uso de estadísticas)
• Contiene el número de veces (y última vez) cada índice se utilizó para satisfacer una búsqueda, escanear, o búsqueda.
• operational_stats (operativos estadísticas)
• Devoluciones de bloqueo, bloqueo, y las estadísticas de acceso para cada índice que puede ayudarle a determinar cómo fuertemente un índice está siendo
• physical_stats (físico estadísticas)
• Devuelve el tamaño y fragmentationstatistics para cada índice y debe ser la fuente principal para determinar cuando un índice necesita ser desfragmentado.

sys.dm_db_missing_index_*views

Una de las categorías más interesantes disponibles en SQL Server 2008.
Cuando un índice de tipo missing se genera, SQL Server registra los detalles de la falta de índice, que a continuación se pueden ver usando sys.dm_db_missing_index_ *views.

Estadisticas de Consulta

Que es?

El sys.dm_exec_ * DMV devuelve información relacionada con la conexión a la instancia, así como ejecución de la consulta.
El DMV siguientes devuelven información acerca de las conexiones y peticiones activamente ejecutando:

• sys.dm_exec_connections
• sys.dm_exec_sessions
• sys.dm_exec_requests

Conexiones

• Contiene una fila para cada conexión a la instancia
• Cuando la conexión se ha realizado junto con las propiedades de conexión y la configuración de cifrado, la última vez que se ejecutó una lectura o escritura

Sesiones

• Contiene una fila para cada sesión autenticado actualmente
• El estado actual de cada opción de consulta posible y el estado de ejecución actual.
• Devuelve el acumulado lee, escribe, CPU, y la duración de ejecución de consultas para la sesión

Peticiones

• Contiene una fila por cada solicitud que se está ejecutando en la instancia
• Contiene el tiempo de inicio, tiempo transcurrido, el tiempo estimado de finalización, lee, escribe y CPU para la solicitud.

Recursos de hardware

• SQL Server utiliza un modelo de procesamiento de cooperación para satisfacer las peticiones. Cada petición que se ejecuta es asignado a un <<modo planificador de usuario>> Mode User Scheduler (UMS).
• SQL Server tiene una UMS por procesador disponibles para satisfacer las solicitudes de consulta.
• Cuando la solicitud se envía a la cola de espera, SQL Server establece un valor llamado el tipo de espera que indica el tipo de recurso que la solicitud está esperando.
• La cantidad de tiempo, que la solicitud tuvo que esperar, para que  el recurso esté disponible, es llamado el tiempo de espera

sys.dm_os_wait_stats

• Enumera el monto total de la señal de espera y el tiempo de espera para cada tipo de espera.
• Señal de espera y el tiempo de espera, es un valor agregado desde la última vez que las estadísticas se borran.
• DMV puede ser borrado:
• reiniciando la instancia
• ejecutar el siguiente código: DBCC SQLPERF (WAITSTATS, CLEAR)

Trabajar con el almacén de datos de rendimiento

¿Qué es?

Conocida como Performance Studio, es una característica nueva en SQL Server Management Studio (SSMS), que le permite configurar y recopilar datos de rendimiento para las instancias a través de su entorno que se puede utilizar para su posterior análisis

• Data Collector
• SSIS
• Trabajos de SQL Server Agent

La recolección de datos para el almacén de datos de rentabilidad se configura con uno de los siguientes tipos de colectores:

• T-SQL Query
• SELECT para ejecutar
• Los resultados de la consulta se almacena en una tabla
• Traza de SQL (archivo)
• Los resultados de la traza se escriben en un archivo
• La función fn_trace_gettable para extraer los contenidos del archivo
• Almacenan en las tablas snapshots.trace_info y snapshots.trace_data
• contador de rendimiento
• Le permite definir cualquier combinación de objetos, contadores e instancias de contador
• Almacenados en la tabla snapshots.performance_counters
• Query Activity
• Recopila información de sys.dm_exec_requests, sys.dm_exec_sessions
• Y sys.dm_exec_query_stats