Check VLF Counts

Today I stumbled across a database with 87,302 VLF’s. Yes, that’s right… 87 THOUSAND. Most of our databases have a few dozen VLF’s, but this was an old database that had grown to 1.5 TB and had the default autogrowth settings left in tact. How did we discover this? During a routine reboot of the server, this database took 30 minutes to recover, but there were no error messages or status messages in the log.

Now, this blog post is not about VLF’s or why you should keep the number of VLF’s to a small, manageable number — although I hear under 50 is a good rule of thumb. No, the purpose of this blog post is to share a little script I wrote to check the number of VLF’s each database uses:

Create Table #stage(
    FileID      int
  , FileSize    bigint
  , StartOffset bigint
  , FSeqNo      bigint
  , [Status]    bigint
  , Parity      bigint
  , CreateLSN   numeric(38)
);
 
Create Table #results(
    Database_Name   sysname
  , VLF_count       int 
);
 
Exec sp_msforeachdb N'Use ?; 
            Insert Into #stage 
            Exec sp_executeSQL N''DBCC LogInfo(?)''; 
 
            Insert Into #results 
            Select DB_Name(), Count(*) 
            From #stage; 
 
            Truncate Table #stage;'
 
Select * 
From #results
Order By VLF_count Desc;
 
Drop Table #stage;
Drop Table #results;

This script is low-impact and is safe to run on large, production databases during business hours. However, just be aware that it’s using some undocumented commands.

For more information on VLF’s, check out these excellent articles:

Index Interrogation for SQL Server 2008

I had previously posted an index interrogation script for SQL Server 2005. I’ve updated that script for 2008; namely, it includes filtered index definitions. For anyone interested:

Declare @objectID int = Object_ID('Sales.SalesOrderHeader');
 
With indexCTE(partition_scheme_name
            , partition_function_name
            , data_space_id)
As (
    Select sps.name
        , spf.name
        , sps.data_space_id
    From sys.partition_schemes As sps
    Join sys.partition_functions As spf
        On sps.function_id = spf.function_id
)
 
Select st.name As 'table_name'
    , IsNull(ix.name, '') As 'index_name'
    , ix.object_id
    , ix.index_id
	, Cast(
        Case When ix.index_id = 1 
                Then 'clustered' 
            When ix.index_id =0
                Then 'heap'
            Else 'nonclustered' End
		+ Case When ix.ignore_dup_key <> 0 
            Then ', ignore duplicate keys' 
                Else '' End
		+ Case When ix.is_unique <> 0 
            Then ', unique' 
                Else '' End
		+ Case When ix.is_primary_key <> 0 
            Then ', primary key' Else '' End As varchar(210)
        ) As 'index_description'
    , IsNull(Replace( Replace( Replace(
        (   
            Select c.name As 'columnName'
            From sys.index_columns As sic
            Join sys.columns As c 
                On c.column_id = sic.column_id 
                And c.object_id = sic.object_id
            Where sic.object_id = ix.object_id
                And sic.index_id = ix.index_id
                And is_included_column = 0
            Order By sic.index_column_id
            For XML Raw)
            , '"/><row columnName="', ', ')
            , '<row columnName="', '')
            , '"/>', ''), '')
        As 'indexed_columns'
    , IsNull(Replace( Replace( Replace(
        (   
            Select c.name As 'columnName'
            From sys.index_columns As sic
            Join sys.columns As c 
                On c.column_id = sic.column_id 
                And c.object_id = sic.object_id
            Where sic.object_id = ix.object_id
                And sic.index_id = ix.index_id
                And is_included_column = 1
            Order By sic.index_column_id
            For XML Raw)
            , '"/><row columnName="', ', ')
            , '<row columnName="', '')
            , '"/>', ''), '')
        As 'included_columns'
    , ix.filter_definition
    , IsNull(cte.partition_scheme_name, '') As 'partition_scheme_name'
    , Count(partition_number) As 'partition_count'
    , Sum(rows) As 'row_count'
From sys.indexes As ix
Join sys.partitions As sp
    On ix.object_id = sp.object_id
    And ix.index_id = sp.index_id
Join sys.tables As st
    On ix.object_id = st.object_id
Left Join indexCTE As cte
    On ix.data_space_id = cte.data_space_id
