November 30, 2017 at 9:54 pm
We have a main analytics SP that calls 100 other sub SPs, all of which act upon the same set of data (Claims), check it for some business rules, and output discrepancies.
The SPs need not all be run sequentially, they can be broken out into sections, which are dependent on previous sections (sequential), but independent within the section (parallel).
After looking at many options like Service Broker, Agent Jobs, Batch Files, SSIS etc., I used this CLR code to parallelize the sections, and it gave great performance improvement.
However, when I run multiple (5, 10, 15) main SPs concurrently (each of which analyzes different claims) , performance starts to taper as concurrency increases. I guess this is because of the overhead of creating multiple parallel threads through the CLR. I also see lot of XTP_THREAD_POOL sessions idle in sp_who2.
Has anyone used CLR for parallelizing Stored Procedures in Critical OLTP Production workloads ?
Are there any best practices for performance tuning SQL CLR ?
Is there a threshold for number of parallel threads that can be opened before the overhead makes things worse ?
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply