Funcionalidad de mantenimiento estadísticas (autostats) en SQL Server

Seleccione idioma Seleccione idioma
Id. de artículo: 195565 - Ver los productos a los que se aplica este artículo
Expandir todo | Contraer todo

En esta página

Resumen

La funcionalidad de mantenimiento estadística recién introducido AutoStat, puede generar sobrecarga no deseada en un sistema de producción realizando una de las siguientes acciones:
  • Iniciando la estadísticas actualizaciones durante periodos de mucha producción.

    -o bien -
  • Iniciar un excesivamente alto número de procesos de UPDATE STATISTICS en un momento determinado en tiempo.
El propósito de este artículo es detallar las condiciones en la que espera ver autostats generado y UPDATE STATISTICS se ejecuta contra tablas en una base de datos.

Para obtener información acerca de autostats en SQL Server 2000, vea "Estadísticas utilizado por el optimizador de consultas de Microsoft SQL Server 2000" en el siguiente sitio Web de MSDN:
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx


Nota Si utiliza Microsoft SQL Server 2005, consulte el documento de Microsoft siguiente para obtener información acerca cómo se utilizan las estadísticas por el optimizador de consultas en SQL Server 2005:
http://technet.microsoft.com/en-us/library/cc966419.aspx

Más información

Información general

SQL Server hace uso de un optimizador basado en coste que puede ser extremadamente sensible a información estadística que se proporciona en tablas e índices. Sin información estadística correcto y actualizado, SQL Server puede ser pide que determinar el mejor plan de ejecución de una consulta determinada.

Estadísticas que se mantienen en cada tabla en SQL Server para facilitar el optimizador basado en coste decisiones incluyen el:
  • Número de filas en la tabla.
  • Número de páginas utilizadas por la tabla.
  • Número de las modificaciones realizadas en las claves de la tabla desde la última actualización a las estadísticas.
Información adicional se almacena los índices, incluidos (para cada índice):
  • Un histograma de igualdad alto en la primera columna.
  • Densidades en todos los prefijos de columna.
  • Longitud promedio de clave.
Estadísticas de índices se crean automáticamente siempre que se genera un nuevo índice. Además, ahora es posible crear y mantener las estadísticas en otras columnas así.

Para mantener la información estadística de forma tan actualizada como sea posible, SQL Server introduce AutoStat, que, a través de supervisión de SQL Server de modificaciones de la tabla, es capaz de actualizar automáticamente las estadísticas para una tabla cuando se alcanza un determinado umbral de cambio. Además, SQL Server introduce automática-crear-estadísticas, que hace que el servidor generar automáticamente todas las estadísticas necesarias para la optimización precisa de una consulta específica.

Determinar cuándo es inminente AutoStat generación

Como se mencionó anteriormente, AutoStat actualizará automáticamente las estadísticas de una tabla determinada cuando se alcanza "un umbral de cambio de". La columna sysindexes.rowmodctr mantiene una ejecución total de todas las modificaciones a una tabla que, a lo largo del tiempo, puede afectar negativamente decisión proceso del procesador de consultas. Este contador se actualiza cada vez que cualquiera de los eventos siguientes:
  • Se realiza la inserción de una sola fila.
  • Se realiza una eliminación de fila única.
  • Se realiza una actualización a una columna indizada.
Nota : TRUNCATE TABLE no actualiza rowmodctr.

Después de actualizar las estadísticas de tabla, el valor de rowmodctr se restablece a 0 y versión de esquema de estadísticas de la tabla se actualiza.

Además, en situaciones en las que se toma el plan de ejecución del procedimiento almacenado de caché y dicho plan es sensible a las estadísticas, la versión de esquema de las estadísticas se compara con la versión actual. Si hay nuevas estadísticas disponibles, se recompilarán el plan para el procedimiento almacenado.

El algoritmo básico para las estadísticas de actualización automática es:
  • Si la cardinalidad de una tabla es menos de seis y la tabla está en la base de datos tempdb, automáticamente la actualización con cada seis modificaciones a la tabla.
  • Si la cardinalidad de una tabla es mayor que 6, pero menor o igual que 500, actualizar estado cada 500 modificaciones.
  • Si la cardinalidad de una tabla es mayor que 500, actualiza las estadísticas cuando (500 + 20 por ciento de la tabla) se han producido cambios.
  • Para variables de tabla, los cambios de cardinalidad no activa las estadísticas de actualización automática.
Nota : en este sentido más estricta, SQL Server cuenta cardinalidad como el número de filas de la tabla.

