Load Testing Proc.

Question

Load Testing Proc.

bryan99y

SSCarpal Tunnel

Points: 4672
More actions
September 14, 2002 at 12:56 am

#65062

Does anyone have anything to add to this?
Comments for Load Testing would be helpful also.
/*
------
Load Testing - with Transactions per second calculation.
Created: 09-14-2003
------
------
To obtain best results, Load Testing should be done in an isolated environment. Then these results can be compared a shared database environment.
All Performance Monitoring tools(Profiler, Performance Monitor, etc) should be ran on client machines to lower the load on the database derver. Prolier data should be ran in File Collection Mode and later dumped to a table for analysis.
Profiler Events Classes for this Load Test:
Objects:
Closed
Created
Deleted
Opened
Stored Procedures:
SP:CacheMiss
SP:CacheHit
SP:Completed
SP:Starting
TSQL:
SQl:BatchCompleted
SQL:BatchStarting
SQL:StmtCompleted
SQL:StmtStarting
-------
Call Example: LOAD_TEST 5001
-------
-------
Force I/O: (Thus all Load Tests, with the same row insertion and no changes(Schema,
Hardware, etc, should have the same output)
DBCC DROPCLEANBUFFERS
- Removes all clean buffers from the buffer pool.
DBCC FREEPROCCACHE
- Removes all elements from the procedure cache.
(Can remove the above DBCC's to check the results with Data & Procedure Cache holding data and the actual stored procedure, execution plan, etc)
--------
--------
***Set these to OFF before run Load Test.***
If ON, the "MESSAGES" Tab in results pane will show information for each statement ran. Costly here. Will cause the Transactions per second to go down.
Can be good for a one time check of I/O, CPU, etc.
SET STATISTICS TIME OFF
- displays the number of milliseconds required to parse, compile, and execute each statement.
SET STATISTICS IO OFF
- display information regarding the amount of disk activity generated by
Transact-SQL statements
--------
*/
CREATE PROCEDURE Load_Test
@No_Rows int
AS
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME LIKE 'Begin_Date')
DROP TABLE Begin_Date
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME LIKE 'Test_Transaction')
DROP TABLE Test_Transaction
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
CREATE TABLE Begin_Date
(
begindate DateTime PRIMARY KEY
)
CREATE TABLE Test_Transaction
(
Col1 int PRIMARY KEY,
Col2 DateTime,
Col3 Money
)
DECLARE @begindate DateTime
DECLARE @i int
SET @begindate = GETDATE()
INSERT INTO Begin_Date(begindate)
VALUES(@begindate)
SET @i = '1'
WHILE (@i < @No_Rows)
BEGIN
INSERT INTO Test_Transaction
VALUES(@i,'10-31-2001',96189576)
-- or variables where the values are
SET @i = @i + 1
CONTINUE
END
DECLARE @total_transactions int
SET @total_transactions = (SELECT COUNT(*) FROM Test_Transaction)
DECLARE @transactions_per_sec INT
DECLARE @datediff1 INT
SET @datediff1 = (SELECT DATEDIFF(SECOND, begindate, GETDATE()) FROM Begin_Date)
SET @transactions_per_sec = @total_transactions/@datediff1
SELECT @transactions_per_sec AS 'Transactions per second ', @total_transactions AS 'Total Transactions ', @datediff1 AS 'Test Time - seconds '

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Herve Roggero Ten Centuries Points: 1230 More actions · Answer 1

Just one thing:

It seems that your transactions/sec calculation include the Count(*) from the Test_Transaction table, which I am assuming is not something you want to measure other that for the metrics calculation. Depending on the number of records you are inserting, this may have a serious impact on your metrics.

Initial Code:

DECLARE @total_transactions int

SET @total_transactions = (SELECT COUNT(*) FROM Test_Transaction)

DECLARE @transactions_per_sec INT

DECLARE @datediff1 INT

SET @datediff1 = (SELECT DATEDIFF(SECOND, begindate, GETDATE()) FROM Begin_Date)

SET @transactions_per_sec = @total_transactions/@datediff1

Consider: