| Author |
Topic |
|
malraff
Starting Member
10 Posts |
 Posted - 2010-04-12 : 09:31:21
|
| hi alli will soon be moving our sql instance to a dedicated SAN, and i have 1 week to test. ill test out sql by running the applications that generally are slow and cause locksbut is there anyway to simulate lots of users connected to sql and have them doing random queries at the same time? |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-12 : 09:48:52
|
| There are a few packages to simulate load but they all tend to be very expensive / have a lot of technical know how required.We use loadrunner for load testing but you have to buy licence slots in half day / day allocations. Load runner simulates multiple users through a web front end.I think you could use profiler (bundled with ms sql server) to trace a particularly heavy session. Then backup the db and restore to your SAN and then replay the trace? Never done that but I think it would work.I'm sure there will be others with more perf methadologiesCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
malraff
Starting Member
10 Posts |
Posted - 2010-04-12 : 10:18:32
|
| Hi Charlienever used profiler before, and did not know you could play back !will have a look now |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-12 : 10:22:13
|
quote:
Originally posted by Transact CharlieI think you could use profiler (bundled with ms sql server) to trace a particularly heavy session. Then backup the db and restore to your SAN and then replay the trace? Never done that but I think it would work.
Just guessing here, as I've never done it either. But wouldn't it be a good idea to take the backup before the trace is applied? That way, the database will be in the same state when they trace is run as it was when the trace was recorded.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-12 : 10:27:50
|
| yeah -- probably. I was kinda just in free form mode. I've never done it before but I do know that you can replay traces.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
malraff
Starting Member
10 Posts |
Posted - 2010-04-12 : 10:56:01
|
| would i need to take a backup up at the end of day, and then start profiler 1st thing the next day?and then apply the backup to the test server and re run the profileeg would profiler have issues if the data has changed, deleted etc since it was run, which would be the case if i ran profiler then took the backup...? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-12 : 11:01:21
|
| I honestly don't know. I've never done it. I just know that you can. I'd be interested to see how you get on so if you feel like posting your experiences that would be ace.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
malraff
Starting Member
10 Posts |
Posted - 2010-04-12 : 11:11:07
|
| no probs, if i get it working ill post my results for sure! |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-12 : 11:48:08
|
| At a guess, to guarantee things go smoothly, ensure there were no users connected to the database, then take backup, and then start profiler. I wouldn't want there to be any changes between the start of the backup and when profiler started. This way, playing profiler forward over the backup should give the same results as the original usage of the database. Of course, this isn't always possible. However, you can reduce the amount of downtime by taking a full backup first. Then kick everyone off the database before taking a delta backup. The time it takes to do the delta backup will be very short, unless you're processing a very large no of transactions per second. Then, restore the Full backup with no recovery, followed by the delta with recovery. Of course, if you know the isn't going to be used outside of normal working hours, this is a little overkill. Also, I would guess you'd want to see some sort of activity shortly after you start profiler. Otherwise you may have to play for quite some time to get your results. And remember, this advise is coming from people who've never done this before. :)------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
|
sql testing