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?

Webcast Tomorrow!

I’m excited to be doing a webcast tomorrow with the infamous illustrious Brent Ozar for Quest’s Pain-of-the-Week. The title is “Getting Started with SQL Server Management Studio,” and as you’ve probably gathered, it’s pretty entry-level stuff. If you read my blog, then chances are you don’t need to watch this webcast. But if you know anyone who’s trying to learn SQL Server or is trying to make the upgrade from 2000 to 2005/2008, this may be a good webcast for them.

I’ve also got a few other speaking engagements coming up:

June 2nd: Cedar Valley .NET User Group
I’ll be reprising my Iowa Code Camp presentation on “SQL Server for the .NET Developer” for CVINETA. This presentation focuses on what you need to know about good table design, indexing strategies, and fragmentation… you know, what you wish every .NET developer knew about SQL Server. 🙂

June 11th: PoTW: Time-Saving SQL Server Management Studio Tips & Tricks
I’ll also be doing this webcast with @BrentO as a follow-up to our webcast tomorrow. It will focus on how to save time and improve your sanity by using some neat little tricks in SSMS 2008.

Performance Considerations of Data Types

I’ve just finished my first real content for the PASS Performance SIG. I decided to write on “Performance Considerations of Data Types,” as I think this is one of the easiest and most overlooked topics in performance tuning. Here’s a summary:

Selecting inappropriate data types, especially on large tables with millions or billions of rows, can have significant performance implications. In this article, I’ll explain why and offer suggestions on how to select the most appropriate data type for your needs. The primary focus will be on common data types in SQL Server 2005 and 2008, but I’ll also discuss some aspects of clustered indexes and column properties. Most importantly, I’ll show some examples of common data-type misuse.

If you’re interested in this content, you can find it here: Performance Considerations of Data Types.

Special thanks to Paul Randal and Paul Nielsen for providing me with technical reviews and great feedback. You guys are awesome!

Thanks also to Mladen Prajdic and Jeremiah Peschka for their great input. You guys are awesome, too!

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,

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/

Automated Index Defrag Script

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

So after much delay, here’s the latest and greatest version of my index defrag script.

A summary of the changes:

  • Added support for centralized execution. Option to defrag indexes for a single database from another database, or for all non-system databases.
  • Consolidated Enterprise and Standard versions of the script with new @editionCheck variable.
  • Added parametrization for MaxDop restrictions during rebuilds; validates that the value does not exceed the actual number of processors on the server.
  • Changed minimum fragmentation default value from 10 to 5 to match BOL recommendations.
  • Limited defrags to objects with more than 8 pages.
  • Added a debug option to give a little more insight into what’s happening and to assist with troubleshooting.
  • Updated logic for handling partitions and LOBs.

And a couple of notes and explanations:

Don’t you know you can just pass NULL to sys.dm_db_index_physical_stats for the DatabaseID?
Yes, I realize you can do this. But I don’t want to defrag the system databases, i.e. tempdb, so I opted to handle it this way instead.

How long will this take?
It depends. I don’t necessarily recommend running it without specifying a database; at least, not unmonitored. You *can* do that, but it could take a while. For example, to run sys.dm_db_index_physical_stats for all databases and tables, totaling 2TB, took me 4.5 hours; that doesn’t even count the actual defrags.

Where should I put this?
It’s up to you. If you have a database for items like centralized maintenance or scratch tables, that may be a good place for it. If you prefer, you could also put this in each individual database and call it locally, too. I would not put this in the master or msdb databases.

This is pretty close to a complete rewrite, so please let me know if you encounter any bugs. And now… the code!

If Not Exists(Select [object_id] From sys.tables Where name = N'dba_indexDefragLog')
    -- Drop Table dbo.dba_indexDefragLog
    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
        , durationSeconds   int                 Not Null
        Constraint PK_indexDefragLog Primary Key Clustered (indexDefrag_id)
    Print 'dba_indexDefragLog Table Created';
