SQL DBAs know that SQL Profiler is a great tool. It’s tried and true. But the problem is that it will become obsolete. That’s why now is a great time to become familiar with and use SQL Extended Events so that when the time comes and SQL Profiler is laid to rest, your DBAs will be ready for the change.
In managing a database or sometimes numerous databases, we often get caught up in the day-to-day maintenance and lose sight of the other principles we know are critical to ensuring optimal performance. Here’s a quick refresher.
Monitor & Maintain Regularly
I recommend a Goldilocks approach to day-by-day database management: find a level of effort that is "just right." Don’t get lost in the forest verifying every configuration option and following up on every performance hiccup. Don’t bite off more project work than you can chew. I recommend:
- Put automated systems in place to generate alerts for non-compliant configurations or performance issues. A third party application or an in-house developed monitoring tool can meet this need.
- Try to break big projects down into achievable units and schedule a gradual implementation. Scale efforts to something that will be comprehensive, meaningful, easy to communicate, and actionable. A professional health check scheduled routinely every 3-12 months is a great way to identify potential database project work that will benefit the environment.
By refining the approach to daily database administration, your team’s efforts will be more productive and likely better received by your business partners.
It Takes a Village
While it may seem obvious in the context of this blog, collaboration remains a best-kept secret in maintaining a healthy database environment. As you define policies and principles, and especially each time you evaluate and tune for performance, be sure every stakeholder is engaged. From the DBA responsible for a given database to the business team that depends on that database, you will be delightfully surprised by the insights you’ll gain and the impact this approach has to increased performance.
Additionally, be open to creative solutions for augmenting the talent of your existing team. I recommend a combination of tactics:
- Hire or train a dedicated SQL server DBA. While most DBAs bring broad knowledge, each has a specific areas of expertise. Be sure at least one of your team has that special touch with your SQL servers.
- Augment the skillset of your DBA team with strategic managed services. As needs fluctuate in scale and skillset, a strategically-paired managed service provider will augment the talent readily available to you, alleviating frustration from your core DBA team.
- Partner with a SQL server consultant company you trust. A professional consultant works on countless server environments every year, providing you the fresh recommendations of an experienced, outside perspective. Plus, your consultant can keep you ahead of future performance issues by identifying hidden problems brewing within your database environment.
Keep an open mind to your approach to performance tuning. Some tuning techniques are excellent solutions to performance today, but not tomorrow.
As you evaluate solutions, consider that some fixes are easy to implement but will not scale with increased demand. Take the time to pay it forward
Keeping an eye on the horizon also serves us well as business leaders, as we all aspire to be innovative. Databases benefit immensely when we find new, improved solutions to managing them. When favorite tuning techniques no longer seem to do the trick, encourage collaboration between DBAs and invite consultants to offer recommendations. Your environment is always changing, which means the best tuning solutions are, too.
Eventually, optimization will not be enough. No matter how sharp you keep those servers running, your business will eventually outgrow them. So plan for growth. While none of us has a crystal ball, we can forecast demand by documenting the events that spike demand on a database environment, observing trends, and maintaining a dialog with leadership about anticipated business events that may lead to increased demand and warrant investments in new hardware.
For the latest in how health checks can help improve and maintain your server health, download our latest white paper, The Business Value of Complete SQL Server Health Checks.
Andy McDermid, Principal SQL Server DBA, Datavail—Andy is a MCITP certified MS SQL DBA who delivers and manages delivery of DBA services to many diverse clients. He enjoys helping his clients by finding and deploying pragmatic and practical solutions for their database issues. Andy is always working to improve and expand his DBA skills and he likes to share the experience via writing.