Voice of the DBA

Tag: Performance

  • Extreme SQL

    I have made a career of working with SQL and databases. Usually I’ve looked for interesting companies and people, but I’ve avoided extreme situations. For me, that often is very large, or very real time environments. I once declined a job for a 13TB database on SQL Server 6.5. My suspicion is that job would have taken me away from my young children and wife far too often.

    Facebook has a lot of users, and a lot of queries they run. With over 1billion daily users and hundreds of TBs of daily uploads, they really need strong databases. While they have multiple databases, and that includes SQL ones, they have struggled with analytic queries in the past. They started using Presto as a solution, an open source query engine for running analytic queries against data in different storage locations like RDBMSes or in something like Hive/HDFS. This sounds like what Polybase does for SQL Server.

    The problem with any engine at Facebook’s scale is the load. While they like Presto, they needed to make it work better. They initially built a caching layer that required users to build ETL jobs to load data into SSDs attached to the Presto cluster. However, they outgrew this and ended up turning to a distributed file system called Alluxio.

    The article linked above talks a bit about how this works, and allows users to query petabytes of data. Most of us have users that often don’t qualify their queries completely, so we expect that some queries that might need to scan 100GB end up reading much more until the users tune them appropriately.

    The thing I found interesting in here is that some queries were taking up to 10s, which users found unacceptable. The move to Alluxio gave them a 30-50% boost, which doesn’t sound like a lot. 5-7s over 10 isn’t a great savings to me. The reduction in reads, is impressive, which is good, but I wonder to what expect there is some management and tuning needed here to ensure the cache works well.

    I have no desire to work on these extreme systems, but I am glad someone does. The lessons and tricks learned here often trickle down to improve the daily performance many of us see in our smaller systems. I think that the Hyperscale work Microsoft is doing, and the Big Data Clusters, are fascinating ways of organizing SQL Server based servers, and some of that tech will likely trickle down and help us continue to improve our smaller systems’ performance over time.

    Steve Jones

    Note: Podcasts are suspended for a week as I deal with the PASS Summit.

  • Waits and Queues

    How many of you have read this article: SQL Server Performance Tuning Using Waits and Queues? (PDF) If you haven’t, you might look at a review of the paper from Jeremiah Peschka. There is good advice about which parts to read and which to ignore a decade later. There’s an updated version from Paul Randal and SQLSkills as well.

    This leads me to where you might expect this week, with a question for you. Do you use waits and queues to troubleshoot? (Survey)

    Not every performance problem needs you to dig deeply into the way that the database engine processes your query, but there are no shortage of times when understanding waits and queues can help you determine how to tune your system. This is a well known method of trying to determine where to focus your efforts, so I wonder how many of you have tried this.

    If you have, let us know what’s worked or what hasn’t. If you’ve never tried this, then drop a quick note as well if you have a reason you haven’t used waits and queues.

    Performance tuning is a skill everyone needs, and most everyone can work on. This is often one of the most popular topics for talks and classes, and any help you can get will grow your career. Both in your own self confidence as well as impressive to interviewers when you have an answer or a methodology to give when solving any problems you’re given. Whether in an interview or in your job.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Simulating Load

    I needed to generate some load for a demo and wasn’t looking forward to doing it. I was in a hurry, and didn’t want to deal with a lot of setup. Fortunately, I’d seen a technique for doing this in one of Brent Ozar’s classes. I’d taken a couple, and they’re good. One of the items I took away was the need to simulate a load that might help me analyze my system.

    Brent’s written about his technique, and I decided to adapt this to my own demo database. I took his code, and then added a few items.

    Examining Parameters

    I was happy with a load of ten items, so that makes things simple. I could reuse most of Brent’s code, but unlike his system, I have different parameters in use for different types of calls. In examining the types of procedures that might generate load, I found I had these types of parameters

    • Integers
    • Dates
    • Strings

    The basic code uses RAND() to generate a number, but what about the other items? I didn’t want scalars, so I needed random other items.

    For dates, I still used a similar random integer, but then used that with DATEADD() to alter an existing date. My code would generate a separate random number and then use that to go backward in time a certain number of days.

    SELECT @off = RAND() * 1000;
    
 SELECT @dt = DATEADD(DAY, 0 - @off, SYSDATETIME());

    For strings, it was different. In this case, it was search items and while there can be a (seemingly) infinite number of possibilities, I can simulate this. I can select from the table and use a random ordering to get different terms. If this isn’t efficient, that’s fine. I want a load on the system.

    DECLARE @term VARCHAR(100);
    
SELECT @term = SearchTerm
    
FROM dbo.SearchTerms
    
ORDER BY NEWID();
    
SELECT @term;

    Once I had these techniques, I built a stored procedure just like Brent’s, and then replaced his procs with my own. I built parameters in front of the CASE statement, and then use the appropriate random parameter for the procs as needed.

    Running the Load

    I used the same SQLQueryStress tool that Brent did, setting the connection credentials, threads, and iterations as needed to generate load. This gives me control over the load I need to generate.

    I had 10 possible procedures, each of which runs with different parameters each time the proc is called. For 100 iterations of 4 threads, I generate 400 calls to my load procedure, which generate 400 random calls to 10 other procs with different parameters

    A quick and dirty load on my server. Not representative of any production load, but good enough to stress the system and let me look for places to tune code. And, a way that I might get some of the weird random things users do.

  • Lack of Memory

    I was reading Brent’s look at “normal memory” for his SQL ConstantCare® clients. It’s a look at some stats from quite a few servers that customers have set up and he is monitoring. Since most of us only have a limited set of instances to examine, it’s nice to see what a wide variety of installs are using. Even though this doesn’t necessarily mean we should change anything in our environment, it does help me to understand some general trends, and perhaps think about how to recommend settings for new installations.

    When we build a server, we expect it to work a certain way. Over time, we are almost always adding data to the system, but often we don’t add more RAM. This would be like buying a vehicle and loading it up with supplies you need for work. If the amount of supplies kept growing, would you keep piling them on or buy/rent/borrow a bigger vehicle? Some would, some wouldn’t.  I’m always amazed by how far people will push a situation, especially when fairly small and inexpensive changes might make a big difference.

    Data size isn’t always a good indicator of how much memory you should allocate, but it can be. Certainly the more data we query in a workload, the more memory makes a difference. Bad code is more likely to surface issues, but memory is often cheaper than paying developers, or usually, finding and training developers to write better code. For a DBA, this might be one of the relatively few differences we can make in the short term that has a far reaching effect. Adding memory might cover up some poor coding and speed up the experience for clients in the short term. In the long term, things will likely tip over again.

    If you manage an instance, you can’t usually change code, and it can be hard to get developers to prioritize changing existing code. Most developers are pressured to move forward with new work, not refactor old work. One of the reasons I like moving clients to a database DevOps flow is that DBAs with query tuning skills can spend more time tuning some queries and giving code to developers. It’s much easier to get developers to fix bad code if you give them better code than ask them to find time to rewrite things.

    Getting the best performance out of your database server often a balancing act among various choices that each only solve a portion of the problem. Don’t be hesitant about asking for more memory over time, but also be careful that you don’t just depend on this one technique to reduce the number of customer complaints. Learn to write better code and teach others to do the same.

    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.