Performance tuning for CLR?

chrisrdba 381 Reputation points
2022-05-04T15:27:26.707+00:00

Greetings. The developers are just starting to dabble in CLR in our environment, and I need to understand how to do performance tuning.

I can grab the plan handle from the cache, but when I try to use that in sys.dm_exec_query_plan it doesn't return anything.

I get why, it totally makes sense, but what's the alternative? How is performance tuning done on CLR? Shockingly I cant find anything on google about this.

Thanks!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,298 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 106.3K Reputation points
    2022-05-04T21:47:22.947+00:00

    I disagree with Tom. As long as you can get over the hurdle with the security handling, the CLR sounds like the perfect choice here. SSIS is one more moving part. And if it stops running for some reason, you would not notice until someone screams about the missing data. If the CLR method starts failing you get error messages up front.

    I'm not sure why you would need to performance tune this thing. It sounds like something that should be plain and swift. At least the part you have control over. The external API is obviously out of our control.

    If the C# code would actually prove to be slow, I think the best would to do the performance tuning outside SQL Server in a standalone program, and then bring back any improvements to SQL Server.


1 additional answer

Sort by: Most helpful
  1. Tom Phillips 17,721 Reputation points
    2022-05-04T15:35:32.737+00:00

    CLRs are external applications. There is no difference between performance tuning any external application and a CLR.

    I would HIGHLY recommend against creating CLRs. What exactly is your CLR doing?