SQL Server 2008 R2 not freeing cached resources?
Ran into interesting scenario this afternoon when I was doing some house cleaning of my SQL Server 2008 R2 install. I had been bad last year, had 7 databases that were no longer in use, but hadn't detached them. Today I went through and detached them one by one, namely because I was running out of memory on my web server. I assumed as the databases were detached (one of them was actively being used all the way up to this morning) that it would gradually free cached queries that were no longer needed. To my surprise it didn't. Even after leaving only 2 databases, both only a few hundred rows it still hovered at the exact same memory usage. It took restarting the SQL Server service to bring it back down to 74mb.
Is there a reason for this design? If this had been a critical production environment I wouldn't have liked to have had to restart the entire service. Maybe Denali will "correct" this.
Content Incoming…
As you might have seen, content from 2002->2011 is being imported, should be wrapped up sometime this weekend. It's kind of a pain to extract from WordPress to WordPress, I might do what I did this summer with a custom script from my old SQL Server database backup.
NULL == NULL, in SQL Server that is never gonna happen
Learned a little "gotcha" yesterday when I was debugging a stored procedure that used coalesce with both a parameter and the column I was writing a conditional on.
I had something like the following:
CREATE PROCEDURE dbo.getPeopleSP
(@LocationID INT = NULL)
AS
SELECT
dbo.People.ID,
dbo.People.Name,
dbo.People.LocationID
FROM dbo.People
WHERE dbo.People.LocationID = COALESCE(@LocationID, dbo.People.LocationID)
Thinking that since the @LocationID was optional, the COALESCE would take care of the rest if @LocationID was NULL. This is NOT the case. In order do what I had wanted you have to do this:
CREATE PROCEDURE dbo.getPeopleSP
(@LocationID INT = NULL)
AS
SELECT
dbo.People.ID,
dbo.People.Name,
dbo.People.LocationID
FROM dbo.People
WHERE (@LocationID IS NULL OR dbo.People.LocationID = @LocationID)
Not a deal breaker for me, I wish there was an optional parameter in COALESCE to handle it the way you would think it would, NULL being equal to NULL.