C# CLR performance

Question

Post reply

C# CLR performance

Viewing 15 posts - 16 through 30 (of 44 total)

You must be logged in to reply to this topic. Login to reply

Tomas Bergvall SSC Enthusiast Points: 113 More actions · Answer 1

-> HowardW

Yes, the SQL server is 64bit

Doing the calculations in .Net does not remove parallelism since you can expicitly specify that in the code.

HowardW One Orange Chip Points: 29109 More actions · Answer 2

Tomas Bergvall (4/18/2011)
Doing the calculations in .Net does not remove parallelism since you can expicitly specify that in the code.

You can only multi-thread if you're running your CLR as UNSAFE - even then, it's highly advised against.

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 3

Terminology gents...internal vs. external multi-threading perhaps?...in this case .NET multi-thread != SQL Server parallellism...different scopes. Using a CLR method does not prevent SQL Server from employing parallelism in a query plan where a CLR method is involved.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

HowardW One Orange Chip Points: 29109 More actions · Answer 4

This is specifically in relation to a CLR procedure, not a function. I don't believe SQL Server has any opportunity to parallelise execution of a CLR Procedure. How would it know whether it was safe to do so, what would it parallelise?

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 5

Agreed on procs, I think it is seen as a black box. It's not like T-SQL where it can be broken down by statement. Just throwing it out there in case it cleared the air on terminology.

<edit>regarding terminology, "method" = "function" in my posts...my .NET to T-SQL speech adapter goes on the fritz at times</edit>

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 6

Tomas Bergvall (4/18/2011)
-> opc.three
I'm not entirely sure of how to view the MemToLeave setting but I tried this post http://www.johnsansom.com/index.php/2009/03/sql-server-memory-configuration-determining-memtoleave-settings/#axzz1JsE4avf6
and got the following result from the SQL. Is it possible to view that setting in another way?
Total avail mem, KBMax free size, KB
8442427040 6574744000

MemToLeave is the memory allocated within the SQL Server memory space that is dedicated to non-native SQL Server activities...things like extended stored proc, Linked Server and CLR activities. It can be increased using the -g startup parameter.

http://msdn.microsoft.com/en-us/library/ms190737.aspx

I suspect that all the heavy lifting you're doing in your CLR proc is causing you to eat up the non-SQL Server memory in a hurry, causing that process to slow down and swap. Have you checked your SQL Server error log for odd entries related to the CLR? Try increasing the -g setting (the default if 256MB) in 64MB increments and see what that does. Note that your startup parameters may not contain a -g entry by default so you may be adding one. There is a link on how to set them within the article I linked to above. (gets soapbox) Just remember that every MB you allocate for MemToLeave is a MB SQL Server cannot use to cache pages used to serve query results which will lead to more swapping on your system.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Tomas Bergvall SSC Enthusiast Points: 113 More actions · Answer 7

I tried setting MemToLeave in increments without any difference in execution time. I even tried setting it to 2GB without noticing any performance gains.

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 8

Tomas Bergvall (4/19/2011)
I tried setting MemToLeave in increments without any difference in execution time. I even tried setting it to 2GB without noticing any performance gains.

In your earlier post you showed what your VAS looked like this per the John Sansom query...but the numbers look too high...unless you showed us bytes, not kilobytes.

Total avail mem, KB Max free size, KB

-------------------- --------------------

8442427040 6574744000

I will assume those numbers are bytes, not kilobytes, otherwise it means you have 8.4 terabytes of VAS free on your server which is unlikely 🙂 Either that or my math is off...either way please let us know. If these are bytes, when translated to GB that's 8GB of VAS.

I think what is happening is that your instance is starting off with 2GB of VAS free (after your latest change) and in the course of your process running SQL Server has to work to de-allocate ~6GB of buffer pool space and allocate it to the MemToLeave space. That is likely a very expensive since anything in the buffer pool it wants to take to give to MemToLeave it has to flush to disk.

Can you restart this shared instance at-will?

If you have the time and inclination I would proceed as:

1) Set or leave -g at 2GB.

2) Restart the instance.

