miércoles, 23 de marzo de 2011

Estadísticas de consultas de ejecución

Comprender las estadísticas del DMV
Antes de comenzar a discutir la manera de recoger las estadísticas de rendimiento que desea revisar alguna información básica acerca de las estadísticas del DMV. Con SQL Server 2005, Microsoft introdujo una serie de puntos de vista sistema llamado DMV. Estos puntos de vista le permiten sonda de SQL Server para determinar la salud, los problemas de diagnóstico, o revisar la información operativa de una instancia de SQL Server. 

Las estadísticas del DMV se recogen mientras que SQL Server se está ejecutando, y se restablecen cada vez que se inicia SQL Server. Estadísticas de determinados DMV también se puede restablecer al quitar y volver a crear sus componentes. Esto es cierto para objetos como procedimientos almacenados y tablas. Para otra información del DMV se puede restablecer mediante la ejecución de comandos DBCC.

Cuando se utiliza cualquier DMV que usted necesita para tener en cuenta la duración de SQL Server ha estado recopilando información del DMV para determinar la utilidad de los datos devueltos por el DMV puede ser. Si SQL Server sólo ha estado funcionando durante un corto período de tiempo, es posible que no desea utilizar algunas estadísticas del DMV, ya que no representan una muestra relativa de la carga de trabajo real que una instancia podría encontrarse. Además de SQL Server sólo se puede mantener tanta información, por lo que cierta información se puede perder, mientras que SQL Server realiza las actividades de gestión. Así que si SQL Server ha estado funcionando durante un largo período de tiempo existe la posibilidad de que alguna información estadística ha sido sobrescritos.

Por lo tanto cada vez que utilice un DMV mantener estos puntos en cuenta al revisar la información devuelta a través de SQL Server 2005 DMV. Sólo hacer cambios de base de datos o aplicación del código cuando se tiene la certeza de la información obtenida del DMV es exacta y completa.

sys.dm_exec_query_stats Usando estadísticas del DMVEl DMV sys.dm_exec_query_stats devuelve las estadísticas de consulta de las cuentas individuales TSQL dentro de una instancia de SQL Server 2005. Esta DMV devuelve un número de estadísticas útiles para los estados TSQL se ejecutan en una instancia como, contar con la ejecución, la cantidad de CPU utilizada, el número de E / S se utiliza, etc Usted puede leer los libros en pantalla de SQL Server para obtener más información detallada sobre todos los disponibles columnas devueltas cuando se ejecuta este DMV.

Cuando se selecciona la información de la sys.dm_exec_query_stats DMV se vuelve un montón de estadísticas, pero no le da el texto de la consulta SQL asociadas a las estadísticas que aparecen. Para obtener el texto de la consulta SQL asociadas a las estadísticas que necesita para unirse a la sys.dm_exec_query_stats registro DVM conjunto con las columnas de regresar de la función de gestión sys.dm_exec_sql_text dinámica (DMF), así:

SELECT * FROM
  sys.dm_exec_query_stats
  CROSS APPLY
  sys.dm_exec_sql_text (sql_handle)

Aquí he utilizado el operador CROSS APPLY para unirse a la DMV sys.dm_exec_query_stats con la sys.dm_exec_sql_text DMF con valores de tabla. El operador CROSS APPLY le permite unirse a un conjunto con una función. Este operador utiliza un valor de columna de cada fila en el conjunto de la izquierda, y luego utiliza ese valor como entrada a la función de la derecha. Una fila se completa automáticamente en el resultado final conjunto que contiene las columnas de la izquierda y columnas que devuelve la función en el derecho en cualquier momento la función devuelve DMF ninguna información. En este ejemplo, el operador CROSS APPLY se une a la fila de la sys.dm_exec_query_stats con la salida de la sys.dm_exec_sql_text DMF utilizando el "sql_handle" valor de una fila sys.dm_exec_query_stats.  

La identificación de declaraciones dentro de un loteEl texto de la consulta SQL de regresar de sys.dm_exec_sql_text DMF podría ser un procedimiento almacenado, una única instrucción TSQL, o una serie de declaraciones. Esto devuelve DMF lo que comúnmente se conoce como un lote de TSQL. Así que, en realidad, la instrucción SELECT anterior sólo se une a las estadísticas de una sola declaración (sql_handle), con el lote SQL que está asociado. Por lo tanto es necesario hacer un poco más para obtener la declaración real de que las estadísticas de consulta se aplican. Para obtener el único estado asociadas a las estadísticas de consulta que necesite utilizar el "compensar" los valores de columna de sys.dm_exec_query_stats DMF. El código de abajo tiene los valores de desplazamiento para devolver la única instrucción TSQL asociados a cada conjunto de estadísticas de consulta SQL:

