Five 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 of tracking the use of a server, changing the database size, use indexes and rebuild them automatically 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 Machanics. This procedure allows you to view pending requests to the server and all associated information such 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 cycle twice in a given interval (eg 5s) to measure the activity (almost) instant of different queries, 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 faster alternative, 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 database size and log files of 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 in an Excel spreadsheet is:

  SELECT @ @ SERVERNAME AS Instance
 db.name AS Base
 SUM (CASE WHEN 0 THEN af.groupid = 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 db AS
 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 recovery mode.

DBINFO

Simply create an SQL job running this script on a regular basis to store this 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 of 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 of a database (including heaps, that is to say, the tables themselves) and a myriad of information necessary to evaluate 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 display Seeks, Scans and Lookups 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 = 2 = 0 And i.is_unique_constraint
 THEN 'Drop Index' + i.name
 + 'On' + @ dbName
 + '. Dbo. "  + OBJECT_NAME (ddius. [object_id]) + ';'
 WHEN i.type = 2 = 1 And i.is_unique_constraint
 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 partitionCTE AS cte
 ON = i.OBJECT_ID cte.OBJECT_ID
 And i.index_id = cte.index_id
 WHERE dbid = @ ddius.database_id
 BY ORDER
 (Ddius.user_seeks ddius.user_scans + + ddius.user_lookups) ASC
 , User_updates DESC; 

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

  SELECT t.name AS 'Table'
 'Create Index NONCLUSTERED 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
 AS FROM 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 sys.dm_db_missing_index_details AS ddmid
 ON = ddmig.index_handle ddmid.index_handle
 AS t INNER Join sys.tables
 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

As and when data is 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 actions to be taken in case of fragmentation too high.

In my case I'm running a job on weekends running the procedure dba_IndexDefrag_sp Michelle Ufford. This procedure allows to check all the indexes in all databases and servers, according to the level of fragmentation, to reorganize or to regenerate completely. Options allow the procedure to change the fragmentation levels trigger both actions, to specify a base or a single table to check or set the number of processors to use.

5. Last MERGE

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

  ANSI_WARNINGS SET OFF;
 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;