Click here to monitor SSC
Alex Kuznetsov

Close Those Loopholes: Stress-Test those Stored Procedures

03 February 2008

You can write a stored procedure that tests perfectly in your regression tests. You will hand it to the tester in the smug certainty that it is perfectly bug-free. Dream on, for without stress-testing you could easily let some of the most unpleasant bugs through. Alex continues his excellent series, by showing how to catch those subtle problems.

This is the fourth article in the series, Close These Loopholes in Your Database Testing. It is worth reading them in sequence.

Finding those more subtle problems

In some cases, stored procedures will work perfectly as long as there is no concurrency, but then fail intermittently in a more realistic multi-user environment. I will explain this in more detail, as well as provide some examples, below. In this article I shall demonstrate a few simple techniques which will allow you to make sure that your stored procedures work in a multi-user environment. This is done by emulating real life concurrency in a test harness written in C# using NUnit.

Of course, there are many ways to run stress tests. I have chosen to use C# and NUnit, because it allowed me to reuse my code to the fullest, and it allowed me to incorporate my stress tests into my unit test harness, which is written in C# using NUnit. Anyway, the main point of this article is to encourage you to do stress testing, and the choice of tools to implement tests is less important. How you do your stress testing is up to you – what works best for me might not be the best choice for you.

Note: Strictly speaking, stress tests are different from unit tests. Unit tests are supposed to run in exactly the same way every time they run. On the other hand, stress tests usually do not have to provide identical results every time they run. If your stress test is done right, and if the module being tested has problems,  then most likely the stress test will expose the problems, but not in exactly the same way every run.

Prerequisites.

In order to emulate concurrency, you would need to issue queries at the same time from at least two connections, so the test harness needs to provide these two connections. You also have  to guarantee that your two connections are going to issue queries simultaneously for a considerable time. This is why the test harness needs to do its best to ensure that queries from different connection actually run simultaneously. The following simple class StressTester meets both requirements, and it can be reused several times - it will be used twice later in this article. To use StressTester, you need to provide two implementations of the following interface:

    public interface IStressTestable

    {

        //this method makes sure everything is ready to go

        //before the stress testing starts

        void Prepare(SqlConnection connection);

        //this method actually does the stress testing

        void Run();

    }

 

The StressTester class itself is quite short:

    public class StressTester : IDisposable

    {

        private readonly SqlConnection _connection1;

        private readonly SqlConnection _connection2;

        private readonly IStressTestable _runner1;

        private readonly IStressTestable _runner2;

 

        public StressTester(string connectionString,

           IStressTestable runner1, IStressTestable runner2)

        {

            _connection1 = new SqlConnection(connectionString);

            _connection1.Open();

            _connection2 = new SqlConnection(connectionString);

            _connection2.Open();

            _runner1 = runner1;

            _runner2 = runner2;

            _runner1.Prepare(_connection1);

            _runner2.Prepare(_connection2);

        }

 

        private static bool AnotherConnectionStartedUp(

               SqlConnection connection)

        {

            SqlCommand command = connection.CreateCommand();

            command.CommandText = "Readers.WaitForAnotherConnection";

            command.CommandType = CommandType.StoredProcedure;

            object obj = command.ExecuteScalar();

            return (obj != null);

        }

 

        public void RunConcurrently()

        {

            Thread t1 = new Thread(

                new ThreadStart(delegate

                               {

                               if (AnotherConnectionStartedUp(_connection1))

                                        {

                                            _runner1.Run();

                                        }

                               }));

            Thread t2 = new Thread(

                new ThreadStart(delegate

                               {

                                if (AnotherConnectionStartedUp(_connection2))

                                        {

                                            _runner2.Run();

                                        }

                               }));

 

            t1.Start();

            t2.Start();

            t1.Join();

            t2.Join();

        }

 

        public void Dispose()

        {

            _connection1.Close();

            _connection2.Close();

        }

    }

 

The important piece here is the AnotherConnectionStartedUp method, which detects whether another connection is ready to start stress testing, as should be clear from the following source code:

CREATE PROCEDURE Readers.WaitForAnotherConnection

AS

BEGIN TRAN

 

DECLARE @otherSpid INT, @d DATETIME, @i INT

 

SELECT @i = COUNT(*) FROM Data.Hook1 WITH(HOLDLOCK)

SELECT @otherSpid = NULL, @d = DATEADD(second, 10, Getdate())

