This is Part 2 of a series. Please see Part 1 for the background and more.
I have two answers to this question.
1. I write more dynamic SQL in any given week than I had previously in my career – all years combined!
2. Many DBAs deal with issues around parameter sniffing and plans being stuck in cache that don’t work well for a number of their requests as a result. This hasn’t been an issue for me because my plan cache is constantly churning and the majority of queries are ad-hoc. I usually see 75-90 percent of my cached plans being created within the previous few hours, although that number has been changing lately as we’ve been shipping query optimizations the past few months.
The company’s primary product is a SaaS offering, so the software/IT side of the house is fairly important. Like any software product that’s over a decade old, there’s a buildup of technical debt and some of that lives in SQL Server. From my observations of the database environment, I get the feeling that early days were focused more on data management than performance management and capacity planning. When performance started to become a concern, more hardware was thrown at it.
There’s a hefty tradeoff to be made between delivering new features and critical fixes to customers. For me to make significant changes to the database technology requires making a compelling business case and getting buy-in from a lot of parties. I’m a technical person, not a politician, so I don’t make these pitches often.
I’m fortunate in that I have a decent degree of autonomy in managing the data environment. If I identify an improvement that can be made without having to make application changes, I’m able to do so without having to force my way into the development schedule (but as I’ll note in a future answer, I am integrating myself with the workflow more so that people are better-informed about what I’m doing).
There are two of us. We each have a few specialties but we aren’t “territorial” and we try to share as much as possible. If we aren’t both directly involved in a given project, we keep each other in the loop as it progresses.
Edit: At the time this post was written and published, there were two people on the team managing the databases. There is now only one.
Stay tuned for the thrilling conclusion in Part 3, coming soon!