Whether you are new to the DBA role or you are a seasoned veteran, there is always a way to improve or streamline your performance monitoring process. Here are 10 facts about database performance monitoring that can help you resolve performance issues more efficiently and find more satisfaction in your DBA role.
Fact 1: Database performance can be bad even when everything is online and resources aren’t under pressure
When database performance is less than optimal and you’ve confirmed everything is online and consumption is normal, you will need to look deeper to find the root of the problem. Look at query plans, joins, or filters used by the database query optimizer. Be sure to check for:
- Inefficient query plans
- Data skew
- Nonexistent indexes
- Unmanaged database statistics
- Poor database design, blocking, or database schema changes
Takeaway for the DBA: In the absence of an obvious event or outage, you can speed up identification of the root cause by first looking for exceptionally slow queries.
Fact 2: Agile development practices can wreak havoc on database performance
The widespread adoption of agile and DevOps has increased quality and streamlined processes, but continuous delivery and deployment can have the opposite effect on database performance.
In an agile or DevOps environment, possible sources of performance issues include:
- Adding, changing, or deleting database objects such as tables, functions, or views
- Adding rows in a table without partitions
- Adding an extra index to a table
You can mitigate the risk of introducing performance issues in new versions by creating a throughput baseline after making changes and monitoring database schema changes in real time.
Takeaway for the DBA: Anticipating the potential impact of changes helps you avoid surprises later.
Fact 3: Database logs contain a lot of information that is not available in performance metrics
Of course you want to monitor and track performance metrics, but if you rely on just those metrics, you will miss out on a lot of valuable performance information. For example, just because you know the average number of queries per second doesn’t mean you know which queries are running slow. That’s the information you need in order to fix the problem, and it’s available in the database logs.
It’s worth the effort to review all of your database logs, including system-generated logs, slow query logs, scheduled task logs, backup logs, and maintenance routine logs.
Takeaway for the DBA: A busy database system can generate a ton of events every minute, so going through each log manually is unrealistic. Use the log events to create charts and dashboards so you get an at-a-glance representation of the system’s health.
Fact 4: Alarm notifications can save you time and frustration
Most alarms just tell you that a metric has breached a threshold, but some performance monitoring tools offer smart alarms that can start diagnostics for you by presenting metrics and trend lines, interpreting the alarm, and giving pointers on how to fix the issue.
Takeaway for the DBA: Smart alarms take some of the guesswork and initial triage out of your hands, which expedites the resolution process.
Fact 5: Mobile performance monitoring is the key to having a life away from work
Performance monitoring apps are available for most devices and OS and include features such as:
- Heatmaps: Give a visual prompt to identify the biggest, most troublesome issues at a glance
- Color- and number-coded alarms: Provide easy-to-understand issue severity information for each SQL server
Takeaway for the DBA: Mobile performance monitoring apps let you identify and start triaging issues from anywhere at any time. You no longer have to trek into the office in the middle of the night to attend to alarms.
Fact 6: SaaS database performance monitoring systems cost less, scale easier, and require much less maintenance
Traditional, on-premise performance monitoring systems are a great option for some organizations, but there is no denying that the popularity of SaaS solutions is growing. There are a lot of reasons to choose a SaaS performance monitoring solution, including:
- Security: Most SaaS solutions are built on a highly secure cloud infrastructure, such as Azure or Amazon
- Scalability: The cloud provides almost instant scaling up or down as your needs change
- Flexibility: Mobile apps, easy-to-use UI, and cloud-based tools allow distributed teams to work together seamlessly
- Resiliency: SaaS applications are engineered for high availability and resiliency
Takeaway for the DBA: As SaaS performance monitoring solutions become mainstream, there is no doubt that they are more agile and feature-rich than traditional, on-premise solutions. Perhaps the most important selling point is that initial concerns about cloud security are being shown to be unfounded.
Fact 7: Performance monitoring can be overwhelming if you don’t shut out the noise
Performance monitoring is intended to keep DBAs informed about problems within the system. But too much information can cause alarm fatigue, which can cause DBAs to miss critical issues. Fortunately, there are ways to calm the chaos:
- Configure alerts and customize responses
- Turn off alarms for events that often trigger a false positive
- Use tuning and diagnostics to help prevent downtime and failures instead of living in reactive mode
Takeaway for the DBA: Cutting down on the noise will help you isolate and focus on fixing real issues.
Fact 8: Checklists are a DBA’s best friend
Create daily, weekly, monthly, and quarterly checklists to ensure you are paying close attention to alerts, event logs, security policy violations, and wait statistics. Tracking these statistics will facilitate performance monitoring by identifying ongoing issues and helping you proactively address them before they become a major problem.
Takeaway for the DBA: Catching issues early is key to minimizing performance problems. Adhering to your checklist schedule will also help you stay current on system maintenance and tuning opportunities.
Fact 9: Your ultimate goal is to improve performance, not just fix things when they break
Every DBA plays the role of Chief Fire Extinguisher on some level, but creating a performance monitoring strategy to proactively address issues is far less exhausting than constantly jumping from one crisis to the next. Tuning and optimization are key to setting up a performance monitoring system that isolates the big issues, helps you prioritize (or even prevent) the smaller problems, and allows you to customize monitoring to the specific needs of your databases.
For even more precise tuning, look for a performance monitoring solution that uses AI for SQL Server optimization.
Takeaway for the DBA: Looking at performance history over longer periods of time helps you proactively anticipate performance issues. Artificial intelligence can analyze your execution plan for you and determine how to change it so SQL Server executes operations more efficiently.
Fact 10: There is always something new to learn about database performance monitoring
Technology changes in the blink of an eye, so be sure to stay current with the latest performance monitoring trends and tools. Make yourself the go-to expert in your company and embrace continuous learning by:
- Attending conferences
- Joining user groups
- Participating on forums
- Getting certified
Takeaway for the DBA: Choose to learn all you can about database performance monitoring. Being the resident database performance monitoring expert will not only make you an essential resource in your organization, it will also improve your job satisfaction and self-confidence.