Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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!

share|improve this question
    
Is this a rich client app? All that differs is the connection string? –  Rikalous May 17 '13 at 10:32
    
Almost, it's a .Net application (not a GUI app, but a service) which connects to SQL. And yes, the only difference between the two test scenarios is the connection string ... –  Jackfruit May 17 '13 at 10:46
    
How are you measuring the memory usage? Is it consuming loads of memory in one hit or a gradual increase over time? –  Rikalous May 17 '13 at 10:48
    
Every minute I write the private memory usage to a file. Over the course of 24 hours it peaks frequently at 150-250Mb in scenario 1 and peaks at the same frequency at 1000Mb in scenario 2. –  Jackfruit May 17 '13 at 11:09
    
You might want to get hold of a profiler (e.g. RedGate Memory profiler) to get some more detail on what is going on –  Rikalous May 17 '13 at 11:23

1 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.

share|improve this answer
2  
The key point here is that enterprise will "eat" all memory unless you specify a max size in the config. The product assumes it is the only application running on a server and configures itself appropriately –  Hogan Jul 4 '13 at 16:00
    
Yes,but another key point for large tables is enterprise feature called marry-go-round(as explained in answer :) )! –  yoodaa Jul 4 '13 at 16:11
    
Which has nothing to do with the OP's question. –  Hogan Jul 4 '13 at 17:50
    
There are way to much differences. Also most likely the local instance will use shared memory over tcp ip. But the main point has already been discussed here. –  Octavian Feb 8 '14 at 21:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.