5 tareas comunes de administración de SQL Server 2005

En este artículo voy a presentar algunas secuencias de comandos o procedimientos almacenados de SQL Server 2005 que utiliza regularmente como parte de la administración de mis servidores. Estos scripts permiten a granel supervisar el uso de un servidor, cambiando el tamaño de las bases, el uso de índices y su reconstrucción automática o comprobar la fecha de la última utilización de una tabla.

1. La actividad del servidor de seguimiento

Monitoreo de servidores SQL es posiblemente una de las responsabilidades más importantes de los administradores de bases de datos. Para ello se utiliza la sp_WhoIsActive procedimiento desarrollado por Adam Machanic. Este procedimiento se mostrarán las solicitudes pendientes en el servidor y toda la información asociada como el usuario ejecuta la aplicación, la CPU, I / O o de las propias aplicaciones.

sp_WhoIsActive: El consumo total

Un parámetro en el procedimiento para ejecutar el procedimiento dos veces en un intervalo determinado (por ejemplo, 5 años) para medir la actividad (casi) instantánea de las diferentes aplicaciones, lo que fácilmente puede determinar que consume la mayoría de los CPU, el uso tempdb o hace accesos al disco.

sp_WhoIsActive: Consumo de 5 años

El procedimiento también se muestra información sobre los tipos de consultas Espera y ocasionando bloqueos.

sp_WhoIsActive: Waits y consultas

Un clic en cada vínculo, se muestra el archivo XML asociado a la solicitud, resulta conveniente considerar la solicitud, obviamente, demasiado recursos.

Una más rápida, pero menos completo y que yo uso regularmente se encuentra disponible aquí.

2. Vigilancia de la utilización de espacio en disco

Puedo utilizar el procedimiento de sp_SDS (registro gratuito obligatorio) escrito por Richard Ding para generar un informe sobre el tamaño de las bases de datos y registros de una instancia. El espacio también se calcula para ambos tipos de archivos y el cuerpo.

sp_SDS

Una alternativa a este procedimiento almacenado para ser más fácil de copiar / pegar en una hoja de cálculo de Excel es la siguiente:

  SELECT @ @ SERVERNAME como instancia
 db.name Como base
 SUM (CASE WHEN af.groupid = 0 THEN 0 ELSE 8192.0E * af.size / 1048576.0E END) AS Taille_Base,
 SUM (CASE WHEN af.groupid = 0 THEN 8192.0E * af.size / 1048576.0E ELSE 0 END) AS Taille_Log,
 SUM (8192.0E * af.size / 1048576.0E) AS Taille_Totale
 Maestro de .. sysdatabases como DB
 INNER JOIN master .. sysaltfiles como AF AF. [] Dbid = db. [Dbid]
 Db.name DONDE NO IN ( 'distribución', 'recursos', 'master', 'tempdb', 'Modelo', 'msdb')
 GRUPO DE db.name POR 

Si usted desea controlar el tamaño de sus bases de datos, probablemente será necesario crear una tabla específica en una administración de base de datos. Esta tabla contendrá todos los datos que usted considere interesante. Para ello se utiliza la dbinfo guión escrito por Tim Ford. Se puede grabar en una tabla del tamaño de los archivos (bases de datos y registros), espacio libre, el nombre de archivo o el método de restauración.

Dbinfo

Sólo tienes que crear un puesto de trabajo de SQL ejecutar este script a intervalos regulares para almacenar los datos en la tabla dbinfo, que le permiten crear informes o para predecir la evolución del tamaño de las bases, por ejemplo utilizando SQL Server Analysis Services.

3. Análisis del índice de rendimiento de

El índice de gestión es otra tarea importante para la administración de servidores SQL. Para analizar la pertinencia y el uso de estos índices en las tablas de una base de datos, uso un script desarrollado por Jason Strate. Este script muestra todos los índices en una base de datos (incluidas las pilas, es decir, los propios cuadros) y un sinfín de información para juzgar su utilidad como el número de Analiza y busca, columnas indexadas, el tamaño del índice o el impacto (positivo) del índice en los usuarios. El script también muestra los índices de falta sugerido por SQL Server.

