Voice of the DBA

Author: way0utwest

Exploring the Caves of Code Analysis in #SQLPrompt

I enjoy themes, and when I ran across the SQL Prompt Treasure Island, I had to take a few minutes and go through it. I wrote about a few of the items, and this post continues on with a feature that was added last year to SQL Prompt, Code Analysis.

Code Analysis

One of the big leaps forward for computer science, in my opinion, was the development of various static code analysis (SCA) tools. These are automated programs that examine the structure of source code and look for potential issues with the way the algorithms are implemented. This was originally the job of a fellow programmer, and still is in many cases, but humans make mistakes, and reviewing someone else’s code is a tedious, somewhat boring task. Over time, many humans become worse at it as we look for certain issues, but may ignore others.

Over time, SCA tools have included scanning for potential security issues, such as buffer overruns, which can easily permeate many systems if the developers do not follow coding practices designed to avoid issues. I wish we had such advances in SQL tools, but they’re not here yet.

In SQL Prompt v9, Redgate added some SCA features. These were a set of rules that are used to scan your T-SQL code for potential issues. such as casting data types without specifying a length, or as the Caves of Code Analysis post shows, forgetting to qualify an object. In my example below, the green squiggly line below the code represents an SCA finding, and as I’ve hovered over the line, I see the warning about an old style join.

Each of the rules is designed to highlight some issue that is known to be a potential problem. For the most part, these are useful tools and you should use these to examine your code. Some, however, aren’t useful and can be annoying.

There is a dialog that allows you to enable or disable any of the rules, which are divided into different types. I often disable ST002, which deals with aliases. I prefer the old style equal sign as opposed to the AS syntax. This isn’t a code issue, so I don’t need the warning.

This is a basic set of SCA tooling for T-SQL code, but it does serve to educate newer developers about the dangers of using certain patterns in their code. It also reminds experienced people if they’ve done something like used COUNT() in a test instead of EXISTS(). Those types of changes can often improve the overall quality of your code.

Give SQL Prompt a try today and I’m sure you’ll be pleased with just how quicker you can write code and learn about the potential issues you’ve been including in your code.

April 16, 2018
Believing the Data

This is interesting to me. Let’s leave aside the moral, ethical, or political sides of this. Let’s just look at the data. A cop gave a ticket to a self driving car. Apparently the officer thought the car was within 10 feet of a pedestrian. The logs for the car show that it was 10.4 feet away. A negligible difference to me, but that might matter in a court of law.

As we use digital data more and more to control vehicles, devices, even behavior, we are going to have problems, complaints, and unforeseen issues amongst humans and computers. Certainly our laws need reforming to deal with the digital world, but we also will have some cultural transformations to undertake.

Much of our arbitration in the world has dealt with how well humans argue or debate topics. We are inconsistent, easily confused creatures, yet we often use our judgment and impressions to make decisions about whether we believe one individual over another. This has often been the case in law, even as more science with cameras, DNA, and other forensic techniques have tried to provide definitive proof one way or the other.

As we use more digital devices, there will be, or should be, more logs and audit records of how systems behave. This worries me a bit in Machine Learning systems, but experts seem to believe we can unpack the rationale for decision making if necessary. In any case, we need to learn to trust and believe in the data, even if our eyes and instincts lead us to different conclusions. This doesn’t mean that the data makes the decision, but we can’t discard the data because we don’t like it. Certainly context matters, and in the original case above, we’d want to consider velocity and acceleration, and possibly other factors to determine if the car was too close.

Ultimately, I believe digital records will start to prevail in more circumstances than we might be comfortable with. We’ll slowly change, and likely subsequent generations will trust data more than we do, perhaps even more than their senses. To me, this means that those of us that deal with data systems need to ensure there is extremely strong security and integrity of data records, and that we disclose algorithms and data processing techniques in a transparent way. Trust requires knowledge, which requires transparency. That’s something we certainly need to improve on as an industry.

Steve Jones

The Voice of the DBA Podcast

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

April 13, 2018
Remove-DbaBackup with dbatools
I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

One important item for any system administrator to manage is the removal of old files that aren’t useful. I know most of us hate to delete data, but there are log files, backups, and more that will clog up a drive over time if they’re not managed. I’ve had SQL Servers stop because old copies backups filled the disk and I’ve had IIS servers start throwing errors because 2 years worth of logs were on stored on the C drive.

Maintenance plans had a way to remove files and we have xp_delete_file, but there are limitations to ensure that only backup files are deleted. I think those are silly, but it wasn’t my decision to include restrictions, and I don’t get a vote on future changes.

In any case, dbatools has a cmdlet that can help: Remove-DbaBackup. I was interested to see if this worked on it’s own or had restrictions, but it seems to work wonderfully for me.

Required Parameters

Most cmdlets will allow quite a few parameters to be optional. In this case, however, there are some requirements. First, you need a path for the backup files. That makes sense and no big deal.

However, you also need a retention period. You can’t skip this, as if you do, you get a prompt.

The retention periods aren’t obvious, but not that hard to remember. There’s a numeric counter and a one character time period item. They are:
- h for hours
- d for days
- w for weeks
- m for months
That’s it and not a big deal, though for testing I need to play with my system clock a bit.

In any case, after this parameter, you need a backup extension. This is the file extension, without the period. You can put in anything, which is cool.

There are some other params, but not required.

For testing, I copied some backups and then changed some extensions. As you can see, my test folder has SQL backups with various extensions I’ve encountered as well as a few text documents.

If I run Remove-DbaBackup with some options, I’ll see what will happen with the –WhatIf parameter. I see plenty of files being marked for deletion as long as I have the right extension.

This is handy, and it makes perfect sense when you read it. This is exactly the type of maintenance job that you want to set up on a server to remove old files. I don’t know that I’d use this for general cleaning of files that I might need soon for a backup, since I always want to be sure that I have a good backup before I remove old files, but for managing very old files, this is helpful.

And, a little scripting logic would show you how to find the date of the most recent full backup and then remove files older than that. Or maybe older than the last two fulls.
April 13, 2018
Considering Other Views

I love my Pebble 2 watch. It’s simple, the charge lasts for days, and it does what I want out of a device. However, it’s a device that isn’t made anymore, and after a fall, I have a crack in the screen. Since I like having some sort of watch in my wrist, and I like gathering some health data, I started looking for other options. I was reading a review of the Fitbit Versa the other day, and the first part of the review was a literal problem with much software design I’ve seen recently.

The first item in the review talks about notifications. Those are valuable on a device like this, and one of problems is text size. There’s a quote in there: “The text size is tiny, even when you select the item”. That’s a problem I’ve had with many devices, including the Pebble. In fact, I see this in software overall as a problem with design. It’s one that comes about because I think that far too often we don’t consider a wider range of viewpoints.

I’m getting older. A consequence of that is my eyes do not focus as well as they used to and I need to increase the fonts on screens. I can easily do this in browsers, and make SSMS fonts larger in most cases, but not all. I can increase the size of some things in Windows, but that can cause issues in others. My phone allows some font changes, but that can be limited. There are times when notifications or other text is hard to read, and this limits the usefulness of these features.

Technology has been growing and expanding as I’ve aged. However, it seems that more and more often companies are using people in their 20s and 30s to design systems that will increasingly be used by an aging population. From dashboards in vehicles to labels in applications, it seems that far too many designers don’t consider the impact of their font, icon, and graphic choices on older eyes. Even the design of our operating systems don’t seem to have deeply embedded extensive flexibility of changing text and icon sizes. Sure we can alter resolution, but that’s a very intrusive operation and may break other things. Often we just need labels enlarged.

Software and UX design are hard, and often we get caught up in our own viewpoint of how to build an application. We won’t ever completely solve that as users will always have different ways they want to work with our systems. The ability to change options, and especially deal with accessibility choices will grow as our systems move out to a wider and wider audience. What might have seemed simple and intuitive may be more complex for new users that haven’t evolved with out system.

Our database software likely isn’t going to be often seen by end users, and certainly we don’t have control over the tools they use, but we still ought to consider how our objects will be used by others. More descriptive names, extended properties that tools can read, and even views to remap complex structures are good ways to provide an easier interface to the data for report designers and third party tools. Those items come with a cost and have to be maintained as we change schemas, but that might be a small price to pay if we can prevent lots of support tickets requesting details about what OrdrLnPrc means.

Steve Jones

The Voice of the DBA Podcast

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

April 12, 2018