Infrastructure at your Service
Doing some performance troubleshooting for one of my customers I identified some issues with very large table variables inside Stored procedures. Table variables limitations are not well understood by developers although they are now well documented. Table variable rows estimation Let’s have a look at an example with my customer context which is SQL Server 2016, so compatibility level 130 at the database level. You can reproduce this demo with the Wide World Importers database….
I have blogged several times about unusually large MSDB databases like here. Most of the time the problem comes from the backup history which is never purged. This time it’s different. Try to guess, if not the backup history, what can cause MSDB to increase abnormally in size? Let’s start by looking at the total size of the database: It’s huge. The database size is almost 90GB. The 10% configuration on data files causes large…
As a consultant at dbi services we can dedicate a significant part of our time to training and I took the opportunity today to attend an online conference. In this blog post, I will present this event and one of the sessions that particularly interested me. The event I followed is the Virtual Symposium – SQL Server & Azure SQL organized by SQLServerGeeks. It’s a free event that can easily be followed on Zoom but…
Introduction When performing an audit on an SQL Server VM the first thing the system administrator told me was that there’s no issue with the CPU on this box, the monitoring shows it’s always around 50% usage. Problem Here is the Task Manager: The average CPU usage on this VM is indeed 50% but half of the cores are stuck at 100%. There are two SQL Server instances installed on this server but they are…
Introduction In a previous blog post, I did a T-SQL script reading Audit files to send emails with valuable information about instance changes. In this one, you will see how to get nice emails from critical errors or specific events occurring on the instance using the ErrorLog. A common best practice for any DBA is to configure SQL Server Agent Alert for errors with severity 17 to 25. Alerts are also often set for less…
This week I noticed some slowness on queries related to backup history. The instance had a huge msdb database (more than 10GB size), the backup history had never been purged. I already wrote a post on this topic and provided a small PowerShell script using dbatools to clean the backup history. The issue with sp_delete_backuphistory or Remove-DbaDbBackupRestoreHistory is that the operation is done as one transaction and this could badly impact the msdb transaction log…
Introduction Very often with our customers, there is only one person with the DBA role. The configuration of the instances is then under control and if anything strange has to be questioned, the culprit is quickly identified. 🙂 When many people, not necessarily having DBA knowledge, have high permissions (sysadmin) on instances it becomes important to know who does what. In this blog post, we will see how to get notified by email when certain…
In the blog post, I will share a case of troubleshooting a connectivity issue with SQL Server. The problem occurred in a migration context. The SQL Server databases (in version 2008 to 2014) from multiple applications were consolidated on a new server with SQL Server 2019. Application servers were also migrated to new VMs. The new servers are hosted on a brand new infrastructure managed by a third-party provider which my customer has very little…
I’m currently working on the migration of a Failover Clustered SSAS instance from Windows Server 2012 to Windows Server 2019. The context is quite complex and in order to choose the right migration scenario I need to play with it on my LAB environment first. I usually work with AlwaysOn architectures and it’s not often that I need to set up an FCI with its Cluster Shared Volume. As a reminder, a Cluster Shared Volume…
Introduction In this blog post, I will demonstrate how the Resumable capability of Online index rebuild operation can help you to keep the transaction log file size under control. An index rebuild operation is done in a single transaction that can require a significant log space. When doing a Rebuild on a large index the transaction log file can grow until your run out of disk space. On failure, the transaction needs to rollback. You…
