How to bring your powerful database server to its knees

Don't let the mild-mannered appearance of this group fool you.

 

We're in our last day of Arena training before go live on Tuesday next week. Chuck is teaching the Report Builder in SQL Reporting Services.  With these eager students, his first exercise was to simply select first and last name and then run the report.  This resulted in 9 simultaneous attempts to report all 60,000 people in our database.  It soon became clear we had a problem.

We're running SQL Server in an ESX guest VM.  This has been our configuration for 9 months with no issues.  The CPU on the host Dell 2950 server (total of 8 cores) spiked for a minute or two but then settled back down.  Yet the server was still whimpering in the corner.  No one could do anything.  We had a class full of students trying to use a server that was dead for all practical purposes.

Turns out SQL Server itself was okay.  IIS (running on the same ESX guest VM with SQL Server) was gulping enormous slurps of RAM to generate the multiple thousand-page reports.  Windows responded by trying to expand its page file.  Under ESX, this is not a good scenario.  We had an unscheduled outage on our main database server in the middle of the work day.  Clif not happy.  The good thing is, you add RAM to a guest VM with just a few mouse clicks and a reboot, which Ian did.  After a 20 minute delay, the class resumed.  From then on, Chuck made sure to run the examples himself with everyone else just watching.

With the number of guest VMs we have running on that host, Ian thinks we need to add to the 16 GB of physical RAM we already have on that box.  This is why IT is so FUN!