September 15, 2009 at 1:49 am
Hey Daniel,
danielmanke (9/14/2009)
Unfortunately, the application is an off the shelf ERP and I have no control over the development and function of this code. At one time it was a t-sql code (which I could at least read to know what it was trying to do).
Are you saying that at one point in time, the ERP product used T-SQL, but now uses an assembly?
danielmanke (9/14/2009)
I was forced into converting to a C# assembly after migrating to sql server 2005. So I have to work with what they give me...
This is the bit that has confused me. Who wrote this C# assembly? I am intrigued to know what is calling what, and who wrote each bit!
danielmanke (9/14/2009)
I was able to test the -g switch. I believe it will give me more time until memory pressure, but it is not a solution. I am painted into the corner of restarting sql server on a periodic basis. I am hoping to restart only on the weekends.
If the component (whoever wrote it) simply requires slightly more virtual memory than it was getting previously, you may find you never get the problem again.
If it has a bug (like a memory leak) then it needs fixing! Please tell me as much as you can about the exact architecture here, and who is responsible for each bit.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 15, 2009 at 7:37 am
Paul White (9/14/2009)
Marios Philippopoulos (9/14/2009)
Sorry, my bad, I meant:
dbcc freesystemcache ('all')This will address the problem, while helping you avoid an instance restart.
Really sorry Marios, but still no go 🙂
Books Online
Executing DBCC FREESYSTEMCACHE clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachstore in the plan cache, the SQL Server error log will contain the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations." This message is logged every five minutes as long as the cache is flushed within that time interval.Generally speaking, there is no way to free up the so-called MemToLeave region (used by CLR) aside from restarting SQL Server.
It has worked for me though; I had the same problem as in this post, ran the DBCC statement and the issue was corrected (temporarily).
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 15, 2009 at 2:40 pm
Marios Philippopoulos (9/15/2009)
It has worked for me though; I had the same problem as in this post, ran the DBCC statement and the issue was corrected (temporarily).
Yeah - I've seen it 'work' temporarily too - but it's a huge sledgehammer to put a very small crack in a nut 🙂
Dumping the server's cache will free up some space in the multi-page region, but only if there are plans and stuff which are over 8KB (and so not allocated from buffer pool). So, I'll agree with that - but with the caveat that dumping the cache will provide only (very) temporary relief, and may seriously hurt a production server 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 15, 2009 at 2:42 pm
Paul White (9/15/2009)
Marios Philippopoulos (9/15/2009)
It has worked for me though; I had the same problem as in this post, ran the DBCC statement and the issue was corrected (temporarily).Yeah - I've seen it 'work' temporarily too - but it's a huge sledgehammer to put a very small crack in a nut 🙂
Dumping the server's cache will free up some space in the multi-page region, but only if there are plans and stuff which are over 8KB (and so not allocated from buffer pool). So, I'll agree with that - but with the caveat that dumping the cache will provide only (very) temporary relief, and may seriously hurt a production server 🙂
Agreed, and after your latest explanation, it is actually clearer to me why it worked that way.
BTW, it only bought me a few hours, and that only on a TEST server.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 16, 2009 at 6:17 am
Paul,
The program is called Datatel Colleague. It is an ERP for educational institutions. At one time, the code for functions was just that, functions written in tsql. Since migrating to sql server 2005, the vendor rewrote these functions (maybe 750 or so) in C# and placed them into 2 assemblies. The application's processes (things like payroll and a host of other complex reports and queries) call the various functions in these 2 assemblies. It is not 1 function, but many functions. I have no control over how the functions are called and used. I can only sit and watch as my Max free space, KB gets smaller and smaller until a process calls code that cannot be be loaded and executed. I then get the memory error as stated previous. The vendor has acknowledged that the code is memory intensive and could be written better, but the rewrite is still pending. While I appreciate the effort, at this point I am using the downtime during business hours to justify the purchase of 64 bit servers. My time would be much better spent planning the Database server move from 32 bit to 64 bit db servers...
Daniel
September 16, 2009 at 6:43 am
Good luck Daniel - and many thanks for taking the time to write such a useful and comprehensive reply.
I hope the guys with the chequebooks see sense.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 22, 2009 at 9:08 am
danielmanke (9/14/2009)
Unfortunately, the application is an off the shelfERP and I have no control over the development and function of this code. At one time it was a t-sql code (which I could at least read to know what it was trying to do).
string SQL = "Select [sys].[assemblies].[name]," +
" [content] " +
"From [sys].[assemblies] " +
" Inner Join " +
" [sys].[assembly_files] " +
" On [sys].[assemblies].[assembly_id] = [sys].[assembly_files].[assembly_id] " +
" And [file_id] = 1;";
using (SqlConnection conn = new SqlConnection(myConnectionString))
{
conn.Open();
using (SqlCommand sqlCmd = new SqlCommand(SQL, conn))
{
using (SqlDataReader sdr = sqlCmd.ExecuteReader())
{
while (sdr.Read())
{
long fieldLength = sdr.GetBytes(1, 0, null, 0, 0);
// Now set the local variable buffer to be a byte[] array of the relevant length
byte[] buffer = new byte[fieldLength];
// Retrieve the bytes from the data reader into the newly declared buffer
fieldLength = sdr.GetBytes(1, 0, buffer, 0, ((int)(fieldLength)));
// and write the content of the assembly to it
using (FileStream fs = new FileStream(@"C:\" + sdr.GetString(0) + ".dll", FileMode.Create))
{
using (BinaryWriter bw = new BinaryWriter(fs))
{
bw.Write(sa.Content);
}
}
}
}
}
}
Syntax may be wrong, the forum posting text box doesn't do very good code completion... Reflector is your friend.
edit -> PS - remember to fill in 'myconnectionstring'
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 22, 2009 at 9:46 am
That is interesting... I will try at first opportunity. Another quick question...
When I use the -g switch, the max free size is not increasing. I am assuming it is because any new (additional above default) space reserved for CLR is not contiguous. Is there a way to maximize this contiguous space, or do I get what I get by default?
Thanks,
Daniel
September 24, 2009 at 2:17 pm
danielmanke (9/22/2009)
That is interesting... I will try at first opportunity. Another quick question...When I use the -g switch, the max free size is not increasing. I am assuming it is because any new (additional above default) space reserved for CLR is not contiguous. Is there a way to maximize this contiguous space, or do I get what I get by default?
Thanks,
Daniel
The max free size should increase after instance restart.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 25, 2009 at 6:42 am
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
September 25, 2009 at 11:01 am
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.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 29, 2009 at 6:28 am
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
September 29, 2009 at 6:56 am
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
October 2, 2009 at 2:45 pm
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 | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
October 3, 2009 at 7:12 am
It's probably worth clarifying that 64-bit SQL Server does not have a 'MemToLeave' region - see http://blogs.msdn.com/psssql/archive/2009/08/26/come-on-64bit-so-we-can-leave-the-mem.aspx
You might also like to read this: http://blogs.msdn.com/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx since it covers some poorly-documented issues pretty thoroughly.
The 'downsides' Jonathan mentions to having plentiful VAS on 64-bit aren't really bad things at all. Yes you can get ad-hoc plan cache bloat, and the token and perm store can go a bit squirrelly, but both have solutions which are very much less dramatic than FREESYSTEMCACHE('ALL').
CLR is so potentially problematic on 32-bit that I personally don't even consider it any more 🙂
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply