11 October 2016
11 October 2016

How to accelerate your SQL Server performance using SQL Monitor

Guest post

This is a guest post from Phil Grayson. After spending 14 years creating one of the world’s largest central healthcare databases, Phil Grayson (@databoffin) now works as a consultant for xTEN, where his varied but in-depth experience has helped to transform IT systems across Europe.

He is also the co-founder of Aireforge, the creators of OmniCompare and OmniGuard, a proud Friend of Redgate, and a volunteer at SQLBits.

racecar1c

I’ve been lucky enough to spend some time as a racing driver, racing BMWs for three years before joining the BRSCC Porsche Championship. Steve McQueen famously said ‘Racing is life. Anything before or after is just waiting’. When I’m waiting, I’m a SQL Server DBA consultant and throughout my racing and DBA career, I’ve realised the key to better performance in both is data.

The biggest driver is data

If you’re collecting the right data, reviewing it, and taking actions based on that data, you can extract the optimum performance from your engine or SQL Server without your car going up in smoke or your server crashing.

So how do we get this data? In a car, you have readily available information like speed and fuel levels, plus major alerts like ABS or engine problems. This is similar to the basic information you get from task manager or performance monitor and, although it can be stored, it’s frequently looked at for a moment and then discarded.

The second and more detailed source of information is the on-board computer or ECU, which can provide information about engine faults, speed, fuel levels, oil pressure, and temperatures. There’s a lot of real-time information available here but it’s often lost by the time the mechanic has hooked up a laptop. In the SQL Server arena, this is equivalent to wait stats, plan caches, and the general information you get from DMVs.

The most comprehensive solution is to use a data logger. This plugs directly into the ECU and extracts detailed information while collecting other real-time events such as track location, acceleration, and corner force. Storing this data lets you review how the car is performing, informing you of the exact moment an engine code was alerted or the engine misfired, etc.

The data logger is much like Redgate’s SQL Monitor, collecting and storing real-time data and letting you go back and review what happened during the peaks and troughs of your application’s workload. In the same way you can compare lap times to find the average or theoretical fastest lap, you can use SQL Monitor to quickly find irregularities such as CPU spikes and resource waits.

Tracking SQL Server performance using SQL Monitor

Data logger analysis software and SQL Monitor

If more severe alerts occur like blocking or deadlocks, they’re logged along with point-in-time information, enabling you to see what other issues occurred and determine if there’s a pattern.

Only by having this detailed data of what happened, when, can you determine the why. And once you have the why, you can figure out what you need to change to improve it.

The biggest obstacle is change

When you make a change to a car, you often put pressure on another area. A rear spoiler may increase downforce at the back of the car and stop oversteer, for example, but can reduce straight-line speed and make the car understeer. You could resolve this by adding a front spoiler to add more downforce at the front end, but this again reduces straight-line speed.

The same is true for databases, where changes are made to address a specific problem and an issue arises elsewhere which is also ‘fixed’. This is how systems can become oversized, over-indexed and unnecessarily complicated.

SQL Monitor can get you out of a corner here by giving you the ability to see the direct impact of any change you make. You can compare a specific period in time with another or with several periods to show the normal running values and extremes. This data is crucial when reviewing system changes and understanding how, for example, a code change at lunchtime caused increased blocking in the evening.

This is why recording system changes, benchmarking and historical data is important. By looking back at the data, you may find your systems now consume much more CPU during peaks or begin to struggle from resource contention after a change.

Keeping tracking of changes

racecar2

After every track day, I store data from the car along with the weather conditions, temperature, initial/return tyre temperatures, and the suspension setup. I also note any significant changes to the car such as air filter type, fuel level or any additions like a quick-shifter. This data can then be reviewed when I next visit the track, helping me understand why the car is performing better or worse – and giving me the option to restore the configuration to what it was.

Similarly for SQL Server, I often record setup information before making any changes, especially during benchmarks, tests or system reviews. Here, I augment SQL Monitor with the free tool I helped to develop, OmniCompare, which takes a snapshot of the SQL Server settings and lets me review them, compare them against the new setup, and share the configuration with colleagues.

(Interestingly, tracking external changes will become much easier in the next major release of SQL Monitor, which will feature the ability to annotate graphs to see the impact of releases or schema changes.)

Increasing stability