Where ix.object_id = IsNull(@objectID, ix.object_id)
Group By st.name
    , IsNull(ix.name, '')
    , ix.object_id
    , ix.index_id
	, Cast(
        Case When ix.index_id = 1 
                Then 'clustered' 
            When ix.index_id =0
                Then 'heap'
            Else 'nonclustered' End
		+ Case When ix.ignore_dup_key <> 0 
            Then ', ignore duplicate keys' 
                Else '' End
		+ Case When ix.is_unique <> 0 
            Then ', unique' 
                Else '' End
		+ Case When ix.is_primary_key <> 0 
            Then ', primary key' Else '' End As varchar(210)
        )
    , ix.filter_definition
    , IsNull(cte.partition_scheme_name, '')
    , IsNull(cte.partition_function_name, '')
Order By table_name
    , index_id;

You may need to create some indexes to see this in AdventureWorks:

Create NonClustered Index IX_Sales_SalesOrderHeader_filtered_2005
    On Sales.SalesOrderHeader(AccountNumber)
    Include (CustomerID, SalesPersonID)
    Where OrderDate >= '2005-01-01'
        And OrderDate < '2006-01-01';
table_name           index_name                               object_id   index_id    index_description                   indexed_columns      included_columns               filter_definition                                            partition_scheme_name partition_count row_count
-------------------- ---------------------------------------- ----------- ----------- ----------------------------------- -------------------- ------------------------------ ------------------------------------------------------------ --------------------- --------------- --------------------
SalesOrderHeader     PK_SalesOrderHeader_SalesOrderID         1010102639  1           clustered, unique, primary key      SalesOrderID                                        NULL                                                                               1               31465
SalesOrderHeader     AK_SalesOrderHeader_rowguid              1010102639  2           nonclustered, unique                rowguid                                             NULL                                                                               1               31465
SalesOrderHeader     AK_SalesOrderHeader_SalesOrderNumber     1010102639  3           nonclustered, unique                SalesOrderNumber                                    NULL                                                                               1               31465
SalesOrderHeader     IX_SalesOrderHeader_CustomerID           1010102639  5           nonclustered                        CustomerID                                          NULL                                                                               1               31465
SalesOrderHeader     IX_SalesOrderHeader_SalesPersonID        1010102639  6           nonclustered                        SalesPersonID                                       NULL                                                                               1               31465
SalesOrderHeader     IX_Sales_SalesOrderHeader_filtered_2005  1010102639  13          nonclustered                        AccountNumber        CustomerID, SalesPersonID      ([OrderDate]>='2005-01-01' AND [OrderDate]<'2006-01-01')                           1               1379

Index Defrag Script, v4.0

UPDATE: This script has been updated. Please view the latest version at http://sqlfool.com/2011/06/index-defrag-script-v4-1/.

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:

UPDATE: This script has been updated. Please view the latest version at http://sqlfool.com/2011/06/index-defrag-script-v4-1/.

/* 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? ;)

Random Number Generator in T-SQL

Ever need to generate a random number in T-SQL? I have, on a couple of different occasions. I’m pretty sure that there’s several different ways of doing this in T-SQL, but here’s what I use:

Declare @maxRandomValue tinyint = 100
	, @minRandomValue tinyint = 0;
 
Select Cast(((@maxRandomValue + 1) - @minRandomValue) 
	* Rand() + @minRandomValue As tinyint) As 'randomNumber';

This approach uses the RAND() function to generate a random seed; it also ensures that the value returned is between the specified min and max value. I’ve been using this method in one stored procedure that’s called a couple of hundred times per second, and it seems to perform pretty well.

What method do YOU use to generate a random number? Is it faster than this method?

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.

Connecting via the Query menu


Now, create a new registered server. Make sure to use the same server.


Create a New Registered Server


New Server Registration

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

Pick a color




Open a new query window via Registered Servers.


New Query Window

New Query Window




Registered Server 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

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

Opening a new connection

Click on Options >>

Connection Properties

Connection Properties

Select Use custom color and click on Select…

Pick a color

Pick a color

Choose the color you prefer, then click on OK

Open a new query

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

Multi-Server Rainbow

Beautiful! SSMS seems to remember the color settings too, so you should only have to set this up once.

Source: http://sqlfool.com/2009/03/ssms-server-settings/

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

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

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

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

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

Missing Index

That pretty much covers it for now. HTH! :)

Michelle