Disaster Recovery Performance Testing

[diebels]

Hello,

I have a really odd performance problem with MSSQL. My staff is trying to load test our SQL server in a disaster recovery effort.

We are using the SQL Northwind DB as a test, and our script is inserting a random amount of rows.

The performance problem is that the amount of time to insert the random records linearly increases as the script runs.

For example:

Run 1: Time to insert 400 rows: 60 ms
Run 2: Time to insert 800 rows: 100 ms
Run 3: Time to insert 200 rows: 120 ms

.
.
.

Run N-1: Time to insert 10 rows: 3230 ms
Run N: Time to insert 1000 rows: 3403 ms

Has anyone seen a situation like this, or can anyone shed any light as to why MSSQL would behave like this?

If you need problem clarification, just ask. I can even post the procedure on here it's so simple.

Best,
Diebels

 

[tlbroadbent]

I would like to see the procedure. I have some ideas about the performance you've documented but need to see the procedure in order decide which idea best fits your scenario. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[diebels]

Here's the procedure that we're using to do our load testing. Note that we are interested in write performance only.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


ALTER proc p_load_test_NW (@max_cnt int) as

--
-- Pass in maximum loops.
-- Select random number * 10000 and load that many records.
-- increment counter, dump tran, continue while loop until untrue.
-- Record ms lapse between each load.
--
declare @cnt smallint, @recs smallint, @dt1 datetime, @dt2 datetime, @rows int

select @cnt = 0
while (@cnt < @max_cnt)
begin
select @recs=rand()*10000
set rowcount @recs
select @dt1 = getdate()
insert into TEST_FOO select * from Orders
select @rows=@@rowcount
select @dt2 = getdate()
select 'Time lapse in Milliseconds = '+convert(varchar,datediff (ms, @dt1,@dt2))+' for '+convert(varchar,@rows)+' records.'
select @cnt = @cnt + 1
dump tran SANTEST with no_log
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


Best,
Diebels

 

[tlbroadbent]

Add a Begin Transaction statement before the Insert and a Commit Transaction after. Remove the dump tran statement. Then rerun and note the results. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.