5 common administrative tasks of SQL Server 2005

In this article I will present some scripts or stored procedures for SQL Server 2005 that I use regularly as part of the administration of my servers. These scripts allow bulk monitor the use of a server, changing the size of the bases, the use of indexes and their automated rebuilding or check the date of last use of a table.

1. Monitoring server activity

Monitoring of SQL Servers is arguably one of the most important responsibilities of administrators to databases. For this I use the procedure sp_WhoIsActive developed by Adam Machanic. This procedure will display the pending requests on the server and all associated information as the user running the application, the CPU, I / O or the applications themselves.

sp_WhoIsActive: Total consumption

A parameter in the procedure to run the procedure twice in a given interval (eg 5s) to measure the activity (almost) instant of different applications, which can easily determine which consumes the most CPU, use tempdb or makes disk accesses.

sp_WhoIsActive: Consumption 5s

The procedure also displays information about the types of queries Wait and causing Blocks.

sp_WhoIsActive: Waits and queries

A click on each link will display the XML file associated with the request, convenient to consider an application obviously too resource-intensive.

A more rapid but less complete and that I use regularly is available here.

2. Monitoring the use of disk space

I use the procedure sp_SDS (free registration required) written by Richard Ding to generate a report on the size of databases and logs an instance. The space is also calculated for both types of files and the total body.

sp_SDS

An alternative to this stored procedure to be easier to copy / paste into an Excel spreadsheet is as follows:

  SELECT @ @ SERVERNAME AS Instance
 db.name AS 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
 FROM master .. sysdatabases AS db
 INNER JOIN master .. sysaltfiles AS ON af af. [Dbid] = db. [Dbid]
 Db.name WHERE NOT IN ( 'distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb')
 GROUP BY db.name 

If you want to monitor the size of your databases, it will probably be necessary to create a specific table in a database administration. This table will contain all data that you deem interesting to watch. For this I use the script DBINFO written by Tim Ford. It can record in a table the size of files (databases and logs), free space, the file name or the method of restoration.

DBINFO

Simply create an SQL job running this script at regular intervals to store the data in the table DBINFO, which will allow you to create reports or to predict the evolution of the size of bases, for example using SQL Server Analysis Services.

3. Analysis of performance index

The management index is another important task for the administration of SQL servers. To analyze the relevance and use of these indexes on the tables of a database, I use a script developed by Jason Strate. This script displays all the indexes in a database (including heaps, that is to say, the tables themselves) and a myriad of information to judge their usefulness as the number of Scans and Seeks, indexed columns, the index size or the impact (positive) index on users. The script also displays the missing indexes suggested by SQL Server.

Analysis Index

If you want to view Seeks, Lookups and scans quickly, you can use the following script:

  SELECT OBJECT_NAME (S. [OBJECT_ID]) AS [OBJECT NAME]
 I. [NAME] AS [NAME INDEX]
 USER_SEEKS,
 USER_SCANS,
 USER_LOOKUPS,
 USER_UPDATES
 AS S FROM sys.dm_db_index_usage_stats
 INNER JOIN AS I sys.indexes
 ON I. [OBJECT_ID] = S. [OBJECT_ID]
 AND I. S. index_id = index_id
 WHERE OBJECTPROPERTY (S. [OBJECT_ID], 'IsUserTable') = 1 

To study the impact of requests for Insert, Update and Delete on the index, you can use this script:

  SELECT OBJECT_NAME (A. [OBJECT_ID]) AS [OBJECT NAME]
 I. [NAME] AS [NAME INDEX]
 A. LEAF_INSERT_COUNT,
 A. LEAF_UPDATE_COUNT,
 A. LEAF_DELETE_COUNT
 FROM sys.dm_db_index_operational_stats (NULL, NULL, NULL, NULL) A
 INNER JOIN AS I sys.indexes
 ON I. [OBJECT_ID] = A. [OBJECT_ID]
 AND I. A. index_id = index_id
 WHERE OBJECTPROPERTY (A. [OBJECT_ID], 'IsUserTable') = 1 

