4

We are floating a new scalable and performance-centric ASP.Net application in our organization which is currently in design phase.

The managers have decided taking a hybrid approach. They have decided to use CLR stored procedures extensively while T-SQL only for simple data manipulations.

I would like to know the following from the community:

  1. Whether there can be performance issues at a later stage with so many CLR stored procedures?
  2. If you had any performance issues, was there a fix available from the MS team?

Note: I did a google and found following most probable issues, haven't found a resolution for the first two though.

  1. When SQL Server loads assemblies, they are cached in memory. When the O/S signals memory pressure to SQL Server, explicit garbage collection may be run, and the assemblies may be unloaded. This can cause performance issues if it happens frequently.

  2. SQL CLR code cannot be costed accurately by the query optimiser, as it does not look at what the code actually does – this can affect execution plans.

  3. SQL CLR code can sometimes prevent parallelism as they are usually single threaded. Sometimes this can hurt performance. << Although I found a solution to this here Multi-threaded code in CLR Stored Procs? >>

Let me know of issues and fixes with CLR stored procedures.

Community
  • 1
  • 1
prabhats.net
  • 444
  • 6
  • 17
  • 1
    Just one word of advice: do **data manipulation** tasks like selecting or updating data in **T-SQL** - it's much better (in every respect, including performance) than using SQL CLR and C# to do the same task. Limit SQL CLR to do the things T-SQL sucks at - calling external web services, extensive and advanced string and data manipulation and stuff like that. Just because you **can** iterate over a table and update each row-by-agonizing-row in a SQL CLR stored procedure doesn't mean **you should** .. – marc_s Mar 29 '12 at 20:33

1 Answers1

2

I have done CLR assemblies for SQL server. It was painful experience. There are many limitations you can use in such kind of CLR project. For example, it would be impossible to use third-party libraries and Open source libraries. Some libs cannot just be deployed to SQL server. You only have a limited libraries you can use.

The second issue is the deployment and permission settings. If you have DBA have very restricted policy for a database, you just cannot deploy your CLR assemblies to the database.

My advice is to write your database components as libraries for your ASP.Net. It is much easy and without limitations like CLR assembly for SQL server.

In 2008, I wrote a serials blogs on this issue: SQL Sever Projects (1-4). You may use them as your reference.

David.Chu.ca
  • 37,408
  • 63
  • 148
  • 190