Análisis de Índice de

Si desea ver busca, búsquedas y exploraciones de forma rápida, puede utilizar el siguiente script:

 SELECT OBJECT_NAME (S. [object_id]) AS [Nombre de objeto], yo [NOMBRE] AS [Index Name], user_seeks, user_scans, USER_LOOKUPS, user_updates sys.dm_db_index_usage_stats DE AS S INNER JOIN sys.indexes como yo en I. [ OBJECT_ID] = S. [object_id] AND I. S. index_id = index_id DONDE OBJECTPROPERTY (S. [object_id], 'IsUserTable') = 1 

Para estudiar el impacto de las solicitudes para insertar, actualizar y eliminar en el índice, puede utilizar esta secuencia de comandos:

  SELECT OBJECT_NAME (A. [object_id]) AS [Nombre de objeto]
 I. [NOMBRE] AS [NOMBRE INDICE]
 A. LEAF_INSERT_COUNT,
 A. LEAF_UPDATE_COUNT,
 A. LEAF_DELETE_COUNT
 Sys.dm_db_index_operational_stats DE (NULL, NULL, NULL, NULL) A
 INNER JOIN AS I sys.indexes
 Sobre I. [object_id] = A [object_id]
 Y I. A. index_id = index_id
 DONDE OBJECTPROPERTY (A. [object_id], 'IsUserTable') = 1 

Para una lista de índices no utilizados y determinar si pueden o no ser eliminado, puede utilizar el siguiente script:

  INT DECLARE @ dbid
 , @ (100 DbName VARCHAR);

 SELECT @ dbid = DB_ID ()
 , @ Dbname = db_name ();

 CON partitionCTE (OBJECT_ID, index_id, row_count, partition_count),
 AS
 (
 SELECT [OBJECT_ID]
 , Index_id
 , SUM ([filas]) AS 'row_count'
 , COUNT (partition_id) AS 'partition_count'
 Sys.partitions DE
 GROUP BY [object_id]
 , Index_id
 )

 SELECT OBJECT_NAME (I. [object_id]), como objectName
 , I.name
 , CAJA
 CUANDO i.is_unique = 1
 Único entonces ''
 ELSE''
 + INDEXTYPE I.type_desc END AS ''
 , Ddius.user_seeks
 , Ddius.user_scans
 , Ddius.user_lookups
 , Ddius.user_updates
 , Cte.row_count
 , CASO CUANDO partition_count> 1, entonces "sí"
 Else 'END n' AS 'particiones? "
 , CAJA
 CUANDO i.type Y i.is_unique_constraint = 2 = 0
 ÍNDICE THEN 'drop' + i.name
 + 'El' + @ dbname
 +. "Dbo".  + OBJECT_NAME (ddius. [object_id]) + ";"
 CUANDO i.type Y i.is_unique_constraint = 2 = 1
 ALTER THEN 'TABLE' + @ dbname
 +. "Dbo".  + OBJECT_NAME (ddius. [object_id])
 + 'CONSTRAINT DROP »i.name + +"; "
 ELSE''
 SQL_DropStatement END AS ''
 Como yo de sys.indexes
 INNER JOIN sys.dm_db_index_usage_stats ddius
 SOBRE i.object_id = ddius.OBJECT_ID
 Y i.index_id = ddius.index_id
 INNER JOIN CTE como partitionCTE
 SOBRE i.object_id = cte.OBJECT_ID
 Y i.index_id = cte.index_id
 DONDE Ddius.database_id dbid = @
 ORDER BY
 (ddius.user_seeks + ddius.user_scans + ddius.user_lookups) AUC
 , User_updates DESC; 