To list unused indexes and determine whether or not they can be removed, you can use the following script:

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

 SELECT @ dbid = DB_ID ()
 , @ DbName = DB_NAME ();

 WITH partitionCTE (OBJECT_ID, index_id, row_count, partition_count)
 AS
 (
 SELECT [OBJECT_ID]
 , Index_id
 , SUM ([rows]) AS 'row_count'
 , COUNT (partition_id) AS 'partition_count'
 FROM sys.partitions
 GROUP BY [OBJECT_ID]
 , Index_id
 )

 SELECT OBJECT_NAME (I. [OBJECT_ID]) AS objectName
 , I.name
 , CASE
 WHEN i.is_unique = 1
 THEN 'UNIQUE'
 ELSE''
 + I.type_desc END AS 'INDEXTYPE'
 , Ddius.user_seeks
 , Ddius.user_scans
 , Ddius.user_lookups
 , Ddius.user_updates
 , Cte.row_count
 , CASE WHEN partition_count> 1 THEN 'yes'
 ELSE 'No' END AS 'partitioned? "
 , CASE
 WHEN i.type And i.is_unique_constraint = 2 = 0
 THEN 'DROP INDEX' + i.name
 + 'On' + @ dbName
 +. "Dbo."  + OBJECT_NAME (ddius. [OBJECT_ID]) + ";"
 WHEN i.type And i.is_unique_constraint = 2 = 1
 THEN 'ALTER TABLE' + @ dbName
 +. "Dbo."  + OBJECT_NAME (ddius. [OBJECT_ID])
 + 'DROP CONSTRAINT' + i.name + ';'
 ELSE''
 END AS 'SQL_DropStatement'
 AS i FROM sys.indexes
 INNER Join sys.dm_db_index_usage_stats ddius
 ON i.object_id = ddius.OBJECT_ID
 And i.index_id = ddius.index_id
 INNER JOIN cte AS partitionCTE
 ON i.object_id = cte.OBJECT_ID
 And i.index_id = cte.index_id
 Ddius.database_id WHERE dbid = @
 ORDER BY
 (ddius.user_seeks + ddius.user_scans + ddius.user_lookups) AUC
 , User_updates DESC; 

Finally, to list the missing indexes suggested by SQL Server, you can use this script:

  SELECT t.name AS 'Table'
 'Create nonclustered index ix_' + t.name + '_missing_'
 + CAST (AS VARCHAR ddmid.index_handle (10))
 + 'On' + ddmid.STATEMENT
 + '(' + IsNull (ddmid.equality_columns,'')
 + CASE WHEN IS ddmid.equality_columns Not Null
 And ddmid.inequality_columns IS NOT NULL THEN ','
 ELSE''END
 + IsNull (ddmid.inequality_columns,'')
 + ')'
 + IsNull ( 'Include (' 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'
 , Ddmigs.last_user_seek
 FROM AS sys.dm_db_missing_index_groups ddmig
 INNER Join sys.dm_db_missing_index_group_stats AS ddmigs
 ON ddmigs.group_handle = ddmig.index_group_handle
 INNER JOIN AS sys.dm_db_missing_index_details ddmid
 ON ddmig.index_handle = ddmid.index_handle
 INNER Join sys.tables AS t
 ON ddmid.OBJECT_ID = t.OBJECT_ID
 WHERE ddmid.database_id DB_ID = ()
 And 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. Defragmenting indexes

Gradually, as more data are added, updated or deleted, the indexes are fragmented and can degrade performance significantly. It is therefore important to have a policy of monitoring the fragmentation index and action in case of too much fragmentation.

In my case I'm running a weekend job that executes the procedure dba_IndexDefrag_sp Michelle Ufford. This procedure allows you to check all indexes of all databases and servers, depending on the level of fragmentation, to reorganize or to regenerate completely. The options allow the procedure to change the fragmentation levels triggering the two actions, specify a base or a single table to verify or identify the number of processors to use.

5. Last Used tables

Depending on your production environment, it may happen that a very large number of tables is created by users who then left abandoned. This can happen for example for imports of files that can then be interesting to check in elsewhere on the SQL server. Remains whether these tables are still used or not before archiving. For this you can use the following script which will give you the last dates of reading and writing tables contained in a given database:

  SET OFF ANSI_WARNINGS;
 SET NOCOUNT ON;
 GO

 WITH AS agg
 (
 SELECT
 [object_id]
 last_user_seek,
 last_user_scan,
 last_user_lookup,
 last_user_update
 FROM
 sys.dm_db_index_usage_stats
 WHERE
 database_id DB_ID = ()
 )
 SELECT
 [Schema] = 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 FROM agg
 UNION ALL
 SELECT [object_id], last_user_scan, NULL FROM agg
 UNION ALL
 SELECT [object_id], last_user_lookup, NULL FROM agg
 UNION ALL
 SELECT [object_id], NULL FROM agg 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;