Cinco tareas administrativas comunes de SQL Server 2005

En este artículo voy a presentar unos cuantos scripts y procedimientos almacenados de SQL Server 2005 que utiliza regularmente en la administración de mis servidores. Estos scripts permiten mayor parte del seguimiento de la utilización de un servidor, cambiar el tamaño de la base de datos, los índices de utilización y a reconstruir de forma automática o comprobar la fecha de último uso de una tabla.

1. Supervisar la actividad del servidor

Seguimiento de SQL Servers es posiblemente una de las responsabilidades más importantes de los administradores de bases de datos. Para esto utilice el procedimiento sp_WhoIsActive desarrollado por Machanics Adán. Este procedimiento se mostrarán las solicitudes pendientes en el servidor y todos los asociados la información que el usuario ejecuta la aplicación, la CPU, I / O o las propias aplicaciones.

el consumo sp_WhoIsActive: Total

Un parámetro en el procedimiento para ejecutar el ciclo dos veces en un intervalo dado (por ejemplo, 5s) para medir la actividad (casi) instante de diferentes consultas, que pueden determinar fácilmente qué consume más CPU, el uso tempdb o hace accesos a disco.

sp_WhoIsActive: 5s Consumo

El procedimiento también se muestra información sobre los tipos de consultas Espere y causando bloques.

Espera sp_WhoIsActive: y consultas

Un clic en cada vínculo, se mostrará el archivo XML asociada a la solicitud, conveniente para considerar una solicitud de recursos, obviamente, demasiado intensiva.

Una alternativa más rápida, pero menos completa y que yo uso regularmente está disponible aquí .

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

Puedo usar el procedimiento sp_SDS (suscripción gratuita requerido) escrito por Richard Ding para generar un informe sobre el tamaño de bases de datos y registros de una instancia. El espacio también se calcula para ambos tipos de archivos y todo 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 Excel es la siguiente:

  SELECT @ @ SERVERNAME AS Instancia
 db.name AS Base
 SUM (CASE WHEN 0 THEN af.groupid = 0 * ELSE 8192.0E af.size / END 1048576.0E) AS Taille_Base,
 SUM (CASE WHEN af.groupid = 0 THEN 8192.0E af.size * / ELSE 1048576.0E 0 END) AS Taille_Log,
 Sum (* 8192.0E af.size / 1048576.0E) AS Taille_Totale
 De maestro .. db sysdatabases AS
 INNER JOIN amo .. sysaltfiles AS DE af af. [Dbid] = db. [Dbid]
 DONDE NO EN Db.name ('distribución', 'Recursos', 'maestro', 'tempdb', 'modelo', 'msdb')
 GROUP BY db.name 

Si desea controlar el tamaño de sus bases de datos, probablemente será necesario crear una tabla específica de una administración de base de datos. Esta tabla contendrá todos los datos que considere interesante de ver. Para esto yo uso el script DBINFO 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 del archivo o el modo de recuperación.

DBINFO

Basta con crear un trabajo de SQL ejecuta esta secuencia de comandos en una base regular para almacenar estos datos en la tabla DBINFO, que le permitirá crear informes o para predecir la evolución del tamaño de las bases, por ejemplo, el uso de SQL Server Analysis Services.

3. Análisis del índice de rendimiento

El índice de gestión es otra tarea importante de la administración de servidores SQL. Para analizar la pertinencia y el uso de estos índices en las tablas de una base de datos, yo uso un script desarrollado por Jason de Strate. Este script muestra todos los índices de una base de datos (incluyendo montones, es decir, los propios cuadros) y una miríada de información necesaria para evaluar 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 guión también muestra los índices que faltan sugerido por SQL Server.

Análisis Índice