There’s a motorsport saying which is To finish first, you first have to finish. There really isn’t much point having the fastest car in a straight line if you can’t stop it, or it handles so poorly you have to be cautious going around corners. With SQL Server, you can also buy the fastest server, but poor code or configurations can cause it to slow down or fail during peaks.

In both racing and databases, it’s important to focus on consistency and simplicity with the aim of creating a stable and predictable system anyone can get good results with. Compare this to a complex system riddled with hard to spot fixes such as query hints, trace flags or special maintenance queries. Yes, non-standard settings have their uses, but in most cases it’s a last resort or temporary fix.

SQL Monitor helps you maintain consistency by collecting performance metrics and providing alerts on the long running queries, blocking and deadlocks which are causing your server to exceed its limits during peaks in workload.

It also offers alerts for other important issues such as failed backups, fragmented indexes and integrity checks. Teams often use these alerts as a to-do list and if you have no alerts, you’ll probably have a consistently performing system too

Pushing your data to the limit

Tuning a car’s performance can be just like a database in that you want to push it to just before the point where something breaks, then back off.

In motor racing, it’s about traction and in an ideal world you would extract 99% of the available traction from your tyres at every corner. Once you exceed 100% and your tyres are spinning or sliding, you need to back off the throttle to reduce traction demand before the tyres will grip again. So by overdriving the car, you actually end up going slower and your lap times suffer. For a fast lap, consistency is key.

This is often the case with SQL Server. A well designed database should be able to run at 95% CPU without seeing a degradation in performance. Once a database has been overloaded, you can’t just back off a little because the lack of CPU time may have resulted in inefficient plans being created and therefore queries that now require more resources to execute.

There may also be a backlog of requests which continue to overload a server long after the problematic query has finished or failed. This would be like wheel-spinning the entire lap. Again, consistency is key to the best performance, and SQL Monitor highlights the inconsistencies for you.

A winning formula

As database professionals, we probably won’t get the champagne and the glory sales get when they land a big deal, or a developer receives for a killer new feature. But that’s not us. We’re not Lewis Hamilton. We’re more like F1’s Adrian Newey.

Using our knowledge and the correct data, we can create a consistent and stable system and, from there, start to push our servers harder, doing more with less, reducing operational costs while at the same time increasing overall performance.

To win in racing, you need to start with a good stable car and then analyze, tune, adapt and analyze again. It’s no different when working with SQL Server: a stable platform and good data will help you win too.

If you’d like to know more about SQL Monitor, you can download a free fully-functional 14-day trial.

If you’d like to know more about Phil Grayson, you can follow him on Twitter.

Tools in this post

SQL Monitor

SQL Monitor is a SQL server monitoring tool that transforms the way you look at your database. It cuts your daily check to minutes, with a web-based overview of all your SQL Servers.

Find out more

Guest post

This is a guest post from Phil Grayson. After spending 14 years creating one of the world’s largest central healthcare databases, Phil Grayson (@databoffin) now works as a consultant for xTEN, where his varied but in-depth experience has helped to transform IT systems across Europe.

He is also the co-founder of Aireforge, the creators of OmniCompare and OmniGuard, a proud Friend of Redgate, and a volunteer at SQLBits.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Using SQL Monitor to Detect Problems on Databases that use Snapshot-based Transaction Isolation

    Use of the read committed snapshot isolation level is often an effective way to alleviate blocking problems in SQL Server, without needing to rewrite the application. However, it can sometimes lead to tempdb contention. This article offers a small-scale solution (not suitable for use on large tables) to detect cases when tempdb contention is related to use of RCSI.

  • Article

    Azure SQL Elastic Pools and SQL Monitor

    This article explains the Azure-tailored metrics and alerts in SQL Monitor that track use of the limited compute resources available to an Azure SQL Elastic Pool, and to each of the databases in it.

  • Webinar

    An introduction to distributed SQL Server monitoring

    As estates increase both in size and complexity, keeping on top of everything becomes both important and difficult. In this webinar, we talk you through the basics of distributed monitoring, and how you can set up a solution to help you keep on top of your entire SQL Server estate, regardless of how complex it becomes.

  • Event

    Microsoft Future Decoded

    Microsoft Future Decoded is your essential guide to disruptive technology and how it will impact people and businesses, today and tomorrow. Back for a fourth year, Microsoft Future Decoded will focus on current and emerging trends at the cross over of business and technology. With cloud, big data and artificial intelligence already rewriting the rules