First, decide what you want to get really good at. Then try to break it in many possible ways and analyze why it broke.
The more you break something, the more you’ll understand it.
I use this technique all the time. Last year, I encountered a problem with a lack of worker threads in SQL Server in a client environment. That issue was particularly difficult, because when the problem occurred it was difficult to observe the SQL Server without using the Dedicated Admin Connection (DAC). At the time, I built some repro scripts in my own environment to show how the issue started, why it was tricky to observe, and how to observe it and confirm the queries at the root of it all without restarting the SQL Server. And just recently I wrote new scripts breaking the same thing in different ways — and showing how parallelism can be a factor — for our 2015 Performance Troubleshooting class. Taking the time to break it myself taught me nuances about workers in SQL Server that I wouldn’t have learned otherwise.
“But Kendra, I don’t have time for this!”
Here’s how to make time. Mix and match these three ideas:
1) Make it a team exercise.
One person breaks something in pre-production, the other has to fix it. You save and publish notes on the error messages and how you responded.
2) Tie it to a learning program.
Purchase one of our training video or in-person classes, and design a learning program that uses the training class as a launching board. Set a goal to write your own scripts breaking something for at least 5 of the modules.
If you really want to lock in the knowledge, write down a summary of what you’ve learned in your own words. You can blog it or publish it as notes for your team at work to reference. Your own notes will help you over time more than you expect.
3) Set goals for mastering specific items for the year.
Talk through it with your manager and document why you want to master the topic, and three things you want to achieve at work after you’re done.
10 Comments. Leave new
I don’t always break things just to learn about them, but when I do, I do it in Production.
(j/k!)
I particularly like how this is safer than the usual way people learn about things by fixing them: because they’re broken in Production.
Kendra, I would love to take one of the weekly tue meetings and talk about this. We had a very same issue with one of our servers. It took some time to track down, the Perfmon looked good… but the server would stop responding. After it came back up.. I would get the SQL Server Alert about workers. So I created a job to run often and write worker information into a table. What I found was that we would get HUGE amounts of threads off one SPID and they would all be in CXPACKET wait. Also thanks to this I was able to see a pattern, of when this would happen. So I created a trace to capture what was going on .. with the server.. when this happened. (You are right, connecting using the dedicated is the only way to get in to the server.) I was able to track.. and trace.. each out of worker event, back to a group of users. They used an Ad-Hoc tool to create queries, and the tool was creating some REAL poor queries. I was unable to get the vendor to fix, so we had to advise the users not query with as many filters as they were doing. This was over a year issue… and I spent a lot of time on SQLServercentral.com and mssqltips.com
Kendra & David,
I agree with David, this would make be a GREAT “how to video (with code)”. We are a 3-person company and I am the DBA / SQL Developer / Tech Support and we do a lot of “just in time learning”. We have one customer that is having some difficulties with this type of SQL problem and need to try to help them figure it out.
I have a demo I do for this as part of our paid training classes, but it’s not part of our free training at this point 🙂
So maybe the demo isn’t currently free, but do you have suggestions on tools you use to create workflows on SQL Server to break things? 🙂
I should probably note that I’m not the same Matt as posted below, so I’m adding a last initial. Didn’t want to implicate other Matt in anything.
Oh sure– and the demo is really just for this one specific example about threadpool waits.
Tools that can be helpful are:
ostress.exe – free in the RML Utilities
HammerDB – https://www.brentozar.com/archive/2012/06/load-test-sqlserver/
Michael J Swart lists a bunch of options here and gives his take on them, too: http://michaeljswart.com/2014/01/generating-concurrent-activity/
For some issues I don’t need fancy tools as much as I need a reasonably sized set of test data to work on. I can’t really reproduce memory grant issues with a 128MB sample databases that easily. In those cases having a restore of the stackoverflow database is super helpful for me, just for data sizes and realistic patterns (although in many cases using a restore of your own production databases is better for you, depending on what you’re doing): https://www.brentozar.com/archive/2014/01/how-to-query-the-stackexchange-databases
And for things like clusters and availability groups, well there’s SO MUCH you can break, it’s mostly just about finding a safe playground and setting it up as similarly as you can to production as possible. The best environments for this are separated off from production (firewalled off, different domains) but very similar hardware, so you can break all sorts of things at the network and hardware level without risking impact.
Good Post kendra. While there are many individual components of sql database engine, I believe ‘mastering in understanding and analysing wait types’ can have positive ripple effect on how much you can learn about sql server
I think this is great! We do have issues in production that come up that we have never seen before, but our team usually does what you have said and picks a few things and learns about them over time. When issues come up, we can usually sort them out pretty quick which is great! We do also have the odd one person break something and the other fix it – but this is more inadvertent than intentional, and usually in production *sigh* 🙂