Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #136 > unrolled thread

SQL Server 2008 Express: Odd Memory and Disk Behaviour

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  SQL Server 2008 Express: Odd Memory and Disk Behaviour Gene Wirchenko <genew@ocis.net> - 2011-04-06 17:06 -0700
    Re: SQL Server 2008 Express: Odd Memory and Disk Behaviour Erland Sommarskog <esquel@sommarskog.se> - 2011-04-07 08:21 +0000
      Re: SQL Server 2008 Express: Odd Memory and Disk Behaviour Gene Wirchenko <genew@ocis.net> - 2011-04-07 13:08 -0700
        Re: SQL Server 2008 Express: Odd Memory and Disk Behaviour Erland Sommarskog <esquel@sommarskog.se> - 2011-04-07 23:28 +0200
          Re: SQL Server 2008 Express: Odd Memory and Disk Behaviour Gene Wirchenko <genew@ocis.net> - 2011-04-07 18:15 -0700

#136 — SQL Server 2008 Express: Odd Memory and Disk Behaviour

Dear SQLers:

  1) The odd memory behavour is that SQLSERVR.EXE is taking about
600MB doing nothing!  Does it ever release memory?  Sure, I was doing
something yesterday.

  2) There are a lot of 368 byte files in C:\Documents and
Settings\LocalService\Application Data\Microsoft\Protect\S-1-5-19.  By
a lot, I mean millions.  By "millions", I mean exactly that.  This is
no exaggeration.  Defrag reports about 5.5M files on my system.   I
can not run chkdsk /f on my C: drive at boot and have it take less
than hours to run.

     What is going on, please?

Sincerely,

Gene Wirchenko

[toc] | [next] | [standalone]

#140

Gene Wirchenko (genew@ocis.net) writes:
>   1) The odd memory behavour is that SQLSERVR.EXE is taking about
> 600MB doing nothing!  Does it ever release memory?  Sure, I was doing
> something yesterday.

Yes, if someone else needs it. But SQL Server is designed to grab as much 
memory as it can. Reading data from the cache is so much faster than reading
from disk. 

You can use sp_configure set to the max amount of memory for the buffer 
pool. On a developer machine, this can be a good idea to avoid that SQL 
Server inflates too much so that other things you are not using for the 
moment are swapped out.
 
>   2) There are a lot of 368 byte files in C:\Documents and
> Settings\LocalService\Application Data\Microsoft\Protect\S-1-5-19.  By
> a lot, I mean millions.  By "millions", I mean exactly that.  This is
> no exaggeration.  Defrag reports about 5.5M files on my system.   I
> can not run chkdsk /f on my C: drive at boot and have it take less
> than hours to run.
 
Any particular reason you suspect SQL Server? Have you looked inside of 
these files?


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#143

On Thu, 7 Apr 2011 08:21:33 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>   1) The odd memory behavour is that SQLSERVR.EXE is taking about
>> 600MB doing nothing!  Does it ever release memory?  Sure, I was doing
>> something yesterday.
>
>Yes, if someone else needs it. But SQL Server is designed to grab as much 
>memory as it can. Reading data from the cache is so much faster than reading
>from disk. 

     The problem with this is that in this case, its usage pushed
memory use over the amount of my system's physical memory, so disk
ended up being read anyway.

>You can use sp_configure set to the max amount of memory for the buffer 
>pool. On a developer machine, this can be a good idea to avoid that SQL 
>Server inflates too much so that other things you are not using for the 
>moment are swapped out.

     Thank you for the tip.
 
>>   2) There are a lot of 368 byte files in C:\Documents and
>> Settings\LocalService\Application Data\Microsoft\Protect\S-1-5-19.  By
>> a lot, I mean millions.  By "millions", I mean exactly that.  This is
>> no exaggeration.  Defrag reports about 5.5M files on my system.   I
>> can not run chkdsk /f on my C: drive at boot and have it take less
>> than hours to run.
> 
>Any particular reason you suspect SQL Server? Have you looked inside of 
>these files?

     I have been able to find limited data on Google that fingers it.
Specifically, I believe it was the Reports module having something
about a cryptokey failing and retrying (and logging) every THREE
SECONDS.

     I did not find a solution there.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#145

Gene Wirchenko (genew@ocis.net) writes:
>>Gene Wirchenko (genew@ocis.net) writes:
>>>   1) The odd memory behavour is that SQLSERVR.EXE is taking about
>>> 600MB doing nothing!  Does it ever release memory?  Sure, I was doing
>>> something yesterday.
>>...
> 
>      The problem with this is that in this case, its usage pushed
> memory use over the amount of my system's physical memory, so disk
> ended up being read anyway.
 
How much memory do you have in your machine? Less than 600 MB? That's
difficult to believe these days.

If you want to say that other processes were pushed to disk, I believe
you, but normally SQL Server does not allocates so much memory that it
ends up on disk.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#147

On Thu, 07 Apr 2011 23:28:51 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>>Gene Wirchenko (genew@ocis.net) writes:
>>>>   1) The odd memory behavour is that SQLSERVR.EXE is taking about
>>>> 600MB doing nothing!  Does it ever release memory?  Sure, I was doing
>>>> something yesterday.
>>>...
>> 
>>      The problem with this is that in this case, its usage pushed
>> memory use over the amount of my system's physical memory, so disk
>> ended up being read anyway.
> 
>How much memory do you have in your machine? Less than 600 MB? That's
>difficult to believe these days.

     No, 1.5GB.

>If you want to say that other processes were pushed to disk, I believe
>you, but normally SQL Server does not allocates so much memory that it
>ends up on disk.

     Yes.  Right now, it has started (with boot) and is taking 45296K,
but I have not used it yet.  Dare I give it the opportunity to get
greedy?

     This may end up being irrelevant shortly.  I am getting a new
system now so I can run SQL Server 2011, oops, I mean SQL Server
codenamed Denali.  (It is amusing reading that on a Web page written
as if it were a real product name.)  I am still going to have to keep
using SSE 2008 at least for testing since the company I work for will
be dragged off XP kicking and screaming.  I share their sentiments,
but I am going to be doing technical review on a book on SS Denali.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web