WHILE @otherSpid IS NULL AND Getdate()<@D BEGIN

      select      @otherSpid = req_spid

      from master.dbo.syslockinfo

      where   rsc_objid = OBJECT_ID('dbo.Hook1')

                  AND req_spid <> @@SPID

END

WAITFOR DELAY '00:00:00.050'

COMMIT

SELECT @otherSpid otherSpid

GO

 

It is essential to do this detection: If you don’t, then  one thread may complete its task before the other one starts, which would render your stress test completely useless.

Using the Test Harness

Use the harness: have a select become a deadlock victim.

Now that the harness is ready, it is time to use it. In this section I will utilize it to reproduce a deadlock. A typical textbook example of a deadlock involves two connections modifying different rows then attempting to modify rows already modified and locked by another connection. However, there are other occasions that are likely to lead to deadlocks.  In some cases a query which only reads data without modifying it may still become a deadlock victim;  you will see it for yourself soon.

The following stored procedure issues SELECTs in a loop (it is assumed, and essential, that snapshot isolation is not enabled):

ALTER PROCEDURE Readers.SampleReader

AS

declare @i int, @j int, @c varchar(1000)

select @i=0, @j = 0

set nocount on

set deadlock_priority low

while @i < 10000 begin

  select @c = filler from a1 where j = 119

  SET @i = @i + 1

end

 

Another stored procedure issues modifications in a loop:

ALTER PROCEDURE Writers.SampleWriter

AS

declare @i int, @j int

select @i=0, @j = 0

set nocount on

set deadlock_priority high

while @i < 10000 begin

  update a1 set j = -j where k = 119

  SET @i = @i + 1

end

Using StressTester harness to run these two stored procedures simultaneously is very simple:

    internal class StoredProcedureRunner : IStressTestable

    {

        private SqlCommand _command;

        private readonly string _storedProcedureName;

        private bool runSucceeded = false;

 

        public StoredProcedureRunner(string storedProcedureName)

        {

            _storedProcedureName = storedProcedureName;

        }

 

        public bool RunSucceeded

        {

            get { return runSucceeded; }

        }

 

        public void Prepare(SqlConnection connection)

        {

            _command = connection.CreateCommand();

            _command.CommandText = _storedProcedureName;

            _command.CommandType = CommandType.StoredProcedure;

        }

 

        public void Run()

        {

            try

            {

                _command.ExecuteNonQuery();

                runSucceeded = true;

            }

            catch (Exception e)

            {

                Console.WriteLine(e);

            }

        }

    }

 

    [TestFixture]

    public class DeadlockTests

    {

        readonly string connectionString = "server=(local);trusted_connection=true;database=StressTests";

 

        [Test]

        public void DeadlockTest()

        {

            StoredProcedureRunner runner1 = new StoredProcedureRunner("Writers.SampleWriter");

            StoredProcedureRunner runner2 = new StoredProcedureRunner("Readers.SampleReader");

            using (

                StressTester tester =

                    new StressTester(connectionString, runner1, runner2)

                )

            {

                tester.RunConcurrently();

            }

            //verify that both procedures completed execution

            //all the way to the end

            Assert.IsTrue(runner1.RunSucceeded);

            Assert.IsTrue(runner2.RunSucceeded);

        }

    }

 

Most likely the Readers.SampleReader will be a deadlock victim. If it does not happen, then you should increase the number of iterations, Let me repeat myself: there is no guarantee that you will reproduce a deadlock, but there is a very high probability that you will reproduce it. Come to think of it: if you run your SELECT only once, and run your update only once, the chance that they will embrace in a deadlock is very low. By increasing the number of iterations, you increase the chance that your processes will embrace in a deadlock. In my recent experience, 10K iterations is more than enough under most circumstances. Depending on your environment, you might need a different number.

 

Use the harness: stress test generation of unique numbers.

In the first article of this series, I provided three stored procedures which were supposed to generate unique numbers, and stated that only one of them actually works in a concurrent environment. In this chapter I will stress test all three so that you can see for yourself that two of them have problems. Here is the one that is correct:

CREATE PROCEDURE Writers.GetNextCounter

AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

UPDATE Data.Counter SET Counter = Counter + 1

SELECT Counter FROM Data.Counter

COMMIT

 