Si desea ver Busca, Analiza y búsquedas con rapidez, puede utilizar el siguiente script:

  SELECCIONAR OBJECT_NAME (S. [object_id]) AS [Nombre del Objeto],
 I. [nombre] como [] Nombre de índice
 USER_SEEKS,
 USER_SCANS,
 USER_LOOKUPS,
 USER_UPDATES
 AS S DE SYS.DM_DB_INDEX_USAGE_STATS
 INNER JOIN AS I sys.indexes
 EN I. [object_id] = [S. object_id]
 Y I. S. index_id index_id =
 DONDE OBJECTPROPERTY (S. [object_id], 'IsUserTable') = 1 

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

 SELECCIONAR OBJECT_NAME (A. [object_id]) AS [Nombre del Objeto], I [NAME] AS [Nombre del índice] A. LEAF_INSERT_COUNT, LEAF_UPDATE_COUNT A., A. DE LEAF_DELETE_COUNT SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL, NULL, NULL, NULL) Un INNER JOIN AS I DE I. SYS. INDICES [object_id] = [A. object_id] Y = A. I. index_id index_id DONDE OBJECTPROPERTY (A. [object_id] IsUserTable ') = 1 

Para una lista de índices no utilizados y determinar si o no se puede quitar, puede utilizar el siguiente script:

 INT DECLARE @ dbid, dbName VARCHAR @ (100) SELECT @ dbid = db_id (), @ dbname = DB_NAME (); CON partitionCTE (object_id, index_id, row_count, partition_count) AS (SELECT [object_id], index_id, SUM ([ ]) AS 'row_count FILAS', COUNT (partition_id) «partition_count AS 'del grupo sys.partitions POR [object_id], index_id) OBJECT_NAME SELECT (i. [object_id]) objectName AS, i.name, CASO CUANDO i.is_unique = 1 THEN 'exclusiva' ELSE''END + i.type_desc AS 'INDEXTYPE' ddius.user_seeks, ddius.user_scans, ddius.user_lookups, ddius.user_updates, cte.row_count, CASO CUANDO partition_count> 1 THEN 'sí' Else 'n AS 'particiones? END', CASO CUANDO Y i.type i.is_unique_constraint 2 = 0 THEN 'Drop Índice i.name' On '+ +' + @ + = dbName. dbo '.  + OBJECT_NAME (ddius. [] object_id) + ',' Cuando i.type Y = 1 THEN 'ALTER TABLE' i.is_unique_constraint = 2 + @ + dbName. Dbo '.  + OBJECT_NAME (ddius. [] object_id) La restricción '+' Drop + i.name + ',' SQL_DropStatement 'ELSE''END AS DE sys.indexes AS i INNER JOIN sys.dm_db_index_usage_stats ddius ON = i.OBJECT_ID ddius.OBJECT_ID Y i.index_id = ddius.index_id INNER JOIN cte partitionCTE AS DE i.OBJECT_ID cte.OBJECT_ID = Y = i.index_id cte.index_id ddius.database_id DONDE dbid = @ ORDER BY (ddius.user_seeks ddius.user_scans + + ddius.user_lookups ) user_updates ASC DESC; 

Por último, a la lista de índices que faltan sugerido por SQL Server, puede utilizar este script:

 SELECCIONAR t.name AS 'Tabla', '' + + t.name 'Crear índice no agrupado ix_ _missing_' + CAST (AS VARCHAR ddmid.index_handle (10)) El '+' + ddmid.STATEMENT + '(' + IsNull ( ddmid.equality_columns,'') CASE + cuando no es nulo y ddmid.inequality_columns ddmid.equality_columns no es 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 AS DE INTERIOR ddmig sys.dm_db_missing_index_group_stats Únete ddmigs AS DE ddmigs.group_handle = ddmig.index_group_handle INNER JOIN sys.dm_db_missing_index_details ddmid AS DE INTERIOR ddmid.index_handle ddmig.index_handle Únete a = t sys.tables AS DE ddmid.OBJECT_ID = = t.OBJECT_ID DONDE ddmid.database_id db_id () y CAST ((+ ddmigs.user_seeks ddmigs.user_scans) * ddmigs.avg_user_impact AS INT)> 100 ORDER BY CAST ((+ ddmigs.user_seeks ddmigs.user_scans) * ddmigs.avg_user_impact AS INT) DESC; 

4. índices Desfragmentar

Como los datos se han añadido, actualizado o suprimido, los índices están fragmentadas y puede degradar el rendimiento de manera significativa. Por tanto, es importante contar con una política de control del índice de fragmentación y medidas que deben adoptarse en caso de fragmentación demasiado alto.

En mi caso me estoy quedando un puesto de trabajo los fines de semana de ejecutar el procedimiento dba_IndexDefrag_sp Michelle Ufford. Este procedimiento permite comprobar todos los índices en todas las bases de datos y servidores, de acuerdo con el nivel de fragmentación, reorganizar o regenerar por completo. Las opciones permiten el procedimiento para cambiar los niveles de fragmentación de activación ambas acciones, para especificar una base o una sola tabla para comprobar o establecer el número de procesadores que se utilizan.

5. Última MERGE

Dependiendo de su entorno de producción, puede suceder que un número muy grande de tablas creadas por usuarios que luego dejar a retirarse. Esto puede ocurrir por ejemplo, la importación de los archivos que puede ser interesante para comprobar en el lugar que no sea en el servidor SQL. Queda por ver si estos cuadros se siguen utilizando o no antes de archivarlos. Para ello puedes utilizar el siguiente script te dará las últimas fechas de la lectura y la escritura tablas de una base de datos dada:

  SET ANSI_WARNINGS OFF;
 SET NOCOUNT ON;
 GO

 AS CON agr
 (
 SELECCIONAR
 [Object_id]
 last_user_seek,
 last_user_scan,
 last_user_lookup,
 last_user_update
 DESDE
 sys.dm_db_index_usage_stats
 DÓNDE
 Db_id database_id = ()
 )
 SELECCIONAR
 [Esquema] = OBJECT_SCHEMA_NAME ([object_id]),
 [Table_Or_View] = OBJECT_NAME ([object_id])
 last_read = MAX (last_read)
 last_write = MAX (last_write)
 DESDE
 (
 SELECT [object_id], last_user_seek, de NULL agr
 UNION ALL
 SELECT [object_id], last_user_scan, NULL DE agr
 UNION ALL
 SELECT [object_id], last_user_lookup, NULL DE agr
 UNION ALL
 SELECT [object_id], NULL DE agr last_user_update
 ) AS x ([object_id], last_read, last_write)
 GROUP BY
 OBJECT_SCHEMA_NAME ([object_id]),
 OBJECT_NAME ([object_id])
 ORDER BY 1,2;