Monday, April 18, 2005

Month Variable Table

Every once in a while you need a quick Table w/ a range of dates, that's what this is for...

Declare @iLoop AS INT
SET @iLoop = 0
DECLARE @rptMonths TABLE(rptMonth DateTime)
WHILE @iLoop <= DateDiff(mm, @StartDate, @EndDate)
INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, @iLoop, @StartDate))
SET @iLoop = @iLoop + 1

Wednesday, April 13, 2005

Get Workday count with out weekends

This has come up before in my line of work, to get a total workday count but with out the weekends.

(DATEDIFF(dd, StartDate, EndDate) + 1)
-(DATEDIFF(wk, StartDate, EndDate) * 2)
-(CASE WHEN DATENAME(dw, StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, EndDate) = 'Saturday' THEN 1 ELSE 0 END) AS TotalWorkDays

Thursday, April 07, 2005

Defrag Those Indexes - Maintenance

This article was written back before I was looking into Sql Server 2005. The underlying idea is the same, in order to keep your database running healthy you will need to maintain and administer the underlying architecture. In this short but subtle refresh I've separated the 2000 concepts and implementations from those used today in Sql Server 2005. If you have comments or corrections do feel free to email me or leave a comment.

SQL Server 2000 =====================================================
It is imperative you maintenance your Database. One way to check up on the indexes per table is to run the DBCC SHOWCONTIG command as DBCC SHOWCONTIG ('tbl_YourTableName') with fast,ALL_INDEXES

You'll end up with a very similar display like the following...

DBCC SHOWCONTIG scanning 'tbl_YourTableName' table...
Table: 'tbl_YourTableName' (1113627606); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 1680
- Extent Switches..............................: 217
- Scan Density [Best Count:Actual Count].......: 96.33% [210:218]
- Logical Scan Fragmentation ..................: 0.18%
DBCC SHOWCONTIG scanning 'tbl_YourTableName' table...
Table: 'tbl_YourTableName' (1113627606); index ID: 2, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 480
- Extent Switches..............................: 64
- Scan Density [Best Count:Actual Count].......: 92.31% [60:65]
- Logical Scan Fragmentation ..................: 0.83%
DBCC SHOWCONTIG scanning 'tbl_YourTableName' table...
Table: 'tbl_YourTableName' (1113627606); index ID: 5, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 696
- Extent Switches..............................: 95
- Scan Density [Best Count:Actual Count].......: 90.63% [87:96]
- Logical Scan Fragmentation ..................: 0.72%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What you're really looking for is the Scan fragmentation to be as low as possible. I've also read on other sites that you want the Scan Desity to be as close to each other as possible like for example 87:96 is fairly close and gives you a density over 90%.

From that you can easily run a defrag on each index as follows

DBCC INDEXDEFRAG (8, 1113627606, 5)
DBCC INDEXDEFRAG (8, 1113627606, 2)
DBCC INDEXDEFRAG (8, 1113627606, 1)

More Information can be obtained via: Sql Server Performance\dbcc commands
and here, SQL Server Index Fragmentation and Its Resolution

If you just wish to defrangment the entire database this little script will put you on your way: (from the link above sql server performance site)
SET DatabaseName --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName

CLOSE TableCursor


SQL Server 2005 ===================================================
Fastforward to today,
IndexDefrag still works in 2005, but if you read around the 'net you'll find many more references to choose to rebuild your indexes via the ALTER INDEX command, which is new for SQL Server 2005.

Before you go out and begin de-fragmenting tables like crazy, it's best to have a plan, finding out how much fragmentation is in your table in 2005 is easier too with the newer function that provides this information. below I've expanded on what you can find around the internet, the below script will allow you to identify rows over 100k and with more than 10% fragmentation, I also handled the commonly seen error for databases that are of the standard dictionary order (80), and the common "Error near '(' ".

SET @db_id = DB_ID()

OBJECT_NAME(i.object_id) AS TableName, AS TableIndexName,
phystat.avg_fragmentation_in_percent, rows
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, 'DETAILED') phystat
inner JOIN sys.indexes i WITH(NOLOCK)
ON i.object_id = phystat.object_id
AND i.index_id = phystat.index_id
INNER JOIN sys.partitions p WITH(NOLOCK)
ON p.OBJECT_ID = i.object_id
WHERE phystat.avg_fragmentation_in_percent > 10 AND ROWS > 100000
/end script/

More information on this system function is availble here:
This link will even give you a cool little script you can use to find out what tables in your database are fragmented over 40%, which is very useful if you are planning a weekend of maintenance.

Now that you have the needed information on how fragmented the indexes are you can begin defragmenting using the Alter Index command


What is especially cool about 2005 is that you can choose to throttle the amount of CPUs you use for the defragging process, with the MAXDOP option note, with the following command you can restrict the number of processors down to 2 CPUs:


You can also choose to rebuild your INDEX and continue to make it available for your users as it defrags, I suspect this has performance implications but none the less the command would be:


More information on the ALTER INDEX command is available on the MSDN here:

Happy Defragging!