And here is the test:

    internal class UniqueNumbersLoopReader : IStressTestable

    {

        private SqlCommand _command;

        private int[] numbers = new int[10000];

        private int currentIndex = 0;

        private int errors = 0;

 

        public void Prepare(SqlConnection connection)

        {

            _command = connection.CreateCommand();

            _command.CommandText = "Writers.GetNextCounter";

            _command.CommandType = CommandType.StoredProcedure;

        }

 

        public void Run()

        {

            for(int i=0;i<numbers.Length; i++)

            {

                try

                {

                    numbers[currentIndex++] = Convert.ToInt32(_command.ExecuteScalar());

                }

                catch(Exception e)

                {

                    Console.WriteLine(e);

                    errors++;

                }

                if(errors > 10)

                {

                    break;

                }

            }

            Console.WriteLine(string.Format("{0} completed, {1} failed", currentIndex, errors));

            _command.CommandText = "INSERT data.UniqueNumbers(n, spid) VALUES(@i, @@spid)";

            _command.CommandType = CommandType.Text;

            _command.Parameters.AddWithValue("@i", 0);

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

            {

                _command.Parameters[0].Value = numbers[i];

                _command.ExecuteNonQuery();

            }

        }

    }

 

    [TestFixture]

    public class UniqueNumbersLoopReaderTests

    {

        readonly string connectionString = "server=(local);trusted_connection=true;database=StressTests";

 

        [Test]

        public void UniqueNumbersTest()

        {

            using (SqlConnection connection = new SqlConnection(connectionString))

            {

                connection.Open();

                SqlCommand command = connection.CreateCommand();

                command.CommandType = CommandType.Text;

                command.CommandText = "TRUNCATE TABLE Data.UniqueNumbers";

                command.ExecuteNonQuery();

                command.CommandText = "UPDATE Data.Counter SET Counter = 0";

                command.ExecuteNonQuery();

                using (

                    StressTester tester =

                        new StressTester(connectionString, new UniqueNumbersLoopReader(), new UniqueNumbersLoopReader()))

                {

                    tester.RunConcurrently();

                }

                //verify that you are getting the expected results

                command.CommandText = "SELECT COUNT(DISTINCT n) FROM Data.UniqueNumbers";

                int numbersCount = Convert.ToInt32(command.ExecuteScalar());

//verify that all tests succeeded

                Assert.AreEqual(20000, numbersCount);

                command.CommandText = "SELECT SUM(isDifferent) FROM (SELECT CASE WHEN u.spid = (SELECT TOP 1 u1.spid FROM Data.UniqueNumbers u1 WHERE u1.n < u.n ORDER BY n DESC) THEN 0 ELSE 1 END AS isDifferent FROM Data.UniqueNumbers u) t";

                int numberSwitches = Convert.ToInt32(command.ExecuteScalar());

//verify that threads ran simultaneously

 

                Assert.IsTrue((numberSwitches > 10000),string.Format("Not enough switching: {0}", numberSwitches));

            }

        }

 

    }

 

In my Run method, you’ll have noticed that I finish stress testing before I start saving the unique numbers. This is essential: when you are stress testing, concentrate on it and do nothing else but stress test. Anything that can wait should be postponed until the stress testing is finished.

The second assertion (verify that threads ran simultaneously) needs some explanations. If one thread completed selecting its 10000 numbers before the second one started, the query would return 1. If two threads were always taking turns in acquiring unique numbers, then one thread would get all odd numbers, and the other would get all even ones - in that case the query would return 19999. Typically, if both threads run more or less simultaneously, the query returns at least 10000. (Let me repeat myself: the method StressTester.AnotherConnectionStartedUp is supposed to ensure that both threads do indeed run simultaneously, and it typically does ensure that.) So the second assertion verifies that both threads actually ran simultaneously.

Your mileage may vary. Depending on your hardware you might need some other threshold, not necessarily 10000.

After you have verified that you are actually getting unique consequent numbers, change the stored procedure:

ALTER PROCEDURE Writers.GetNextCounter

AS

-- this approach provides duplicates

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT Counter + 1 AS Counter FROM Data.Counter

UPDATE Data.Counter SET Counter = Counter + 1

GO

Now rerun the test. This time you will get duplicates, and the test will fail. You can inspect the Data.UniqueNumers table and see the duplicates. Change the stored procedure again:

ALTER PROCEDURE Writers.GetNextCounter

AS

-- this approach provides deadlocks

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

UPDATE Data.Counter SET Counter = Counter + 1

SELECT Counter FROM Data.Counter

COMMIT

GO

 