SELECCIONAR SUBSTRING (texto,
        - Un valor inicial de la subcadena
        CASO CUANDO statement_start_offset = 0
             statement_start_offset o es NULL
               ENTONCES 1
               MÁS statement_start_offset / 2 + 1 END,
        - Poner fin a valor de subcadena
        CASO CUANDO statement_end_offset = 0
          O statement_end_offset = -1
          statement_end_offset o es NULL
               ENTONCES LEN (texto)
               MÁS statement_end_offset / 2 FINAL -
                   CASO CUANDO statement_start_offset = 0
                          statement_start_offset o es NULL
                               ENTONCES 1
                               MÁS statement_start_offset / 2 + 1 END
        ) AS TSQL,
        *
DE sys.dm_exec_query_stats 
   CRUZ sys.dm_exec_sql_text APLICABLE (sql_handle);

Aquí he utilizado el "statement_start_offset" valor de la columna para determinar donde el primer carácter de la declaración de TSQL estaría en el texto de consulta SQL. Para un lote único estado de este valor es normalmente de 1, pero para la declaración de varios lotes de este valor algo superior a 1 para todas las declaraciones, pero la primera instrucción del lote. El "statement_end_offset" columna se utiliza para calcular el último carácter de la declaración TSQL en la cadena de texto de consulta SQL de regresar de la DMF sys.dm_exec_sql_text.
La determinación de DBID de Estadísticas DeclaraciónSi se ejecuta cualquiera de la declaración TSQL por encima de usted verá que las estadísticas de estos retornos declaración TSQL consulta, pero sin embargo, no siempre le mostrará que "dbid", la consulta podría ser ejecutado en contra. Demuestran algunos DBIDs, pero no todos. En un entorno de base de datos única solicitud que esa información no es importante. Pero si usted tiene una única instancia de SQL Server soportar aplicaciones múltiples lo más probable es que muchas bases de datos. Cuando este es el caso de que necesite ser capaz de identificar la base de datos para cada declaración, por lo que se puede sintonizar a continuación, las consultas con las estadísticas relacionadas con peor desempeño de una base de datos dada.
Para obtener la "dbid" para todas las filas devueltas de la sys.dm_exec_query_stats usted necesita utilizar un DMV adicionales. El "dbid" se puede encontrar mediante el uso de la información en el plan almacenado en caché que se asocia con la declaración de TSQL. Aquí está un ejemplo de cómo obtener los desaparecidos "dbid" para los estados TSQL utilizando la información del plan en caché:

SELECCIONAR COMO pa.value dbid,
       COALESCE (DB_NAME (st.dbid),
                DB_NAME (CAST (pa.value como int ))+'*') COMO nombrebd,
       SUBSTRING (texto,
        - Un valor inicial de la subcadena
        CASO CUANDO statement_start_offset = 0
             statement_start_offset o es NULL
               ENTONCES 1
               MÁS statement_start_offset / 2 + 1 END,
        - Poner fin a valor de subcadena
        CASO CUANDO statement_end_offset = 0
          O statement_end_offset = -1
          statement_end_offset o es NULL
               ENTONCES LEN (texto)
               MÁS statement_end_offset / 2 FINAL -
                   CASO CUANDO statement_start_offset = 0
                          statement_start_offset o es NULL
                               ENTONCES 1
                               MÁS statement_start_offset / 2 + 1 END
        ) AS TSQL,
        *
DE sys.dm_exec_query_stats 
   CRUZ sys.dm_exec_sql_text APLICABLE (sql_handle) c /
   OUTER APPLY sys.dm_exec_plan_attributes (plan_handle) pa
    DONDE atributo = 'dbid ";

En la consulta anterior he utilizado la "dbid" atributo del plan almacenado en caché para identificar a los desaparecidos "dbid". Los atributos de un plan se obtienen mediante el uso de la "sys.dm_exec_plan_attributes" DMF. Cada plan en caché tiene muchos atributos por lo que obtuvo sólo el "dbid" información de atributos al restringir la información cuando la columna de atributos es igual al valor "dbid". Que he empleado el "OUTER APPLY" operador para unirse a la información de estadísticas de consulta a los resultados de la sys.dm_exec_plan_attribute DMF. El "OUTER APPLY" operador devuelve todas las columnas en el lado izquierdo, incluso si no devuelve un valor de la función a la derecha. Así que incluso si el atributo "plan_handle" de las estadísticas de consulta de no encontrar un plan de las estadísticas será devuelto. Hago esto porque en algunos casos, los planes no se almacenan en caché.

