Voice of the DBA

Category: Blog

  • SQLServerCentral–Fixing the Time Zone

    One of the bugs features of the bbPress forum software is that all posts were shown in a time zone, which for us was a default of UTC. Not a bad choice, but this made for funny displays, depending on where you were. Posting in the UK showed you as having posted an hour ago after Daylight Savings Time. Posting in the US showed future times.

    A mess, for sure.

    It’s fixed, or at least, it’s mostly fixed if you’re a traveler. This fix rolled out while I was in Australia, and as a result, I logged in and my time zone got picked up as Sydney. That made sense, since that’s roughly where I was (Brisbane or Melbourne), but it made for some funny posts when I was back in Denver. I replied to a post yesterday and the time I was for the original post and my reply were both the next morning.

    There’s an easy fix for now. You can access your profile from the upper right menu. Once it comes up, click “Edit Profile” and then find the time zone. If it’s wrong, fix it and the posts will be at the right time.

    It’s something I wish we’d thought of early on, but in testing, we usually were looking for the posts in the right order, and I never considered if the actual time was the time I’d was working in. A mistake on my part, and my apologies for those in the community that were distracted, upset, or confused by this.

  • Just a Week to SQL Sat Pensacola

    One of my favorite events, in a wonderful place to spend the weekend. I’ve been to Pensacola a few times (3?), including a couple times with my daughter. It’s always a fun weekend with Karla, Rodney, Ed, and the other organizers that make it a fun event. Even deep sea fishing Sunday if that’s your thing.

    This year I’m heading back after missing a few events. I’m lucky to go and I’m looking forward to delivering a new presentation. This one is one the future of Database Development with Containers, showing a vision of how things are changing. Microsoft has shown some similar items, but I’ve got some cool technology to show off from the Redgate Research Division, the Foundry.

    If you’re nearby, register and join me next weekend, June 29th, for SQL Saturday #884.

  • A Double Day

    I’m traveling back from Australia today. A few weeks ago I lost a day when I flew West across the Pacific. Today I get it back, with a different sort of time travel. I’ll leave Melbourne at 9:30am and land in Los Angeles at 6:30am.

    Three hours before I leave.

    Of course, but the time I get to Denver, I’ll be a little tired. Hopefully I get back on schedule quickly as work starts again tomorrow.

  • 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.