3

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.

2

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.

0

To stress a system you can either increase load or decrease resources.

There are many commercial load-creating packages out there, and probably some freeware ones, too. It sounds like you've isolated it to just one SP, though, so a custom script to fire off 500 examples of it in parallel should do the trick. Running the script on the DB server will induce even more stress on SQL Server. I have some Powershell scripts that can max out a four-core server for minutes at a time! Multiplying the data with some INSERT dbo.Xx SELECT * FROM dbo.Xx (with appropriate primary key adjustments) will make the SPs work harder, too.

On the resource side, reducing max server memory will cause more bufferpool paging and, maybe, more SP recompiles. Setting the instance processor affinint so it has fewer cores available will cause each to work harder. Of course these may cause subtle, secondary changes which affect what you're trying to measure. New, different execution plans for example.

On the monitoring side I've found these DMVs helpful to understand locking.

HTH

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.