Si observas mi código anterior, verá que yo uso la función COALESCE para determinar lo que se mostrará durante un nombreBD. Si el "dbid" se devuelve desde el sys.dm_exec_sql_text con valores de tabla DMF, entonces el uso que "dbid" para determinar el nombre de base de datos mediante la función DB_NAME, de lo contrario, utilice el "dbid" valor de la sys.dm_exec_plan_attribute DMF para obtener el nombre de base de datos. Tenga en cuenta que cada vez que el sys.dm_exec_plan_attribute "dbid" se utiliza para obtener el nombre de base de datos que añadir un "*" junto al nombre. Tengo que hacer para identificar los nombres de base de datos que fueron obtenidos utilizando el plan de caché "dbid" atributo. Tenga en cuenta que no puede garantizar que el "dbid" en el plan almacenado en caché es la base de datos real que es la instrucción TSQL se procesó en contra. El atributo plan "dbid" valor es la base de datos que estaba siendo "usado" en el momento en el texto de consulta SQL se compila en un plan en caché. Lo más probable será la base de datos adecuada, pero si usted está usando una tercera parte convención de nomenclatura para los objetos de base de datos entonces hay una posibilidad de que usted está utilizando una base de datos durante la ejecución de comandos SQL contra una base de datos diferente. 

Hay una última cosa que debe mencionarse en relación con la dbid regresó con la consulta anterior, y que es la función DB_NAME puede devolver un "NULL" valor. Esto ocurre cuando el valor dbid es 32767. Esta base de datos de Identificación del número está asociado a una base de datos del sistema que es comúnmente llamado el "recurso" base de datos. Este "recurso" base de datos no es tan bien conocida, y es una base de datos real que existe en el sistema, pero no se puede ver en SQL Server Management Studio. Se puede encontrar navegando por el directorio de datos y la búsqueda de archivos mdf y ldf que comienzan con "mssqlsystemreource". La base de datos contiene todos los recursos del sistema compilado procedimientos almacenados y funciones. Así que para la determinación completa de todos los nombres de base de datos posible, he modificado el código anterior a la materia en "recursos" cuando sea una de las funciones DB_NAME pide devolver un valor NULL:

SELECCIONAR COMO pa.value dbid,
       COALESCE (DB_NAME (st.dbid),
                DB_NAME (CAST (pa.value ))+'*', como int
                'Recursos') AS nombrebd,
       SUBSTRING (texto,
        - Un valor inicial de la subcadena
        CASO CUANDO statement_start_offset = 0
             statement_start_offset o es NULL
               ENTONCES 1
               MÁS statement_start_offset / 2 + 1 END,
        - Poner fin a valor de subcadena
        CASO CUANDO statement_end_offset = 0
          O statement_end_offset = -1
          statement_end_offset o es NULL
               ENTONCES LEN (texto)
               MÁS statement_end_offset / 2 FINAL -
                   CASO CUANDO statement_start_offset = 0
                          statement_start_offset o es NULL
                               ENTONCES 1
                               MÁS statement_start_offset / 2 + 1 END
        ) AS TSQL,
        *
DE sys.dm_exec_query_stats 
   CRUZ sys.dm_exec_sql_text APLICABLE (sql_handle) c /
   OUTER APPLY sys.dm_exec_plan_attributes (plan_handle) pa
    DONDE atributo = 'dbid ";

Las estadísticas significativasHasta ahora cada una de mis preguntas que aparecen un montón de estadísticas diferentes. Éstos son grandes, pero cuando usted está haciendo un análisis de rendimiento que desea ser más específico, y para los resultados de consulta para identificar rápidamente las peores realizar consultas. En esta sección voy a perfeccionar el código anterior para proporcionar sólo los delincuentes la parte superior de uso de CPU, E / S, y duración.

El "total_worker_time" columna de la sys.dm_exec_query_stats identifica el total de CPU utilizado por el comunicado. Así que podemos utilizar esta columna para determinar el peor uso de la CPU declaraciones TSQL. Desde esta columna es una columna acumulativo que resume la CPU utilizada cada vez que la declaración fue ejecutado tenemos que realizar un cálculo para determinar el medio de la CPU para cada ejecución de la instrucción. Usted puede utilizar el siguiente código para encontrar el TOP 10 de los comandos que utilizan la mayoría de la CPU para una ejecución única instrucción:

SELECT TOP 10 COALESCE (DB_NAME (st.dbid),
                DB_NAME (CAST (pa.value ))+'*', como int
                'Recursos') AS nombrebd,
       SUBSTRING (texto,
        - Un valor inicial de la subcadena
        CASO CUANDO statement_start_offset = 0
             statement_start_offset o es NULL
               ENTONCES 1
               MÁS statement_start_offset / 2 + 1 END,
        - Poner fin a valor de subcadena
        CASO CUANDO statement_end_offset = 0
          O statement_end_offset = -1
          statement_end_offset o es NULL
               ENTONCES LEN (texto)
               MÁS statement_end_offset / 2 FINAL -
                   CASO CUANDO statement_start_offset = 0
                          statement_start_offset o es NULL
                               ENTONCES 1
                               MÁS statement_start_offset / 2 + 1 END
        ) AS TSQL,
        SUBSTRING (CONVERT (CHAR (23),
                  DATEADD (ms, (total_worker_time / execution_count) / 1000,0),
                          121),
                  12,23) AVG_CPU_MS
