EXEC dbo.LongTermMemory__Archive

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

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.

Msg 9514, Level 16, State 1, Line 1
Xml data type is not supported in distributed queries. Remote object 'OPENROWSET' has xml column(s).

Tables that have xml columns cannot be queried, even if the query accesses non-xml columns of the table.

This statement is not entirely true, when querying only non-xml fields it seems to work just fine.


Repro script (SQLCMD mode)

:CONNECT myServer

use myDB
GO
CREATE TABLE myXml
(ID int, someXml xml)
GO

:CONNECT myRemoteServer

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=myServer;Trusted_Connection=yes;',
'SELECT ID from myDB.dbo.myXml') -- should work just fine
GO

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=myServer;Trusted_Connection=yes;',
'SELECT ID, someXml from myDB.dbo.myXml') -- raises the above mentioned error
GO

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=myServer;Trusted_Connection=yes;', 'SELECT ID, CAST(someXml as varchar(max)) from myDB.dbo.myXml') -- workaround?
GO

If I find an explanation for this limitation I will post an update but I haven't figured out why this is not possible yet.