SQL Express client memory usage differs to SQL Enterprise client memory usage

Question

I am experiencing a big difference in .Net application memory usage using the same app against two copies of the same database. The only difference is that in scenario 1 I am using a local copy of the database registered to an instance of SQL Server 2005 Express - and in scenario 2 I am using a remote copy of the database registered to an instance of SQL Server 2008 Enterprise.

To my knowledge, I would only expect a difference in the SQL performance and SQL memory usage (since Express has a 1GB limit).

But - what I see is an enormous difference (1GB) of memory usage between them - i.e. the SQL Express scenario using 1GB more of memory mostly. SQL Express also seems to be much slower particularly working with big tables and large queries - but I would expect this memory hit to be in SQL and not on my consuming/client application???

The app connects to SQL server using System.Data.SqlClient.SqlConnection and carries out frequent SqlCommand and SqlBulkCopy operations.

Any helpful thoughts would be very much appreciated!

Answer 1

For you second question about slow express on big tables and queries, that is normal because express version is consuming more memory and disk than enterprise version. The Enterprise version is using a feature called Enhanched-Read-ahead and Scan(a.k.a Merry-go-round scans),which has enormous difference in performance of large queries.

For example:

Assume that UserA and UserB both issue the SELECT * FROM Customer command, which results in a table scan. UserA issues the command first; UserB issues the command 20 seconds later. The table has 100 million rows (it's a very big table), and the scan is running on a machine that has non Enterprise Edition installed. The table scan for UserA begins reading the Customer table on the first page of the table. Twenty seconds later, the scan for UserB starts reading pages that UserA has already read, even though some of the pages might already have been flushed back out of cache. Sometimes this process creates tremendous disk contention and draws on memory.

The Enterprise Edition performs an Enhanced Read-ahead and Scan a little differently. Unlike with the non Enterprise Edition versions, when UserA issues the SELECT * FROM Customer command, the Enterprise Edition begins scanning the Customer table. When UserB issues the same command 20 seconds later, the table scan for UserB starts exactly where UserA is currently reading. UserA and UserB's queries will both read the last page of the Customers table at about the same time, but then UserB's scan will go back to the beginning of the Customer table to scan the pages that UserA had scanned before UserB began its query. This process dramatically reduces disk contention and the draw on memory.