Microsoft SQL Server Environmental Checks

July 25, 2017January 31, 2018 Jeff Mlakar10 Comments

When called in to diagnose a performance issue, what are the 1st things you review? How much of the environment do you check before going straight to the line of code you think is at fault? Because of the urgency many DBAs and developers make the mistake of diving straight into the code looking for an answer. What if the solution is not in the code but rather in the overall environment? Environmental troubleshooting is often a quick and overlooked method for discovering and configuring SQL Server. Perform at a higher level without the need for application change controls!

NOTE: this article is the summary of a 3 part series on optimizing SQL Server configurations along with Windows Server and VMware. Please read these first then return to read the rest.

Part 1: SQL Server Environmental Diagnostics Guide
Part 2: SQL Server Environmental Diagnostics Guide – Windows Server
Part 3: SQL Server Environmental Diagnostics Guide – VMware

SQL Server Environmental Diagnostics Guide – VMware

July 20, 2017January 31, 2018 Jeff Mlakar3 Comments

Nowadays everyone virtualizes. SQL Server is no exception. VMware is the most common hypervisor by far (sorry Hyper-V) so it is very beneficial for DBAs to know their way around vSphere, understand how to optimally configure SQL Server and VMware to work together, and know the red flags to watch for performance.

This is part 2 of a 3 part series about SQL Server Environmental Diagnostics. Check out Part 1 and Part 2 as well.

SQL Server Environmental Diagnostics Guide – OS

July 10, 2017January 31, 2018 Jeff Mlakar5 Comments

Diagnostics and the optimization of configurations are important for any SQL Server instance. However, it is not only the database server which need attention. The operating system, Windows Server, needs consideration as well. There are various general configurations to consider optimizing on any Windows Server hosting a SQL Server instance.

NOTE: this deals only with Windows Server. I know that Linux is now recently an option but this article will deal only with Windows Server.

This is part 2 of a 3 part series about SQL Server Environmental Diagnostics. Part 1 can be read here.

SQL Server Environmental Diagnostics Guide

June 18, 2017January 31, 2018 Jeff Mlakar12 Comments

Supporting an application as a DBA means you have encountered complaints about performance. The investigation starts and the root cause must be determined. Often times the end result is not a bug in the code but rather something specific to the environment. Environmental problems are usually the culprit.

Performance problems for a SQL Server based application are likely to be caused by environmental factors and not buggy code.

Whether it is a configuration you can change in SQL Server, Windows Server, VMware, or the network it is likely the first course of action is to perform a quick assessment of the environment. This is where understanding the various configurations and best practices are key. Knowing what to look for can save tons of time.

A mistake I often see is a performance issue is passed off to someone else (more senior) and that engineer assumes a lot of things without checking. People are going to relay the problem as they see it – not as it actually is. This leads to skipping over some elementary checks which can save time and frustration from tracking down imaginary bugs.

Start troubleshooting with a quick environmental check.

Below are common environmental mishaps I see when troubleshooting SQL Server performance complaints. Consider these 1st line of action before getting into execution plans, statistics, indexing, and code refactoring.