Prev: Distributed Transaction on SQL 2005
Next: Getting a LockMatchID error using sp_rename
From: DoleBludger on 14 Jul 2010 10:40
erver windows 2003 sp1. 64 bits,

Paging file size for selected drive C is (custom selected) Initial 2046 MB,
and maximum 4092 MB. It is currently allocated 4081 MB. Computer RAM memory
is 8GB.

Server functions as a database MS SQL 2005 server, and it is used under
heavy insert database operations

The problem is that it can be seen on windows task manager there is on PF
usage and Page File Usage hHstory around 8.2 GB paging file size not 4081 GB
as it is given on virtual memory page set up.
From: Erland Sommarskog on 14 Jul 2010 18:08
DoleBludger (DoleBludger(a)discussions.microsoft.com) writes:
> erver windows 2003 sp1. 64 bits,
>
> Paging file size for selected drive C is (custom selected) Initial 2046
> MB, and maximum 4092 MB. It is currently allocated 4081 MB. Computer RAM
> memory is 8GB.
>
> Server functions as a database MS SQL 2005 server, and it is used under
> heavy insert database operations

Is SQL Server 64-bit as well?

> The problem is that it can be seen on windows task manager there is on
> PF usage and Page File Usage hHstory around 8.2 GB paging file size not
> 4081 GB as it is given on virtual memory page set up.

As I recall, PF Usage is really how much virtual memory you use. On an
SQL Server box it should not exceed the physical memory.

Have you set "max server memory" for SQL Server? With 8GB, I would set
it to 6GB.


--
Erland Sommarskog, SQL Server MVP, esquel(a)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
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: DoleBludger on 16 Jul 2010 04:13
MS SQl 2005 is installed on the 64 bit server.

"By default, SQL Server can change its memory requirements dynamically based
on available system resources. The default setting for min server memory is
0, and the default setting for max server memory is 2147483647. "

That is my set up - default. You recommended 6 GB. There is only one
application on my server and that is MS SQL database, which is with a heavy
load of transactions. What is the maximum value to be selected then?

I think that is commit charge.

"Commit charge is a term used in Microsoft Windows operating systems to
describe the total amount of virtual address space for which the backing
store is the pagefile. It may be thought of as the maximum potential pagefile
usage."

....


"The Windows Task Manager utility, in its Performance tab, shows three
counters related to commit charge:

Total is the amount of pagefile-backed virtual address space in use, i.e.,
the current commit charge. This is comprised of main memory (RAM) and disk
(pagefiles). The corresponding performance counter is called "Committed
Bytes".
Limit is the maximum possible value for Total; it is the sum of the current
pagefile size plus the physical memory available for paging (this excludes
RAM that is assigned to non-pageable areas). The corresponding performance
counter is called "Commit Limit".
Peak is the highest amount that the total commit charge has reached since
the computer was last rebooted"
>

Still I did not understand quite well.
From: Erland Sommarskog on 16 Jul 2010 04:49
DoleBludger (DoleBludger(a)discussions.microsoft.com) writes:
> MS SQl 2005 is installed on the 64 bit server.

That does not answer the question whether you have 32-bit or 64-bit
SQL Server.

> "By default, SQL Server can change its memory requirements dynamically
> based on available system resources. The default setting for min server
> memory is 0, and the default setting for max server memory is
> 2147483647. "
>
> That is my set up - default. You recommended 6 GB. There is only one
> application on my server and that is MS SQL database, which is with a
> heavy load of transactions. What is the maximum value to be selected
> then?

Microsoft recommends to leave the setting as it is. And there certainly
is an advantage with that: if you add more memory to the box, you don't
have to remember to change the setting. But in practice, there may be
some funky behaviour if your don't set the max server memory. I don't
really have hard facts to back it up - call it superstition if you like.



--
Erland Sommarskog, SQL Server MVP, esquel(a)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
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: DoleBludger on 19 Jul 2010 04:56


Here is the answer: 9.00.1399.06 RTM Standar (64 xbit)
 |  Next  |  Last
Pages: 1 2
Prev: Distributed Transaction on SQL 2005
Next: Getting a LockMatchID error using sp_rename