Rerun the test. This time you will get deadlocks.

Postscript

As you have seen, stress testing is an easy way to emulate real life concurrency and expose any problems in your stored procedures.  In the next article I will demonstrate how to reproduce common database-related errors and how to unit test error handling.

Alex Kuznetsov

Author profile:

Alex Kuznetsov has been working with object oriented languages and databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. Alex has published multiple articles on simple-talk.com and sqlblog.com and wrote a book entitled Defensive Database Programming with SQL Server. Currently he works in an agile team in Chicago. In his leisure time, Alex prepares for and runs ultramarathons.

Search for other articles by Alex Kuznetsov

Rate this article:   Avg rating: from a total of 25 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Cannot reproduce your SampleReader deadlock
Posted by: Arne (not signed in)
Posted on: Monday, February 11, 2008 at 7:41 PM
Message: Alex,

I cannot reproduce your SampleReader deadlock. I increased number of iterations, but that did not help. Do you have any clue?

Subject: A few suggestions
Posted by: Alex Kuznetsov (view profile)
Posted on: Tuesday, February 12, 2008 at 10:10 AM
Message: Arne,

A few suggestions:
1. Make sure read committed snapshot is not enabled.
2. Try running on a box with more CPUs. My development box has four dual core CPUs, which is good enough to run 2-3 threads of stress testing and a local instance of Developer's Edition SQL Server 2005.
3. Try running against a server running on another box.

Subject: Excellent article
Posted by: Joe Webb (view profile)
Posted on: Wednesday, February 13, 2008 at 2:16 PM
Message: Good stuff, Alex. Thanks.

Joe

Subject: Good idea
Posted by: puzsol (view profile)
Posted on: Thursday, February 14, 2008 at 10:57 PM
Message: Ok, so I'm totally missing the point of the testing procedure, but in my opionion, if you are coding this procedure correctly, you don't need a transaction.

ALTER PROCEDURE Writers.GetNextCounter
AS
DECLARE @Ctr int
UPDATE Data.Counter SET @Ctr = Counter, Counter = Counter + 1
SELECT @Ctr as Counter
GO






Subject: I simplified all the examples as much as possible.
Posted by: Alex Kuznetsov (view profile)
Posted on: Friday, February 15, 2008 at 9:12 AM
Message: Puzsol,

Fair enough, but to keep the article simple and easy to understand, I simplified all the examples as much as possible. GetNextCounter was developed using only the simplest SQL, because I did not want any distractions from my main point. Similarly, this is why in these examples there are connection1 and connection2, runner1 and runner2 - in real life there are arrays of connections and IStressTestables.

Subject: I don't see how Readers.SampleReader and Writers.SampleWriter can deadlock.
Posted by: RichardHowells (view profile)
Posted on: Sunday, November 30, 2008 at 7:20 AM
Message: Ok – I’m going to nail my colors to the mast.

I see how it’s possible for Readers.SampleReader and Writers.SampleWriter to deadlock.

Here is my reasoning. Each has only a single atomic SQL statement. These statements may take locks for the duration of their execution, but those locks will not be held after the statement is completed. Therefore – if one of them has a lock that blocks the other, the other will wait until the lock releases. They CANNOT reach the situation where both are already holding a lock and trying to obtain a resource locked by the other.

This seems really fundamental to me. If I have it wrong and they CAN deadlock then I need to understand why, so I can go and change some code out there in production. Someone please educate me.

Subject: Whoops
Posted by: RichardHowells (view profile)
Posted on: Sunday, November 30, 2008 at 11:01 AM
Message: Second para above s/b...

I DON'T see how it’s possible for Readers.SampleReader and Writers.SampleWriter to deadlock.

What a difference a word makes. :-(

Subject: Stress testing with NUnit
Posted by: dananos (view profile)
Posted on: Thursday, December 09, 2010 at 10:54 AM
Message: Here is a handy wee function to call a function multiple times at once:
http://networkprogramming.wordpress.com/2010/12/09/stress-testing-functions-from-nunit/

Helped uncover some nasty 'heisenbugs' ...

Subject: Stress testing with NUnit
Posted by: dananos (view profile)
Posted on: Thursday, December 09, 2010 at 10:55 AM
Message: Here is a handy wee function to call a function multiple times at once:
http://networkprogramming.wordpress.com/2010/12/09/stress-testing-functions-from-nunit/

Helped uncover some nasty 'heisenbugs' ...

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.