SQL Server Performance Forum – Threads Archive

SQL Server 2000 Load Testing

Hi All,
Need some urgent help..
I optimizing Stored Procedures. The current database is of 5 GB in size and a specific tables have more than a million records. And this is just the sample db. The original has more than 20 to 30 million records and expected to grow at the rate of 5 million record per week. Now i need to test the db for such huge data without replicating the information. I cant replicate the entire db.
have to do with sample. Please help…….. thanks!

Hi there. What is it your asking for ? I dont think you can really gauge how performance will degrade without having datasets at least resembling your actual production database. Maybe you can write a script to generate random data to get it up to size. Or do you mean you cant replicate it all because you dont have space ? (Im assuming you mean replicate as in a copy of the data, and not in terms of SQL replication)

Hi, I cant replicate as i dont have enough time and space. But the scripts need to run even with such huge data. Ya, replication does’nt mean sQL Replication. Is there anyway we can calculate or atleast forsee?

My only idea is to instead of growing your dev database, take a copy of it and actually make it smaller. Maybe then by comparing the performance of queries between the smaller one, and the current dev one, you can see which queries are impacted the most by database size. EG. If a query takes 3 secs on your current dev, and just under 3 secs on the trimmed down one, then Id assume this query wont be affected too much. If on the other hand it only takes 0.5 seconds on the trimmed database, it implies it is impacted quite a lot by the database size. However, I’ve not used this method before, and so hopefully someone will point out if its not a very reliable test; but it seems to make sense! <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Obviously (as you know), the only real foolproof test would be to run it on a copy of your production database though.

May be use third party tools like LOAD RUNNER & WIN RUNNER for accurate information on load testing. HTH Satya SKJ

If I understand it correctly, you are trying to optimize some stored procedures. Is this correct? Why can’t you optimize them in the production database? That’s what I often do (assuming that I do this intelligently). Also, if you want to reduce the burdon on your server while testing, you can do a "Display Estimated Execution Plan" in QA, which won’t affect your server’s resources much when run.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

Thanks all!!.. Chappy’s idea sounds interesting. I cant run it on production server directly for lot of security reasons. Could not do much with estimated exec plan, as the sp’s involve quite a few #tables. I’ll try some more methods..and let you know you guys about the result..meanwhile if anybody has anyother ideas, it will be of great help. Thanks
Shanaaz

5 million per week..do u mind me askign what this is for?

When you create a test database from your production database, you might want to consider some of the tools fromhttp://www.red-gate.com that allow you to copy database schema and data from one database to another.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

]]>