Enabling an Index: #SQLNewBlogger

Posted on October 2, 2024 by way0utwest

I don’t do a lot of work with disabled index, but I learned how to re-enable one today, which was a surprise to me. This short post covers how this works.

The Scenario

Imagine that you have an index on a table. In my case, I created this index:

CREATE INDEX LoggerNCI ON dbo.Logger (LogID)

I can then disable this index with the following code:

ALTER INDEX LoggerNCI ON dbo.Logger DISABLE

I had assumed that ENABLE would be the opposite, but SQL Prompt taught me this wasn’t an option. I checked the docs, and sure enough, it’s not ENABLE.

It’s resume. This code turns the index back on and updates it.

ALTER INDEX LoggerCI ON dbo.Logger REBUILD

I can also use either of these items:

CREATE INDEX LoggerNCI ON dbo.Logger (logid) WITH DROP_EXISTING

DBCC DBREINDEX(Logger, LoggerNCI)

Interesting short moment the other day as I realized there are a few options here.

SQL New Blogger

While playing with this, I realized that I didn’t know all the ways this worked, so I spent 10 minutes after I’d worked with the code to put this together.

A nice short way to showcase some learning.

Posted in Blog | Tagged , , , | Comments Off on Enabling an Index: #SQLNewBlogger

Cleaning Up Your Database

Posted on October 2, 2024 by way0utwest

How many of you have objects in your database that aren’t being used? What about something in a schema with a _old in the name? Or _2 or _3 or _delete? There is a lot of old, deprecated stuff I see in production databases. In fact, I’ve been somewhat amazed as I work with clients that many of the scripts we can build from a database with SQL Compare won’t actually execute on an empty database because the script is full of broken code.

I also find plenty of DBAs that want to clean things up, but they don’t. Sometimes they’re afraid they’ll break something, which is certainly possible. Sometimes they can never find the time. Often they might ask a manager, who usually says this isn’t important and don’t bother.

Is it worth it to clean up your databases?

Brent says no for old code. I say maybe for tables and code.

For a lot of code, Brent is right, your boss doesn’t care and it doesn’t necessarily help you. After all, it’s in production now, and if it’s being used, you’re going to just create problems with a DROP. Where is the business value for removing old code (assuming it isn’t being used)? What benefits do your clients get? Not you being happier there are less objects, but what is the business benefit.

That’s the key. Is there a business benefit.  What I’d say is that if you have broken code, it needs to be removed. Because this does impact your software development process, especially when trying to match lower environments. For broken stuff, save the code in your VCS (you do version control database code, right?) and then delete this stuff from prod. It’s broken.

Or fix it.

For tables, I would want to get rid of old tables as well. Why? Well, this is real costs in storage and potential reading of old data. If we moved data to table_old and someone decided they needed to read this for a report at the time, they might still be reading old data. I’d first rename these objects as object_delete_date with the date being a month away. Then I’d set a reminder for that date. On that date, bcp out the data, then drop the table. Period.

Two other things. First, make sure you know how to recreate the table (see the VCS comment above) and bcp in the data. Two, this is low-priority work. If you want to clean the database, know this is a long term, baby step process that will take months or years, and may never end.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

Posted in Editorial | Tagged | 2 Comments

Back to Boston for SQL Saturday

Posted on October 1, 2024 by way0utwest

I’m leaving again tomorrow for a trip. This time I head back to Boston for a Redgate DevOps in a Day on Thursday and SQL Saturday Boston 2024 on Saturday. This is a fun event and one I’ve attended the last few years. It’s one I look forward to as well.

If you’re in the area, register and join me Saturday for a packed schedule of learning on PoSh, Fabric, TempDB, AI, and more. If you want something a little lighter, I’m doing a new session on balancing life with career.

And if you’re coming Saturday, what about coming Friday for a pre-con with Bob Ward with a Cloud Workshop. A good, inexpensive way to get some training from one of the top technical engineers at Microsoft.

Come join me and invest in yourself and your career at SQL Saturday Boston 2024. And if you want, come Thursday as well to our Redgate Database DevOps in a Day. 

Posted in Blog | Tagged , , , | 2 Comments

Effective Engineering

Posted on September 30, 2024 by way0utwest

I’m not the smartest developer or DBA. I find myself mystified at times by Itzik’s posts on T-SQL queries and I’m amazed at times by the complex systems that I see the DCAC people put together. I can usually figure things out (sometimes by asking the authors a question), but it’s not always easy to do. We have some truly gifted, incredibly intelligent people in this business.

I am, however, effective. I have been very successful in my career at getting things done well enough, things that work well, meet the needs of my client/employer, and meeting deadlines. I don’t just slap things together, but think about them, build them, test them (don’t forget this), and then make sure they’re working when they’re deployed.

Sometimes this might take a few PRs or patches for patches, but I get things done. And my customers/clients are happy.

I saw this post on Linked In noting Platform Engineering is Dead, which is a great title, but not really true, and not quite reflected in the piece. The author worked on the Software Delivery Enablement team, which is what the platform engineering team is supposed to do.

I see similar complaints about DevOps, and previously saw complaints about Cloud computing or Agile or Scrum or SOLID. There have been similar complaints about how some new methodology or idea isn’t working and should be abandoned in favor of this other new thing.

Ultimately, near the end of the piece on Linked In, there is this:“we also knew how to help them use solutions to deliver software better, and we partnered with them instead of inflicting things upon them.”

This is what Software Engineering should be. In waterfall, we want to have customers tell us what they want and build that. Often customers don’t know what they want, so we decided Agile would help. DevOps is a way of talking about a partnership between developers and operations that still delivers what the customer wants, quickly.

Platform Engineering or Software Delivery Enablement or whatever name you give it is still partnering with customers to deliver what they need. Not what you think they need or what you want to build, but what they need.

Whenever Agile or Scrum or DevOps or Platform Engineering doesn’t work, it’s because you’ve forgotten that this is a partnership. That’s what effective engineering is, and it’s what I’ve practiced. Partnering with others to achieve our aims.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

Posted in Editorial | Tagged | Comments Off on Effective Engineering