Voice of the DBA

Tag: syndicated

  • Chips and Margaritas at SQL Saturday #461

    It was a few years back that my wife and I planned a trip to the Austin City Lights music festival to see Stevie Wonder. This was a bucket list item for us, and we thoroughly enjoyed a long weekend in the city. I’ve been for SQL in the City as well, and each time I’ve gone, I’ve enjoyed a lunch at one of the Mexican restaurants South of the river. My kids always joke that my wife and I will fill up on chips and margaritas at Mexican restaurants, and it’s somewhat true. Often dinner becomes lunch the next day.

    It’s just two weeks to SQL Saturday #461 in Austin, and I’m looking forward to going back. In fact, I’m going to make it a point to at least go get chips and a drink at the same place. I can’t remember the name, but I know how to get there, so I’ll be fine.

    However the main event is the free, one day SQL Saturday event taking place. I’ll be there delivering my Branding for a Dream Job presentation, but there are tons of other great talks. From AlwaysOn to Power BI to Azure to Writing Faster Queries, you’ll have a great day of learning on all sorts of SQL Server topics.

    If you’re anywhere near Austin, register today and come join us in Austin for a fun day that kicks of my SQL Saturday 2016 tour.

  • Rebuilding a Heap–Don’t Do It

    I saw someone mention recently that you can run a REBUILD on a heap table. I hadn’t realized that, but when I looked in BOL, I saw that indeed you can run this on a heap. That’s interesting, in that you can now move the pages in a heap around to remove fragmentation. At first glance that sounds good, but I wasn’t sure.  A little more searching was required.

    The authoritative source for me on many things like this is SQLskills, and sure enough, Paul Randal has a myth piece on this. Paul has a great explanation, but basically if you rebuild the HEAP structure, you are creating work in that every non-clustered index also has to be rebuilt. Why? The pointers back to the heap pages, which are locations, will change.

    What about adding a clustered index and dropping it? Nooooooo, and again, I learned something new. This causes two rebuilds of the non-clustered indexes as they are rebuilt with the cluster addition and then rebuilt when the table changes back to a heap (to get the heap locations). That’s crazy, and certainly not what we want.

    The short answer here is that you want a clustered index, for the maintenance reasons here, as well as others. If you don’t have a fundamental reason not to create a clustered index, just add one.

    Just do it.

  • T-SQL Tuesday #74–The Changes

    It’s T-SQL Tuesday time, the monthly blog party on the second Tuesday of the month. This is the first T-SQL Tuesday of 2016, and all you have to do to participate is write a blog post. It’s supposed to be today, but write one anytime you can and look for the invitation next month.

    I’ve got a list of topics here, but watch the #tsql2sday tag on Twitter.

    This month’s topic comes from @SLQSoldier, Robert Davis. The topic is Be the Change, and it’s a good one.

    Quick Changes

    I’m going to write about SQLServerCentral here. Years ago we were updating our email system to send a high volume of email in two ways. At the time we’d considered purchasing software from others, but found the cost to be significant at our volumes (5-6 figures a year). Instead we needed to handle emails stored in our SQL Server database in two ways:

    • Thousands of bulk emails sent overnight, as quickly as possible
    • Quick, high priority emails sent in response to actions

    These two conflicting requirements meant that a simple queue of emails to send wasn’t easy for us to design around. We also needed to deal with the issues of scaling, so we wanted to have mutliple separate machines that could help spread the load.  We were building a small .NET process that would run every minute and send a series of emails.

    Our design process led us to the need to build in priority levels into our table. We couldn’t think of more priorities, but we allowed for them with a smallint. Our bulk emails were inserted with a priority of 2, and the registration emails, forum notes, etc, were stored with priority 1.

    Once we had a separaton of emails, we needed a way to determine what was sent already. To do this, we used a NULL date for the sending date. This allowed each process to determine when new information had been inserted into the table, and needed to be processed.

    This worked well for a single machine. The process would:

    • query for xx priority 1 emails
    • send priority 1 emails
    • update sent priority 1 emails with the sent date/time.
    • query for yy priority 2 emails
    • send priority 2 emails
    • update priority 2 emails with sent date/time.

    The updates actually occurred for each email sent, so we could easily track the time/order of sends for troubleshooting purposes. We would query a few hundred emails each minute, let’s say 500, knowing that was the rate at which we could send emails. We wanted all priority 1 emails to go, so our value for yy would be  500 – xx.

    As we worked to scale things out, we also needed to track what items were queried by which client. Our solution here was to add a machine name to the data, which was blank when emails were inserted, but would be updated by a client with its name as it queried rows. Since we were looking to determine which emails to send, we’d update xx rows with the name of a client process and then query back those rows. The query used the sent date of NULL with the client name to get the correct rows.

    Using a combination of the date sent, the client name, and the priority, we could easily manage detecting and working with changes to this table and build a high volume queue that worked extremely well on SQL Server 2000, and all versions since.

  • Hash Tables in PowerShell–Advent of Code Day 3

    I continue to work on solving the Advent of Code puzzles in both PowerShell and T-SQL after completing them in Python.

    When I hit day 3 in PowerShell, it was a few new tricks to learn, one of which was reading a large string from a file. However the interseting thing for me was learning to work with hash tables.

    The Day 3 puzzle looks at moving Santa around on a grid. We don’t know the size or shape of the grid, just that we get directions in 1 of 4 ways (^<>V) and then must move to a new house. The first puzzle asks how many houses get one present.

    This is interesting, and naturally to me the first thing that occurs is a dictionary. I used one in Python, adding new elements as I received new coordinates. I wasn’t sure how to do this in PowerShell, and ended up searching a bit about two dimensional arrays, thinking I could perform a count there, adding indexes as needed. However I ran into hash tables while searching, and this was a better solution for me.

    The short part of working with hash tables is that you declare them with a simple command. In my case, since I had a delivery at coordinates (0,0), I wrote this:

    $houses = @{“0,0” = 1}

    This uses the @{} syntax to set up a key value pair. Essentially a dictionary. From here, I computed new “keys” from the directions, and could add new values like this:

    $houses.Add(“1,1”, 1)

    Of course, I had to check for existing values, and if there were existing values, I had to increment them.

    $houses.Set_Item(“1,1”, $houses[“1,1”] + 1)

    With that code, I could easily solve the puzzle. However I was struck by the various ways I work with the hash tables. I use braces, {}, to declare the table. I use brackets, [], to access elements and then parenthesis, (), when calling methods. All of that makes programming sense, but it’s something to keep in mind, especially as those three marks mean different things in Python.

    I also learned how to search with ContainsKey and ContainsValue, as well as how to Set_Item and Get_Item, which didn’t appear to work with the ISE Intellisense.

    All in all, it was interesting working with a hash table and good to learn PowerShell supports them. They are very handy constructs when building up a set of data that you’ll need to work with, and you need more than the simple buckets an array provides.