Lessons learned by a time travelling SQL Server DBA – Part One “The Past”

Reading Time: 4 minutes

I thought for a couple of blog posts I would write a fictional tale about lessons learned by a time travelling SQL Server DBA. Because at the start of a new year people tend to look into the past and the future.

I decided it would be a good idea to cover some topics in a different way. So, this post is part of an experimental format.

All characters are entirely fictional and anybody with the same names or any similarities as any of the characters is purely coincidental.

In the beginning

It was the beginning of 2020 and our DBA had enjoyed their break over the holiday period. However, now it was the start of a New Year and they were about to start a new position.

He had already decided that it would be easier to commute rather than attempt a drive to the new office. With this in mind, he got up early on the Monday and made his was towards the train station.

When the train arrived, it seemed a bit out of place. He couldn’t figure out why, however he stepped on the train anyway.

After he got off the train he looked up at the office building, which looked fairly new. When he entered the building, somebody called him by another name. However, he put it down to a mistake on their part.

Afterwards, he was introduced to his new colleagues and asked if he could help with a performance problem.

Performance issue

His new colleague told him that one of the servers had a performance issue and queries were now taking a long time to run. To start with he asked them which version of SQL Server was running on the server.

After his colleague told him he did not know he asked if they were already connected to the SQL Server instance. His colleague said yes. However, he was surprised when he saw that his colleague was using Query Editor.

After this revelation, his colleague showed him that the server was running SQL Server 2000 Service Pack 3.

Because of this revelation, he looked around the office and realised there were a lot of older computers around. So, he asked his colleague what year it was. His colleague told him it was 2005.

After being told this he was feeling a bit confused and wondered what was happening. However, he decided to go along with it and help his colleague.

Because he wanted to fix the issue he asked his colleague to query a DMV. His new colleague then asked what a DMV was.

Remembering how he did things fifteen years ago he asked his colleague to query sysprocesses. After looking into it further they were able to identify a rogue query and killed it off.

Other problems

During the day he also helped with other problems, using the methods he had used many years ago.

For example, using DBCC SHOWCONTIG to identify fragmentation with a particular index. After identifying the fragmentation, he then used DBCC REINDEX to rebuild the fragmented index.

In addition, because they were using SQL Server 2000 he had to customise Log Shipping. Because there were problems with the transaction logs being copied over a slow network link and you could not natively compress backups in SQL Server 2000.

He used a used a third-party application to compress them first. After they were compressed to smaller files, they could be transferred over the slow network link faster and extracted at the other end to be restored.

Because he was really keen he also advised on how to improve the time it took to run DBCC CHECKDB against a very large database as well. He remembered some performance improvements he had done in the past for SQL Server 2012.

Deployments

Before he left for the day he was given a pager, so that he could be contacted in emergencies.

In addition, he was asked if he could stay late the following Wednesday evening. Because a big deployment was taking place which happened every six weeks or so and can take up most of the evening.

He fell asleep almost immediately when he got home later that evening.

Who knows what the next day was going to bring?

To be continued…

I hope you all enjoyed part one of this series.

Lessons learned by a time travelling SQL Server DBA – Part two is now available. So, you can read it after this post by clicking the link here.

In addition, you can now read part three here.

Final word (for part one)

I hope you enjoyed this trip down memory lane in this post about lessons learned by a time travelling SQL Server DBA – Part One. For some of you it’s nostalgia.

However, fully aware that some people still have to deal with SQL Server 2000 and big waterfall deployments. In addition, also aware that a minority of people still use pagers.

For those of you who are not aware, Query Editor is what people use to use to perform queries in SQL Server before SQL Server Management Studio.

In addition, DBCC SHOWCONTIG is what people use to use in SQL Server to identify fragmentation in indexes. Before the introduction of the sys.dm_db_index_physical_stats DMV in SQL Server 2005.

In fact, the results of this were talked about due to a question on the SQLHelp hashtag on Twitter recently. Because it looks like a vendor is still using the syntax from the results.

You can read the syntax for this command and the results in detail here. However, as it states at the top of the web site avoid using this with newer versions of SQL Server.

In addition, I mention a couple of ways you can improve the performance of DBCC CheckDB in an old post. Which you can read in detail here.

However, I am interested in knowing people’s thoughts on this format. So please feel free to leave a comment.