Voice of the DBA

Tag: Performance

  • Checking Baselines with SQL Monitor

    We’ve got a demo of SQL Monitor available that takes data from the actual SQLServerCentral systems and presents it publicly. The data is slightly delayed, but it’s the real data and it shows the performance of our system.

    I actually use this at times when the site isn’t responding well. It’s quicker for me to hit monitor.red-gate.com than log into VPN and check the real instance.

    I was reading a report for a customer recently and they mentioned they used the baseline feature to track performance of the system when they make changes, using this to decide if they think the changes were positive or negative.

    Note: they rarely roll back, instead using this data to determine if they need to dig into more specifics and then create a new deployment (roll forward) that can address issues.

    Baselines

    There has been a lot written about baselines, but essentially these are a way of capturing what is “normal” for your system. If current performance deviates from normal, you might have cause for concern.

    Of course, this is a gross way of looking at a system and you have to apply some of your own knowledge to determine if you have issues, or some transient workload is altering performance during your sample period.

    You can see a demo of baselines online and play with the ranges. It looks similar to this image below, in which case I can see the last 24 hours of my CPU time, and then compare that with the 24 hours before that.

    baseline

    Using this type of diagram, I can quickly visualize issues that might be occurring without resorting to complex analysis.

    This doesn’t give me the exact problem. I’d still need to dig into the code being run, look at execution plans and other detailed data (much of which I can get from SQL Monitor as well), but this does let me make an initial judgment on where to focus my time.

    I would highly encourage you to run, keep, and update baselines on your system. At the very least, you should have an idea of what types of performance should exist on critical systems.

  • Normalizing Performance

    This week I ran across a post from Glenn Berry on hardware performance that I thought framed the concept of performance value well. In the post, Glenn talks about the latest TPC-E benchmark and the SQL Server performance values that vendors release. Glenn mentions that he always tries to divide out the numbers to provide better comparisons for the various hardware choices.

    That makes a lot of sense to me, as I think very few of us would be able to afford the top of the line systems on which vendors run the benchmarks. Most of us try to somehow compare the results in some way and then make our own decisions for our smaller systems. I don’t know many people that run 36 core machines, but I do know lots that need to decide which 4 or 8 core systems they should choose.

    The idea of normalizing performance to smaller loads is something we do often. We need to do this, because we often can’t get the same size, scale, or specifications in our test systems as we have in production. As much as we’d like to have them, resources are limited, and we do need to have some way of extrapolating the results in our development and test systems forward to production.

    Glenn has a way of doing this for CPUs, and while you might not agree with his method, at least he has an organized way of doing things, and then letting empirical results provide feedback on whether this works well. You should do the same thing, whether you’re trying to gauge disk or T-SQL speed. Develop a hypothesis (or read about how other do so)  for measuring your performance on a lesser, and then your primary system. Take time to run some test the same way, even if it’s single query performance on a production system while it’s live.

    You won’t get perfect results and scalability, but you’ll develop a few metrics that allow you to determine if your decisions in development will have severe negative impacts. There still might be problems, but you should eliminate more and more of these over time.

  • The Cardinality Estimator in SQL Server 2014 – Going Forward and Backward

    I saw a talk from Joe Sack (b | t) on the cardinality estimator (CE) in SQL Server 2014 and found it very interesting. To be fair, some of the "how it works" isn’t something I care about much, but I did like Joe sharing some places in which you might find problems with your queries and how the cardinality estimator might affect you. The talk is worth seeing if you get the chance.

    However one of the really interesting things, and an item I appreciate Microsoft building, is a switch to turn off the new CE. Actually, it’s not turned off, but you can set it to pre-SQL Server 2014 behavior (essentially 2005-2012) or to SQL Server 2014 behavior. What’s even better is that you can set this in a number of ways.

    Setting Database Behavior for all Queries

    Turning on the new CE is as simple as setting the compatibility level to 120. This will turn on the new CE for your queries in this database.

    The flip side is setting your compatibility level to something below 120 (110, 100, etc.) and your queries will use the old CE behavior in the query optimizer.

    Setting Behavior for Queries

    You can also specifically test queries with either the new or old CE. The QUERYTRACEON option can be used with these two flags.

    • 9481 – Uses the 2012 (pre-2014) CE with queries. This is used when the database is in SQL Server 2014 (compatibility mode 120) mode.
    • 2312 – Uses the new 2014 CE when the database, or defaults, are set to use the 2012 CE.

    This is documented in KB 2801413 from Microsoft.

    Setting the Server Level

    There is a trace flag that you can use at Server startup that globally sets the CE behavior. Set 9481 at startup and your SQL Server 2014 databases will use the old CE by default. Joe documented this on his blog.

    Usage

    I think this is great because if you are concerned about workloads being effected by the CE changes, then you can set the old CE as the default and test on your real production server by executing specific queries with the new CE and the query plan.

    Alternatively, if you upgrade and find problems, you can duplicate the old CE by using a query hint and see if the query performs better.

    I’d like to see this upgrade/downgrade granularity in more features that can potentially affect performance and I would say this is a fantastic architectural win by the SQL Server development team.

  • Performance Tuning Through Panic

    We’ve all been there at one time or another: panicked by a performance issue. I once upgraded a FoxPro for DOS application to a VFP front end with a SQL Server back end. We had a much more powerful server, generations ahead of the previous file server share. When the first users logged in and searches ran slower, I was in a panic. My team worried about the size of our inventory data in memory and quickly upgraded our 4GB, 1996-era server to 8GB of RAM, hoping for improvement. We also hurriedly split out queries and separated some data in an effort at quick tuning. It wasn’t much above a knee-jerk reaction, but we had users, and management, yelling at us on an hourly basis.

    I’m sure many of you have had other performance problems that resulted in some panic during your career. I’ve had more since then, and will probably have more in the future. Many of us do, and many of us perform knee-jerk performance tuning. However you can, and should, avoid tuning in a panic, as pointed out by Paul Randal. Doing so usually doesn’t fix the problem, and when it reoccurs, it’s more embarrassing with people less likely to listen to your next great idea.

    Tuning a system is a science, but it’s a complex science. Without experience, without having knowledge about all the complex interactions, and a deep understanding of how all the parts fit together, it often looks like a hopeless task. Who can keep all that information in their head and relate all the various components of the application? The network, the OS, the SAN, clients, all can cause problems that appear to be in the database.  As Paul points out, failing to understand the actual root cause can lead you to make changes that will have no effect on the actual underlying issue. You’ll often realize this when the problem re-occurs in a short time.

    I think one of the best reason to hire consultants is that they see so many problems that they can recognize patterns in performance that many of us don’t see. What they do appears to be an art because they can seemingly understand what information is relevant, and what isn’t. They walk down a list of potential issues, gathering evidence in a logical way, but one that can appear random to someone with much less knowledge and experience.

    All of us can learn more, we can read about problems others have had and learn how they solved the issue. However we often face the same reaction that many consultants do: no one wants to implement the advice. Until clients and vendor are willing to tune their code, many problems will only be masked by hardware.

    For a time. Then they’ll reappear.

    Steve Jones