Friday, 24 April 2009

SQL 2008 SP1 - a must have!

Some time ago I installed SQL 2008 on a VPC which was also running MOSS and PerformancePoint.  Soon enough the VPC started to run out of memory.  I tore my hair out for a few days because nothing I did made a good dent in the problem.  


Till I found the silver bullet.

Consistently SSAS was high on the list of memory-hungry services - plateauing around the 500Mb mark.  Yeah, that's a lot.  With only a couple of cubes and no queries running it did seem to be hugely excessive. 

What to do?  Install SP1.

That's it.

Usage dropped down to somewhere around 30-80Mb.  Much more memory friendly!

Interestingly, of the many bug fixes included, in SP1 (full list),  a memory leak in SSAS is not one of them! 

Thursday, 9 April 2009

TSQL: Truncating Datetime

It crops up all the time.  You have a datetime column.  In your select, you don't care about the time, you only care about the date because you want to group all records for a particular date, for example.  So what's the quickest way to go about it?


Well let's start with the options...
  1. CONVERT
  2. FLOOR
  3. DATEADD/DATEDIFF
If you are using SQL 2008, you could add another option and CAST to DATE.  Perfect if the recipient of the data will accept it.  I'm gonna assume however, as is most likely at this point in time, that you aren't using SQL 2008 yet as that does seem to still be the case in most organisations.  I'm also going to assume that you need to return a datetime.

So, to option 1: CONVERT.

A popular choice because it's easily readable.  Let me give you an example.

CAST((CONVERT(varchar(11),getdate(),120)) as DATETIME)
This will return 2009-04-09 00:00:00.000

Option 2:  FLOOR

CAST(FLOOR(CAST(Getdate() as FLOAT)) as DATETIME)
Returns 2009-04-09 00:00:00.000

Obviously faster than convert because we aren't working with any strings.

Option 3: DATEADD

DATEADD(dd, DATEDIFF(dd, 0 getdate()), 0)
Returns 2009-04-09 00:00:00.000 (shock horror!)

Guess what?  This version is even faster again because we don't have any conversion to do at all.

To what degree are these options faster?

Well, a quick test by plonking a million records into a temp table which contains only a datetime column showed my execution times to be fairly similar but my CPU times  were increasingly quicker.

CONVERT - 1591
FLOOR - 434
DATEADD - 382

The convert is horrible obviously and should never be considered even at the expense of readability.  Clearly there's not a vast difference between FLOOR and DATEADD but I guess I like the DATEADD method for it's lack of conversion as well as it's performance gain, however small.  It just seems cleaner to me.