THE SQL Server Blog Spot on the Web

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

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

CloudSeeder: CLR Stored Procedures For Creating CPU Pressure

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.

Published Tuesday, October 23, 2012 4:49 PM by Adam Machanic
Filed under: , , ,

Attachment(s): cloudSeeder.sql

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

 

D said:

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

November 23, 2013 9:00 PM
 

Adam Machanic said:

@D

Use a monitoring tool.

November 25, 2013 11:38 AM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

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