3) Immediately run the VAS query and record the result.

4) Start your process.

5) Run the VAS query every 30 seconds while your process is running and record the result.

6) Run the VAS query after the process has finished and record the result.

If you see the VAS rise throughout the processing, and end up with ~8GB free after your process is complete it will prove my theory and you'll know you need to start with a -g setting of 8GB or more in order to avoid the swapping and buffer pool re-allocation.

A quicker test would be to run your process a second time and see if it is any faster. The first time you ran it you would have incurred the expense of the buffer pool re-allocation so the second run may go faster.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

HowardW One Orange Chip Points: 29109 More actions · Answer 9

opc.three (4/19/2011)
Tomas Bergvall (4/19/2011)
I tried setting MemToLeave in increments without any difference in execution time. I even tried setting it to 2GB without noticing any performance gains.
In your earlier post you showed what your VAS looked like this per the John Sansom query...but the numbers look too high...unless you showed us bytes, not kilobytes.
Total avail mem, KB Max free size, KB
-------------------- --------------------
8442427040 6574744000
I will assume those numbers are bytes, not kilobytes, otherwise it means you have 8.4 terabytes of VAS free on your server which is unlikely 🙂 Either that or my math is off...either way please let us know. If these are bytes, when translated to GB that's 8GB of VAS.

That's entirely consistent with a 64 bit system. The 2GB limit is based on 32 bit architecture.

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 10

Here is why it looks odd to me. If he truly has 8,442,427,040 KBs of VAS free that translates to ~8 Terabytes. Is my math wrong? Or am I being tripped up by something specific to 64-bit?

DECLARE @kilobytes DECIMAL(12, 2),

@megabytes DECIMAL(12, 2),

@gigabytes DECIMAL(12, 2),

@terabytes DECIMAL(12, 2) ;

SELECT @kilobytes = 8442427040,

@megabytes = @kilobytes / 1024.0,

@gigabytes = @megabytes / 1024.0,

@terabytes = @gigabytes / 1024.0 ;

SELECT @kilobytes AS kilobytes_avail,

@megabytes AS megabytes_avail,

@gigabytes AS gigabytes_avail,

@terabytes AS terabytes_avail ;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

HowardW One Orange Chip Points: 29109 More actions · Answer 11

No, it really is 8TB. VAS != available memory on the server.

64 bit systems can address 2^64 bytes of memory, but as this is hugely above any likely scale to be reached, it's capped to 8TB.

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 12

Tomas Bergvall (4/8/2011)
That database server is really fast (120GB RAM and 24 Intel Xeon 2,4GHz CPU's).

Per the original post the DB server in question has 120GB physical RAM.

That's beside the point though, the VAS query is not supposed to show the max amount of memory available on the server, or even the amount allocated to SQL Server, it is supposed to show the amount of allocated VAS within the SQL Server process.

Tomas, can you please clarify what numbers were posted?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

HowardW One Orange Chip Points: 29109 More actions · Answer 13

Sorry OPC, it really is 8TB and is on all 64-bit versions of SQL Server - MemtoLeave is simply irrelevant on a 64-bit version - unless it's been messed around with, it's always massively more than can be physically be required. Try it on your own system if you have any 64-bit servers.

Also refer here:

http://msdn.microsoft.com/en-us/library/ms187499.aspx

All SQL Server editions: Up to process virtual address space limit:
7 terabytes on IA64 architecture
8 terabytes on x64 architecture

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 14

I see the issue now...I assumed the query was telling us something specific about the section of VAS not allocated to the buffer pool, as set by using the -g startup parameter. On 32-bit systems I think the query holds true to that end...but not on 64-bit systems. We really need a query that reports the amount of VAS not allocated to the buffer pool. That said, do you think the theory about the CLR process needing to grab additional memory causing SQL Server to have to shrink the buffer pool to do it holds water?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 15

Bob B said -g has no effect on 64-bit systems. Anyone else have a theory?

http://www.sqlservercentral.com/Forums/Topic1095860-391-1.aspx

Tomas, have you (re)considered writing a process in T-SQL to replace your CLR proc? 😀

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato