Solved

Insert Statements Load Testing

Posted on 2010-08-16
4
291 Views
Last Modified: 2012-05-10
Experts -

I'm tryng to simulate the user load testing for the following insert statement. The idea is running this in multiple sessions/threads and measuring

the various performance (CPU usage, Transaction per second, IO, etc). This is an interactive application and I'd like to create the load of 5000 or

more users as possbile to simulate the testing. I understand that it depends on the specs on the server computer it runs on of course, but assuming

you buy the best server available for around $10K?
DECLARE @row INT;

DECLARE @string VARCHAR(50), @length INT, @code INT;

SET @row = 0;

WHILE @row < 100000 BEGIN

   SET @row = @row + 1;



   -- Build the random string

   SET @length = ROUND(80*RAND(),0);

   SET @string = '';

   WHILE @length > 0 BEGIN

      SET @length = @length - 1;

      SET @code = ROUND(32*RAND(),0) - 6;

      IF @code BETWEEN 1 AND 26 

         SET @string = @string + CHAR(ASCII('a')+@code-1);

      ELSE

         SET @string = @string + ' ';

      END 





   -- Ready for the record

   SET NOCOUNT ON;

   INSERT INTO [SP].[SMSG].[TMSG VALUES (

     -- @row,

      @string,

      ROUND(2000000*RAND()-1000000,9),

      ROUND(2000000*RAND()-1000000,9),

      GETDATE(),

      CONVERT(DATETIME, ROUND(60000*RAND()-30000,9)),

      null,

      null,

      @string,

      @string,

      null,

      null   )

END

Open in new window

0
Comment
Question by:sventhan
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
dwkor earned 500 total points
ID: 33447077
I cannot talk about server specs but one thing I noticed - it looks like your table has identity (@row). If you have clustered index defined on that field and expect to have load > a few hundred inserts per second, I would suggest to change clustered index to something else. Otherwise you will have contention because of "hot spots" during insert.
0
 
LVL 7

Expert Comment

by:lundnak
ID: 33447470
Could you restate your question?  I'm unable to understand what you are looking for from the experts.
0
 
LVL 18

Author Comment

by:sventhan
ID: 33447816
Sure.

I Would like to measure the performance metrics during the maximum load of a certain table. To keep it simple just run the same insert stmt again and again (using various threads/users/agents) to the same table till I get 100% CPU usage. What is the maximum capacity the system can handle for just doing a multiple insert to the same table(Daily Partitioned) with clustered index on it?

This is a messaging application and this table is going to grow with the messages. I'm doing the capacity planning.

Thanks,
~sve.

0
 
LVL 18

Author Closing Comment

by:sventhan
ID: 33450998
Thanks for the help.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

937 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now