Simon Hughes - Software Architect, Tutor, Developer

In SQL 2009 DBCC INDEXDEFRAG will no longer work as Microsoft have removed the command. You need to user ALTER INDEX instead: CREATE PROCEDURE sjh_defragment_indexes_2008 (     @maxfrag FLOAT ) AS   /* Simon Hughes 15 May 2010   For SQL 2008+ This stored procedure checks index fragmentation in a database and defragments indexes whose fragmentation fall above a specified threshold: @maxfrag   Must be run in the database to be defragmented.   exec sjh_defragment_indexes_2008 5 */   SET NOCOUNT ON SET XACT_ABORT ON   -- Write start time for information purposes PRINT 'Started defragmenting at: ' + CONVERT ( VARCHAR , GETDATE ())   -- Check fragmentation DECLARE @execstr NVARCHAR(255) DECLARE lcsr CURSOR local fast_forward READ_ONLY FOR     SELECT 'ALTER INDEX [' + i.name + '] ON [' + so.name + '] REORGANIZE;' AS execstr     FROM    sys.sysobjects AS so          ...

Shrinking a database is bad. Why? Well its because it causes massive index fragmentation. (You can read about it here ). DECLARE @Base SYSNAME   DECLARE database_cursor CURSOR FOR     SELECT QUOTENAME( name ) AS Base     FROM    master..sysdatabases     WHERE   DATABASEPROPERTYEX( name , 'Status' ) = 'ONLINE'             AND DATABASEPROPERTYEX( name , 'IsAutoShrink' ) = 1   OPEN database_cursor FETCH NEXT FROM database_cursor INTO @Base   WHILE @@FETCH_STATUS = 0 BEGIN     EXEC ( 'ALTER DATABASE ' + @BASE + ' SET AUTO_SHRINK OFF' )     FETCH NEXT FROM database_cursor INTO @Base END CLOSE database_cursor DEALLOCATE database_cursor

You can read all about SQL defragmentation here by Satya Jayanty. The bottom line - defragmentation is bad. It causes high CPU load and poor SQL performance. Shrinking your database causes massive index fragmentation. You can read about that here , therefore you should always turn off auto-shrink (I also have a script for that here ). For SQL 2000 you must use DBCC SHOWCONTIG. You can use the SQL given here . However, for SQL Server 2005 onwards, there is a better way using the dynamic management view sys.dm_db_index_physical_stats introduced in SQL 2005. Note: In SQL 2009 this proc is not going to work because Microsoft are going to remove DBCC INDEXDEFRAG from the next version of Microsoft SQL Server (read 2009), you need to user ALTER INDEX instead, as shown in my next blog entry . SELECT OBJECT_NAME (i.object_id) AS TableName,         i.name AS TableIndexName,         phystat.avg_fragmentation_in_percent,  ...

Quite a good article by Oleg Netchaev on how to automatically generate INSERT statements for a given table. The SQL for this is available here . If you only want a small subset generated, then have a look at the SSMS Tools Pack which is free.

Full credit for this article goes to Jonathan Payne, who works as one of our esteemed DBAs. Unfortunately he has no blog, so here it is: What has been going on? Over the past week we have been testing and comparing VMware’s ESXi virtualisation product with Microsoft’s Hyper-V. This has come about because our existing virtualisation infrastructure, which is built on VMware’s WMware Server, has not been performing as expected and has been have a real impact on our services. The servers V1, V2 and BuildServer are all virtual machines running in this environment on the physical server Virtual1 and we have had problems with VMware Server where it just couldn’t seem to allocate enough CPU resources to run the VMs correctly when there was at least 60% of CPU capacity not being used. What are we comparing? We have investigated the use of ESXi or Hyper-V to provide the virtualisation environment. These are both type 1 hypervisors, which means that hardware resources are presented directly...