I have an issue where a software driven replication system will fail to fully replicate when the main DB is under heavy load. For example if we execute a sproc 500 times, maybe 450 will actually stick. BUT only when we are under crazy DB load (one example is the SAN admins updating firmware during business hours). I'm concerned creating transactions for each message, and waiting for them to timeout, and rollback as there is the potential for the destination SQL server to be on WAN in another datacenter.
Inquiry: I want to thrash my DEV SQL server so hard to replicate massive locking/cxpacket waits in development. But not enough to totally deadlock all access for hours, just maybe to simualate packet loss.
Scenario:
Server B (utility machine) has a local SQL Express instance. It is used for transient data.
It fires off a series of sprocs against the destination database to insert new rows, and then insert new rows into child tables linked by PK's. So on and so on.
Usually it works just dandy. Except the production DBA's don't let the subject matter experts on this system have any control, or visibility into lock stats. I have full control and can profile the system that is propagating data to the destination, but no analysis as to what happens at the other end, other than waiting.. waiting.. waiting… Am I locked? I dunno!
I want to simulate this in the lab and try to tune the sprocs that are used. I'm thinking of a heartbeat update anx xp_cmdshell update to a bookkeeping table and also using ping analysis using xp_cmdshell to query /refine/tune transactions, and rollback transactions if necessary, in the event that all are not committed. Then try again, maybe on another distributed host.
Problem:
The last time this happened was due to the fact that TEMPDB was HUGE (massive temp tables being utilized) and had to page. DBA's said there was nothing wrong, but also reported disk queue lengths obove 50+ for hours at a time. Gotta love it.
Best Answer
Just found out about Hammer DB which is an open-source, configurable load generator. We're using it for VM overhead evaluation. You should be able to pick just the right number of simulated users to stress your system without breaking it.