Mitch Wheat - Treat the cause, not the symptoms!

A new version of VSCommands 2010 was released earlier this month. New features include file structure, output windows highlighting (makes errors easier to spot), and friendly solution name (handy where you might be working in multiple branches of the same project).

Paul Randal, who teaches the SQL Server portion of the SharePoint MCM, has released an updated version of the database maintenance whitepaper for SharePoint 2007 (which was somewhat out of date). You can download it from here.

Ola Hallengren has recently released a new version of his maintenance solution scripts. He has added the ability to log to a table, which is useful if you want to analyse how your indexes are rebuilt and reorganized over time. All you need to do is to set @LogToTable = 'Y'.

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', 

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', 

@FragmentationMedium = 'INDEX_REORGANIZE', 

@FragmentationLow = NULL, 

@LogToTable = 'Y' 

It can also be used with DatabaseBackup and DatabaseIntegrityCheck.

http://ola.hallengren.com/Versions.html

http://ola.hallengren.com/scripts/MaintenanceSolution.sql

If you’re thinking of buying a new shiny 550MB/s SSD, there are two things you should keep in mind:

1. Does your motherboard support SATA 3.0?
2. SATA revision 3.0 (also known as SATA 6) is NOT SATA 3

(Some motherboards specs even refer to Sata-600 and Sata-300 to try and limit the confusion)

SATA revision 2.0 (SATA 3 Gbit/s)

With a native transfer rate of 3.0 Gbit/s, and taking 8b/10b encoding into account, the maximum uncoded transfer rate is 2.4 Gbit/s, providing a peak throughput of about 300 MB/s.

SATA revision 3.0 (SATA 6 Gbit/s)

Provides a peak throughput of about 600 MB/s (Megabytes per second) including the protocol overhead (10b/8b coding with 8 bits to one byte).

Ref.

My realisation came about today through a conversation I had with Hadley Willan (aka Hadders). He’s a gold mine of tech info; I wish he would blog these gems more often…

Visual Studio LightSwitch 2011 is launching July 26th. Sounds just like another Visual Studio add-in being released? It’s definitely more than that. It’s going to have a big impact on how some businesses build applications.

[NOTE: If you are planning to install LightSwitch Beta 2, and Visual Studio 2010 Express, Professional, Premium or Ultimate is installed, you must first install Visual Studio 2010 SP1]

Not new, but several excellent Visual Studio 2010 debugging tips from Scott Guthrie, some you probably know and perhaps the odd one you might not be aware of (or have forgotten about).

Just recently I’ve been converting a small utility that uses SQL-DMO to use SQL-SMO instead (SQL-DMO is deprecated in SQL Server 2008 R2; if you really need to use it, install the Backward Compatibility Components from the SQL Server 2008 Feature Pack from the Microsoft Download Centre). Remembering all those posts saying how slow SMO was compared to DMO, a colleague mentioned this:

SMO has the concept of Delayed Instantiation of Object Properties. The concept consists of not returning every property of a database object when the object is requested. Some properties, like the StoredProcedure's IsSystemObject property, are not returned by default when you request a Stored Procedure or a collection of Stored Procedures from the Database. It is only when you explicitly access the IsSystemObject property that SMO goes back to the database to get its value.

[…]

If you find your SMO code is running slowly, use SQL Profiler to check whether you are accessing properties that are not returned by default and creating extra roundtrips to the database. Force SMO to load those properties by default by using the Server.SetDefaultInitFields method:

            Server server = new Server();

            // Load all properties for columns
            server.SetDefaultInitFields(typeof (Column), true);

            // ...or load all properties for all objects
            server.SetDefaultInitFields(true);

To build an SMO application, you need to reference the SMO assemblies. Click ‘Add Reference’ and navigate to the folder

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

Add references to:

• Microsoft.SqlServer.ConnectionInfo.dll
  
  
• Microsoft.SqlServer.Smo.dll
  
  
• Microsoft.SqlServer.Management.Sdk.Sfc.dll
  
  
• Microsoft.SqlServer.SqlEnum.dll

(These are the minimum files that are required to build an SMO application)

Ref.: How to: Create a Visual C# SMO Project in Visual Studio .NET

SQL Server Code Name "Denali" CTP3 has been released. You can download from TechNet here (I believe it requires a current subscription).

Resources:

I mentioned Tibor Karaszi’s guide a while back and he’s updated it for the datetime datatypes added in SQL Server 2008. A great reference: The ultimate guide to the datetime datatypes

Just for my reference: Windows Azure Platform FAQs contains a nice overview and description of many of the aspects of Microsoft’s cloud offering, including SQL Azure, and a list of feature differences between SQL Server versions and SQL Azure.