How to do DB load test

For a portal which with very high traffic, Database is the one the will directly affect the performance of your website, so every time, when you need to deploy some store procedure or database changes, you will always think that whether you stuff will greatly affect the sites. 

Recently In order to reproduce one db issue we came across, we are trying to reproduce it on our dev environment. but after quite a lot try, we don't have any luck. so we are thinking whether we can do some load test on our database.  fortunately , with VS2008 we can actually do that easily. You might ask ,a lot people are already on VS2010, but unfortunately , this feature had been turned off in VS2010, but it still there, if you want to use it , you have to buy it... so you know why Microsoft is rich...

I had done some search via internet, but didn't find too much articles introducing how to do it, so I write this post, hopefully it will help someone later on, knowledge should be shared. 

Step 1 , you should use your VS2008 to create a test project, it is easy

Step 2,In the solution explorer, right click your project name , on the coming out context menu, click add a unit test, then select database unit test, refer to the following snapshot.

Step 3, in the coming out dialog, it will ask you to select a database, if your database connection is already there, you can just select one, or create a new one.

step4, create the test, on the following snapshot reflect screen, you can just click the "click here to create" link to create a database unit test case. basically once you hit the link , it will go to code mode, and allow you to type in a SQL statement, which you used for test.

 

Code window

Step 5, add a load test, in your test project , go to solution explorer, and right click your project, on the coming out context menu

Follow the load test wizard, usually, for Database test, we select step load, which means we will increase user load steady until it reach the maximum.

In the test mix, you can select the database test , which you just created.

Then your database load test is ready. run it , and check the performance counters....