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.
