Home Uncategorized CloudSeeder: CLR Stored Procedures For Creating CPU Pressure

    CloudSeeder: CLR Stored Procedures For Creating CPU Pressure

    475
    2

    Sometimes, in the interest of testing various scenarios that your server might encounter, it’s useful to be able to quickly simulate some condition or another. I/O, memory, CPU pressure, and so on.

    This latter one is something I’ve been playing with a lot recently. CPU pressure in SQL Server creates all sorts of interesting side-effects, such as exacerbating waits and making various other conditions much easier to reproduce.

    In order to make this simpler, I’ve created the attached CLR library. This is a simple pair of stored procedures:

    startSeeding takes two arguments: The first parameter is the number of threads you’d like to create, and the second is a bit, indicating whether or not the threads should be affinitized. If they are, the threads will run preemptively, and the CPU pressure will be of the external sort. If they are not affinitized, the pressure will be of the internal sort, and you’ll be able to see SQL Server scheduler contention. Of course you can run the procedure two or more times and create a mix of threads, if that’s what you’d like to do.

    stopSeeding takes no arguments. It attempts to cancel all of the threads that have been started.

    “Installing” the script is easy. Create a database on a server that has CLR enabled. Make the database trustworthy to bypass CLR security restrictions. And then run the script to create the objects. The code was compiled for .NET 3.5, so this should work on either SQL Server 2008, 2008R2, and 2012.

    Be careful about how many threads you create. It’s amazing how quickly things can get out of hand. Start small. Trust me on this.

    On that note, I make absolutely no guarantees that this won’t crash your server, or worse. As a matter of fact, that’s kind of the point!

    Enjoy, and let me know if you have any feedback, feature requests, or whatever.

    File Attachment: cloudSeeder.zip

    Previous articleThinking Big (Adventure)
    Next articleCapturing Attention: Writing Great Session Descriptions
    Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.

    2 COMMENTS

    1. But How we will get a notification alert when CPU is consumed more than 90 %. Is there any way to do so. Please help…

    Comments are closed.