So last week, I explained different ways to generate concurrent activity. I have my own favorites, but you may have your own. So pick your favorite method; whichever method you picked, it will help you call the procedure
Now comes the task of defining the procedure
s_DoSomething. It can be whatever you like depending on what functionality you want to test or exercise. I want to demonstrate some patterns that I follow when I define that procedure. Those patterns all start with …
the Basic Definition
To test the concurrency of a single procedure just call it:
use tempdb GO CREATE PROCEDURE dbo.s_DoSomething AS EXEC Adventureworks2012.dbo.uspGetManagerEmployees 14;
Of course, I could have changed the framework to call my procedure directly but I don’t out of habit. I always leave
tempdb hard-coded in the framework.
With Arbitrary Parameter Values
Often the procedures I want to test are defined with parameters. If variety is important, but the parameter values are not, then that’s when the random tricks come in:
ALTER PROCEDURE dbo.s_DoSomething AS DECLARE @someString nvarchar(100) = cast(newid() as nvarchar(100)); DECLARE @someInt int = RAND() * 100; DECLARE @someDate datetime = dateadd(MINUTE, RAND() * 10000, getdate()); DECLARE @someLongerString nvarchar(1000) = REPLICATE(@someString,20); EXEC Adventureworks2012.dbo.usp_ProcWithParameters @someString, @someInt, @someDate, @someLongerString;
With Less Arbitrary Parameter Values
Check out this next example. Pulling a value from the target database is often preferable to calling the procedure with a random integer value.
ALTER PROCEDURE dbo.s_DoSomething AS DECLARE @BusinessEntityId int; SELECT TOP 1 @BusinessEntityId = BusinessEntityID FROM AdventureWorks2012.HumanResources.Employee ORDER BY newid(); EXEC AdventureWorks2012.dbo.uspGetEmployeeManagers @BusinessEntityId;
Calling More Than One Procedure
It’s as simple as calling one after the other. But sometimes I want the frequency of the calls “weighted”.
For example, I want to have a DELETE, INSERT and UPDATE statements called 10% of the time each. The remaining 70% of the time I want to call a SELECT statement. Then I have something like:
ALTER PROCEDURE dbo.s_DoSomething AS declare @r int = RAND() * 10; IF (@r = 0) -- delete 10% of the time DELETE AdventureWorks2012.Person.BusinessEntity WHERE BusinessEntityID = CAST(RAND()*1000 as INT); IF (@r = 1) -- insert 10% of the time INSERT AdventureWorks2012.Person.BusinessEntity (rowguid) VALUES (newid()); IF (@r = 2) -- update 10% of the time UPDATE AdventureWorks2012.Person.BusinessEntity SET rowguid = newid() WHERE BusinessEntityID = CAST(RAND()*1000 as INT); IF (@r > 2) -- select the rest of the time SELECT BusinessEntityId, rowguid, ModifiedDate FROM AdventureWorks2012.Person.BusinessEntity WHERE BusinessEntityID = CAST(RAND()*1000 as INT);
Counting Errors Concurrently
I want to track (server side) how often
s_DoSomething fails. But I don’t want tracking to be a concurrency bottleneck itself. Here’s a cool trick for that:
First define these procedures:
create procedure dbo.s_IFailed as go create procedure dbo.s_IPassed as go alter procedure dbo.s_DoSomething as begin try declare @i int = rand() * 10; select @i = 1 / @i -- might divide by 0! exec dbo.s_IPassed; end try begin catch exec dbo.s_IFailed; end catch
This lets me use DMVs to monitor the success rate because I can check the execution count of my dummy procedures. For example,
exec sp_recompile 'dbo.s_IFailed'; -- reset counts from other tests. exec sp_recompile 'dbo.s_IPassed'; go /* run concurrent test here, or...*/ set nocount on; exec dbo.s_DoSomething; go 10000 select object_name(object_id), execution_count from sys.dm_exec_procedure_stats where object_name(object_id) in ('s_IFailed','s_IPassed') /* -- count s_IPassed 9031 s_IFailed 969 */
This relies on the DMV
sys.dm_exec_procedure_stats which was introduced in 2008. It’s like a cheap do-it-yourself, performance counter.
Next week I want to show a demo. I want to show this technique in action. I’ll be troubleshooting a common concurrency problem, the deadlock.