SSMS Server Settings
I think this has been discussed before on better blogs than mine, but it’s just so darn cool that I want to help spread the word.
In SSMS 2008, you can change the color of the status bar for servers. This gives you a nice visual reminder as to which server you’re currently connecting to. Since I’ve made the DEV/PROD mistake before, this is something I’m a big fan of.
So let’s walk through how you can set this up:
Opening a new connection
Click on Options >>
Connection Properties
Select Use custom color and click on Select…
Pick a color
Choose the color you prefer, then click on OK
Open a new query
Click on Connect.
That’s all there is to it. Pretty easy, huh? Now let’s see what happens when we connect to multiple servers…
Multi-Server Rainbow
Beautiful! SSMS seems to remember the color settings too, so you should only have to set this up once.
Source: http://sqlfool.com/2009/03/ssms-server-settings/
Automated Index Defrag Script
Filed under: Performance & Tuning, SQL 2008, SQL Tips, T-SQL Scripts
Note: This script has been updated. You can find the latest version here: http://sqlfool.com/2009/06/index-defrag-script-v30/
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')BEGIN -- 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';END IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') = 1BEGIN DROP PROCEDURE dbo.dba_indexDefrag_sp; PRINT 'Procedure dba_indexDefrag_sp dropped';END;Go 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 */AS/********************************************************************************* Name: dba_indexDefrag_sp Author: Michelle Ufford, http://sqlfool.com Purpose: Defrags all indexes for one or more databases Notes: CAUTION: TRANSACTION LOG SIZE MUST BE MONITORED CLOSELY WHEN DEFRAGMENTING. @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; BEGIN 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 ELSE 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 BEGIN 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 SELECT 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; END 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 BEGIN 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 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, rebuild it */ IF @fragmentation >= @rebuildThreshold 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 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; /* Are we executing the SQL? If so, do it */ IF @executeSQL = 1 BEGIN 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 ( databaseID , databaseName , objectID , objectName , indexID , indexName , partitionNumber , fragmentation , page_count , dateTimeStart , durationSeconds ) SELECT @databaseID , @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; 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 PRINT IsNull(@sqlCommand, 'error!'); END 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; END /* Do we want to output our fragmentation results? */ IF @printFragmentation = 1 BEGIN IF @debugMode = 1 RAISERROR(' Displaying fragmentation results...', 0, 42) WITH NoWait; SELECT databaseID , databaseName , objectID , objectName , indexID , indexName , fragmentation , page_count FROM #indexDefragList; END; /* 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 0ENDGo
Thanks to my beta testers, @scoinva, @davidmtate, @jdanton, and @SuperCoolMoss!
Special thanks to SCM for keeping on me to finish this.
Happy Defragging!
Michelle
Source: http://sqlfool.com/2009/03/automated-index-defrag-script/
Index Fragmentation PotW Webcast – Now Available
Filed under: Miscellaneous, Performance & Tuning, Presentations, Syndication
The Pain-of-the-Week webcast I did last Thursday is now online! You can find it here: http://www.quest.com/common/registration.aspx?requestdefid=23092.
The topic of the webcast is index fragmentation: what is it, how to identify it, and how to fix it. You can find the materials for this webcast in my previous blog post. I’ll also be updating SQLServerPedia with the materials, hopefully tonight.
Thanks to the great feedback I’ve received, I’ll be doing another Pain-of-the-Week webcast, this time with Brent Ozar. More info to follow as we hammer out the specifics.
In a related note, I have updated my index defrag script. I’m just waiting for my awesome beta testers to let me know everything works for them. It works just fine on my servers (lol) but I don’t have access to any Standard editions, etc., so I just want to be extra sure before I unleash it on the SQL world. I should have it online tomorrow.
Source: http://sqlfool.com/2009/03/index-fragmentation-potw-webcast/
PoTW Webcast Materials
For those interested, here’s the links I mentioned during my presentation:
… and here’s the scripts that I used during my presentation:
USE sandbox;Go /* Create a table to promote fragmentation */CREATE TABLE dbo.fragmented( myGuid UNIQUEIDENTIFIER Not Null CONSTRAINT PK_fragmented PRIMARY KEY CLUSTERED (myGuid)); /* Populate our table */SET NOCOUNT ON;DECLARE @iterations INT = 0; WHILE @iterations < 250000BEGIN INSERT INTO dbo.fragmented SELECT NewID(); SET @iterations += 1; END; /* Verify the number of records in our table */EXECUTE SP_SPACEUSED 'dbo.fragmented'; /* Let's take a look at sys.dm_db_index_physical_stats */SELECT OBJECT_NAME(ddips.OBJECT_ID) AS 'tableName' , i.name AS indexName , ddips.*FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'fragmented'), NULL, NULL , 'DETAILED') AS ddipsJoin sys.indexes AS i ON ddips.OBJECT_ID = i.OBJECT_ID And ddips.index_id = i.index_id; SELECT *FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'fragmented'), NULL, NULL , 'LIMITED'); /* ShowContig should return us the same information */DBCC ShowContig ('fragmented'); /* ExtentInfo is an undocumented command that will show us the number of allocated extents */DBCC ExtentInfo (0, 'fragmented', 1); /* Grab our page number */SELECT OBJECT_NAME(p.OBJECT_ID) AS 'tableName' , i.name AS 'indexName' , p.partition_number , au.type_desc , CONVERT (VARCHAR(6), CONVERT (INT, SUBSTRING (au.first_page, 6, 1) + SUBSTRING (au.first_page, 5, 1))) + ':' + CONVERT (VARCHAR(20), CONVERT (INT, SUBSTRING (au.first_page, 4, 1) + SUBSTRING (au.first_page, 3, 1) + SUBSTRING (au.first_page, 2, 1) + SUBSTRING (au.first_page, 1, 1))) AS 'firstPage' , CONVERT (VARCHAR(6), CONVERT (INT, SUBSTRING (au.root_page, 6, 1) + SUBSTRING (au.root_page, 5, 1))) + ':' + CONVERT (VARCHAR(20), CONVERT (INT, SUBSTRING (au.root_page, 4, 1) + SUBSTRING (au.root_page, 3, 1) + SUBSTRING (au.root_page, 2, 1) + SUBSTRING (au.root_page, 1, 1))) AS 'rootPage' , CONVERT (VARCHAR(6), CONVERT (INT, SUBSTRING (au.first_iam_page, 6, 1) + SUBSTRING (au.first_iam_page, 5, 1))) + ':' + CONVERT (VARCHAR(20), CONVERT (INT, SUBSTRING (au.first_iam_page, 4, 1) + SUBSTRING (au.first_iam_page, 3, 1) + SUBSTRING (au.first_iam_page, 2, 1) + SUBSTRING (au.first_iam_page, 1, 1))) AS 'firstIAM_page'FROM sys.indexes AS iJOIN sys.partitions AS p ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_idJOIN sys.system_internals_allocation_units AS au ON p.hobt_id = au.container_idWHERE OBJECT_NAME(p.OBJECT_ID) = 'fragmented'ORDER BY tableName; /* View our pages */DBCC TraceOn (3604);DBCC Page (sandbox, 1, pageNumberHERE, 3);DBCC TraceOff (3604); /* Rebuild our index */ALTER INDEX PK_fragmented ON dbo.fragmented REBUILD; /* Clean-Up! */DROP TABLE dbo.fragmented;
Thank you for watching the webcast! 
Michelle
Source: http://sqlfool.com/2009/03/potw-webcast-materials/
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.salesSELECT 'Amanda', 'Sales', 420 UNION AllSELECT 'Barry', 'Sales', 360 UNION AllSELECT 'Chris', 'Marketing', 398 UNION AllSELECT 'David', 'Sales', 371 UNION AllSELECT 'Ethan', 'Customer Support', 123 UNION AllSELECT 'Faith', 'Sales', 206 UNION AllSELECT 'Gavin', 'Marketing', 396 UNION AllSELECT 'Heather', 'Marketing', 51 UNION AllSELECT 'Iris', 'Customer Support', 79 UNION AllSELECT '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 , totalFROM 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 , totalFROM myCTEWHERE salesRank = 1ORDER 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 , totalFROM myCTEWHERE salesRank <= 2 -- this is the only differenceORDER 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/
380PASS Meeting – Success!
Our first meeting was a success by all accounts! We had a great turnout with 30 people showing up.
As an ice breaker activity, we had folks pair up and try to identify something related to SQL Server, i.e. commands or terms, for every letter of the alphabet. There was a 5 minute time limit, and with only 20 seconds to spare, Randy Vandergaast and Dave Brosseau turned their sheet in to each win a $25 gift card to Barnes & Noble.
Chris Leonard gave the group a great overview of PASS and the PASS Summit in Seattle.
Brian Duhn then gave a comprehensive overview of how to set up Server-to-Server Service Broker. The entire slide show can be found at http://380pass.org, under Resources.
Thanks to our awesome sponsors, we had some great prizes to give away, and everyone walked away with something.
Thanks to everyone who attended for helping us have a great launch meeting!
Michelle
380PASS Meeting Tonight!
Our very first I380 PASS meeting is tonight! If you’ve not already registered for the event, you’re still welcome to come. The meeting’s being held at New Horizons in Hiawatha (map). Food and beverages will be provided at 5:30pm, and the meeting will start at 6pm.
Brian Duhn (MCITP), a senior DBA with GoDaddy.com, will be presenting on Server-to-Server Service Broker. Quest has generously provided food and awesome swag for tonight’s meeting.
More details can be found on our official Chapter website at http://380pass.org.
I look forward to seeing you there!
Michelle
SQL Quiz 3: Are you being treated fairly?
In his latest SQL Quiz, Chris Shaw (aka @SQLShaw) asked,
“Do you feel like you are being treated fairly at your current or past employers? The question stems from the fact that very few people today stay at a company 20 to 30 years like they did when I was growing up. Do you feel like the company feels a loyalty toward the employee or do you think that they look at you just as head count?”
Wow. Talk about a loaded question!
I guess first we need to settle on a definition of “being treated fairly.” For my purposes, I’m going to interpret this as a mutually beneficial relationship in which my contributions are acknowledged and rewarded, and I’m able to maintain a healthy work-life balance.
So let’s take a look at the question again.
Am I being treated fairly at my current place of employment?
Absolutely. I love this company. It’s a good, positive work environment, and I’m encouraged to maintain a good work-life balance. For example, when I put in extra hours during a big project or an unexpected crisis, I usually get to leave work a little early on other days.
Side note: today’s my team’s Employee Appreciation Day. This afternoon, we’re going out to eat at a Japanese steakhouse, then out for a movie. All on the company’s dime and time. How cool is that?! Yes, we have openings. 
Have I been treated fairly at previous places of employment?
Umm… ermm… various sounds of hesitation…
The short answer is “no.” But, really, it’s been my own fault. I’ve let companies take advantage of me. I’ve worked too many hours, taken on too much responsibility, let myself be paid crap wages. So really, if anything’s to blame, it’s my eager-to-please nature. The reality is, if you open yourself up to being taken advantage of, you will be. This is true of people as well as companies.
If you don’t feel you’re being treated fairly at your job, schedule a meeting with your boss and explain your concerns. Don’t be all whiny about it, either. Be professional and stick to facts, such as “This last month, I’ve averaged 70 hours a week. Either reduce my hours, pay me overtime, or give me comp time.” It’s a pretty fair request. If they don’t address your concerns, then it’s probably time to start looking for a new job.
Do you feel like the company feels a loyalty toward the employee or do you think that they look at you just as head count?
It depends on the company. I think, realistically, most companies don’t feel any loyalty to an employee. That doesn’t mean the employee’s not appreciated and valued, and the company doesn’t want to retain the employee. But rather, at the end of the day, the company will do what’s best for the company, and if that means letting a valuable employee go for the “greater good of the company”… so be it.
Tag! You’re It.
This can be a pretty sensitive topic, so I’m going to tag the one guy I know who’s ballsy enough to do it. Hey, SQLAgentMan (@SQLAgentMan)… yeah, I’m talking about you.
Source: http://sqlfool.com/2009/03/sql-quiz-3-are-you-being-treated-fairly/
PASS Update
SQL PASS
Hi folks. If lately you’ve noticed a few less blog posts than normal, it’s because I’m spending a lot of time starting up a brand new PASS Chapter. In fact, we’re only one week away from our first meeting! For those who haven’t been following the updates, we’re starting a PASS Chapter in east Iowa (Cedar Rapids, Iowa City, Davenport, etc.). Our first meeting will feature a presentation by Brian Duhn, Senior DBA, MCITP, on server-to-server service broker.
Any and everyone is invited to join us. More information can be found on http://380pass.org.
Pain of the Week Webcast
I’ve also been asked by Quest Software to give a webcast on index fragmentation next week. The webcast will air on Thursday, 12 March 2009, at 10AM CT. This will be my first webcast, so be gentle! More information can be found on Quest’s Pain of the Week website.
SQL Challenge
If you haven’t heard of it yet, Adam Machanic has posted a T-SQL challenge. Check it out… the winner gets a 1-year subscription to MSDN!
Find Missing Indexes in Stored Procs with T-SQL
Filed under: Performance & Tuning, SQL Tips, Syndication, T-SQL Scripts
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 NullBEGIN EXECUTE ('Create Procedure dbo.dba_missingIndexStoredProc_sp As Print ''Hello World!''') RAISERROR('Procedure dba_missingIndexStoredProc_sp created.', 10, 1);END;Go /* Drop our table if it already exists */IF Exists(SELECT OBJECT_ID FROM sys.tables WHERE [name] = N'dba_missingIndexStoredProc')BEGIN DROP TABLE dbo.dba_missingIndexStoredProc PRINT 'dba_missingIndexStoredProc table dropped!';END /* 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;Go ALTER PROCEDURE dbo.dba_missingIndexStoredProc_sp /* Declare Parameters */ @lastExecuted_inDays INT = 7 , @minExecutionCount INT = 7 , @logResults BIT = 1 , @displayResults BIT = 0 AS/********************************************************************************* 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; BEGIN /* 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 BEGIN /* 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; END; 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 BEGIN 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();'; END /* We're not logging it, so let's display it */ ELSE BEGIN 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();'; END; /* See above; this part will only work if we've logged our data. */ IF @displayResults = 1 And @logResults = 1 BEGIN SELECT * FROM dbo.dba_missingIndexStoredProc WHERE executionDate >= @currentDateTime; END; /* 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;ENDGo
Not know what “EXECUTE dbo.dba_logError_sp;” is doing? Check out my blog post on Error Handling in T-SQL.
HTH!
Michelle
Subscribe to my blog!
It's easy, free, and will keep you informed of my latest SQL tips and scripts.Like what you see? Subscribe! 
Around the Web
SQLRockstar Blog Ranking
