Month: February 2021

Identifying SQL Server Performance Problems Part 3

  By

Continuing with our Performance Tuning series as you have learned baselining and knowing your normal system performance metrics is an important part of performance tuning. In part 1 we discussed collecting vital system metrics such as CPU Utilization and Memory usage. In part 2 we took a glimpse into how we can gather those metrics and other using the different tools that are available to use like Performance Monitor, Profiler, Extended Events and DMVs. In this part 3 we will look at how identifying the appropriate waits events and corresponding resolution will be critical for resolving performance issues. But first you… Continue Reading

SQL Index Creation Using DROP EXISTING= ON

  By

When you are making changes to an existing Non-Clustered index SQL Server provides a wide variety of options. One of the more frequently used methods is DROP EXISTING; in this post you will learn all about that option. This option automatically drops an existing index after recreating it, without the index being explicitly dropped. Let us take a moment understand the behavior of this choice. DROP EXSITING=ON which is my preferred method, will DROP the current index only after it finishes creating and building the index with the new definition. The pitfall is that if the index does not exist, you… Continue Reading

Identifying SQL Server Performance Problems Part 2

  By ,

Performance Monitoring Tools In this second post we are diving into what tools you can use to capture the important performance metrics you need to gather for baselining your environment. Be sure to read part one first. Performance Monitor First let’s start with what comes native to Windows Server platform, whether you are on an Azure SQL Virtual Machine or your standard SQL Server instance install on premises you have a native tool, called Performance Monitor (affectionately known to most as PerfMon). This reliable tool allows you to easily monitor performance metrics through a Graphical User Interface (GUI), by selecting performance counters for the operating system… Continue Reading

Mastering TempDB: The Basics

  By

I regularly deliver a presentation called “Mastering TempDB”. I wrote it based on what I wish I knew about TempDB “growing up” in my career as a SQL Server DBA. It really is a breakdown of what TempDB is, how it is used, what problems actually occur within it, and how to best configure it. With advice from my colleagues, I have decided to break that down into this article to educate you. What is TempDB? TempDB is a global resource used by everyone and everything inside SQL Server. Think of it as a dumping ground for anything that doesn’t… Continue Reading