In this survey I'd like you to run some code and then send me the results (and I'm sure someone will put together a PowerShell script to make it easy to run on multiple instances).

I want to know how much of your precious server memory is being wasted storing empty space on data file pages. I'm sure you'll be interested in it too!

Here's the code (works on 2005+):

SELECT
    COUNT (*) * 8 / 1024 AS MBUsed,
    SUM (CONVERT (BIGINT, free_space_in_bytes)) / (1024 * 1024) AS MBEmpty
FROM sys.dm_os_buffer_descriptors;
GO

[Edit: forgot that many systems have way more memory than my 16GB laptop, so updated code above uses BIGINT now.

And here's a PowerShell script courtesy of Nic Cain, assuming server list in c:\temp\serverlist.txt:

$SQLQuery = 'SELECT
COUNT (*) * 8 / 1024 AS MBUsed,
SUM (CONVERT (BIGINT, free_space_in_bytes)) / (1024 * 1024) AS MBEmpty
FROM sys.dm_os_buffer_descriptors;
GO'

$servers = get-content c:\temp\serverlist.txt
foreach ($srv in $servers)
{
invoke-sqlcmd -Server $srv -Database master -Query $SQLQuery
}

/Edit]

Send me the results for as many systems as you can, preferably production systems - either as a comment on this post, in email, or in a spreadsheet in email.

I'll collect all the results and explain what this means, how to drill in further, and what you can do about it in a week or two.

Thanks!