Oops I got tagged

I got tagged by Bart Bultinck, so here it goes... 5 things you may or may not know about me

1. My nickname dis4ea comes from a documentary about coffee, disphoria is extreme unhappiness and people who are addicted to coffee might suffer from it when they lack coffee.
2. One of my grandmothers was actually Dutch and my father lived in The Netherlands during the first years of his life.
3. My brother is a system engineer, my father worked as a manager in the technical department of a computer hardware retailer and my mother worked as a sales representative in a computer hardware retailer.
4. I don't drink alcohol either (respect Bart) and it is indeed very difficult to convince people that you really don't...
5. My favorite instrument is the guitar and I love all kinds of music where the guitar is the important instrument (especially blues). My favorite artists are Stevie Ray Vaughan, Joe Satriani, Buddy Guy, Jimi Hendrix and Metallica.

My turn to tag:

Bregt
NickVDA
Killspid
Sven Cipido
General

Big Procedure Cache in SQL Server 2005

As I once mentioned in a post I felt that the procedure cache was really out of proportion. We were assured that this was not an issue but now I stumbled upon the following post where they explain that the behavior has been seriously changed in SP2.

Align SQL Server 2005 plan cache size limit to a size similar to that of SQL Server 2000: The maximum size limit of the SQL Server 2005 Plan Cache in RTM and SP1 is significantly larger than in SQL Server 2000. To reduce the SQL Server 2005 plan cache maximum size limit to a size similar to that of SQL Server 2000, the limit for signaling internal memory pressure on a cache is changed from that of SQL Server 2005 RTM and SP1. The following table shows how the maximum limit of the plan cache is determined for each version of SQL Server:

SQL Server 2005 RTM & SP1: 75% of server memory from 0-8GB + 50% of server memory from 8Gb-64GB + 25% of server memory > 64GB

SQL Server 2005 SP2: 75% of server memory from 0-4GB + 10% of server memory from 4Gb-64GB + 5% of server memory > 64GB

Example:
For a SQL Server with 32Gb total SQL server memory,
SQL Server 2005 RTM and SP1 cachestore limit will be 75% X 8 + 50% X (32 - 8) = 18GB

SQL Server 2005 SP2 cachestore limit will be 75% X 4 + 10% X (32-4) = 5.8GB

I suppose I was not the only one who thought this was just too much. I love the way SQL Server is self tuning for a great part but maybe it would be nice to allow expert users to configure some aspects themselfs anyway, especially with x64 where memory usage is lifted to a whole new dimension. When looking at the fixlist for Service Pack 2 I am getting more and more convinced to wait for it before installing SQL Server 2005 in large production environments.

Troubleshooting Query Performance Issues Related to Plan Cache in SQL 2005 RTM and SP1

The SQL Programmability team have started a series of posts that everyone - using SQL Server 2005 - should read (not for the faint of heart though).

They could help you isolate performance issues very fast and we all know this can be the difference between success or catastrophic failure.