Nota : en además cardinalidad, la selectividad del predicado también afecta a la generación de AutoStats. Esto significa que las estadísticas no esté actualizado afer cada 500 modificaciones si cardinalidad < 500 o cada 20 % de los cambios de si cardinalidad > 500. Un factor (intervalos de valor de 1 a 4, 1 y 4, ambos inclusive) de escalado se genera en función de la selectividad y un producto de este factor y el número de cambios como obtenidas el algoritmo sería el número real de modificaciones necesarias para la generación de AutoStats.

El algoritmo anterior puede ser summarised en el formulario de una tabla:
_________________________________________________________________________________
 Table Type | Empty Condition | Threshold When Empty |Threshold When Not Empty 
_________________________________________________________________________________
 Permanent  | < 500 rows      | # of Changes >= 500  | # of Changes >= 500 + (20% of Cardinality)
___________________________________________________________________________
 Temporary  | < 6 rows        | # of Changes >= 6    | # of Changes >= 500 + (20% of Cardinality)
___________________________________________________________________________
Table
Variables   | Change in cardinality does not affect AutoStats generation.
___________________________________________________________________________
las siguientes son dos ejemplos para demostrar este concepto:

Ejemplo 1

Considere la tabla authors de la base de datos pubs, que contiene 23 filas y tiene dos índices. El índice agrupado único, UPKCL_auidind, se indiza en una columna, au_id, y se ha creado un índice no agrupado compuesto aunmind, en las columnas au_lname y au_fname. Dado que esta tabla contiene menos de 500 filas, AutoStat empezará una vez se han producido 500 cambios en los datos de la tabla. Los cambios pueden ser uno de 500 o más inserta, elimina, cambia a una columna indizada como au_lname o cualquier combinación de ambos.

Puede, por lo tanto, predecir cuando se inicia UPDATE STATISTICS supervisando el valor sysindexes.rowmodctr, que se incrementará tras cada actualización. Cuando alcanza o supera 500, puede esperar UPDATE STATISTICS se inicie.

Ejemplo 2

Considere una segunda tabla, t2, que tiene una cardinalidad de 1.000. Para las tablas con más de 500 filas, SQL Server le UPDATE STATISTICS cuando (500 + 20 por ciento) han realizado cambios. Realizando los cálculos matemáticos, 20 por ciento de 1.000 es 200, por lo que puede esperar ver AutoStat iniciar después de aproximadamente 700 modificaciones han realizado en la tabla.

Determinación de Autostats automatizar

Para automatizar la determinación de cuándo se ejecutará AutoStat, puede sondear la tabla sysindexes y identificar cuando las modificaciones de la tabla alcancen el punto de partida. El siguiente es un algoritmo básico para hacerlo:
   if (sysindexes.rows > 500)
      if (sysindexes.rows * 0.20 >= sysindexes.rowmodctr && production
      hours) //500 change leeway
         begin
            disable autostats
            log autostats disable
         end
      else
         begin
            stats ok
         end
   else
      if (sysindexes.rowmodctr >= 425) //75 change leeway
         begin
            disable autostats
            log autostats disable
         end
				

Más adelante se puede programar un trabajo para hacer lo siguiente:
  • Ejecute UPDATE STATISTICS en todas las tablas para el que se veían obligados para deshabilitarlos durante el día.

    - y -
  • Volver a habilitar AutoStat, porque el contador de modificación de cada tabla se han restablecido a 0 cuando se ejecutó UPDATE STATISTICS.

Controlar si se ejecuta UPDATE STATISTICS en una tabla

La solución más obvia a esta pregunta, cuando ha demostrado para ser Problematic, AutoStat es deshabilitar la generación de estadística automática, dejando, por lo tanto, los administradores de base de datos libres para programar UPDATE STATISTICS durante horas de menos intrusivos. Puede hacerlo mediante la instrucción UPDATE STATISTICS o el procedimiento sp_autostats almacenados. La sintaxis de la instrucción UPDATE STATISTICS es:
   UPDATE STATISTICS <table>...with NORECOMPUTE
				

La sintaxis para el procedimiento sp_autostats almacenado es:
sp_autostats <nombre_tabla>, <stats_flag>, <index_name>
donde <stats_flag> es "on" o "off".

También puede utilizar sp_dboption para deshabilitar la aparición automática de UPDATE STATISTICS o CREATE STATISTICS en un nivel por base de datos:
<dbname>sp_dboption <nombrebd>, 'estadísticas de actualización automática', < en | desactivar >

-o bien -

<dbname>sp_dboption <nombrebd>, 'auto crear estadísticas,' < en | desactivar >

Controlar el número de procesos simultáneos de UPDATE STATISTICS

Actualmente, corta deshabilitar AutoStat para tablas específicas, no es posible configurar el número de instrucciones UPDATE STATISTICS automáticas que se están ejecutando simultáneamente (DCR 51539 se ha registrado para este). El servidor sin embargo, no, limitar el número de procesos simultáneos de UPDATE STATISTICS para cuatro por procesador.

