Index Defrag Script, v4.0
In my blog post, "Index Defrag Script Updates - Beta Testers Needed", I stated "I'll hopefully have the new version online in just a few days." That was dated January 26th. I had every intention of following through with it, too, but something came up:
My daughter, Chloe Lynn, was born on February 10th. She's a happy, healthy baby girl who consumes all of my free time and already has both her parents wrapped around her adorable little finger. So while I apologize for the delay in posting the latest version, I hope you can understand and forgive me. 
Alrighty, back to SQL stuff! This version of the script has been significantly overhauled from previous versions. Here's a full synopsis of the changes and enhancements:
- There's now a time limit option so you have more control over how long your defrags run. This time limit is checked *before* a defrag is begun, so it's still possible to have a defrag occur after the time limit is exceeded (i.e. a large index).
- I've added a static table for managing the index defrag scans. This way, you can start and stop the defrag process without the need to rescan. This is especially useful for VLDB's or any environment where you're unable to complete the defrags in one operation.
- Just in case you want to perform a rescan, even if there's still indexes left to defrag from your last rescan, there's a parameter to force it.
- There's now an option to sort by page count, range scan count, or fragmentation level. Range scan count is defaulted, as the indexes that have high amounts of range scans will benefit the most from having a defragged index. You can also specify whether you want to sort by ASC or DESC.
- There's now min and max parameters for page counts. This is useful for a) ignoring indexes with less than 1 extent (as recommended by Microsoft) and b) for scheduling index operations by size. For instance, you may want to defrag your small indexes during business hours but leave your big indexes for evening or weekend hours.
- There's now a parameterized option for sorting in TEMPDB. This may reduce execution time and will prevent unnecessary database file size inflation during defrags. NOTE: Make sure you have enough free space in TEMPDB prior to enabling this option.
- I moved the SQL statement output to display before execution so you can see what's currently executing.
- I've added a debug output of the parameters selected. I've added additional validation to the start of the script, so this will help show you if an invalid value was submitted and overwritten.
- I've added new columns to the log table to show what command is being executed and what error, if any, occurred when trying to execute.
- I've added try/catch logic to handle errors during execution; this way, a single error will not prevent the whole script from terminating.
- The script will now force a rebuild for indexes with allow_page_locks = off.
- For those who use partitioning, you can now exclude the right-most populated partition from the defrag operation. This won't be applicable for all partitioning schemes, but for sliding-window scenarios (one of the most common schemes), it'll reduce contention on the partition that's being actively written to.
- I've fixed a bug where tables with LOB indexes may have more than one record returned from sys.dm_db_index_physical_stats.
- For various reasons, I've removed the option to rebuild stats.
Also, if you have a previous version of the script installed, this version will rename those tables, since there have been some changes made to them.
FAQ:
I often receive the same questions about this script, so allow me to answer them here:
"I keep running the script, but my index is still fragmented. Why?"
This is most likely a very small index. Here's what Microsoft has to say:
"In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index. For more information about mixed extents, see Understanding Pages and Extents."
"What database should I create it in?" or "Can I create this in the MASTER database?"
It's up to you where you create it. You could technically create it in the MASTER database, but I recommend creating a utility database for your DBA administrative tasks.
"Can I run this againt a SharePoint database?"
I've never tried personally, but I've been told it runs just fine.
"What are the minimum requirements to run this script?" or "Will this run on SQL Server 2000 instances?"
You need to be on SQL Server 2005 SP2 or higher.
Without further ado, here's the script:
/* Scroll down to the see notes, disclaimers, and licensing information */ DECLARE @indexDefragLog_rename VARCHAR(128) , @indexDefragExclusion_rename VARCHAR(128) , @indexDefragStatus_rename VARCHAR(128); SELECT @indexDefragLog_rename = 'dba_indexDefragLog_obsolete_' + CONVERT(VARCHAR(10), GETDATE(), 112) , @indexDefragExclusion_rename = 'dba_indexDefragExclusion_obsolete_' + CONVERT(VARCHAR(10), GETDATE(), 112) , @indexDefragStatus_rename = 'dba_indexDefragStatus_obsolete_' + CONVERT(VARCHAR(10), GETDATE(), 112); IF Exists(SELECT [OBJECT_ID] FROM sys.tables WHERE [name] = 'dba_indexDefragLog') EXECUTE SP_RENAME dba_indexDefragLog, @indexDefragLog_rename; IF Exists(SELECT [OBJECT_ID] FROM sys.tables WHERE [name] = 'dba_indexDefragExclusion') EXECUTE SP_RENAME dba_indexDefragExclusion, @indexDefragExclusion_rename; IF Exists(SELECT [OBJECT_ID] FROM sys.tables WHERE [name] = 'dba_indexDefragStatus') EXECUTE SP_RENAME dba_indexDefragStatus, @indexDefragStatus_rename; Go CREATE TABLE dbo.dba_indexDefragLog ( indexDefrag_id INT IDENTITY(1,1) Not Null , databaseID INT Not Null , databaseName NVARCHAR(128) Not Null , objectID INT Not Null , objectName NVARCHAR(128) Not Null , indexID INT Not Null , indexName NVARCHAR(128) Not Null , partitionNumber SMALLINT Not Null , fragmentation FLOAT Not Null , page_count INT Not Null , dateTimeStart DATETIME Not Null , dateTimeEnd DATETIME Null , durationSeconds INT Null , sqlStatement VARCHAR(4000) Null , errorMessage VARCHAR(1000) Null CONSTRAINT PK_indexDefragLog_v40 PRIMARY KEY CLUSTERED (indexDefrag_id) ); PRINT 'dba_indexDefragLog Table Created'; CREATE TABLE dbo.dba_indexDefragExclusion ( databaseID INT Not Null , databaseName NVARCHAR(128) Not Null , objectID INT Not Null , objectName NVARCHAR(128) Not Null , indexID INT Not Null , indexName NVARCHAR(128) Not Null , exclusionMask INT Not Null /* 1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday */ CONSTRAINT PK_indexDefragExclusion_v40 PRIMARY KEY CLUSTERED (databaseID, objectID, indexID) ); PRINT 'dba_indexDefragExclusion Table Created'; CREATE TABLE dbo.dba_indexDefragStatus ( databaseID INT , databaseName NVARCHAR(128) , objectID INT , indexID INT , partitionNumber SMALLINT , fragmentation FLOAT , page_count INT , range_scan_count BIGINT , schemaName NVARCHAR(128) Null , objectName NVARCHAR(128) Null , indexName NVARCHAR(128) Null , scanDate DATETIME , defragDate DATETIME Null , printStatus BIT DEFAULT(0) , exclusionMask INT DEFAULT(0) CONSTRAINT PK_indexDefragStatus_v40 PRIMARY KEY CLUSTERED(databaseID, objectID, indexID, partitionNumber) ); PRINT 'dba_indexDefragStatus Table Created'; IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') = 1 BEGIN DROP PROCEDURE dbo.dba_indexDefrag_sp; PRINT 'Procedure dba_indexDefrag_sp dropped'; END; Go CREATE PROCEDURE dbo.dba_indexDefrag_sp /* Declare Parameters */ @minFragmentation FLOAT = 10.0 /* in percent, will not defrag if fragmentation less than specified */ , @rebuildThreshold FLOAT = 30.0 /* in percent, greater than @rebuildThreshold will result in rebuild instead of reorg */ , @executeSQL BIT = 1 /* 1 = execute; 0 = print command only */ , @defragOrderColumn NVARCHAR(20) = 'range_scan_count' /* Valid options are: range_scan_count, fragmentation, page_count */ , @defragSortOrder NVARCHAR(4) = 'DESC' /* Valid options are: ASC, DESC */ , @timeLimit INT = 720 /* defaulted to 12 hours */ /* Optional time limitation; expressed in minutes */ , @DATABASE VARCHAR(128) = Null /* Option to specify a database name; null will return all */ , @tableName VARCHAR(4000) = Null -- databaseName.schema.tableName /* Option to specify a table name; null will return all */ , @forceRescan BIT = 0 /* Whether or not to force a rescan of indexes; 1 = force, 0 = use existing scan, if available */ , @scanMode VARCHAR(10) = N'LIMITED' /* Options are LIMITED, SAMPLED, and DETAILED */ , @minPageCount INT = 8 /* MS recommends > 1 extent (8 pages) */ , @maxPageCount INT = Null /* NULL = no limit */ , @excludeMaxPartition BIT = 0 /* 1 = exclude right-most populated partition; 0 = do not exclude; see notes for caveats */ , @onlineRebuild BIT = 1 /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */ , @sortInTempDB BIT = 1 /* 1 = perform sort operation in TempDB; 0 = perform sort operation in the index's database */ , @maxDopRestriction TINYINT = Null /* Option to restrict the number of processors for the operation; only in Enterprise */ , @printCommands BIT = 0 /* 1 = print commands; 0 = do not print commands */ , @printFragmentation BIT = 0 /* 1 = print fragmentation prior to defrag; 0 = do not print */ , @defragDelay CHAR(8) = '00:00:05' /* time to wait between defrag commands */ , @debugMode BIT = 0 /* display some useful comments to help determine if/where issues occur */ AS /********************************************************************************* Name: dba_indexDefrag_sp Author: Michelle Ufford, http://sqlfool.com Purpose: Defrags one or more indexes for one or more databases Notes: CAUTION: TRANSACTION LOG SIZE SHOULD BE MONITORED CLOSELY WHEN DEFRAGMENTING. DO NOT RUN UNATTENDED ON LARGE DATABASES DURING BUSINESS HOURS. @minFragmentation defaulted to 10%, will not defrag if fragmentation is less than that @rebuildThreshold defaulted to 30% as recommended by Microsoft in BOL; greater than 30% will result in rebuild instead @executeSQL 1 = execute the SQL generated by this proc; 0 = print command only @defragOrderColumn Defines how to prioritize the order of defrags. Only used if @executeSQL = 1. Valid options are: range_scan_count = count of range and table scans on the index; in general, this is what benefits the most from defragmentation fragmentation = amount of fragmentation in the index; the higher the number, the worse it is page_count = number of pages in the index; affects how long it takes to defrag an index @defragSortOrder The sort order of the ORDER BY clause. Valid options are ASC (ascending) or DESC (descending). @timeLimit Optional, limits how much time can be spent performing index defrags; expressed in minutes. NOTE: The time limit is checked BEFORE an index defrag is begun, thus a long index defrag can exceed the time limitation. @database Optional, specify specific database name to defrag; If not specified, all non-system databases will be defragged. @tableName Specify if you only want to defrag indexes for a specific table, format = databaseName.schema.tableName; if not specified, all tables will be defragged. @forceRescan Whether or not to force a rescan of indexes. If set to 0, a rescan will not occur until all indexes have been defragged. This can span multiple executions. 1 = force a rescan 0 = use previous scan, if there are indexes left to defrag @scanMode Specifies which scan mode to use to determine fragmentation levels. Options are: LIMITED - scans the parent level; quickest mode, recommended for most cases. SAMPLED - samples 1% of all data pages; if less than 10k pages, performs a DETAILED scan. DETAILED - scans all data pages. Use great care with this mode, as it can cause performance issues. @minPageCount Specifies how many pages must exist in an index in order to be considered for a defrag. Defaulted to 8 pages, as Microsoft recommends only defragging indexes with more than 1 extent (8 pages). NOTE: The @minPageCount will restrict the indexes that are stored in dba_indexDefragStatus table. @maxPageCount Specifies the maximum number of pages that can exist in an index and still be considered for a defrag. Useful for scheduling small indexes during business hours and large indexes for non-business hours. NOTE: The @maxPageCount will restrict the indexes that are defragged during the current operation; it will not prevent indexes from being stored in the dba_indexDefragStatus table. This way, a single scan can support multiple page count thresholds. @excludeMaxPartition If an index is partitioned, this option specifies whether to exclude the right-most populated partition. Typically, this is the partition that is currently being written to in a sliding-window scenario. Enabling this feature may reduce contention. This may not be applicable in other types of partitioning scenarios. Non-partitioned indexes are unaffected by this option. 1 = exclude right-most populated partition 0 = do not exclude @onlineRebuild 1 = online rebuild; 0 = offline rebuild @sortInTempDB Specifies whether to defrag the index in TEMPDB or in the database the index belongs to. Enabling this option may result in faster defrags and prevent database file size inflation. 1 = perform sort operation in TempDB 0 = perform sort operation in the index's database @maxDopRestriction Option to specify a processor limit for index rebuilds @printCommands 1 = print commands to screen; 0 = do not print commands @printFragmentation 1 = print fragmentation to screen; 0 = do not print fragmentation @defragDelay Time to wait between defrag commands; gives the server a little time to catch up @debugMode 1 = display debug comments; helps with troubleshooting 0 = do not display debug comments Called by: SQL Agent Job or DBA ---------------------------------------------------------------------------- DISCLAIMER: This code and information are provided "AS IS" without warranty of any kind, either expressed or implied, including but not limited to the implied warranties or merchantability and/or fitness for a particular purpose. ---------------------------------------------------------------------------- LICENSE: This index defrag script is free to download and use for personal, educational, and internal corporate purposes, provided that this header is preserved. Redistribution or sale of this index defrag script, in whole or in part, is prohibited without the author's express written consent. ---------------------------------------------------------------------------- Date Initials Version Description ---------------------------------------------------------------------------- 2007-12-18 MFU 1.0 Initial Release 2008-10-17 MFU 1.1 Added @defragDelay, CIX_temp_indexDefragList 2008-11-17 MFU 1.2 Added page_count to log table , added @printFragmentation option 2009-03-17 MFU 2.0 Provided support for centralized execution , consolidated Enterprise & Standard versions , added @debugMode, @maxDopRestriction , modified LOB and partition logic 2009-06-18 MFU 3.0 Fixed bug in LOB logic, added @scanMode option , added support for stat rebuilds (@rebuildStats) , support model and msdb defrag , added columns to the dba_indexDefragLog table , modified logging to show "in progress" defrags , added defrag exclusion list (scheduling) 2009-08-28 MFU 3.1 Fixed read_only bug for database lists 2010-04-20 MFU 4.0 Added time limit option , added static table with rescan logic , added parameters for page count & SORT_IN_TEMPDB , added try/catch logic and additional debug options , added options for defrag prioritization , fixed bug for indexes with allow_page_lock = off , added option to exclude right-most partition , removed @rebuildStats option , refer to http://sqlfool.com for full release notes ********************************************************************************* Example of how to call this script: Exec dbo.dba_indexDefrag_sp @executeSQL = 1 , @printCommands = 1 , @debugMode = 1 , @printFragmentation = 1 , @forceRescan = 1 , @maxDopRestriction = 1 , @minPageCount = 8 , @maxPageCount = Null , @minFragmentation = 1 , @rebuildThreshold = 30 , @defragDelay = '00:00:05' , @defragOrderColumn = 'page_count' , @defragSortOrder = 'DESC' , @excludeMaxPartition = 1 , @timeLimit = Null; *********************************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; SET Quoted_Identifier ON; BEGIN BEGIN Try /* Just a little validation... */ IF @minFragmentation IS Null Or @minFragmentation Not Between 0.00 And 100.0 SET @minFragmentation = 10.0; IF @rebuildThreshold IS Null Or @rebuildThreshold Not Between 0.00 And 100.0 SET @rebuildThreshold = 30.0; IF @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]' SET @defragDelay = '00:00:05'; IF @defragOrderColumn IS Null Or @defragOrderColumn Not In ('range_scan_count', 'fragmentation', 'page_count') SET @defragOrderColumn = 'range_scan_count'; IF @defragSortOrder IS Null Or @defragSortOrder Not In ('ASC', 'DESC') SET @defragSortOrder = 'DESC'; IF @scanMode Not In ('LIMITED', 'SAMPLED', 'DETAILED') SET @scanMode = 'LIMITED'; IF @debugMode IS Null SET @debugMode = 0; IF @forceRescan IS Null SET @forceRescan = 0; IF @sortInTempDB IS Null SET @sortInTempDB = 1; IF @debugMode = 1 RAISERROR('Undusting the cogs and starting up...', 0, 42) WITH NoWait; /* Declare our variables */ DECLARE @objectID INT , @databaseID INT , @databaseName NVARCHAR(128) , @indexID INT , @partitionCount BIGINT , @schemaName NVARCHAR(128) , @objectName NVARCHAR(128) , @indexName NVARCHAR(128) , @partitionNumber SMALLINT , @fragmentation FLOAT , @pageCount INT , @sqlCommand NVARCHAR(4000) , @rebuildCommand NVARCHAR(200) , @dateTimeStart DATETIME , @dateTimeEnd DATETIME , @containsLOB BIT , @editionCheck BIT , @debugMessage NVARCHAR(4000) , @updateSQL NVARCHAR(4000) , @partitionSQL NVARCHAR(4000) , @partitionSQL_Param NVARCHAR(1000) , @LOB_SQL NVARCHAR(4000) , @LOB_SQL_Param NVARCHAR(1000) , @indexDefrag_id INT , @startDateTime DATETIME , @endDateTime DATETIME , @getIndexSQL NVARCHAR(4000) , @getIndexSQL_Param NVARCHAR(4000) , @allowPageLockSQL NVARCHAR(4000) , @allowPageLockSQL_Param NVARCHAR(4000) , @allowPageLocks INT , @excludeMaxPartitionSQL NVARCHAR(4000); /* Initialize our variables */ SELECT @startDateTime = GETDATE() , @endDateTime = DATEADD(MINUTE, @timeLimit, GETDATE()); /* Create our temporary tables */ CREATE TABLE #databaseList ( databaseID INT , databaseName VARCHAR(128) , scanStatus BIT ); CREATE TABLE #processor ( [INDEX] INT , Name VARCHAR(128) , Internal_Value INT , Character_Value INT ); CREATE TABLE #maxPartitionList ( databaseID INT , objectID INT , indexID INT , maxPartition INT ); IF @debugMode = 1 RAISERROR('Beginning validation...', 0, 42) WITH NoWait; /* Make sure we're not exceeding the number of processors we have available */ INSERT INTO #processor EXECUTE XP_MSVER 'ProcessorCount'; IF @maxDopRestriction IS Not Null And @maxDopRestriction > (SELECT Internal_Value FROM #processor) SELECT @maxDopRestriction = Internal_Value FROM #processor; /* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */ IF (SELECT SERVERPROPERTY('EditionID')) In (1804890536, 610778273, -2117995310) SET @editionCheck = 1 -- supports online rebuilds ELSE SET @editionCheck = 0; -- does not support online rebuilds /* Output the parameters we're working with */ IF @debugMode = 1 BEGIN SELECT @debugMessage = 'Your selected parameters are... Defrag indexes with fragmentation greater than ' + CAST(@minFragmentation AS VARCHAR(10)) + '; Rebuild indexes with fragmentation greater than ' + CAST(@rebuildThreshold AS VARCHAR(10)) + '; You' + CASE WHEN @executeSQL = 1 THEN ' DO' ELSE ' DO NOT' END + ' want the commands to be executed automatically; You want to defrag indexes in ' + @defragSortOrder + ' order of the ' + UPPER(@defragOrderColumn) + ' value; You have' + CASE WHEN @timeLimit IS Null THEN ' not specified a time limit;' ELSE ' specified a time limit of ' + CAST(@timeLimit AS VARCHAR(10)) END + ' minutes; ' + CASE WHEN @DATABASE IS Null THEN 'ALL databases' ELSE 'The ' + @DATABASE + ' database' END + ' will be defragged; ' + CASE WHEN @tableName IS Null THEN 'ALL tables' ELSE 'The ' + @tableName + ' table' END + ' will be defragged; We' + CASE WHEN Exists(SELECT TOP 1 * FROM dbo.dba_indexDefragStatus WHERE defragDate IS Null) And @forceRescan <> 1 THEN ' WILL NOT' ELSE ' WILL' END + ' be rescanning indexes; The scan will be performed in ' + @scanMode + ' mode; You want to limit defrags to indexes with' + CASE WHEN @maxPageCount IS Null THEN ' more than ' + CAST(@minPageCount AS VARCHAR(10)) ELSE ' between ' + CAST(@minPageCount AS VARCHAR(10)) + ' and ' + CAST(@maxPageCount AS VARCHAR(10)) END + ' pages; Indexes will be defragged' + CASE WHEN @editionCheck = 0 Or @onlineRebuild = 0 THEN ' OFFLINE;' ELSE ' ONLINE;' END + ' Indexes will be sorted in' + CASE WHEN @sortInTempDB = 0 THEN ' the DATABASE' ELSE ' TEMPDB;' END + ' Defrag operations will utilize ' + CASE WHEN @editionCheck = 0 Or @maxDopRestriction IS Null THEN 'system defaults for processors;' ELSE CAST(@maxDopRestriction AS VARCHAR(2)) + ' processors;' END + ' You' + CASE WHEN @printCommands = 1 THEN ' DO' ELSE ' DO NOT' END + ' want to print the ALTER INDEX commands; You' + CASE WHEN @printFragmentation = 1 THEN ' DO' ELSE ' DO NOT' END + ' want to output fragmentation levels; You want to wait ' + @defragDelay + ' (hh:mm:ss) between defragging indexes; You want to run in' + CASE WHEN @debugMode = 1 THEN ' DEBUG' ELSE ' SILENT' END + ' mode.'; RAISERROR(@debugMessage, 0, 42) WITH NoWait; END; IF @debugMode = 1 RAISERROR('Grabbing a list of our databases...', 0, 42) WITH NoWait; /* Retrieve the list of databases to investigate */ INSERT INTO #databaseList SELECT database_id , name , 0 -- not scanned yet for fragmentation FROM sys.databases WHERE name = IsNull(@DATABASE, name) And [name] Not In ('master', 'tempdb')-- exclude system databases And [STATE] = 0 -- state must be ONLINE And is_read_only = 0; -- cannot be read_only /* Check to see if we have indexes in need of defrag; otherwise, re-scan the database(s) */ IF Not Exists(SELECT TOP 1 * FROM dbo.dba_indexDefragStatus WHERE defragDate IS Null) Or @forceRescan = 1 BEGIN /* Truncate our list of indexes to prepare for a new scan */ TRUNCATE TABLE dbo.dba_indexDefragStatus; IF @debugMode = 1 RAISERROR('Looping through our list of databases and checking for fragmentation...', 0, 42) WITH NoWait; /* Loop through our list of databases */ WHILE (SELECT COUNT(*) FROM #databaseList WHERE scanStatus = 0) > 0 BEGIN SELECT TOP 1 @databaseID = databaseID FROM #databaseList WHERE scanStatus = 0; SELECT @debugMessage = ' working on ' + DB_NAME(@databaseID) + '...'; IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait; /* Determine which indexes to defrag using our user-defined parameters */ INSERT INTO dbo.dba_indexDefragStatus ( databaseID , databaseName , objectID , indexID , partitionNumber , fragmentation , page_count , range_scan_count , scanDate ) SELECT ps.database_id AS 'databaseID' , QUOTENAME(DB_NAME(ps.database_id)) AS 'databaseName' , ps.OBJECT_ID AS 'objectID' , ps.index_id AS 'indexID' , ps.partition_number AS 'partitionNumber' , SUM(ps.avg_fragmentation_in_percent) AS 'fragmentation' , SUM(ps.page_count) AS 'page_count' , os.range_scan_count , GETDATE() AS 'scanDate' FROM sys.dm_db_index_physical_stats(@databaseID, OBJECT_ID(@tableName), Null , Null, @scanMode) AS ps Join sys.dm_db_index_operational_stats(@databaseID, OBJECT_ID(@tableName), Null , Null) AS os ON ps.database_id = os.database_id And ps.OBJECT_ID = os.OBJECT_ID and ps.index_id = os.index_id And ps.partition_number = os.partition_number WHERE avg_fragmentation_in_percent >= @minFragmentation And ps.index_id > 0 -- ignore heaps And ps.page_count > @minPageCount And ps.index_level = 0 -- leaf-level nodes only, supports @scanMode GROUP BY ps.database_id , QUOTENAME(DB_NAME(ps.database_id)) , ps.OBJECT_ID , ps.index_id , ps.partition_number , os.range_scan_count OPTION (MaxDop 2); /* Do we want to exclude right-most populated partition of our partitioned indexes? */ IF @excludeMaxPartition = 1 BEGIN SET @excludeMaxPartitionSQL = ' Select ' + CAST(@databaseID AS VARCHAR(10)) + ' As [databaseID] , [object_id] , index_id , Max(partition_number) As [maxPartition] From ' + DB_NAME(@databaseID) + '.sys.partitions Where partition_number > 1 And [rows] > 0 Group By object_id , index_id;'; INSERT INTO #maxPartitionList EXECUTE SP_EXECUTESQL @excludeMaxPartitionSQL; END; /* Keep track of which databases have already been scanned */ UPDATE #databaseList SET scanStatus = 1 WHERE databaseID = @databaseID; END /* We don't want to defrag the right-most populated partition, so delete any records for partitioned indexes where partition = Max(partition) */ IF @excludeMaxPartition = 1 BEGIN DELETE ids FROM dbo.dba_indexDefragStatus AS ids Join #maxPartitionList AS mpl ON ids.databaseID = mpl.databaseID And ids.objectID = mpl.objectID And ids.indexID = mpl.indexID And ids.partitionNumber = mpl.maxPartition; END; /* Update our exclusion mask for any index that has a restriction on the days it can be defragged */ UPDATE ids SET ids.exclusionMask = ide.exclusionMask FROM dbo.dba_indexDefragStatus AS ids Join dbo.dba_indexDefragExclusion AS ide ON ids.databaseID = ide.databaseID And ids.objectID = ide.objectID And ids.indexID = ide.indexID; END SELECT @debugMessage = 'Looping through our list... there are ' + CAST(COUNT(*) AS VARCHAR(10)) + ' indexes to defrag!' FROM dbo.dba_indexDefragStatus WHERE defragDate IS Null And page_count Between @minPageCount And IsNull(@maxPageCount, page_count); IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait; /* Begin our loop for defragging */ WHILE (SELECT COUNT(*) FROM dbo.dba_indexDefragStatus WHERE ( (@executeSQL = 1 And defragDate IS Null) Or (@executeSQL = 0 And defragDate IS Null And printStatus = 0) ) And exclusionMask & POWER(2, DATEPART(weekday, GETDATE())-1) = 0 And page_count Between @minPageCount And IsNull(@maxPageCount, page_count)) > 0 BEGIN /* Check to see if we need to exit our loop because of our time limit */ IF IsNull(@endDateTime, GETDATE()) < GETDATE() BEGIN RAISERROR('Our time limit has been exceeded!', 11, 42) WITH NoWait; END; IF @debugMode = 1 RAISERROR(' Picking an index to beat into shape...', 0, 42) WITH NoWait; /* Grab the index with the highest priority, based on the values submitted; Look at the exclusion mask to ensure it can be defragged today */ SET @getIndexSQL = N' Select Top 1 @objectID_Out = objectID , @indexID_Out = indexID , @databaseID_Out = databaseID , @databaseName_Out = databaseName , @fragmentation_Out = fragmentation , @partitionNumber_Out = partitionNumber , @pageCount_Out = page_count From dbo.dba_indexDefragStatus Where defragDate Is Null ' + CASE WHEN @executeSQL = 0 THEN 'And printStatus = 0' ELSE '' END + ' And exclusionMask & Power(2, DatePart(weekday, GetDate())-1) = 0 And page_count Between @p_minPageCount and IsNull(@p_maxPageCount, page_count) Order By + ' + @defragOrderColumn + ' ' + @defragSortOrder; SET @getIndexSQL_Param = N'@objectID_Out int OutPut , @indexID_Out int OutPut , @databaseID_Out int OutPut , @databaseName_Out nvarchar(128) OutPut , @fragmentation_Out int OutPut , @partitionNumber_Out int OutPut , @pageCount_Out int OutPut , @p_minPageCount int , @p_maxPageCount int'; EXECUTE SP_EXECUTESQL @getIndexSQL , @getIndexSQL_Param , @p_minPageCount = @minPageCount , @p_maxPageCount = @maxPageCount , @objectID_Out = @objectID OUTPUT , @indexID_Out = @indexID OUTPUT , @databaseID_Out = @databaseID OUTPUT , @databaseName_Out = @databaseName OUTPUT , @fragmentation_Out = @fragmentation OUTPUT , @partitionNumber_Out = @partitionNumber OUTPUT , @pageCount_Out = @pageCount OUTPUT; IF @debugMode = 1 RAISERROR(' Looking up the specifics for our index...', 0, 42) WITH NoWait; /* Look up index information */ SELECT @updateSQL = N'Update ids Set schemaName = QuoteName(s.name) , objectName = QuoteName(o.name) , indexName = QuoteName(i.name) From dbo.dba_indexDefragStatus As ids Inner Join ' + @databaseName + '.sys.objects As o On ids.objectID = o.object_id Inner Join ' + @databaseName + '.sys.indexes As i On o.object_id = i.object_id And ids.indexID = i.index_id Inner Join ' + @databaseName + '.sys.schemas As s On o.schema_id = s.schema_id Where o.object_id = ' + CAST(@objectID AS VARCHAR(10)) + ' And i.index_id = ' + CAST(@indexID AS VARCHAR(10)) + ' And i.type > 0 And ids.databaseID = ' + CAST(@databaseID AS VARCHAR(10)); EXECUTE SP_EXECUTESQL @updateSQL; /* Grab our object names */ SELECT @objectName = objectName , @schemaName = schemaName , @indexName = indexName FROM dbo.dba_indexDefragStatus WHERE objectID = @objectID And indexID = @indexID And databaseID = @databaseID; IF @debugMode = 1 RAISERROR(' Grabbing the partition count...', 0, 42) WITH NoWait; /* Determine if the index is partitioned */ SELECT @partitionSQL = 'Select @partitionCount_OUT = Count(*) From ' + @databaseName + '.sys.partitions Where object_id = ' + CAST(@objectID AS VARCHAR(10)) + ' And index_id = ' + CAST(@indexID AS VARCHAR(10)) + ';' , @partitionSQL_Param = '@partitionCount_OUT int OutPut'; EXECUTE SP_EXECUTESQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OUTPUT; IF @debugMode = 1 RAISERROR(' Seeing if there are any LOBs to be handled...', 0, 42) WITH NoWait; /* Determine if the table contains LOBs */ SELECT @LOB_SQL = ' Select @containsLOB_OUT = Count(*) From ' + @databaseName + '.sys.columns With (NoLock) Where [object_id] = ' + CAST(@objectID AS VARCHAR(10)) + ' And (system_type_id In (34, 35, 99) Or max_length = -1);' /* system_type_id --> 34 = image, 35 = text, 99 = ntext max_length = -1 --> varbinary(max), varchar(max), nvarchar(max), xml */ , @LOB_SQL_Param = '@containsLOB_OUT int OutPut'; EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OUTPUT; IF @debugMode = 1 RAISERROR(' Checking for indexes that do not allow page locks...', 0, 42) WITH NoWait; /* Determine if page locks are allowed; for those indexes, we need to always rebuild */ SELECT @allowPageLockSQL = 'Select @allowPageLocks_OUT = Count(*) From ' + @databaseName + '.sys.indexes Where object_id = ' + CAST(@objectID AS VARCHAR(10)) + ' And index_id = ' + CAST(@indexID AS VARCHAR(10)) + ' And Allow_Page_Locks = 0;' , @allowPageLockSQL_Param = '@allowPageLocks_OUT int OutPut'; EXECUTE SP_EXECUTESQL @allowPageLockSQL, @allowPageLockSQL_Param, @allowPageLocks_OUT = @allowPageLocks OUTPUT; IF @debugMode = 1 RAISERROR(' Building our SQL statements...', 0, 42) WITH NoWait; /* If there's not a lot of fragmentation, or if we have a LOB, we should reorganize */ IF (@fragmentation < @rebuildThreshold Or @containsLOB >= 1 Or @partitionCount > 1) And @allowPageLocks = 0 BEGIN SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + N' ReOrganize'; /* If our index is partitioned, we should always reorganize */ IF @partitionCount > 1 SET @sqlCommand = @sqlCommand + N' Partition = ' + CAST(@partitionNumber AS NVARCHAR(10)); END /* If the index is heavily fragmented and doesn't contain any partitions or LOB's, or if the index does not allow page locks, rebuild it */ ELSE IF (@fragmentation >= @rebuildThreshold Or @allowPageLocks <> 0) And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1 BEGIN /* Set online rebuild options; requires Enterprise Edition */ IF @onlineRebuild = 1 And @editionCheck = 1 SET @rebuildCommand = N' Rebuild With (Online = On'; ELSE SET @rebuildCommand = N' Rebuild With (Online = Off'; /* Set sort operation preferences */ IF @sortInTempDB = 1 SET @rebuildCommand = @rebuildCommand + N', Sort_In_TempDB = On'; ELSE SET @rebuildCommand = @rebuildCommand + N', Sort_In_TempDB = Off'; /* Set processor restriction options; requires Enterprise Edition */ IF @maxDopRestriction IS Not Null And @editionCheck = 1 SET @rebuildCommand = @rebuildCommand + N', MaxDop = ' + CAST(@maxDopRestriction AS VARCHAR(2)) + N')'; ELSE SET @rebuildCommand = @rebuildCommand + N')'; SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + @rebuildCommand; END ELSE /* Print an error message if any indexes happen to not meet the criteria above */ IF @printCommands = 1 Or @debugMode = 1 RAISERROR('We are unable to defrag this index.', 0, 42) WITH NoWait; /* Are we executing the SQL? If so, do it */ IF @executeSQL = 1 BEGIN SET @debugMessage = 'Executing: ' + @sqlCommand; /* Print the commands we're executing if specified to do so */ IF @printCommands = 1 Or @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait; /* Grab the time for logging purposes */ SET @dateTimeStart = GETDATE(); /* Log our actions */ INSERT INTO dbo.dba_indexDefragLog ( databaseID , databaseName , objectID , objectName , indexID , indexName , partitionNumber , fragmentation , page_count , dateTimeStart , sqlStatement ) SELECT @databaseID , @databaseName , @objectID , @objectName , @indexID , @indexName , @partitionNumber , @fragmentation , @pageCount , @dateTimeStart , @sqlCommand; SET @indexDefrag_id = SCOPE_IDENTITY(); /* Wrap our execution attempt in a try/catch and log any errors that occur */ BEGIN Try /* Execute our defrag! */ EXECUTE SP_EXECUTESQL @sqlCommand; SET @dateTimeEnd = GETDATE(); /* Update our log with our completion time */ UPDATE dbo.dba_indexDefragLog SET dateTimeEnd = @dateTimeEnd , durationSeconds = DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd) WHERE indexDefrag_id = @indexDefrag_id; END Try BEGIN Catch /* Update our log with our error message */ UPDATE dbo.dba_indexDefragLog SET dateTimeEnd = GETDATE() , durationSeconds = -1 , errorMessage = Error_Message() WHERE indexDefrag_id = @indexDefrag_id; IF @debugMode = 1 RAISERROR(' An error has occurred executing this command! Please review the dba_indexDefragLog table for details.' , 0, 42) WITH NoWait; END Catch /* Just a little breather for the server */ WAITFOR Delay @defragDelay; UPDATE dbo.dba_indexDefragStatus SET defragDate = GETDATE() , printStatus = 1 WHERE databaseID = @databaseID And objectID = @objectID And indexID = @indexID And partitionNumber = @partitionNumber; END ELSE /* Looks like we're not executing, just printing the commands */ BEGIN IF @debugMode = 1 RAISERROR(' Printing SQL statements...', 0, 42) WITH NoWait; IF @printCommands = 1 Or @debugMode = 1 PRINT IsNull(@sqlCommand, 'error!'); UPDATE dbo.dba_indexDefragStatus SET printStatus = 1 WHERE databaseID = @databaseID And objectID = @objectID And indexID = @indexID And partitionNumber = @partitionNumber; END END /* Do we want to output our fragmentation results? */ IF @printFragmentation = 1 BEGIN IF @debugMode = 1 RAISERROR(' Displaying a summary of our action...', 0, 42) WITH NoWait; SELECT databaseID , databaseName , objectID , objectName , indexID , indexName , partitionNumber , fragmentation , page_count , range_scan_count FROM dbo.dba_indexDefragStatus WHERE defragDate >= @startDateTime ORDER BY defragDate; END; END Try BEGIN Catch SET @debugMessage = Error_Message() + ' (Line Number: ' + CAST(Error_Line() AS VARCHAR(10)) + ')'; PRINT @debugMessage; END Catch; /* When everything is said and done, make sure to get rid of our temp table */ DROP TABLE #databaseList; DROP TABLE #processor; DROP TABLE #maxPartitionList; IF @debugMode = 1 RAISERROR('DONE! Thank you for taking care of your indexes! :)', 0, 42) WITH NoWait; SET NOCOUNT OFF; RETURN 0 END
You can also download it here: dba_indexDefrag_sp_v40_public.txt
I've had this latest version in production on terabyte-size databases running SQL Server 2005 and 2008 Enterprise editions for the last 3 months, where it runs nightly without issue. I've also had numerous beta testers report success in their environments, too. But to be safe, make sure to keep an eye on it the first time it runs to ensure you understand the impact on your server.
Enjoy!
Michelle
Poor (Wo)Man’s Graph
Lary shared this poor (wo)man's graph with me today, and I thought it was pretty awesome:
SELECT OrderDate , COUNT(*) AS 'orders' , REPLICATE('=', COUNT(*)) AS 'orderGraph' , SUM(TotalDue) AS 'revenue' , REPLICATE('$', SUM(TotalDue)/1000) AS 'revenueGraph' FROM AdventureWorks.Sales.SalesOrderHeader WHERE OrderDate Between '2003-07-15' And '2003-07-31' GROUP BY OrderDate ORDER BY OrderDate;
This will return a simple but effective "graph" for you:
orderDate orders orderGraph revenue revenueGraph ---------- ------ ------------------------------ -------- ---------------------------------------- 2003-07-15 19 =================== 34025.24 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-16 14 ============== 26687.65 $$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-17 16 ================ 32411.93 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-18 9 ========= 18634.91 $$$$$$$$$$$$$$$$$$$ 2003-07-19 13 ============= 19603.23 $$$$$$$$$$$$$$$$$$$$ 2003-07-20 24 ======================== 47522.80 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-21 9 ========= 11781.62 $$$$$$$$$$$$ 2003-07-22 17 ================= 32322.50 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-23 15 =============== 30906.44 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-24 28 ============================ 51107.90 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-25 15 =============== 27058.10 $$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-26 18 ================== 41076.49 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-27 15 =============== 22169.88 $$$$$$$$$$$$$$$$$$$$$$ 2003-07-28 16 ================ 23945.80 $$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-29 25 ========================= 51122.95 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-30 12 ============ 23476.44 $$$$$$$$$$$$$$$$$$$$$$$ 2003-07-31 18 ================== 36266.76 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Who needs Reporting Services when you've got REPLICATE? 
Generate Columns for Update Statements
I'm not a fan of most CRUD generators. The formatting doesn't match my style, and I usually spend about as much time modifying the generated code as I would spend just writing it from scratch. But there's been times when I've considered using CRUD generators, mainly when I'm writing updates on wide tables. If you've never written an update for a table with many columns, it's not sexy. You're wasting valuable time on a tedious task that you could instead spend reading SQL Server 2008 Internals or chewing the cud with the SQL Twitterati.
Fortunately, Dave Carlile shared another tip with me that helps with this and has made it's way into my little bag of tricks.
Let's assume you having the following outline:
UPDATE sales SET ['insert really long column list'] FROM Sales.vStoreWithDemographics AS sales Join myTempTable AS mtt ON sales.someColumn = mtt.someColumn;
You could use the following code to generate a list of columns for you:
SELECT name + ' = sales.' + name + ',' FROM sys.columns WHERE OBJECT_ID = OBJECT_ID('Sales.vStoreWithDemographics') ORDER BY column_id;
Just replace [Sales.vStoreWithDemographics] with a table of your choice, and replace "sales." with the appropriate alias.This will return a list of nicely formatted columns for you. Best of all, no potential for column typos! Just don't forget to remove the very last comma, otherwise you'll get a syntax error.
CustomerID = sales.CustomerID, Name = sales.Name, ContactType = sales.ContactType, (etc.)
I know, nothing earth shattering, but definitely one of those "huh, why didn't I think of that?" moments. So, thanks, Dave!
Source: http://sqlfool.com/2009/03/generate-columns-for-update-statements
Registered Servers in SSMS
In my last blog post, I discussed changing the color of the status bar in SSMS 2008. I received a couple of comments and even an e-mail discussing how this doesn't seem to always work. After playing with it for a little bit, I've found that the status bar color needs to be set in both Query->Connection->Connect/Change Connection... (here-in referred to as simply the Query menu) and Registered Servers.
Let's run through this. First, connect to an instance with any color using the Query menu.
Now, create a new registered server. Make sure to use the same server.
New Server Registration
Pick a color, but make sure that it's different than the previous color. This is just for demonstration purposes only. Since the whole point is to have a consistent color, you would normally use the same color in both connection methods for the same server.
Pick a color
Open a new query window via Registered Servers.
New Query Window
Registered Server Query Window
Here's what happens when I connect to the same server using both Registered Servers (left) and another window using the Query menu (right).
SSMS - Same Server, Different Colors
For anyone who's using both the Query menu and Registered Servers to connect to servers, then you should walk through the process of connecting to each server via both means and changing the colors to ensure consistency. I did this for 22 servers and it took me less than 10 minutes.
I hope that helps clear up some of the confusion.
Source: http://sqlfool.com/2009/03/registered-servers-in-ssms/
SSMS Server Settings
I think this has been discussed before on better blogs than mine, but it's just so darn cool that I want to help spread the word.
In SSMS 2008, you can change the color of the status bar for servers. This gives you a nice visual reminder as to which server you're currently connecting to. Since I've made the DEV/PROD mistake before, this is something I'm a big fan of.
So let's walk through how you can set this up:
Opening a new connection
Click on Options >>
Connection Properties
Select Use custom color and click on Select...
Pick a color
Choose the color you prefer, then click on OK
Open a new query
Click on Connect.
That's all there is to it. Pretty easy, huh? Now let's see what happens when we connect to multiple servers...
Multi-Server Rainbow
Beautiful! SSMS seems to remember the color settings too, so you should only have to set this up once.
Easy Way To Return Top Records
Okay, so that title may suck. I accept that. It's late and I can't think of anything better at the moment.
Bad blog title aside, let's take a pretty common data request. You need to return the top sales performer in each department. If you've ever had this type of request, then you know there's a few different ways of handling this, and it can be a little complicated. Today, Dave Carlile shared with me a new and pretty simple way of handling this with Row_Number().
The syntax for Row_Number is a little different than what you may be used to: ROW_NUMBER ( ) OVER ( [ PARTITION BY yourColumn ] ORDER BY yourColumn )
PARTITION BY is what you want to group by. This is optional.
ORDER BY is how you want to order your data before assigning a row number. This is required.
Let's take a look at an example.
/* Create a table to play with */ CREATE TABLE dbo.sales ( order_id INT IDENTITY(1,1) , salesPerson VARCHAR(20) , department VARCHAR(20) , total MONEY CONSTRAINT PK_sales PRIMARY KEY CLUSTERED(order_id) ); /* Load it up with some bogus records */ INSERT INTO dbo.sales SELECT 'Amanda', 'Sales', 420 UNION All SELECT 'Barry', 'Sales', 360 UNION All SELECT 'Chris', 'Marketing', 398 UNION All SELECT 'David', 'Sales', 371 UNION All SELECT 'Ethan', 'Customer Support', 123 UNION All SELECT 'Faith', 'Sales', 206 UNION All SELECT 'Gavin', 'Marketing', 396 UNION All SELECT 'Heather', 'Marketing', 51 UNION All SELECT 'Iris', 'Customer Support', 79 UNION All SELECT 'Jamie', 'Customer Support', 242; /* Examine what values are returned for each record */ SELECT ROW_NUMBER() OVER(Partition BY department ORDER BY total DESC) AS 'salesRank' , salesPerson , department , total FROM dbo.sales; /* Let's grab just the top sales performer in each department */ WITH myCTE AS ( SELECT ROW_NUMBER() OVER(Partition BY department ORDER BY total DESC) AS 'salesRank' , salesPerson , department , total FROM dbo.sales ) SELECT salesPerson , department , total FROM myCTE WHERE salesRank = 1 ORDER BY total DESC;
Let's take a look at the options we've specified for Row_Number(). Since we want to know who has the top sales, we're going to order by [total] in descending order. We also want to assign each department its own rank, so we're going to group (partition) by the [department] column. If we did not include the "Partition By" clause, then we'd get only 1 record returned, which would be the top overall sales person (in this case, Amanda).
Now let's do the same thing, but this time we want to return the top 2 sales person in each department.
/* Now grab the top TWO sales performer in each department */ WITH myCTE AS ( SELECT ROW_NUMBER() OVER(Partition BY department ORDER BY total DESC) AS 'salesRank' , salesPerson , department , total FROM dbo.sales ) SELECT salesPerson , department , total FROM myCTE WHERE salesRank <= 2 -- this is the only difference ORDER BY department , salesRank; /* Clean-Up! */ DROP TABLE dbo.sales;
That's all there is to it! Pretty cool, huh? I haven't gotten around to performance testing on large data sets yet, but I definitely like the simplicity of the approach.
Thanks, Dave!
Update: Aaron The Hobt has already done some performance testing on this very subject. The results? Not as good as I was hoping. 
As an aside, I'm going to be participating in the Pain of the Week webcast tomorrow at 11 AM ET. This free webcast will be on index fragmentation: what is it, how to find it, and how to fix it. If you're interested, you can register here: http://www.quest.com/events/ListDetails.aspx?ContentID=8857.
This will be only my second time speaking to an audience (the first time was yesterday at our first PASS Chapter meeting!). So if nothing else, it may be good for a few laughs.
Source: http://sqlfool.com/2009/03/easy-way-to-return-top-records/
SQL Tweaks and Tools That Make My Life Easier
It still surprises me how many people don't know about some of the very things that make my job so much easier. So this next post is dedicated to sharing some of the tweaks and tools I've run across that will help anyone who works with SQL:
Indexes
Anyone who uses included columns is probably well aware of the frustrations that can come from having to look up information on which columns are included. I wrote a stored procedure, dba_indexLookup_sp, to help me with this, before discovering sp_helpindex2. If you haven't heard of sp_helpindex2, it's a re-write of sp_helpindex by Kimberly Tripp. You can find it on Kimberly's blog. The main difference is Kimberly's is a system stored procedure (mine is not) and my version returns partitioning information (Kimberly's does not). Check both out and use whichever one meets your needs best.
KeyBoard ShortCuts
In SQL Server Management Studio (SSMS), click on:
Tools --> Options... --> Environment --> Keyboard
Keyboard Shortcuts
For your copying convenience:
Ctrl+3 Select Top 100 * From
Ctrl+4 sp_tables @table_owner = 'dbo'
Ctrl+5 sp_columns
Ctrl+6 sp_stored_procedures @sp_owner = 'dbo'
Ctrl+7 sp_spaceused
Ctrl+8 sp_helptext
Ctrl+9 dba_indexLookup_sp or sp_helpindex2
Please note that these settings will not take effect until you open a new query window. Here's an example of how you could use this: use Ctrl+4 to find a list of tables, then copy one into your query window; to view a sample of that table's data, highlight the table name (I usually double-click on it) and press Ctrl+3. It's a thing of beauty. Oh, and you may want to remove/change the schema filters if you use schemas other than dbo.
Query Execution Settings
After having one too many issues arise from non-DBA's connecting to the production environment to run a devastating ad hoc, I've had all of our developers and analysts adopt the following settings. The only thing difference between my setting and theirs is that I have "Set Statistics IO" selected. FYI - you can also make these same setting changes in Visual Studio.
In SQL Server Management Studio (SSMS), click on:
Tools --> Options... --> Query Execution --> SQL Server --> Advanced
Query Execution Settings
Copy Behavior
This next tip actually has nothing to do with SQL Server, and can be done with any Microsoft product. However, I just learned about it a few weeks ago and already I use it quite frequently.
Holding down "Alt" while you drag your mouse will change your selection behavior to block selection.
Block Selection
Please note: The following tools requires SQL 2008 Management Studio. These tools will also work when you connect SQL 2008 SSMS to a 2005 instance.
Object Detail Explorer
Finally, there's a reason to use the Object Detail Explorer! My favorite use is to quickly find the table size and row counts of all the tables in a database. If these options are not currently available, you may just need to right click on the column headers and add it to the display.
Object Detail Explorer
Missing Indexes
And lastly, when using SSMS 2008 to execute Display Estimated Query Plan (Ctrl+L), it will show you if you're missing any indexes. This will even work if you connect SSMS 2008 to SQL 2005!
Missing Index
That pretty much covers it for now. HTH!
Michelle
Categories
- Business Intelligence
- Internals
- Miscellaneous
- PASS
- Performance & Tuning
- Presentations
- SQL 2008
- SQL Tips
- Syndication
- T-SQL Scripts
Subscribe to my blog!
| Like what you see? Subscribe! |
 |
Around the Web
Recent Tweets
- @zippy1981 I'm actually using @RedGate SQL Compare right now. It's worth every penny. #sqlhelp #redgate
- +1 :) RT @onpnt: Very well said, Janice :) @JaniceCLee your blog if full of WIN http://bit.ly/aZ4wPR
- @SQLDBA You're flying out of Orlando so there's def the possibility of a better deal. But I wouldn't do it unless you're a morning person :)