Voice of the DBA

Author: way0utwest

  • Testing T-SQL Made Easy

    Abstract:

    Everyone tests that code, but most people run a query, execute a procedure and run another query. This ad hoc, non-repeatable testing isn’t reliable, and encourages regression bugs. In this session you will learn how to begin introducing testing into your development process using the proven tSQLt framework. You’ll run tests with a click of a button, using an ever growing test suite that improves code quality. You will see how to handle test data, exceptions, and edge cases.

    Level: 200 (I am assuming you know something about unit testing and tSQLt)

    Demos:

    • Creating exceptions to standards
    • Catching changes to large tables
    • Ensuring the join works correctly
    • Testing Getdate()
    • Isolating functions and stored procedures from other calls
    • Checking boundary conditions

    Downloads:

    Powerpoint: Testing T-SQL Made Easy.pptx

    Code: .zip file or Github repo

    Presentations

    • SQL Bits XV – May 7, 2016

  • Back Home, Despite a Scare

    I’ve spent the last 11 days traveling in Europe, returning home late yesterday. It wasn’t a holiday or even corporate boondoggle as this was 3 cities, 2 events at which I spoke, travel in planes, trains, and automobiles, a minor sinus infection, and movement across 6 hotels. All with just this luggage:

    13087787_10209272872407736_4048621762789380628_n

    Packed too full, and heavy, but I made it.

    I returned late afternoon and in between spending time with family, I booted up my desktop and was surprised to find that Dropbox gave me an error. As I dug in, one of my SSDs wasn’t visible in Windows, containing VMs, my local DropBox folders and a few other things. Not what I wanted to deal with the first night back.

    I shut down the system and rechecked connections this morning, then rebooted to find things working, but it was a good reminder to double check my backups and ensure that I’ve got copies of data in case I do lose an SSD.

  • Solving Tempdb Issues

    While reading Remus Rusanu’s piece on SQL Server 2016 development, there was an item that struck me. There’s a part of the piece where he notes that an engineer at Microsoft realized that there was a tempdb issue with Azure SQL Database. To solve it, a failover was needed, which could be problematic. The basic issue was that tempdb files were sized unequally, discovered after “months of investigation.”

    Now, on one hand this seems silly. After all, we’ve known since SQL Server 2005 that the guidance was for all files to be sized the same. Shouldn’t engineers at Microsoft be following all the practices  known for optimizing SQL Server performance? I think all Microsoft people should follow this, especially those working on other products (*cough* Sharepoint *cough*), but at the very least SQL Server engineers should have a huge list, perhaps with PBM or unit tests, that warn about non-compliance.

    On the other hand, since we’ve known this is an issue since SQL Server 2005, why does SQL Server allow this? I’d think some simple tooling and a few checks in code could eliminate this as an issue in any new install. Catch the issue during upgrades and grow (or shrink) files to be the same size, or at least warn the administrator. I know there are situations where you need to add a new file in an emergency, but shouldn’t this be an item we push administrators to correct quickly after the emergency is past? Or at least force them to consciously make an exception.

    There are plenty of limitations and restrictions in SQL Server systems that Microsoft forces on us. We have limits on where new features can be used, various historical limits on function parameters, and more. Why not also just enforce limits that prevent issues like this? I’m sure people will complain, but wouldn’t those complaints be outweighed by less issues overall from all customers?

    Steve Jones

    The Voice of the DBA Podcast

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

  • Use Tools

    This editorial was originally published on May 23, 2012. It is being re-run as Steve is out of town.

    As someone that works with Information Technology, I usually work on applications designed to make work easier. For example, we have accounting systems that ease the handling of debits and credits, they detect mistakes in data entry, and in general require less people to handle the accounting needs of many firms. Do we have less accountants?

    I suppose we might have less, but it seems that the finance departments, handling AP and AR in many companies is as large as I remember from my younger days. Perhaps the department is smaller than it might otherwise be, but it’s usually not small.

    In IT, we have all kinds of tools available to us that can help with our jobs. What’s more, we can create new tools as needed to do our jobs. Powershell or Perl scripting, Codeplex projects, and more are available to most of us to help us better manage systems. In SQL Server, we even have a built in framework, Policy Based Management, that helps us prevent changes or problematic configurations. If you find a third party tool that can help, you can make a case for its purchase, using the time savings translated to actual costs. One of those tools might really make your job more enjoyable..

    In theory, we ought to be able to manage many more systems per person than we used to. Does that many that we need less people? Perhaps, but it seems we grow systems fast enough that we still need to hire more staff in many environments.

    I so often see people working in IT fail to take advantage of all the tools we have to automate much of their jobs. They often tell me if they automate too much of their jobs, they won’t be needed and may get let go. Personally I think that’s an excuse not to exercise your skills, challenge your mind, and get rid of tedious work There’s no shortage of work to be done in most companies. If that’s true, why not use your tools to get rid of the tedious work and spend your time on something more interesting?

    Steve Jones