DE sys.dm_exec_query_stats 
   CRUZ sys.dm_exec_sql_text APLICABLE (sql_handle) c /
   OUTER APPLY sys.dm_exec_plan_attributes (plan_handle) pa
    DONDE atributo = 'dbid " 
   ORDER BY AVG_CPU_MS DESC;
El sys.dm_exec_query_stats DMF proporciona una serie de columnas diferentes relacionados con I / O las estadísticas. Usted puede utilizar estas columnas para determinar qué estados TSQL más utilizado I / O. Aquí hay un código que le mostrará los comandos que utiliza la más lógica lectura de E / S por ejecución de la declaración:

SELECT TOP 10 COALESCE (DB_NAME (st.dbid),
                DB_NAME (CAST (pa.value ))+'*', como int
                'Recursos') AS nombrebd,
       SUBSTRING (texto,
        - Un valor inicial de la subcadena
        CASO CUANDO statement_start_offset = 0
             statement_start_offset o es NULL
               ENTONCES 1
               MÁS statement_start_offset / 2 + 1 END,
        - Poner fin a valor de subcadena
        CASO CUANDO statement_end_offset = 0
          O statement_end_offset = -1
          statement_end_offset o es NULL
               ENTONCES LEN (texto)
               MÁS statement_end_offset / 2 FINAL -
                   CASO CUANDO statement_start_offset = 0
                          statement_start_offset o es NULL
                               ENTONCES 1
                               MÁS statement_start_offset / 2 + 1 END
        ) AS TSQL,
       total_logical_reads / execution_count AVG_LOGICAL_READS AS
DE sys.dm_exec_query_stats 
   CRUZ sys.dm_exec_sql_text APLICABLE (sql_handle) c /
   OUTER APPLY sys.dm_exec_plan_attributes (plan_handle) pa
    DONDE atributo = 'dbid " 
   ORDER BY AVG_LOGICAL_READS DESC;
Para mi última consulta significativa recopilación estadística déjame mostrarte cómo encontrar el TOP 10 comandos que tuvo más tiempo para ejecutarse. Por mucho tiempo me refiero a la duración. Para ello utilizo el "total_elapsed_time" columna de la DMV sys.dm_exec_query_stats. Aquí está el código:

 SELECT TOP 10 COALESCE (DB_NAME (st.dbid),
                DB_NAME (CAST (pa.value ))+'*', como int
                'Recursos') AS nombrebd,
       SUBSTRING (texto,
        - Un valor inicial de la subcadena
        CASO CUANDO statement_start_offset = 0
             statement_start_offset o es NULL
               ENTONCES 1
               MÁS statement_start_offset / 2 + 1 END,
        - Poner fin a valor de subcadena
        CASO CUANDO statement_end_offset = 0
          O statement_end_offset = -1
          statement_end_offset o es NULL
               ENTONCES LEN (texto)
               MÁS statement_end_offset / 2 FINAL -
                   CASO CUANDO statement_start_offset = 0
                          statement_start_offset o es NULL
                               ENTONCES 1
                               MÁS statement_start_offset / 2 + 1 END
        ) AS TSQL,
               SUBSTRING (CONVERT (CHAR (23),
                  DATEADD (ms, (total_elapsed_time / execution_count) / 1000,0),
                          121),
                  12,23) COMO AVG_ELAPSED_TIME
DE sys.dm_exec_query_stats 
   CRUZ sys.dm_exec_sql_text APLICABLE (sql_handle) c /
   OUTER APPLY sys.dm_exec_plan_attributes (plan_handle) pa
    DONDE atributo = 'dbid " 
   ORDER BY AVG_ELAPSED_TIME DESC;

Conclusión
SQL Server 2005 le proporciona un número de DMV y DMF nueva que le ayude con la recopilación de información sobre lo que está pasando con SQL Server. El sys.dm_exec_query_stats DMV es una joya pequeña y encantadora que le permite devolver estadísticas de las declaraciones de TSQL. Al utilizar este DMV y unirse con otras del DMV y DMF se puede desarrollar un número de diferentes consultas TSQL para identificar rápidamente worsst su solicitud de realizar consultas. Le sugiero que tome la información que he dado en este artículo y el desarrollo mismo de un conjunto de seguimiento de los resultados TSQL consultas para supervisar y desarrollar las líneas de base de rendimiento para SQL Server 2005 medio ambiente.

No hay comentarios:

Publicar un comentario