Simon Hughes - Software Architect, Tutor, Developer

Some nice articles by Jason Strate on index analysis, page splits, etc.Check them out at: Return of Index Analysis - Part 1   Return of Index Analysis - Part 2   An Index on Indexing #TSQL2sDay   Read the black-ops articles.

I just found my very first commercial software I wrote in Z80 assembler. It's called lifeguard and can be found here . Its basically an unlimited lives finder for the ZX Spectrum and ran on the Multiface hardware. Romantic Robot made the multiface and distributed it for me. They used to pay me a commssion on a monthly basis. I still remember the algorithm for finding lives, grenades, timers, etc. Good times.

Pex and Moles are Visual Studio 2010 Power Tools extension that help Unit Testing .NET applications. Pex automatically generates test suites with high code coverage. Right from the Visual Studio code editor, Pex finds interesting input-output values of your methods, which you can save as a small test suite with high code coverage. Microsoft Pex is a Visual Studio add-in for testing .NET Framework applications.Moles allows to replace any .NET method with a delegate. Moles supports unit testing by providing isolation by way of detours and stubs. The Moles framework is provided with Pex, or can be installed by itself as a Microsoft Visual Studio add-in. Microsoft Moles helps with writing unit tests, but what remains is the tedious task of writing the specific unit tests that exhaustively exercise and validate the logic of the code-under-test. Microsoft Pex can help you in understanding the input/output behavior of your code, finding inputs that cause the code-under-test to crash, and...

Scott Hanselman - The Best Visual Studio 2010 Productivity Power Tools, Power Commands and Extensions My favourites are: PowerCommands Pro Power Tools Lots of others, so go check em out.

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,  ...