|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Monday, March 15, 2010 8:54 PM
Points: 1,200,
Visits: 2,080
|
|
danielmanke (9/25/2009)
When I first restart the server, by default the max free space isd around 150Mb. As we work on the server and run the app the max free space decreases to about 25MB. I then restart the server using the switch (-g 384). I then measure the Max free space, and the value is still about 150Mb. Just curious as to what I need to do to increase not only total VAS reserved for CLR, but also the contiguous max free space size.
Daniel
As far as I know, you don't have control over contiguous max free space size.
You may need to further increase the VAS space allocated through the -g flag to get the space you need.
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Thursday, February 25, 2010 12:06 PM
Points: 25,
Visits: 111
|
|
I am using the -g switch from the command line starting sql server using the following syntax
NET START MSSQLSERVER -g512.
The server starts, but when I look in the log, the log reads:
Registry startup parameters
-d z:\Microsoft SQL Server\MSSQL\data\master.mdf
-e z:\Microsoft SQL Server\MSSQL\log\ERRORLOG
-l z:\Microsoft SQL Server\MSSQL\data\mastlog.ldf
And when I measure my max free space it is no larger then when I start without the -g switch...
It appears that the -g switch is not being applied. Am I using incorrect syntax? Does the -g switch log to the log file?
Thanks,
Daniel
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Thursday, February 25, 2010 12:06 PM
Points: 25,
Visits: 111
|
|
I have answered my own question... When using the NET START command, a / is used instead of a -.
So the correct syntax is NET START MSSQLSERVER /g512.
Once the correct syntax is used the /g startup parameter is logged with the other parameters.
Daniel
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, March 16, 2010 10:13 AM
Points: 1,310,
Visits: 1,228
|
|
Just to chime in somewhat late here, but a few things from this thread that stand out:
First DBCC FREESYSTEMCACHE('ALL') will unload CLR AppDomains from the VAS region and can generally alleviate VAS Fragmentation issues, although temporarily. The trade off is that you have to flush your other cache's, but if the alternative is to restart SQL, flushing caches occurs with the restart, at least by manually flushing the caches, you aren't having to wait for database/instance recovery to run.
Second, despite the fact that you have AWE enabled, you still have to be careful with the -g startup parameter because on 32bit SQL Servers your plan cache can't use AWE, it can only use VAS, so you will be robbing from your procedure cache which is already limited to being at most 1.6GB. Check the size of your procedure cache before commiting to any changes to -g, especially a 512MB one which is going to drop your max size for cache in VAS to 1.3GB roughly. If you have a lot of adhoc requests you might try enabling Forced Parameterization using ALTER DATABASE which will help reduce the sizing needs of the procedure cache and give you more space to tune the -g parameter.
Keep in mind that moving to 64bit is good for VAS extendability, but it also has its own little nusances as well. If you have an adhoc workload problem you may end up having issues on 64 bit with proc cache bloat which can starve your server of buffer cache space. If you have a true memory leak occuring in your CLR where its not disposing properly and objects are ending up in the 2nd and 3rd gen pools then you are only going to have worse problems on a 64bit server. You can see this by looking at your .NET counters for the sqlsrvr process on the SQL Server itself. 64 bit servers also suffer from TokenAndPermUserStore problems, amongst other things because they essentially have unlimited VAS, so the very component you like for bad SQLCLR implementations like converting all functions from TSQL to SQLCLR, can also stab you in the backside with other known problems under specific workloads and scenarios.
If you'd like deep details about how VAS/MTL calculations work see:
Understanding the VAS Reservation (aka MemToLeave) in SQL Server
Jonathan Kehayias
http://www.sqlclr.net
http://sqlblog.com/blogs/jonathan_kehayias
http://www.twitter.com/SQLSarg
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 5:08 AM
Points: 4,709,
Visits: 3,091
|
|
|
|
|
