Blog gratis
Reportar
Editar
¡Crea tu blog!
Compartir
¡Sorpréndeme!
img
img

 

 

SQL Server
Blog. (Apuntes y Recopilaciones) por Norman M. Pardell

img
img
19 de Marzo, 2011 · estadísticasSQL

Actualización de estadísticas síncronas o asíncronas mejoran la respuesta del optimizador de consultas. SQL Server

Las estadísticas usadas para la optimización de consulta, son objetos que contienen información estadística acerca de la distribución de valores en una o más columnas de una tabla o vista indexada. El optimizador de consultas utiliza las estadísticas para estimar la cardinalidad o el número de filas del resultado de la consulta, lo que hace posible que el optimizador de consultas pueda crear un plan de consulta de alta calidad.

 

Hay 3 opciones de estadísticas, configurables, a nivel de cada base de datos:

AUTO_CREATE_STATISTICS

AUTO_UPDATE_STATISTICS

AUTO_UPDATE_STATISTICS_ASYNC

AUTO_CREATE_STATISTICS y AUTO_UPDATE_STATISTICS

La opción automática de creación de estadísticas, AUTO_CREATE_STATISTICS, y la de actualización de estadísticas, AUTO_UPDATE_STATISTICS, están activadas de forma predeterminada:

ALTER DATABASE TU_BaseDatos

    SET AUTO_CREATE_STATISTICS ON;

ALTER DATABASE TU_BaseDatos

    SET AUTO_UPDATE_STATISTICS ON;

GO

 Estas actualizaciones de las estadísticas son sincronas, las consultas siempre se compilan y ejecutan con estadísticas actualizadas; cuando las estadísticas están obsoletas, el optimizador de consultas espera a que las estadísticas estén actualizadas antes de compilar y ejecutar la consulta.

 Se aconseja su uso cuando se realiza operaciones que cambian la distribución de los datos, como truncar una tabla o realizar una actualización masiva de un gran porcentaje de las filas. Si no actualiza las estadísticas después de completar la operación, el uso de estadísticas sincrónicas garantizará que las estadísticas estén actualizadas antes de ejecutar las consultas en los datos cambiados.

AUTO_UPDATE_STATISTICS_ASYNC

La opción automática de creación de estadísticas asincronas, AUTO_UPDATE_STATISTICS_ASYNC, no están activadas de forma predeterminada:

ALTER DATABASE TU_BaseDatos

    SET AUTO_UPDATE_STATISTICS_ASYNC ON;

GO

Estas actualizaciones de las estadísticas son asincrónicas, las consultas se compilan con las estadísticas existentes incluso aunque estén anticuadas; el optimizador de consultas podría elegir un plan de consulta poco óptimo si las estadísticas están obsoletas cuando se compila la consulta. Las consultas que se compilan cuando las actualizaciones asincrónicas se han completado se beneficiarán del uso de estadísticas actualizadas.

 Se aconseja su uso:

 Cuando su aplicación ejecuta frecuentemente la misma consulta, consultas similares o los planes de consulta almacenados en memoria caché similares. Sus tiempos de respuesta a la consulta podrían ser más predecibles con actualizaciones asincrónicas de las estadísticas que con actualizaciones sincrónicas, porque el optimizador de consultas puede ejecutar las consultas de entrada sin esperar a que las estadísticas se actualicen

 Si su aplicación ha experimentado tiempos de espera de solicitud de cliente causados por una o varias consultas que aguardaban la actualización de estadísticas. En algunos casos, la espera por las estadísticas sincrónicas podría causar errores en aplicaciones con tiempos de espera agresivos.

 

 Usar estadísticas sincronas y asincronas, de forma conjunta. 

 La opción AUTO_UPDATE_STATISTICS_ASYNC se establece en el nivel de la base de datos y determina el método de actualización para todas las estadísticas de la base de datos. Sólo es aplicable a la actualización de estadísticas y no se puede usar para crear estadísticas de forma asincrónica. El establecimiento de esta opción en ON no tiene ningún efecto a menos que AUTO_UPDATE_STATISTICS también se establezca en ON. De forma predeterminada, la opción AUTO_UPDATE_STATISTICS_ASYNC está en OFF.

 En aquellos escenarios donde no tenemos un control total sobre las operaciones que se realizan en la base de datos y no podemos hilar siempre muy fino es preferible tener actualizaciones síncronas, de esta forma no obtendremos la mejor de las latencias y posiblemente suframos en algunas consultas, pero minimizamos el riesgo de tener pocas consultas “optimizadas”, en base a unas estadísticas no adecuadas, que nos pueden dar un gran disgusto.

Estadísticas filtradas CREATE STATISTICS

 Las estadísticas filtradas pueden mejorar el rendimiento de las consultas. Para la mayoría de las consultas, el optimizador de consultas genera ya las estadísticas necesarias para un plan de consulta de alta calidad; en algunos casos, para mejorar el rendimiento de la consulta necesita crear estadísticas adicionales con CREATE STATISTICS.

 A modo de ejemplo, se crean las estadísticas NameStatistic para todas las filas de las columnas Colum1ID y Colum2 de la tabla TBA y se deshabilita la posibilidad de volver a calcular las estadísticas automáticamente

CREATE STATISTICS NameStatistic

    ON Tu_BaseDatos. TBA.TBA (Colum1ID, Colum2)

    WITH FULLSCAN, NORECOMPUTE;

 

 Utilizar esta opción puede producir planes de consulta poco óptimos. Se recomienda usar esta opción con moderación y que lo haga únicamente un administrador de sistemas cualificado.

Activar actualización automática de estadísticas en SQL 2000

 

Para activar la actualización automática de estadísticas en SQL 2000:

 

--habilita: auto create statistics

