James Rhoat

James Rhoat

Preparing for SQL Server Database Corruption; initial reaction and analysis

February 6, 2019 by

Corruption is a looming thought through every administrator’s mind, from sysadmins to database administrators there is always a monster hiding in the shadows. SQL Server Database corruption happens all the time around the world and while most of us have been lucky enough to avoid it, we should still be prepared. While there is no way to prevent the corruption from happening, we must work hard to implement practices that minimize damage caused by the corruption. This means good backups and of course running DBCC CHECKDB.

Read more »

Considerations and concerns when setting up Database mail for SQL Server

August 31, 2018 by

Database mail is widely used by DBAs and companies around the world and is one of the features of SQL Server that can be very important for startups. That is because it is a cheap solution for getting alerts from your SQL Server for potential hardware issues, early warning signs of corruption, along with potential resource constraints. However, it can be misused or pose potential security issues. Before we start, to my myself clear I am in favor of configuring Database mail for the DBA team when done properly. In the rest of the article, we will touch on some of the common mistakes people make with database mail and their SQL Servers. We will not discuss the setup of this feature as it was well documented by Bojan Petrovic on SQLShack.

Read more »

SQL Server Setup – Instant File Initialization (IFI)

August 31, 2018 by

If SQL Server needs to allocate space, first it fills the space it needs with zeros. Examples of when it needs to allocate space are creating/restoring a database, growing/allocating data and log files, and finally the troublesome auto growth operation. However, most of these slowdowns/long running actions can be improved by enabling instant file initialization. I say most because the one case where this is required is on log file growth. However, even when Instant file initialization has been enabled the log file will be zeroed out before the space is usable for SQL Server.

Read more »

SQL Server Dedicated Admin Connection (DAC) – how to enable, connect and use

August 31, 2018 by

The dedicated admin connection (DAC) can help you out of a sticky situation. This was built to help you connect to SQL Server and run basic queries in cases with critical performance problems. This works by telling SQL Server to reserve a thread specifically for processing your queries in an emergency. While it does reserve a connection for you, it is only one thread, there is no parallelism happening here, in fact, you will receive an error.

Read more »

Performance troubleshooting when the query plan from the application is different than SSMS

August 24, 2018 by

Troubleshooting performance issues in a database is one of the main jobs of DBAs and by now most can trace the problem back to a query which is either running to slow or is causing a blocking issue on a key table. However, what is often not known is why this doesn’t cause problems in SSMS or why you don’t get the same query plan as what is inside the app. For example, in your extended event trace you see the query running longer from the application when compared to SSMS.

Read more »