March 2011 - Posts

If you have a query such as

select year(OrderDate) yr, count(1)

from AdventureWorks2008.Sales.SalesOrderHeader

group by year(OrderDate)

and you have an index on OrderDate you will find that even though the query optimiser could do a stream aggregate on the index because is sorted by date it doesn’t instead it does a hash aggregate.


What you would like is this,


Unfortunately to achieve this you have to have computed columns for year, month, day and possibly time and then index that (to provide total fidelity with the ideal solution).

alter table Sales.SalesOrderHeader add [OrderYear] as Year(OrderDate)

alter table Sales.SalesOrderHeader add [OrderMonth]as Month(OrderDate)

alter table Sales.SalesOrderHeader add [OrderDay]  as Day(OrderDate)

alter table Sales.SalesOrderHeader add [OrderTime] as cast(OrderDate as time)

This doesn’t perform because the index is much wider due to the year, month and day functions all returning integers, this means that your index key is 16 bytes compared to the only 8 for a normal datetime column. You might say well why not cast the columns to tinyint and smallint. Well while that reduces the size of the index, it then stops the optimiser from matching the index to the query.

This is not only about getting streaming aggregates its also about being able to filter based on years and years and months. i.e. a query such as

select OrderDate

from AdventureWorks2008.Sales.SalesOrderHeader

where Year(OrderDate) = 2001

should be able to have the appropriate seek predicate on the OrderDate index, rather than having to just have a predicate. Combining the two together one should be able to do

with summary as


    select year(OrderDate) yr, count(1) cnt

      from AdventureWorks2008.Sales.SalesOrderHeader

  group by year(OrderDate)


select *

  from summary

 where yr between  2001 and 2002

And have the optimiser perform a seek on the index and then a streaming aggregate.

I’ve created a suggestion for this. If you find you have queries like this that aggregate based on year or year and month then this suggestion will help you so please vote for it.

I’m building a little sync app to sync data between databases and came across this error. I thought it was a duff install but spent some time on it this morning.

Well the issue was that when you create a windows application in Visual Studio 2010 not only does it choose the .Net Client Profile it also selects x86 as the default target platform. As I am running on a x64 laptop I installed the x64 bits for Sync Services and so understandably the x86 version of the libraries is not installed

The solution is easy

Change your target platform to any or x64 for your application.(any should default to x64 on an x64 machine)

We’ve moved a few things around which has freed up some places on the Performance Monitoring and the Optimising BI Training Days at SQLBits 8.

SQL Server Performance Monitoring and Troubleshooting with Klaus Aschenbrenner

It's Monday, 10:30am. You are just receiving an email that informs you that your SQL Server has enormous performance problems! What can you do? How can you identify the problem and resolve it fast? Which tools provides you SQL Server for this task? In this workshop you will see you can do effective performance monitoring and troubleshooting with SQL Server. You will learn how to identify performance bottlenecks with tools and information provided by SQL Server and how to resolve the identified performance bottlenecks.Click To Register

Optimising Microsoft Business Intelligence with Thomas Kejser

Thinking about building a huge, TB sized cube? Loading millions of rows / second into your database? Maybe you just want to make your BI system run faster? In this pre-con session I will talk you through some interesting, high scale customer scenarios and the lessons learned from them. We will dig into both SSAS and SSIS, and show you how to get the most performance out of these products.

Click To Register

Posted by simonsabin | 1 comment(s)

I will be, as part of the SQL Connections conference, be delivering an advanced reporting services workshop.

If you register with the promotion code sql-bits, before the 31st March when the Super Early Bird offer ends you will only pay £639.20 +vat (full price £999)


To register go to   

I look forward to seeing you there

Posted by simonsabin | with no comments

If you are intending on coming to SQLBits in April the you have to register in the next few days to ensure you get a place. We are almost full and I expect we will be full in the next few days.

I hope you can make it and will see you there.

Posted by simonsabin | 1 comment(s)

SQL Server 2008 R2 brings us a great new feature of shared data sets. This allows datasets used by parameters and multiple reports to be defined in one place and used in multiple reports.

I’ve been developing a report for SQLBits that required use of sub reports. They all use a shared data set for the agenda items. Initially I was developing in report builder but have now gone back to BIDS. In doing this however all the reports bust reporting the following error

Data Retrieval failed for the subreport '###', located at ### Please check the log files

I’ve spent hours trying to figure out why. Often you get errors due to bad typed parameters or missing parameters however in this case all was correct.

Doing a search I found a thread and some connect items that state that you can’t use preview in BIDS when using sub reports and shared data sets.

If you find you are encountering this bug then please vote on the connect item.

Posted by simonsabin | 1 comment(s)
Filed under: