Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-06-16 : 08:47:15
|
| Eduardo writes "Hi,I changed the value of the -g parameter for a MS SQL Server database in a machine with 4GB of memory.How can I check, after the server is up, if this parameter is into effect?Thanks," |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-16 : 17:52:24
|
| Also don't forget about the /PAE and /3GB switches.I would start a trace and record memory consumption.-g Specifies an integer number of megabytes (MB) of memory that SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. The memory outside of the memory pool is the area used by SQL Server for loading items such as extended procedure .dll files, the OLE DB providers referenced by distributed queries, and automation objects referenced in Transact-SQL statements. The default is 256 MB.Use of this option might help tune memory allocation, but only when physical memory exceeds the configured limit set by the operating system on virtual memory available to applications. Use of this option might be appropriate in large memory configurations in which the memory usage requirements of SQL Server are atypical and the virtual address space of the SQL Server process is totally in use. Incorrect use of this option can lead to conditions under which an instance of SQL Server may not start or may encounter run-time errors.Use the default for the -g parameter unless you see any of the following warnings in the SQL Server error log:"WARNING: Failed to reserve < X > bytes of contiguous memory.""WARNING: Failed to reserve contiguous memory of Size= 65536.""WARNING: Due to low virtual memory, special reserved memory used < X > times since startup. Increase virtual memory on server."These messages might indicate that SQL Server is trying to free parts of the SQL Server memory pool in order to find space for items such as extended stored procedure .dll files or automation objects. In this case, consider increasing the amount of memory reserved by the -g switch.Using a value lower than the default will increase the amount of memory available to the buffer pool and thread stacks; this may, in turn, provide some performance benefit to memory-intensive workloads in systems that do not use many extended stored procedures, distributed queries, or automation objects.-g This parameter will specify the amount of virtual address space that SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. What does that mean? This memory allocation outside of the SQL Server memory pool is used for loading times such as extended stored procedures, dll files, OLE DB providers referenced by queries, and automation objects reference in Transact-SQL statements. The default size of this memory is 128 MB, which is usually fine for most SQL Server installations. Keep in mind that SQL Server installations under 2 GB (SQL Server 2000 Standard Edition) or 3 GB (Enterprise Edition) will only use 128 MB no matter what size you set with the -g parameter. You can however, use this parameter to optimize the memory on installations above the 2 GB and 3 GB cutoffs. Microsoft recommends that you do not use this parameter unless you see the following error in your error log. WARNING: Clearing procedure cache to free contiguous memory However, I have used this and seen others use this parameter for errors such as: OLE DB provider 'SQLOLEDB' reported an error. The provider ran out of memory.Peter LarssonHelsingborg, Sweden |
 |
|
|
|
|
|
|
|
-g startup parameter