Voice of the DBA

Tag: Performance

  • The One Metric to Rule Them All

    When we want to know if our databases are running well, most of us look at metrics for our servers. Usually some combination of CPU, RAM, Disk IO are chosen, though network, user connections, batches and transactions can be a part of tracking performance. I would guess if I asked any of you that are responsible for performance whatmight be a good measurement for your most important database server, you’d have some idea of what CPU, RAM, or something else should be running at. I know I can easily check on SQLServerCentral.com at monitor.red-gate.com and usually expect to see CPU in the 20-25% range. If it’s substantially higher, I know something is going on.

    I was listening to one of the DevOps Cafe podcasts recently where the subject was monitoring your infrastructure and how well it was performing. and one of the hosts mentioned listening to Werner Vogels from Amazon years ago. Apparently despite all the technical measurements that were available for Amazon’s IT infrastructure, the one measure that they most often used to track how well everything was working was the order rate. Given their size and experience, Amazon could look at order rate and decide if their systems were performing well, both hardware and software.

    Most of us might not be as tightly focused on our business, but I’m wondering if any of you use business based metrics when examining the performance of your hardware and software. Is there a metric for your business that might help you determine if your hardware is actually performing as expected? Or if your code does what the business needs in an application?

    At SQLServerCentral, we have various ways of analyzing our systems, but we have added some business based metrics. One of those is forum posts/hour. We should see a similar curve throughout the week that shows how many posts we’re getting. Across years of data, we have a fairly steady rate of posts that occur on a regular basis. If we’re not getting a steady set of posts, we know something might be wrong.

    Perhaps you don’t have any business based metrics that you use, but are there any that might make sense for your industry? Let us know today. And if you think that there isn’t a good way to measure your hardware and software with business metrics, I’d be interested in your explanation as well.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Why Use R Services?

    I ran across a blog post from the very talented Joe Sack recenty, who I hope makes a few more minutes for me the next time we’re in the same town together ;).

    The post is a look at some of the customers that are using R Services in SQL Server 2016. As you might expect, there are highlights from customers that show dramatic improvement in performance. Going from 200 valuations/hour to 12,000/minute and taking a process from 2 days to 1 hour. I’m sure not all operations improved that much, but I bet most had some improvement. R Services is a big improvement in the way data is analyzed with the R language.

    What I really like, however, is that the piece includes some of the gotchas customers experienced, with links on how one might go about fixing the issues. There are also hints on visualizations and performance tuning options. I like looks at technology that include some details on those items that work well or don’t work well. This is a good overview that I hope gets more customers interested in using R services.

    But, I hope that we see deeper pieces that can give technical guidance on specific scenarios. Which models scale well and which don’t? Which options in some analysis improved (or hurt) performance? There are plenty of items which are best answered with specific examples, rather than general advice. With all the customers and data Microsoft gathers, especially for companies that might use R Services in azure, I’d expect that we could get some useful, detailed examples on how this (and other) technologies actually work in the real world. I’d like to see this guidance, not just with R Services, but with other technologies as well. Certainly providing some sample data sets and code that performs well really (or tuning options), is what most of us want to see.

    Of course, I’d like to see more of these stories and details at SQLServerCentral as well, so if you are solving problems and want to publish something, drop us a note. We’d love articles from the real world, whether on SQL Server 2016, or any prior version. The more reference problems and solutions we have, the more people learn to code better.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.1MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Finding Problem Code

    Performance of a SQL Server seems to be one of the main concerns for most developers and DBAs. We are under pressure to constantly squeeze more performance out of our applications and reduce the time it takes for users to interact with systems. Reports can be especially problematic as users don’t want to wait for results, but certainly data entry tasks can’t be impacted either.

    Whether or not we can actually make changes to the system, or even have time to bother might be irrelevant. I’m sure there are plenty of databases that some of us don’t even bother to try to tune because we’re busy elsewhere. Likely writing more code for additional features or reports that have been requested. As a result, we may pile up lots of code that isn’t necessarily run often. However when that code is executed, we’ll receive no shortage of criticism if the code doesn’t perform well.

    This week I wanted to know how you might go about finding the code that is problematic if there isn’t a complaint. If you do have time to pro-actively tune your system, what are the techniques you use to examine a system. I suspect the more advanced people will have answers, and I hope they share them as there’s no shortage of readers that may struggle to improve the impressions of their database server.

    What’s the best way to find the SQL statements you need to tune?

    It’s a simple question, but imagine that you know there are complaints, but don’t know what is the best way to focus your time. You, as a new or accidental DBA, want to improve the system, but where do you start? What code is the problem? Is the report that your boss complains about that’s the issue or are there other queries that slow down the report execution?

    Share the way you’d tackle a system when you don’t have a specific query in mind to tune.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Searching for Plans

    Today’s editorial was originally published on Sept 13, 2010. It is being re-run as Steve is away at training.

    I was re-reading Conor Cunningham’s “Does Join Order Matter?” recently and found this quote in the post that stood out. As an aside, read this post. It’s fantastic.

    “…SQL Server’s QP is actually not set up to give you the best plan.  Instead, it is set up to give you a good enough plan quickly (where good enough is very close to “best”).”

    I get that, and it makes sense. Why spend 4 hours optimizing a query that runs in 2 minutes to get it down to 1:50 (Conor’s example) when the optimizer can spend 1 sec and have it execute in those two minutes. If the optimizer looked for the best plan, I’d be very angry as a customer. Especially if this happened with any frequency.

    Or would I?

    I’m not suggesting that we ought to optimize every query, or that I’d prefer the optimizer didn’t try to normalize queries, or that I want to see the internal tree and try to figure out if I can rewrite the query. I might, however, want to do that for some queries. There are queries that I might want to find the “best” plan for, either because they take a long time to run, or they are run often. I could then use that in something like a USE PLAN.

    Computers are cheap these days, as is disk. If I were to somehow able to copy my database onto an instance of SQL Server on a cheap PC, wouldn’t it be cool if I could set that optimizer loose on my query and let it try ALL possible plans? I’d be happy if it spent that 4 hours and gave me back an optimal plan that I could carry back over to my expensive, busy server, and shave that 10sec off a query that I run 10,000 times a day.

    I could see some value in a “test” instance of SQL Server that just looked for optimal plans. Now if I could just get Microsoft to build it.

    Steve Jones