If ObjectProperty(Object_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') = 1
    Drop Procedure dbo.dba_indexDefrag_sp;
    Print 'Procedure dba_indexDefrag_sp dropped';
Create Procedure dbo.dba_indexDefrag_sp
    /* Declare Parameters */
      @minFragmentation     float           = 5.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 */
    , @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 */
    , @onlineRebuild        bit             = 1     
        /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */
    , @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 */
    Name:       dba_indexDefrag_sp
    Author:     Michelle Ufford, http://sqlfool.com
    Purpose:    Defrags all indexes for one or more databases
      @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
      @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.
      @onlineRebuild        1 = online rebuild; 
                            0 = offline rebuild
      @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
    Date        Initials	Description
    2008-10-27  MFU         Initial Release for public consumption
    2008-11-17  MFU         Added page-count to log table
                            , added @printFragmentation option
    2009-03-17  MFU         Provided support for centralized execution, 
                            , consolidated Enterprise & Standard versions
                            , added @debugMode, @maxDopRestriction
                            , modified LOB and partition logic                            
    Exec dbo.dba_indexDefrag_sp
          @executeSQL           = 0
        , @minFragmentation     = 80
        , @printCommands        = 1
        , @debugMode            = 1
        , @printFragmentation   = 1
        , @database             = 'AdventureWorks'
        , @tableName            = 'AdventureWorks.Sales.SalesOrderDetail';
Set NoCount On;
Set XACT_Abort On;
Set Quoted_Identifier On;
    If @debugMode = 1 RaisError('Dusting off the spiderwebs 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
            , @partitions           smallint
            , @fragmentation        float
            , @pageCount            int
            , @sqlCommand           nvarchar(4000)
            , @rebuildCommand       nvarchar(200)
            , @dateTimeStart        datetime
            , @dateTimeEnd          datetime
            , @containsLOB          bit
            , @editionCheck         bit
            , @debugMessage         varchar(128)
            , @updateSQL            nvarchar(4000)
            , @partitionSQL         nvarchar(4000)
            , @partitionSQL_Param   nvarchar(1000)
            , @LOB_SQL              nvarchar(4000)
            , @LOB_SQL_Param        nvarchar(1000);
    /* Create our temporary tables */
    Create Table #indexDefragList
          databaseID        int
        , databaseName      nvarchar(128)
        , objectID          int
        , indexID           int
        , partitionNumber   smallint
        , fragmentation     float
        , page_count        int
        , defragStatus      bit
        , schemaName        nvarchar(128)   Null
        , objectName        nvarchar(128)   Null
        , indexName         nvarchar(128)   Null
    Create Table #databaseList
          databaseID        int
        , databaseName      varchar(128)
    Create Table #processor 
          [index]           int
        , Name              varchar(128)
        , Internal_Value    int
        , Character_Value   int
    If @debugMode = 1 RaisError('Beginning validation...', 0, 42) With NoWait;
    /* Just a little validation... */
    If @minFragmentation Not Between 0.00 And 100.0
        Set @minFragmentation = 5.0;
    If @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';
    /* 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
        Set @editionCheck = 0; -- does not support online rebuilds
    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
    From sys.databases
    Where name = IsNull(@database, name)
        And database_id > 4 -- exclude system databases
        And [state] = 0; -- state must be ONLINE
    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) > 0
        Select Top 1 @databaseID = databaseID
        From #databaseList;
        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 #indexDefragList
              database_id As databaseID
            , QuoteName(DB_Name(database_id)) As 'databaseName'
            , [object_id] As objectID
            , index_id As indexID
            , partition_number As partitionNumber
            , avg_fragmentation_in_percent As fragmentation
            , page_count 
            , 0 As 'defragStatus' /* 0 = unprocessed, 1 = processed */
            , Null As 'schemaName'
            , Null As 'objectName'
            , Null As 'indexName'
        From sys.dm_db_index_physical_stats (@databaseID, Object_Id(@tableName), Null , Null, N'Limited')
        Where avg_fragmentation_in_percent >= @minFragmentation 
            And index_id > 0 -- ignore heaps
            And page_count > 8 -- ignore objects with less than 1 extent
        Option (MaxDop 1);
        Delete From #databaseList
        Where databaseID = @databaseID;
    Create Clustered Index CIX_temp_indexDefragList
        On #indexDefragList(databaseID, objectID, indexID, partitionNumber);
    Select @debugMessage = 'Looping through our list... there''s ' + Cast(Count(*) As varchar(10)) + ' indexes to defrag!'
    From #indexDefragList;
    If @debugMode = 1 RaisError(@debugMessage, 0, 42) With NoWait;
    /* Begin our loop for defragging */
    While (Select Count(*) From #indexDefragList Where defragStatus = 0) > 0
        If @debugMode = 1 RaisError('  Picking an index to beat into shape...', 0, 42) With NoWait;
        /* Grab the most fragmented index first to defrag */
        Select Top 1 
              @objectID         = objectID
            , @indexID          = indexID
            , @databaseID       = databaseID
            , @databaseName     = databaseName
            , @fragmentation    = fragmentation
            , @partitionNumber  = partitionNumber
            , @pageCount        = page_count
        From #indexDefragList
        Where defragStatus = 0
        Order By fragmentation Desc;
        If @debugMode = 1 RaisError('  Looking up the specifics for our index...', 0, 42) With NoWait;
        /* Look up index information */
        Select @updateSQL = N'Update idl
            Set schemaName = QuoteName(s.name)
                , objectName = QuoteName(o.name)
                , indexName = QuoteName(i.name)
            From #indexDefragList As idl
            Inner Join ' + @databaseName + '.sys.objects As o
                On idl.objectID = o.object_id
            Inner Join ' + @databaseName + '.sys.indexes As i
                On o.object_id = i.object_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 idl.databaseID = ' + Cast(@databaseID As varchar(10));
        Execute sp_executeSQL @updateSQL;
        /* Grab our object names */
        Select @objectName  = objectName
            , @schemaName   = schemaName
            , @indexName    = indexName
        From #indexDefragList
        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''s any LOBs to be handled...', 0, 42) With NoWait;
        /* Determine if the table contains LOBs */
        Select @LOB_SQL = ' Select Top 1 @containsLOB_OUT = column_id
                            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('  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
            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));
        /* If the index is heavily fragmented and doesn't contain any partitions or LOB's, rebuild it */
        If @fragmentation >= @rebuildThreshold And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1
            /* Set online rebuild options; requires Enterprise Edition */
            If @onlineRebuild = 1 And @editionCheck = 1 
                Set @rebuildCommand = N' Rebuild With (Online = On';
                Set @rebuildCommand = N' Rebuild With (Online = 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')';
                Set @rebuildCommand = @rebuildCommand + N')';
            Set @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.'
                            + @schemaName + N'.' + @objectName + @rebuildCommand;
        /* Are we executing the SQL?  If so, do it */
        If @executeSQL = 1
            If @debugMode = 1 RaisError('  Executing SQL statements...', 0, 42) With NoWait;
            /* Grab the time for logging purposes */
            Set @dateTimeStart  = GetDate();
            Execute sp_executeSQL @sqlCommand;
            Set @dateTimeEnd  = GetDate();
            /* Log our actions */
            Insert Into dbo.dba_indexDefragLog
                , databaseName
                , objectID
                , objectName
                , indexID
                , indexName
                , partitionNumber
                , fragmentation
                , page_count
                , dateTimeStart
                , durationSeconds
                , @databaseName
                , @objectID
                , @objectName
                , @indexID
                , @indexName
                , @partitionNumber
                , @fragmentation
                , @pageCount
                , @dateTimeStart
                , DateDiff(second, @dateTimeStart, @dateTimeEnd);
            /* Just a little breather for the server */
            WaitFor Delay @defragDelay;
            /* Print if specified to do so */
            If @printCommands = 1
                Print N'Executed: ' + @sqlCommand;
        /* Looks like we're not executing, just printing the commands */
            If @debugMode = 1 RaisError('  Printing SQL statements...', 0, 42) With NoWait;
            If @printCommands = 1 Print IsNull(@sqlCommand, 'error!');
        If @debugMode = 1 RaisError('  Updating our index defrag status...', 0, 42) With NoWait;
        /* Update our index defrag list so we know we've finished with that index */
        Update #indexDefragList
        Set defragStatus = 1
        Where databaseID       = @databaseID
          And objectID         = @objectID
          And indexID          = @indexID
          And partitionNumber  = @partitionNumber;
    /* Do we want to output our fragmentation results? */
    If @printFragmentation = 1
        If @debugMode = 1 RaisError('  Displaying fragmentation results...', 0, 42) With NoWait;
        Select databaseID
            , databaseName
            , objectID
            , objectName
            , indexID
            , indexName
            , fragmentation
            , page_count
        From #indexDefragList;
    /* When everything is said and done, make sure to get rid of our temp table */
    Drop Table #indexDefragList;
    Drop Table #databaseList;
    Drop Table #processor;
    If @debugMode = 1 RaisError('DONE!  Thank you for taking care of your indexes!  :)', 0, 42) With NoWait;
    Set NoCount Off;
	Return 0

Thanks to my beta testers, @scoinva, @davidmtate, @jdanton, and @SuperCoolMoss!
Special thanks to SCM for keeping on me to finish this.

Happy Defragging!


Source: http://sqlfool.com/2009/03/automated-index-defrag-script/

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/

Find Missing Indexes in Stored Procs with T-SQL

This post should probably be called “The Power of Twitter.” I’ve said it before, I’ll say it again: I love Twitter. I mostly follow SQL Server people, so it’s become a great source of new information and help when I feel like banging my head against the wall when I get stumped.

So last week, Jonathan Kehayias (@jmkehayias) posted a link to a missing index script on the MSDN forums. Jonathan’s script is modified from something he put together for a question posed by Jeremiah Peschka (@peschkaj).

Jonathan’s script intrigued me. I’d never tried to search a query plan’s XML before, and it certainly presents some interesting possibilities. After dealing with a missing index in production a few weeks ago (caused by an index change), I thought it’d be a great idea to put a regular monitor in place.

Everything was going well until I ran into a problem where I couldn’t get the proc name returned. The results spanned numerous databases; I had the object_id and database_id, but I wanted to store the proc name instead. I tried several different methods, including sp_msforeachdb and sp_executeSQL, and while I had a working solution, it was a little more clunky than I liked. So I asked my awesome followers on Twitter for any tips and within minutes I had half a dozen responses. In the end, @MladenPrajdic solved my problem with a pretty simple solution: put ‘Use ?;’ at the start of my sp_msforeachdb statement. Thanks again, Mladen!

I’ve now had this process running on my server for a few days now, with good success. The stored procedure below will return the database name, proc name, and query plan XML for any stored proc with a missing index. This is a centralized proc that will store the results in a table for later action. If you click on the XML, you should see the actual query plan with the missing index details. Because this looks at cached query plans, your best bet is to run it fairly regularly (maybe daily) to increase your chances of catching any problem procs.

/* Create a stored procedure skeleton */
If ObjectProperty(Object_ID('dbo.dba_missingIndexStoredProc_sp'), N'IsProcedure') Is Null
    Execute ('Create Procedure dbo.dba_missingIndexStoredProc_sp As Print ''Hello World!''')
    RaisError('Procedure dba_missingIndexStoredProc_sp created.', 10, 1);
/* Drop our table if it already exists */
If Exists(Select Object_ID From sys.tables Where [name] = N'dba_missingIndexStoredProc')
    Drop Table dbo.dba_missingIndexStoredProc
    Print 'dba_missingIndexStoredProc table dropped!';
/* Create our table */
Create Table dbo.dba_missingIndexStoredProc
      missingIndexSP_id int Identity(1,1)   Not Null
    , databaseName      varchar(128)        Not Null
    , databaseID        int                 Not Null
    , objectName        varchar(128)        Not Null
    , objectID          int                 Not Null
    , query_plan        xml                 Not Null
    , executionDate     smalldatetime       Not Null
    Constraint PK_missingIndexStoredProc
        Primary Key Clustered(missingIndexSP_id)
Print 'dba_missingIndexStoredProc Table Created';
/* Configure our settings */
Set ANSI_Nulls On;
Set Quoted_Identifier On;
Alter Procedure dbo.dba_missingIndexStoredProc_sp
        /* Declare Parameters */
            @lastExecuted_inDays    int = 7
          , @minExecutionCount      int = 7
          , @logResults             bit = 1
          , @displayResults         bit = 0
    Name:       dba_missingIndexStoredProc_sp
    Author:     Michelle Ufford, http://sqlfool.com
    Purpose:    Retrieves stored procedures with missing indexes in their
                cached query plans.
                @lastExecuted_inDays = number of days old the cached query plan
                                       can be to still appear in the results;
                                       the HIGHER the number, the longer the
                                       execution time.
                @minExecutionCount = minimum number of executions the cached
                                     query plan can have to still appear 
                                     in the results; the LOWER the number,
                                     the longer the execution time.
                @logResults = store results in dba_missingIndexStoredProc
                @displayResults = return results to the caller
    Notes:      This is not 100% guaranteed to catch all missing indexes in
                a stored procedure.  It will only catch it if the stored proc's
                query plan is still in cache.  Run regularly to help minimize
                the chance of missing a proc.
    Called by:  DBA and/or SQL Agent Job
    Date        User    Description
    2009-03-02  MFU     Initial Release for public consumption
    Exec dbo.dba_missingIndexStoredProc_sp
          @lastExecuted_inDays  = 30
        , @minExecutionCount    = 5
        , @logResults           = 1
        , @displayResults       = 1;
Set NoCount On;
Set XACT_Abort On;
Set Ansi_Padding On;
Set Ansi_Warnings On;
Set ArithAbort On;
Set Concat_Null_Yields_Null On;
Set Numeric_RoundAbort Off;
    /* Declare Variables */
    Declare @currentDateTime smalldatetime;
    Set @currentDateTime = GetDate();
    Declare @plan_handles Table
        plan_handle     varbinary(64)   Not Null
    Create Table #missingIndexes
          databaseID    int             Not Null
        , objectID      int             Not Null
        , query_plan    xml             Not Null
        Constraint PK_temp_missingIndexes Primary Key Clustered
            databaseID, objectID
    Begin Try
        /* Perform some data validation */
        If @logResults = 0 And @displayResults = 0
            /* Log the fact that there were open transactions */
            Execute dbo.dba_logError_sp
                  @errorType            = 'app'
                , @app_errorProcedure   = 'dba_missingIndexStoredProc_sp'
                , @app_errorMessage     = '@logResults = 0 and @displayResults = 0; no action taken, exiting stored proc.'
                , @forceExit            = 1
                , @returnError          = 1;  
        Begin Transaction;
        /* Retrieve distinct plan handles to minimize dm_exec_query_plan lookups */
        Insert Into @plan_handles
        Select Distinct plan_handle
        From sys.dm_exec_query_stats
        Where last_execution_time > DateAdd(day, -@lastExecuted_inDays, @currentDateTime)
            And execution_count > @minExecutionCount;
        With xmlNameSpaces (
            Default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
        /* Retrieve our query plan's XML if there's a missing index */
        Insert Into #missingIndexes
        Select deqp.[dbid]
            , deqp.objectid
            , deqp.query_plan 
        From @plan_handles As ph
        Cross Apply sys.dm_exec_query_plan(ph.plan_handle) As deqp 
        Where deqp.query_plan.exist('//MissingIndex') = 1
            And deqp.objectid Is Not Null;
        /* Do we want to store the results of our process? */
        If @logResults = 1
            Insert Into dbo.dba_missingIndexStoredProc
            Execute sp_msForEachDB 'Use ?; 
                                    Select ''?''
                                        , mi.databaseID
                                        , Object_Name(o.object_id)
                                        , o.object_id
                                        , mi.query_plan
                                        , GetDate()
                                    From sys.objects As o 
                                    Join #missingIndexes As mi 
                                        On o.object_id = mi.objectID 
                                    Where databaseID = DB_ID();';
        /* We're not logging it, so let's display it */
            Execute sp_msForEachDB 'Use ?; 
                                    Select ''?''
                                        , mi.databaseID
                                        , Object_Name(o.object_id)
                                        , o.object_id
                                        , mi.query_plan
                                        , GetDate()
                                    From sys.objects As o 
                                    Join #missingIndexes As mi 
                                        On o.object_id = mi.objectID 
                                    Where databaseID = DB_ID();';
        /* See above; this part will only work if we've 
           logged our data. */
        If @displayResults = 1 And @logResults = 1
            Select *
            From dbo.dba_missingIndexStoredProc
            Where executionDate >= @currentDateTime;
        /* If you have an open transaction, commit it */
        If @@TranCount > 0
            Commit Transaction;
    End Try
    Begin Catch
        /* Whoops, there was an error... rollback! */
        If @@TranCount > 0
            Rollback Transaction;
        /* Return an error message and log it */
        Execute dbo.dba_logError_sp;
    End Catch;
    /* Clean-Up! */
    Drop Table #missingIndexes;
    Set NoCount Off;
    Return 0;

Not know what “EXECUTE dbo.dba_logError_sp;” is doing? Check out my blog post on Error Handling in T-SQL.



Source: http://sqlfool.com/2009/03/find-missing-indexes/

Things You Know Now…

Mike Walsh tagged me in his blog post, Things You Know Now…, and asked, “It doesn’t have to be DBA skills, but what do you wish you knew when you were starting?” This is a really great question, and I’ve given it some pretty serious consideration. Here’s my top 3:

Just because you don’t know everything doesn’t mean you know nothing.
It’s taken me a few years to figure this one out. I’ve come to the hard realization that, no matter how much I know about something (*coughsqlservercough*), I will never know everything. There will always be some new feature, or some new language, that you just won’t have the time or need to learn. And while there’s value in being a Jack-or-Jill-of-all-trades, there’s probably more value in knowing a few things really well. Pick what you love and dive into it wholeheartedly. Make sure you stay current on technologies, so you don’t get outdated, but do so within your specific area. You’ll love your job and be a more valuable employee for it.

Give back to the community, and don’t be afraid of looking stupid.
Whether you’re afraid to ask a question or provide an answer, don’t be. I’ve found that, as long as you approach it intelligently and politely, you’ll be fine. Eleanor Roosevelt once said, “You gain strength, courage, and confidence by every experience in which you really stop to look fear in the face.” I believe this wholeheartedly. For me, starting a blog wasn’t nearly as scary as the decision to put my real name on it. And responding to questions on the MSDN forum? I was pretty nervous that some MVP or Microsoft employee would ridicule my suggestions. But you know what? It hasn’t happened yet, and I gain more confidence with every blog post, every forum response, and every published article. And if I don’t have the right answer? Then I read up on the correct answer, which is just another opportunity to learn.

Work yourself out of a job.
That old adage, “work smarter, not harder,” definitely has merit. Write good, thorough code. Plan for growth. And automate the hell out of everything (without losing quality). Afraid you’ll lose your job? It’s certainly possible, but not likely. Any boss worth his salt will notice you get more work done in less time than your co-workers. This will lead to better reviews, increased responsibility, and possibly even promotions (side note on this: if you like code, don’t do it! I learned the hard way, meetings are boring. 🙂 ).

Tag! You’re It.

I’m calling these guys out to post their own responses to Mike’s question:


Michelle Ufford (aka SQLFool)

Source: http://sqlfool.com/2009/02/things-you-know-now/