Databases Reference
In-Depth Information
SET nocount ON ;
DECLARE @i INT = 0 ;
WHILE ( @i < 100 )
BEGIN
EXEC tempdbdemo.dbo.usp_temp_table ;
SET @i += 1 ;
END ;
The
usp
_
temp
_
table
stored procedure creates a table in tempdb with three columns and a unique
clustered index on Column 1. The table is then populated with 10 rows. The
usp
_
loop
_
temp
_
table
stored procedure runs the
usp
_
temp
_
table
procedure 100 times.
To simulate multiple users trying to run the same procedure at the same time, I'm going to use a tool
called
OStress,
which is part of a download called RML Utilities.
NOTE
At the time of writing, RMLUtilities has a dependency on SQL Native 10
which comes with the SQL Server 2008 client tools. Fortunately, you can just
download it for free from here and install it alongside SQL Server 2012:
http://
Microsoft SQL Server 2008 R2 Native Client).
RMLUtilities can be found here:
http://www.microsoft.com/en-us/
For the purpose of the demo I'm just going to use OStress very simply to run the
usp
_
loop
_
temp_table
procedure using 300 connections. The aim is to simulate 300 people
running a stored procedure that recursively calls another stored procedure 100 times.
OStress needs to be run from the command prompt:
C:\"Program Files\Microsoft Corporation"\RMLUtils\ostress -Schristianvaio\NTK12 -E
-Q"EXEC demo.dbo.usp_loop_temp_table;" -ooutput.txt -n300
Of course,
christianvaio\NTK12
is my SQL Server instance name, so change it to your own if you're
following along.
While OStress is running, take a look at the
sys
.
dm
_
os
_
waiting
_
tasks
DMV using the following
script, reproduced here with the kind permission of Robert Davis (
http://www.sqlsoldier.com/
WITH TASKS
AS (SELECT session_id,
wait_type,
wait_duration_ms,
blocking_session_id,
resource_description,
PageID = Cast(Right(resource_description, Len(resource_description)-
Charindex(':', resource_description, 3)) As Int)
From sys.dm_os_waiting_tasks
Where wait_type Like 'PAGE%LATCH_%'
And resource_description Like '2:%')
Search WWH ::
Custom Search