Requesting your input on Resource Governor
I am writing a whitepaper, and planning more presentations, on using Resource Governor in SQL Server 2008, and am looking for your feedback on making both of these more complete and relevant to real-world implementations — since 99% of my usage of the feature so far has been mostly academic. So, I have some questions I'd like you to consider.
Are you using Resource Governor today? Are you planning to use it in the near future? If you answer yes to either question, can you provide some details about how you are going to use it (beyond the Resource Governor de facto mission statement)? If you answer no to both questions, can you explain why? And yes, it's okay to say, Enterprise Edition costs too much, or you have no short-term plans to upgrade to 2008 regardless of edition. 🙂
Whether or not you are using or planning to use Resource Governor, you might still be able to help me. If you are familiar with Resource Governor, do you have any outstanding questions about the feature, things you don't understand or specific items that aren't covered by the documentation? Did you stumble across any cool factoids or potential gotchas while using the feature that you would not have learned about in Books Online?
If you have interesting answers to any of the above questions, I'd love to hear about them. And if you don't feel comfortable posting here, you can e-mail me privately at aaron.bertrand @ gmail.
Thanks in advance; I am looking forward to reading about your opinions and experiences.
So I would like to use this as a potential approach to get some teams to work together better.
One of the issues in this environment I inherited (well I bought it when I took the job 🙂 ) is data duplicated everywhere. There are some design challenges but the bottomline is several teams all get a copy of the warehouse to work with. Rather than publish data marts and optimize things this is how they did it. I am working on a longer term plan but the short term problem is: getting heat for "wasting" san space with the same database everywhere.
Warehouse team doesn't want the other teams that do ad hoc querying to hurt the warehouse performance so they decided (pre-mike) to just give them their own copies and refresh nightly or whatever works for them.
So part of me thinks resource gov. -could- help. Some of the performance headaches have been CPU/Mem related. Obviously I/O is a bigger concern but there could be benefit upon upgrade.
For a longer term fix, obviously redesign, publishing marts and getting away from some of the heavy querying direct in the warehouse by non-SQL people is the real right answer 🙂
Thanks Brent, that's good feedback. The good news is that I/O controls are allegedly coming in a future version. That should help more people, but I still see plenty of queries that go out of control with one or the other. MAXDOP queries from hell are the most frequent.
Also for people with slightly older gear, finding another 16 GB of RAM or filling that empty socket is not always easy.
I tried it, but without IO throttling, I'm having a hard time finding a valid use for it. Everybody I work with is IO-bound, not CPU or memory bound. CPU and memory are crazy cheap these days, but SSD's haven't come down in price enough to really remove the IO bottlenecks inexpensively.