A DBA (Database Administrator) is tasked with maintaining an efficient database where important information can be stored, analyzed, and quickly accessed. Query tuning within SQL Server is exactly what your business needs to gather and retrieve all this important information faster, saving you valuable bandwidth, time, and money.
You may have already worked a little bit with SQL Server, but you could be asking yourself, what do I do now? Here are a few tips and tricks for you and your business to make sure that you get the most out of your queries.
1. Assess and Analyze
When it comes to query tuning, you need good visibility so you can identify the root cause of the problem. This basic query analysis is the first step. This should include analyzing top wait types, missing indexes, resource contention, and blocked queries, to name a few. Don’t get too bogged down in the details, though. Start with the basics:
- Understand the difference between tables and row counts. For query analysis, you need to be operating on a table, rather than a table-valued function or view.
- Examine the filters and JOIN and WHERE clauses
- Understand how many rows you’re going to work with. Use of a SQL diagramming tool is recommended
- Double check to see if extra columns are involved. Examine SELECT* or scalar functions.
2. Discover Flaws with Advanced Query Analysis
Dig into your query analysis by reviewing existing keys, constraints, or indexes to make sure that there are no duplicates or overlapping of indexes. After finding the flaws and identifying constraints, start adjusting your SQL server to your needs. It’s important to approach database tuning in small steps; if you try to do too much, too soon, you risk the chance of cancelling out your other efforts. Additionally, it will be difficult to know for certain which change created the desirable results.
Another step of advanced query analysis is to consider the actual execution plan. This is different from the estimated plan, because an actual execution plan uses real-time statistics instead of estimated statistics. Then you can compare the actual plan with the estimated plan, and if they are different you can investigate further.
While doing advanced query analysis, make sure to keep an eye out for inhibitors of performance that might pop up frequently, which could include:
- Code first generators
- Nested views
- Abuse of wildcards
- Scalar functions
- Row-by-row processing
3. Find Hidden Opportunities
As a new DBA, be prepared to spend a lot of time working and tweaking with the SQL server. Don’t fret; once you get the hang of how queries are functioning within your specific database, you’ll start to find hidden opportunities for speeding things up and improving functionality.
One way to find opportunities and really be able to analyze your database in-depth is through a database performance analyzer (DPA) tool. With this you can look at exactly which query is causing a bottleneck, and how it impacts response time. A DPA is also a vital tool for continual monitoring of your database, so you can identify problems quickly before they cause any issues.
Sometimes, as a DBA, nobody appreciates your work until something’s gone wrong (and they’re looking for someone to blame). But by working hard, doing your researching, and following these database performance tips, you will be sure to keep your database running like a fine-tuned machine.