Por último, a la lista de los índices de falta sugerido por SQL Server, puede usar esta secuencia de comandos:

 AS t.name SELECT 'Table', 'Crear un índice no agrupado ix_' t.name + + '_missing_' + (10 CAST (ddmid.index_handle AS VARCHAR)) + 'On' ddmid.STATEMENT + + '(' + IsNull ( ddmid.equality_columns,'') + ddmid.equality_columns CASO CUANDO SE ddmid.inequality_columns NOT NULL y IS NOT NULL THEN''ELSE''END + IsNull (ddmid.inequality_columns'') + ')' + IsNull ( 'Incluir ( '+ ddmid.included_columns +');',',') AS sql_statement, ddmigs.user_seeks, ddmigs.user_scans, CAST ((ddmigs.user_seeks + ddmigs.user_scans) * ddmigs.avg_user_impact AS INT) AS 'est_impact' sys.dm_db_missing_index_groups ddmigs.last_user_seek A PARTIR DEL INTERIOR ddmig sys.dm_db_missing_index_group_stats Regístrate como ddmigs SOBRE ddmigs.group_handle = ddmig.index_group_handle INNER JOIN AS sys.dm_db_missing_index_details ddmid SOBRE ddmig.index_handle = ddmid.index_handle sys.tables AS t INNER JOIN ON ddmid.OBJECT_ID = t.OBJECT_ID DONDE ddmid.database_id DB_ID = () y CAST ((ddmigs.user_seeks + ddmigs.user_scans) * ddmigs.avg_user_impact como int)> 100 ORDER BY CAST ((ddmigs.user_seeks + ddmigs.user_scans) * ddmigs.avg_user_impact AS INT) DESC; 

4. Índices de desfragmentación

Poco a poco, a medida que los datos son agregados, actualizados o borrados, los índices están fragmentados y puede degradar el rendimiento significativamente. Por tanto, es importante tener una política de control del índice de la fragmentación y la acción en caso de fragmentación demasiado.

En mi caso, estoy de ejecutar un trabajo de fin de semana que ejecuta la dba_IndexDefrag_sp procedimiento Michelle Ufford. Este procedimiento le permite revisar todos los índices de todas las bases de datos y servidores, dependiendo del nivel de fragmentación, para reorganizar o para regenerar por completo. Las opciones que el procedimiento para cambiar los niveles de fragmentación de la activación de dos acciones, especificar una base o una sola tabla para verificar o identificar el número de procesadores que se utilizarán.

5. Última tablas utilizadas

Dependiendo de su entorno de producción, puede suceder que un gran número de mesas es creado por los usuarios que luego abandonadas. Esto puede ocurrir, por ejemplo, para la importación de archivos que puede ser interesante para comprobar en cualquier otra parte del servidor SQL. Sigue siendo si estos cuadros se siguen utilizando o no antes de archivo. Para ello puede utilizar el siguiente script que le dará la última fecha de lectura y escritura de las tablas contenidas en una base de datos dada:

 SET ANSI_WARNINGS OFF; SET NOCOUNT ON; IR CON agravada AS (SELECT [object_id], last_user_seek, last_user_scan, last_user_lookup, sys.dm_db_index_usage_stats last_user_update DE DONDE = database_id DB_ID ()) SELECT [esquema] = OBJECT_SCHEMA_NAME ([object_id]), [table_or_view ] = OBJECT_NAME ([object_id]), last_read = MAX (last_read) last_write = MAX (last_write) FROM (SELECT [object_id], last_user_seek, NULL de agravación UNION ALL SELECT [object_id], last_user_scan, NULL de agravación UNION ALL SELECT [ object_id], last_user_lookup, NULL de agravación UNION ALL SELECT [object_id], NULL, agravada DE last_user_update) como X ([object_id], last_read, last_write) GROUP BY OBJECT_SCHEMA_NAME ([object_id]), OBJECT_NAME ([object_id]) ORDER BY 1 , 2;