sp_dboption TubaseDatos, 'auto create statistics', 'ON'

GO

--habilita: auto update statistics

sp_dboption TubaseDatos, 'auto update statistics', 'ON'

GO

--habilita: sp_autostats

EXEC sp_autostats 'ON'

GO

 

 

Cuando se actualizan las estadísticas automáticas

 

 

  Para tablas con muchas filas, hemos de tener cuidado ya que las estadísticas se actualizan automáticamente cuando se han cambiado (insert/update/delete) mas del 20% de la cantidad de filas actuales de la tabla. Ese valor se guarda en la columna [rowmodctr] en la tabla de sistema [sysindexes].

 

 Por poner un ejemplo, en una tabla con 80.000.000 registros, e imaginando que hay un promedio de 200.000 registros modificados por día. Y luego solo se hacen select el resto del día:

 

100.00 * (200,000 / 80,000,000) = 0.25

 

 Como veis, la cantidad de filas que se insertan en un día equivalen al 0.25, de la cantidad de filas actuales y por tanto SQL Server no actualizara las estadísticas hasta que el valor en [rowmodctr] sea el 20% de las filas. Por eso hay que vigilar de cerca las tablas con muchas filas y de ser posible buscar el tiempo adecuado para actualizar sus estadísticas. Ya que puede penalizar la creación de los planes de ejecución, en estas tablas.

 

 

 Para forzar la actualización de estadísticas, en todos los objetos de las bases de datos, para evitar estar en el anterior escenario comentado:

 

--Actualizo estadisticas

EXEC sp_updatestats

GO

 

 

 Os paso el procedimiento que nos dice el tamaño de un tabla de nuestra base de datos:

 

sp_spaceused 'Nombre_De_Tu_Tabla'

 

 Os paso un procedimiento para ver los tamaños de todas las tablas de una base de datos, en SQL Server 2000: (En versiones posteriores, esta información se muestra desde el Management Studio)

 

CREATE PROCEDURE dbo.TableSpaceUsed

AS

 

-- Create the temporary table...

CREATE TABLE #tblResults

(

   [name]   nvarchar(20),

   [rows]   int,

   [reserved]   varchar(18),

   [reserved_int]   int default(0),

   [data]   varchar(18),

   [data_int]   int default(0),

   [index_size]   varchar(18),

   [index_size_int]   int default(0),

   [unused]   varchar(18),

   [unused_int]   int default(0)

)

 

-- Populate the temp table...

EXEC sp_MSforeachtable @command1=

         "INSERT INTO #tblResults

           ([name],[rows],[reserved],[data],[index_size],[unused])

          EXEC sp_spaceused '?'"

  

-- Strip out the " KB" portion from the fields

UPDATE #tblResults SET

   [reserved_int] = CAST(SUBSTRING([reserved], 1,

                             CHARINDEX(' ', [reserved])) AS int),

   [data_int] = CAST(SUBSTRING([data], 1,

                             CHARINDEX(' ', [data])) AS int),

   [index_size_int] = CAST(SUBSTRING([index_size], 1,

                             CHARINDEX(' ', [index_size])) AS int),

   [unused_int] = CAST(SUBSTRING([unused], 1,

                             CHARINDEX(' ', [unused])) AS int)

  

-- Return the results...

SELECT * FROM #tblResults

 

 

 

Como calcular la longitud de los campos de todas las tablas de una bbdd

--Longitud de campo de todas las tablas en sql sever 2005

SET NOCOUNT ON DBCC UPDATEUSAGE(0) -- DB size.

EXEC sp_spaceused-- Table row counts and sizes.

CREATE TABLE #t ( [name] NVARCHAR(128), [rows] CHAR(11), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' SELECT *FROM #t-- # of rows.

SELECT SUM(CAST([rows] AS int)) AS [rows]FROM #t DROP TABLE #t

--en lugar de select sum(cast(rows as int) que dice el número total de filas, puedes poner

SELECT SUM(CAST([rows] AS int)) AS [rows], sum(cast(reserved as flaot))*1024 as bytereservados FROM #t

 

Fuentes:

Microsoft, MSDN

Apunte y recopilación por Norman M. Pardell

 

Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.

publicado por normanmpardell a las 21:17 · Sin comentarios  ·  Recomendar
Comentarios (0) ·  Enviar comentario
Esta entrada no admite comentarios.
img
.Sobre mí
FOTO

Norman M. Pardell

MCITP: Database Administrator & Database Developer, SQL Server 2008. MCC Award Certificate. Consultor Senior de bases de datos en Capgemini España, S.L. Asesoramiento en implementación, desarrollo y gestión de bases de datos en grandes compañías. Actualmente, asignado a proyecto en compañía líder en el sector energético global. Más de 10 años trabajando con SQL Server (y otros gestores de BBDD)

» Ver perfil

img
.Secciones
» Inicio
img
.Enlaces
» Microsoft MSDN Foros
» Windows Server 2012
img
.Más leídos
» Asignar la cantidad correcta de Memoria para SQL Server
» Base de Datos Sospechosa (Suspect)
» Como modificar la Intercalación (Collation) en SQL Server
» Detectar bloqueos. SQL Server V.2005 y superiores
» Funciones SQL Server. Funciones escalares y funciones con valores de tabla.
» Integridad y corrupción en las bases de datos: DBCC CHECKDB
» Log de transacciones ( .ldf ). SQL Server.
» Migrando SQL Server 2005, 2008, 2008 R2 a SQL Server 2012
» Tomo I. Memoria RAM. Optimización de sistemas de 32 y 64 bits. SQL Server 2008.
» Transacciones activas. SQL server 2008
img
.Nube de tags [?]
                                                           
img img
FULLServices Network | Crear blog | Privacidad