February 2007 - Posts

Paul Randal has compiled a list of over 40 blogs from the SQL Server product team. It's great to see and there's a lot of extremely useful and informative content available, in fact it's pretty hard keeping upto date with them all!
Posted by sqldbatips | 1 comment(s)
Filed under:

One of the new features in SQL Server SP2 is the vardecimal storage format which can be used to significantly reduce the space required by decimal and numeric columns in a table at the cost of a small CPU overhead. The storage savings must be balanced against the additional CPU usage that is required to convert the storage format of the rows every time that they are accessed. Additionally, writing to a table that is using vardecimal storage format can slow performance because of an increased number of page splits. Refer to Books Online for all the caveats and notes about using this new storage format (available only in Developer,Enterprise and Evaluation editions).

A new system stored procedure sp_estimated_rowsize_reduction_for_vardecimal allows you to supply a table name and it will return estimated space savings (if any) however it only works on 1 table at a time so I decided to "supersize" it! The procedure in the attached zip file (and shown below) will examine all tables in all databases for possible space savings from the vardecimal storage format. A couple of notes

  • You must be a sysadmin to run this procedure
  • It must be created in the master database
  • It doesn't take into account userdefined types
  • Remember these are estimates, always test to determine the IO vs CPU tradeoffs

use master


create procedure sp_estimated_vardecimal_reduction_alldb


set nocount on

declare @db sysname

declare @cmd nvarchar(4000)


create table #results


   Databasename sysname not null,

   Tablename nvarchar(256) not null,

   avg_rowlen_fixed_format decimal (12,2) null,

   avg_rowlen_vardecimal_format decimal (12, 2) null,

   row_count  int null,

   constraint pk_results primary key clustered






create table #data


   avg_rowlen_fixed_format decimal (12,2) null,

   avg_rowlen_vardecimal_format decimal (12, 2) null,

   row_count  int null



declare dcur cursor local fast_forward for

select [name] from sys.databases where database_id > 4

and state_desc = 'ONLINE'


open dcur

fetch next from dcur into @db

while @@fetch_status = 0



   set @cmd = N'use ' + quotename(@db) + N'


   declare @obj nvarchar(256)


   declare tcur cursor local fast_forward for

   select distinct s.name + N''.'' + t.name as [Table]

   from sys.tables t

   join sys.schemas s on t.[schema_id] = s.[schema_id]

   join sys.columns c on c.[object_id] = t.[object_id]

   join sys.types ty on c.system_type_id = ty.system_type_id

   where ty.name in (''decimal'',''numeric'')


   open tcur

   fetch next from tcur into @obj

   while @@fetch_status = 0



      truncate table #data

      begin try

      insert #data

      exec sp_estimated_rowsize_reduction_for_vardecimal @obj

      insert #results select db_name(),@obj,* from #data

      end try

      begin catch

      end catch


      fetch next from tcur into @obj



   close tcur

   deallocate tcur'


   exec sp_executesql @cmd


   fetch next from dcur into @db



close dcur

deallocate dcur


select z.* from (

select *,

case when avg_rowlen_fixed_format > 0 then


/avg_rowlen_fixed_format) * 100) as numeric(5,2))

else 0 end as [Compression%] from #results) z

where [Compression%] > 0

order by Databasename,Tablename


drop table #data

drop table #results





Posted by sqldbatips | with no comments

 ** Updated please check here for details of hotfix ** 

Users of SQL Server 2005 maintenance plans should be aware of a change in SP2 that affects existing cleanup tasks until updated using SQL Server SP2 tools.

Who is affected?

You are affected if you use SQL Server 2005 maintenance plans, those plans include a History Cleanup Task or a Maintenance Cleanup Task, and you install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance plans are not affected.

What is the issue?

Some maintenance plans might include the Maintenance Cleanup Task and the History Cleanup Task, which allow users to delete information older than a specified interval. When SQL Server 2005 was released, this interval was measured in days, weeks, months, or years. In response to customer feedback, SQL Server 2005 SP2 includes significant enhancements to maintenance plans, including an enhancement that allows users to specify the cleanup interval in hours. After upgrading to SP2, and until you update cleanup tasks using SQL Server SP2 tools as described below, existing cleanup task intervals are misinterpreted. This leads to earlier data cleanup than was intended.

What if I have already installed SP2 on my server?

You can restore your maintenance plans to their former behavior by opening them up in the Maintenance Plan Designer, opening any cleanup tasks, adjusting the age units to the proper value, and saving the maintenance plan. Use SQL Server 2005 SP2 tools to make these changes.

What if I haven't yet installed SP2 on my server?

We are currently investigating our approach to this problem and will provide guidance shortly. If you depend on the Maintenance Cleanup Task or the History Cleanup Task and are not able to verify and possibly update cleanup tasks using matching server and tool versions, you may want to wait for that guidance before installing SP2.

It has been a while in coming but you can now download the CTP of the SQL Server 2005 Best Practice Analyzer tool. The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment. This download is the February 2007 Community Technology Preview of SQL Server 2005 Best Practices Analyzer. More details to follow once I get a bit of time to play with it.

Download SQL Server 2005 Best Practices Analyzer

Posted by sqldbatips | with no comments

You can download the latest versions of the SQL Server 2005 samples and databases using the link below.There is also a new sample database called AdventureWorksLT. This is a scaled-down sample database that those who are new to SQL Server will find easier to use and understand.


Among the new additions to the samples are the Aggregation Manager for Analysis Services which lets you view, design, edit, and add aggregations based on the information collected in the query log, the ASUV (Analysis Services Upgrade Verification) tool which lets you compare MDX query results and performance between a Microsoft SQL Server 2000 Analysis Services databases and a Microsoft SQL Server 2005 Analysis Services database and ASTrace which lets you capture an Analysis Services trace and log it into a SQL Server table which can then be queried via TSQL or read using SQL Server Profiler.

Get it while it's hot!

Download SQL 2005 Service Pack 2 

Readme for Microsoft SQL Server 2005 Service Pack 2

What's New in SQL Server 2005 SP2

A list of the bugs that are fixed in SQL Server 2005 Service Pack 2

Download SQL 2005 Books Online Feb 2007 (SP2)

Download Feature Pack for Microsoft SQL Server 2005 - February 2007

Download SQL Server 2005 Reporting Services Add-in for SharePoint Technologies

Download SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007

It seems like it has been in beta forever but you can now download the RTM version of the Clusprep (Microsoft Cluster Configuration Validation Wizard) tool. The Microsoft Cluster Configuration Validation Wizard, a.k.a. "ClusPrep", is a validation tool that does a complete system inventory and runs focused tests on servers that are configured and ready for Microsoft Server Cluster installation (in other words, before the servers are a cluster). ClusPrep will also run the inventory and many of these same tests after clustering is installed, however, because of their potentially disruptive nature, most storage tests are not run if the servers are already a cluster.

 Download it here

Posted by sqldbatips | with no comments
Filed under: ,

This white paper describes how application developers can apply performance-tuning techniques to their Microsoft SQL Server 2005 Analysis OLAP solutions. It's a very interesting and thorough (and long!) paper and well worth a read.

Download it here

Also highly recommended if you're using Analysis Services 2005 is the Microsoft SQL Server 2005 Analysis Services book. I'm only halfway through it currently but it's greate to finally get to grips with some of the internals and it's the the closest thing you can get to the Inside SQL Server series but for Analysis Services rather than SQL Server (I also highly recommend that series of books as well). I'm learning something new with every chapter!

Posted by sqldbatips | with no comments