SQL Server Administration

My favorite PerfMon counters

About two months back I ended up moving to another job, which has unfortunately kept me to be bit too occupied to find the time to blog, until now. Due to this previously mentioned career change, I have been working quite a bit with monitoring, and it gave me a spark to write this post about my favorite PerfMon counters.

Like most DBAs I rely quite a lot on monitoring, and like most DBAs I too have my own set of PerfMon counters that I rely on to provide me an accurate view of what’s happening in the environment I am administering. In this post, I’ll describe what are my favorite PerfMon counters.

Connecting to SQL Server instance through Dedicated Admin Connection

Dedicated Admin Connection is one of the easy-to-forget features in SQL Server that can really save your day. DAC (no relation to Data-Tier Applications, just shares the acronym), as it’s often called, is the way you can try to access a SQL Server instance that is in such a bad shape, that no normal connection is available. This can be due to resource exhaustion or if you happened to create a slightly wonky logon trigger. In this post we’ll look at how you enable Dedicated Admin Connection (for remote users) and how you connect to SQL Server using the DAC.

Continue reading “Connecting to SQL Server instance through Dedicated Admin Connection”

Offloading DBCC CHECKDB

I recently had a discussion about the ability to offload DBCC CHECKDBs to a secondary database using Active Secondaries in SQL Server Availability Groups. While it is fully possible to run database consistency checks against secondary database (and there’s plenty of recommendations floating around for doing this), it needs to be pointed out that using Availability Groups for this does not equal of running checks on the primary database.

Let’s consider this for a bit…

Continue reading “Offloading DBCC CHECKDB”

SQL Server and Joins

I was recently looking at some Execution Plans with a co-worker and we ended up discussing the different types of joins in a SQL Server and what implications they might have when it comes to query performance. While many of us are familiar with writing joins, as we usually don’t query just a single table, there are quite few things about the physical joins that may not quite obvious. In this post our focus will be in the physical joins, but we will also very briefly look at the different types of logical joins also.

Continue reading “SQL Server and Joins”

SQL Server Data Compression

Older compression tech — Compression has been around for a while…

SQL Server has had the Data Compression feature for a while, ever since the version 2008, so it is hardly a new thing. However as it has been Enterprise Edition feature until SQL Server 2016 Service Pack 1, it is not something you see employed very often. Technically speaking, you could also compress data before 2008 by using NTFS file level compression on a read only data. However with the implementation of SQL Server Data Compression you could now do it inside the database on a page or a row level.

Continue reading “SQL Server Data Compression”

Difference between a backup and a valid backup

It took me a while to write the first post for 2017, but it has been rather hectic at the office since I moved inside the organization to quite a different role. However I noticed this story a while back and it has been haunting me ever since. Now if you are a DBA then you probably already understand just how important it is to have, not just backups, but valid backups. But what is the difference of a backup and a valid backup?

Continue reading “Difference between a backup and a valid backup”

SQL Server minimum configuration

For the last blog post of the year 2016 I chose something that has been bothering me a bit as of late. Over the past couple months I have come across a number of cases where, after migrating databases to a new server, the end users are reporting increasingly bad performance. What has been common to these situations is that the new server has more and faster CPU cores, more memory, faster disks and should offer better performance, not worse.

Now the first thing I usually do when troubleshooting performance issues, is to check what kind of hardware we have and how the SQL Server has been configured. This is a really simple step to start with: Run msinfo32 to get the hardware details and then query sys.configurations to see how the SQL Server is configured. In these cases, all of the SQL Server configurations were left to their default settings. The result: Systems running to dangerously low levels of available memory which leads to extensive paging of the memory to the disk, and some really wild-looking query parallelization issues.

Continue reading “SQL Server minimum configuration”