Determinar cuándo se ejecutan Autostats

Puede utilizar el indicador de traza 205 para informar cuando se se vuelve a compilar un procedimiento almacenado depende de las estadísticas como consecuencia de AutoStat. Este indicador de traza escribir los mensajes siguientes en el registro de errores:
1998-10-15 11:10:51.98 spid9 recompilar emitido: nombreProc: sp_helpindex
LineNo: 75 StmtNo: 29
Cuando el indicador de traza 205 está habilitado, el siguiente mensaje corchete también el mensaje AutoStat 8721 cuando se actualizan las estadísticas. El mensaje de apertura de los corchetes se puede distinguir por el valor RowModCnt, que será mayor que 0. El corchete de cierre después de UPDATE STATISTICS, tendrá un valor RowModCnt de 0:
Cambio de esquema spid8 11:38:43.68 1998-10-15: Tbl Dbid: ObjID 7:
RowModCnt 133575514: RowModLimit 60500: 60499
Para este mensaje, "RowModCnt" es el número total de modificaciones a la tabla. "RowModLimit" es el umbral que, cuando se sobrepase, da como resultado una ejecución de instrucción UPDATE STATISTICS para la tabla.

También es posible habilitar el indicador de traza 8721, volcará información en el registro de errores cuando se ha ejecutado AutoStat. Es un ejemplo del tipo de mensaje que puede esperar para ver el:
1998-10-14 16:22:13.21 spid13 AUTOSTATS: Tbl actualizado: [authors]
Modalidades de filas: 23: enlace 501: 500 duración: 47ms UpdCount: 2
Para este mensaje, "Modalidades" es el número total de modificaciones a la tabla. "Dependiente" es el umbral de modificación, "Duración" es la cantidad de tiempo que necesita la instrucción UPDATE STATISTICS para completar y "UpdCount" es el número de las estadísticas actualizadas.

También puede utilizar al Analizador de SQL Server para identificar cuándo se ejecutan las instrucciones UPDATE STATISTICS. Para ello, siga estos pasos:
  1. En el menú Analizador , haga clic en Herramientas y, a continuación, haga clic en Opciones .
  2. En la ficha General , vaya a eventos y, a continuación, seleccione All clases de sucesos .
  3. Definir una nueva traza y en eventos , seleccione varios , seleccione el sub-event Estadísticas de actualizaciones automáticas .
Nota: Si se actualizan las estadísticas de muchos por AutoStat, un gran número de mensajes se puede escribir en el registro de errores. Pruebe exhaustivamente con estos indicadores de traza antes de utilizar en cualquier producción o un servidor crítico en caso contrario.

Bloqueos de esquema

SQL Server emplea dos tipos de bloqueos de esquema, los cuales se toman cuando actualiza las estadísticas de una tabla:
   Sch-S: Schema Stability Lock
   ----------------------------
   This lock ensures that a schema element, such as a table or index, will
   not be dropped while any session holds a schema stability lock on the
   schema element.

   Sch-M-UPD-STATS: Schema Modification Lock
   -----------------------------------------
   This is a non-blocking lock that is used by the system to ensure that
   only one automatic UPDATE STATISTICS process is run against a table at
   any given point in time. The sp_lock stored procedure will report this
   lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M.
				
se pueden ver estos bloqueos por ejecutar sp_lock o seleccionándolo en la tabla syslockinfo.

Propiedades

Id. de artículo: 195565 - Última revisión: viernes, 2 de noviembre de 2007 - Versión: 7.6
La información de este artículo se refiere a:
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
  • Microsoft SQL Server 2000 Enterprise Edition 64-bit
Palabras clave: 
kbmt kbinfo KB195565 KbMtes
Traducción automática
IMPORTANTE: Este artículo ha sido traducido por un software de traducción automática de Microsoft (http://support.microsoft.com/gp/mtdetails) en lugar de un traductor humano. Microsoft le ofrece artículos traducidos por un traductor humano y artículos traducidos automáticamente para que tenga acceso en su propio idioma a todos los artículos de nuestra base de conocimientos (Knowledge Base). Sin embargo, los artículos traducidos automáticamente pueden contener errores en el vocabulario, la sintaxis o la gramática, como los que un extranjero podría cometer al hablar el idioma. Microsoft no se hace responsable de cualquier imprecisión, error o daño ocasionado por una mala traducción del contenido o como consecuencia de su utilización por nuestros clientes. Microsoft suele actualizar el software de traducción frecuentemente.
Haga clic aquí para ver el artículo original (en inglés): 195565

Enviar comentarios

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com