Simon Hughes - Software Architect, Tutor, Developer

At our company, we have a 4 core blade running SQL Server 2008 Enterprise Edition. This is a very, very busy SQL Server and is running on HP EVA hardware, so the I/O is not an issue. We have over 50 warehouses all running SQL Server 2008 and replicating data to/from this primary database. This database is also used by our website, webservices and import/export applications. The only thing this database does not serve is our SSRS reports, which are hosted on another blade. What is an issue, however, are the CPU's. They were very busy. To help with this we can play around with two settings on SQL Server: MAXDOP (Max degree of parallelism) Cost threshold for parallelism Our DBA and I had a few discussions around this recently. Read plenty of articles on it, such as Adam Machanic On a normal system, leaving these settings alone are fine, but on a very very busy system you need to change them. MAXDOP=1. What this does is to always use 1 core for the query. MAXDOP=0, Cost...

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

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

If you experience terrible performance and constant refreshing of a Script Documents node when using the Visual Studio Debugger, follow the instructions here Work around: This work around requires the Silverlight 2 Tools for Visual Studio 2008 SP1 to be installed. So if you don't have these tools installed, you can download them from Microsoft ( http://www.microsoft.com/downloads/details.aspx?familyid=C22D6A7B-546F-4407-8EF6-D60C8EE221ED&displaylang=en ). If this is not an option for you, there is some alternative work arounds at the bottom of this blog post. Right click on your project and open up project properties Go to the 'Start Options' sheet, and click the 'Silverlight' button at the bottom of the screen. Because the debugger cannot debug both Silverlight code and Script code at the same time, this will disable Script debugging and therefore avoid the problem.

I setup a Virtual PC recently and noticed my hard disks were very busy lately. My VirtualPC is not on, but checking what files were being accessed (Task Manager --> Performance --> Resource monitor) I noticed that the VHD virtual disk image was being read a lot. Which is really odd as it's not in use. The solution was to disble the service "Superfetch". Hey presto, all is quiet again. Superfetch was trying to keep my system running fast by pre-loading the VHD into memory. Madness. I've had superfetch turned off all day, and my hard disks's have never been so quiet. Wonderful. Now, if only there was a way to exclude folders from superfetch... Having a Google around there's no exclusion list, however, there is a way to refine what's going on via the registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management\PrefetchParameters Modify the EnablePrefetcher key to one of the following settings: Disa...

If you use Virtual PC 2007 you know that the virtual hard drive files (.VHDs) can grow to extremely large sizes. The .VHD files dynamically expand when software is installed in the .VHD. After installation, the original setup files are deleted and no longer take up space in the .VHD. However the .VHD does not correspondingly dynamically shrink in size. Once a .VHD file expands - it doesn't shrink. EXAMPLE: If the installation of a product uses 500MB to expand its setup files and only really consumes 200MB in the "C:\Program Files\" directory, the .VHD file expands accordingly. When the 500MB of setup files are subsequently deleted after installation has completed, the .VHD file doesn't shrink afterwards. That is wasted host storage. So the question is: How do I shrink the size of a .VHD when I know it's inflated unnecessarily? Here's how to do it: Clear out the machine of filler Install ccleaner from http://ccleaner.com   which deletes all t...