Voice of the DBA

Tag: Performance

  • More Query Tuning?

    This is probably a topic near and dear to the hearts of Grant, Brent, Paul #2 (White), and plenty other more well known speakers in the #sqlfamily community that often present on the topic of writing more efficient code. They do a fantastic job and if you get the chance to see any of these three, take it.

    Recently I saw someone on Twitter ask for more query tuning sessions at SQL Saturday and larger conferences. These seem to be very popular sessions, usually well very attended. Despite this, I don’t see a lot of these sessions compared to the popularity. I sometimes wonder if this is because relatively few speakers want to tackle complex challenges? Or maybe many don’t feel confident portraying themselves as experts in this subject? Is query tuning 101, or even 201, boring and less interesting for speakers?

    I don’t know, and I’ve avoided the topic myself. Part of this is to not conflict with friends, but also it’s a complex topic to try and cover. Despite that, I keep thinking that some more basic concepts would be welcome by many that attend SQL Saturday events. I expect that a there could probably be some sort of performance or tuning talk every hour at a conference and plenty of people would attend. That makes me think I ought to do a tuning session of some sort, just to help ensure this topis is covered more often.

    Picking a mix of topics and levels is often a difficult task for many organizers. As this year’s Summit sessions were released, I’ve seen a number of speakers bemoan that their favorite topic has few, often just one or two, talks scheduled. I think this is somewhat inevitable as new technologies get folded into the Microsoft Data Platform. I know that many of us are excited about one thing, the item that we use most often, or that we’d like to use more. I think the addition of Python is great, but it’s a small part of the platform, it’s new, and I don’t know how many other people want to use it. The same thing could be said for containers, for Query Store, and more.

    Building a schedule for a conference is about making choices and decisions that give a variety of topics, but also include some depth and detail in those areas that are popular. If you’re attending (or attended) a conference this year, the Summit, SQL Bits, a SQL Saturday, etc., what do you want to see on the program? Should there be more performance tuning sessions or do you like a wide variety of topics that let you choose what might suit you?

    Steve Jones

    The Voice of the DBA Podcast

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

  • SQL Server Tuning Skills

    Performance tuning a SQL Server instance, database, or even query is a skill that most of us could improve. In fact, I see that the query performance presentations are often the most popular learning items at any event. I’ve seen quite a few people asking for more tuning talks in different areas, which is a good thing. I think most SQL Saturdays could schedule a different tuning session every hour, or even repeat the same one a few times, and the room would be mostly full each time.

    Learning to look at the various possible problems in a SQL Server is both a science and an art. There are good methods, such as waits and queues, for examining where an issue occurs. There are basic techniques for looking at execution plans and then making changes to indexes or T-SQL. There are different options for queries, SET values, and more that will affect your query or even all your queries.

    Sometimes it seems there might be too many options for a beginner to decide how to begin examining a system, especially one that is having issues. I can’t cover all of the issues in this short piece, but I can give you some guidance that might help. First, make sure that you know how to gather information about your issue. We have forums at SQLServerCentral to answer your questions, but you have to do some work. Gail Shaw wrote a great piece on how to post performance problems.

    Ultimately, you need to understand the recommendations that we may give. It’s helpful to learn about indexes, and the differences in the various types (including Columnstore). You should also have a baseline so that you can measure the impact of your changes. How do you know the problem is fixed? Maybe the user gets lucky with a quicker experience when you tell them you’ve changed the system, but will they call again soon? That second call might be worse than the first one. Even getting a baseline of the poor performance now will help you understand (and prove) that the system is running better.

    You also need to understand what not to tune, and which items to ignore. I thought quite a bit about this after reading Paul Randal’s post on wait types to ignore. This is part of his 101 series, which are a good way to start improving your skills. Performance tuning takes practice and knowledge, which takes time to acquire. Even if you’re fighting a problem today, try and learn a little something about how you get your system to run better.

    I’d also recommend that you look to continue your education over time. Take advantage of those sessions at local events, like user groups, virtual groups, and SQL Saturdays. Take advantage of pre-cons at larger events, which pack lots of learning into a few hours. If you go, take a paper pad, not a laptop, and take lots of notes. If you can’t get away, maybe you can lobby for some tuning classes from sqlSkills, Brent Ozar Unlimited, or even self-paced courses on Pluralsight.

    Learning to become better at tuning is an ongoing process, and one that requires some investment. Perhaps some money, but definitely some time and practice are necessary. Don’t let this be a daunting task, but one that’s a journey. Your goal is to get a little better each time you work on this skill, not to be an expert that knows it all. I’m not sure anyone gets to that point, but you can become as good at this skill as any of the presenters you watch. It might take you longer than it took them, but you’re also able to build on their work and learn from their knowledge. Take advantage of that opportunity.

    Steve Jones

     

  • We Need DevOps for Performance

    I read this nice piece on CosmosDB and setting performance levels. It covers how you set some level of performance for Request Units (RU) and CosmosDB handles the rest. Great, right? Set the level of performance you need and that gets handled. The question I have is how do you know what level of performance you need?

    This is one of the issues with the Cloud services that I see. It doesn’t matter if you use PaaS or IaaS, most of us really don’t know what level of performance works. We tend to guess, and far too many of us don’t use regular monitoring to decide if we are over or under powered. I find many DBAs and sysadmins would prefer to be over-provisioned and then let the server trundle along at a lower resource usage so that no one complains.

    When we move to a cloud type service, we tend to be more cost conscious, which makes sense when we’re paying by the minute. We want to minimum level of hardware we can get, but we don’t want to cause unnecessary complaints, either from customers because the system is slow, or from the CFO because costs are high. Using your old method of over-provisioning hardware (or DTUs) usually causes complaints from the CFO.

    The piece goes into some ways that you can start to evaluate your performance level for CosmosDB, and how to deal with throttling while you tune your RUs with a new application. This works great if you’re in a (more) greenfield area of development. Not so great if you’re lifting and shifting some application from another platform. Then we might need to ensure that we are responding quickly to issues, or better yet, have scripted responses that scale up or down.

    The same thing should be done for our relational systems. The Ops part of DevOps needs to be using monitoring and instrumentation to measure performance, adding capacity as appropriate, which should be before users realize there’s an issue. With today’s virtual systems, adding CPU and RAM usually is fairly easy, and it’s easy in the cloud as well.

    Of course, all this monitoring isn’t just to add capacity. Having a better sense of what’s going on can help you pinpoint poor code. Getting someone to fix that code becomes a lot easier if you can show that better code would cost less for our systems. It can be amazing how much more developers care about their code when the CFO gets involved.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Graphing Performance

    We have a lot of different database platforms to choose from when building software. Most of us reading this are SQL Server users, and likely relationally biased. However, key-value stores, document databases, graph databases, and more are out there. If you work with developers that embrace change and new options, likely you’ve been asked about implementing some sort of NoSQL database instead of SQL Server for some project. Maybe you’ve even been asked to migrate away from SQL Server to an Open Source (OSS) NoSQL platform, with the lack of software cost being a factor.

    I do think that there are some domains of problems that relational systems don’t handle well. Certainly at scales (data volume or rate), there are better ways to deal with some data sets in a less structured and tightly coupled way. We see that in the large scale web companies like Google, Twitter, Facebook, etc. If these companies had tried to build their entire system on a RDBMS platform, they would have struggled to grow, and maybe not even reached the size they are.

    I’ve been reading and playing with the new graph capabilities of SQL Server 2017, trying to determine what I think of the concepts. Certainly large scale many-many relationships don’t seem to be a strength of relational databases and I’ve thought there are certain types of queries or data models that might be better handled by a graph database.

    Then I ran across this report from a few researchers that examine how graph database compare to relational ones. After all, we’ve grown accustomed to using RDBMSs in many environments and situations. What better way to evaluate the performance of a specialized database than compare its performance in the problem domain its designed to solve to that of a general database platform.

    The results are a little surprising. Even with a sub-optimal query language, I would have expected the graph database to perform better. Instead, relational seems to handle the reference graph workload better. Raw performance isn’t everything. Ease of development and ability to scale are important. There may be other considerations in your system as well, but I did find this to be an interesting paper.

    We will see how the world of specialized databases handles real world workloads over time as more companies use them, but for now, I’d be skeptical of replacing an existing, working RDBMS with something unproven. I’d need to see a good POC that shows quite a bit of improvement across a variety of metrics, not just scalability.

    Steve Jones

    The Voice of the DBA Podcast

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