THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance impact: Try/Catch blocks in SQLCLR – a follow up

My previous post showed a simple test that appears to suggest that you may experience significant performance degradation if multiple users are calling the same SQLCLR function at the same time and they are all catching a lot of exceptions.

 

However, it’s not clear whether that behavior is limited to SQLCLR or applies to .NET in general. To see if I would run into similar behavior, I wrote a simple C# program for a quick test. To simulate the concurrent exception-handling load, the test program spawns 10 background threads, each calling the following method nonstop (the complete program is listed at the end of this post):

 

    static void TryCatch()

    {

        int c = 0;

 

        for(int i = 0; i < 200000; i++)

        {

            try { c= i/c; } catch (Exception) { c = 1;}

        }

    }

 

Note that variable c is assigned value 0, thus forcing a divide-by-zero exception, and this exception is handled 200,000 times in a loop.

 

The elapsed time of the method is measured both when there is no additional background threads and when 10 additional threads are running.

 

On my old two-core 2GB PC workstation, the following output from the program is typical among many runs:

 

C:\junk>test2.exe

TryCatch() without any background thread = 15617968 ticks

TryCatch() without any background thread = 15559616 ticks

TryCatch() without any background thread = 15566064 ticks

TryCatch() without any background thread = 17472496 ticks

TryCatch() without any background thread = 15782952 ticks

Thread 0 Called TryCatch() 1000 times.

Thread 1 Called TryCatch() 1000 times.

Thread 2 Called TryCatch() 1000 times.

Thread 3 Called TryCatch() 1000 times.

Thread 4 Called TryCatch() 1000 times.

Thread 5 Called TryCatch() 1000 times.

Thread 6 Called TryCatch() 1000 times.

Thread 7 Called TryCatch() 1000 times.

Thread 8 Called TryCatch() 1000 times.

Thread 9 Called TryCatch() 1000 times.

TryCatch() with 10 background threads = 17498336 ticks

TryCatch() with 10 background threads = 17535984 ticks

TryCatch() with 10 background threads = 17664424 ticks

TryCatch() with 10 background threads = 17515200 ticks

TryCatch() with 10 background threads = 17465312 ticks

TryCatch() with 10 background threads = 17498432 ticks

TryCatch() with 10 background threads = 17508656 ticks

TryCatch() with 10 background threads = 17710856 ticks

^C

 

At least for this test, the adverse concurrency impact that we saw with SQLCLR--and reported in the previous post--is not observed.

 

Although it’s not strictly an apple-to-apple comparison between this test without SQLCLR and that described in the previous post with SQLCLR, the end user experience is so different that it calls into question why SQLCLR does not seem to handle many concurrent exceptions as gracefully. I don’t have an answer.

 

I have absolutely no knowledge of how SQLCLR works internally, and can’t explain the concurrency behavior observed in the previous post.

 

By the way, when I set variable c to 1 in the TryCatch() method, thus avoiding the exception, its concurrency impact (or the lack of) did not change much, if at all.

 

Anyway, here is the test program. For the output presented above, the program was compiled with .NET Framework 3.5.

 

using System;

using System.Diagnostics;

using System.Threading;

 

public partial class Test

{

 

    public static void Main()

    {

        Stopwatch stop_watch = new Stopwatch();

 

        // warming up a bit

        for(int i = 0; i < 5; i++)

        {

            TryCatch();

        }

 

   // measure the elaped time without any additional background threads

        for(int i = 0; i < 5; i++)

        {

            stop_watch.Reset();

            stop_watch.Start();

            TryCatch();

            stop_watch.Stop();

            Console.WriteLine("TryCatch() without any background thread = {0} ticks",

                                stop_watch.ElapsedTicks);

 

            Thread.Sleep(2000);

        }

 

        Thread[] user_threads = new Thread[10];

 

        for(int i = 0; i < 10; i++)

        {

            user_threads[i] = new Thread(new ThreadStart(StartTryCatch));

            user_threads[i].Name = i.ToString();

            user_threads[i].IsBackground = true;

            user_threads[i].Start();

            Thread.Sleep(10);

        }

 

        Thread.Sleep(5000);

 

        // now measure the elaped time again with 10 additiona threads running

        for(int i = 0; i < 20; i++)

        {

            stop_watch.Reset();

            stop_watch.Start();

            TryCatch();

            stop_watch.Stop();

            Console.WriteLine("TryCatch() with 10 background threads = {0} ticks",

                                stop_watch.ElapsedTicks);

 

            Thread.Sleep(2000);

        }

 

        // this will never be reached. You have to Cltr-C to stop the program

        for (int i = 0; i < 10; i++)

        {

            if (user_threads[i] != null)

            {

                user_threads[i].Join();

            }

        }

    }

 

    static void StartTryCatch()

    {

        int i = 0;

 

        while (true)

        {

            if (i % 1000 == 0)

            {

                Console.WriteLine("Thread {0} Called TryCatch() 1000 times.", Thread.CurrentThread.Name);

            }

 

            TryCatch();

            Thread.Sleep(10);

 

            i++;

        }

 

    }

 

    static void TryCatch()

    {

        int c = 0;

 

        for(int i = 0; i < 200000; i++)

        {

            try { c= i/c; } catch (Exception) { c = 1;}

        }

    }

};

 

Published Friday, July 08, 2011 12:48 AM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Christian said:

Hi Linchi,

What would happen if you moved the exception handling to T-SQL?

Does it still impact performance as much?

ie. if SQLCLR fails, T-SQl will catch it, and send a different value to the SQLCLR.

For my specific case, I use a CLR proc to login to a webservice. The webservice gives me a session and on subsequent calls I re-use this session, but if the session is expired, I need exception handle to catch it and re-login.

July 8, 2011 1:19 PM
 

Linchi Shea said:

Hi Christian;

For the issue I was discussing, it probably would change the nature of the discussion to move the exception handling out of the SQLCLR code.

I should really stress that if you use exception handling spareingly in SQLCLR, you should be fine, at least I didn't see any issue. But if you look at your SQLCLR code paths, and there is a path that may have to catch exceptions thousands upon thousands of times, you may have a problem, and could you have a serious problem if the code path may be exercised by multiple users.

July 8, 2011 1:35 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement