Voice of the DBA

Author: way0utwest

Watching the Sands of Suggestion 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 Code Snippet Code recently, and this post continues to move across the map.

Incredible Suggestions

The first thing that most people notice after they install SQL Prompt is the suggestions that pop up as you type SQL code. This was one of the first things that captivated my interest after I started using the product. Seeing lists of tables, of columns, of valid syntax, pop up as I type is so useful that I struggle when I don’t get the suggestion box. By default, this appears quickly, and one of my customizations is that I have slowed it slightly so that quick typos don’t pop the box if I get rid of them immediately.

However, as the Treasure Map shows, it’s not just suggestions, but also the fact that I can hover over a suggestion and get more data, like the code that defines a table, view, or procedure. This is especially handy when using views, as I’ll look to see if I’m starting to nest views in queries.

I do find that CTRL+Shift+D is one of the shortcuts that I often need. I may create a table or run some code in another tab, and I don’t get refreshed suggestions automatically. There is an experimental feature to auto refresh suggestions, but this means more polling of the database, and I try to avoid adding more load to processes that are running. The shortcut works well for me.

The Dependencies Tip

SQL Prompt has lots of features, and plenty that I don’t use. A few I don’t use because I don’t know about them. The dependencies tip is one of these. I didn’t know about it until I read the Treasure Map post, but now I think that’s a really cool features. If I’m looking to alter my schema, one of the important things to know is what dependencies I have. Certainly I could use SQL Search, but being able to quickly decide what objects I need to consider, or where I need to make other changes, is great.

The Treasure Map describes this, but I had to experiment a bit to understand how this works. I’ve added a short animation to show this.

Now that I know this, I’ll get the list, copy it, and use it as a TODO list of things to alter in this same commit. I’ll also know where to test changes before I actually commit this code to a shared repo where others will see it.

There are plenty of other small features in this list, and you ought to experiment with these if you write a lot of T-SQL. You’ll find them to be helpful and handy.

April 24, 2018
New IT Departments

I had a friend that used to run an Exchange system. Actually, he was part of a team of four that managed a 50,000+ mailbox system for a very large company. In 2000 or 2001, he told me that his job would be done by computers in a few years and he had decided to leave the industry. Over the last 15 years, he’s worked in another field.

I have no idea if those Exchange systems are still around, and I would concede that mail is better purchased as a service for most organizations than managed in-house. However, I think my friend made a mistake. There are still plenty of people working in technology infrastructure in companies, making a good salary in good working conditions. I’ve spent my career in IT in one way or another, either as a developer, Operations staff, or manager. I see no sign of this going away quickly, though certainly many menial, simple tasks like checking logs and backups are being increasingly handled by automated systems.

When I see articles like this one (Why IT as you know it is dead), I’m not sure what to think. One one hand, I do think IT is changing, especially in larger organizations, where there is pressure to reduce costs (often labor) as well as increase the speed of output. DevOps is one way that we try to improve our systems, though the cultural change is very hard. Often this means that developers produce work in smaller chunks, and may release more often, but don’t get more work done. This is because the cultural change is hard, and most of us don’t want to change our habits.

On the other hand, I also think that in many ways IT is the same. We can’t respond as quickly as business analysts or customers come up with ideas. I know most of those ideas probably aren’t great, and IT doesn’t want to waste resources on something that will not prove to be valuable. Just as happened 30 years ago, departments will create their own POC applications. This used to happen in Lotus 1-2-3, then Access and Excel, now it may happen with low-code development tools, whatever those are.

I don’t really worry about this, as I’ll find ways to make things better. If someone wants an Access or Power BI application, let them build it. If it’s really useful, and others need access, we can upgrade and invest in a better system. I’ll go along and get along, working to build the things that the organization finds useful. I just realize that my time is limited, and if someone else can prove a concept is valid, perhaps that means I should spend time ensuring that works or gets rebuilt in a better way. I also know many of those ideas and concepts won’t prove themselves, so it’s fine if there’s some sort of citizen development (or shadow IT) in an organization.

To me, the key is that we enforce security for our data. If anyone wants to build software, that’s fine. They just need to ensure that they use the same security and authentication mechanisms that other systems use. We need to protect the data, no matter what application is going to be used to view, analyze, or manipulate it.

Steve Jones

The Voice of the DBA Podcast

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

April 23, 2018
Checking Tempdb 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.

In SQL Server 2016, the setup program was altered to better configure tempdb at installation time. This was in response to the observation that few people actually make any changes to the default configuration, which was suboptimal in SQL Server 2014-.

Going through and checking all of the configurations you have isn’t easy, and isn’t necessarily the type of work that anyone wants to do. dbatools makes this really easy and quick with Test-DbaTempDbConfiguration.

Using this cmdlet is easy. I’ll call this with an instance and get results of a number of checks that are useful for your tempdb configuration:

This isn’t necessarily easy to read, so let’s add a Format-Table.

That’s not great, as I’m missing the CurrentSetting field. I’ll add a SELECT and include the fields I want. I can even add multiple instances in here:

Now I can scan through here, looking to see if my settings have deviated from the recommendations and best practices. This could easily be used to filter the results for items that don’t match, save the results as a CSV, and you now have a picklist of items to work on as you find time.

dbatools is an essential tool for me. I’d urge you to download the module and experiment with the cmdlets.

April 20, 2018
Keep It All

I love this quote, though I’m not sure it’s accurate. From The Future of Data Storage, the piece states: “What’s the most expensive thing you can do with a piece of data? Throw it out.”

That’s from a storage vendor, and obviously they’d prefer that you keep all your data, which means more storage and backup space needed. Certainly I do think that losing data that’s valuable can be expensive, but I also think that we often keep around older data that we don’t use, or won’t use, which is expensive. Not for individual pieces, but in aggregate, it becomes expensive. This is especially true if you move to the service area where you pay for what you use, as opposed to investing in a large chunk of storage that has a fixed cost.

I didn’t really think a lot of the piece, though it did get me thinking about backups. I’ve run backups for my entire career, and in 99 point some number of nines cases, I haven’t ever used the backup file again. These were insurance against potential problems. Even in places where I restored the backup to verify the process worked, I often just discarded the backup file at some point.

Early in my career, we had tape rotation systems to reuse the media a certain number of times, while also ensuring that we had off site copies and specific points in time saved. Today there are plenty of backups systems that perform deduplication and complex disassembly or re-assembly of files from blocks to use space more efficiently. That doesn’t always work well for database restores, especially when time is of the essence.

As vendors look to add more intelligent, or at least more efficient, processing to backup systems, I wonder if they really think about databases and how we use files. I hope so, and I’d like something that was optimized for database restores. I don’t mind combining the duplicate parts of files into some index, but I need to have the latest files available for quick restores. What about backing up a database to a file and keeping this file online and ready. Then, after the next backup, move the previous one to an area that dedups it, maybe takes it offline, etc. That way I have the best of both worlds. I rarely go back further than the latest full backup for a restore, so keep this ready.

Of course, we need to consider log backups, which really need to be kept online and intact if they have been made since the last full backup. Keeping track of that is a pain, but it’s something software could easily handle. Once we’ve made a new full backup, you can mark older log backups for deduplication. Though, if you’re building this into a system, perhaps performing a restore of the full backup files automatically should be included as well.

Steve Jones

The Voice of the DBA Podcast

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

April 20, 2018