Tag: Performance Tuning

Filtered Index Basics

  By ,

In this post, we continue with another beginner’s blog of database features that may be unknown to many. Let’s take a look at filtered indexes. Many database administrators are fully aware of the power of indexes and know how to create them. However, I find that some have yet to dive into fully optimizing their indexes and taking advantage of what filtered indexes has to offer. What is a filtered index? Simply it’s an index with a where clause. It is an optimized non clustered index that can be narrowed down in scope to better fit a subset of data.… Continue Reading

DMV’s for the Beginner

  By ,

I give performance presentations at many different events to all levels of SQL Server professionals. Over time I’ve noticed that some DBAs and developers have never looked at the dynamic management views (dmv’s)  that are available within SQL Server starting with SQL Server 2005. These DMVs are useful means to determine what is going on with a particular SQL Server instance. So, in this post I am going to list a few of my very favorite DMVs as a reference for those who may be new to them. Performance Tuning These dynamic management views are what I use first when… Continue Reading

Join me for a PASS Summit Pre-Con

  By

I am very excited to announce I will be delivering a pre-conference session on SQL Server Performance Tuning on November 5th at PASS Summit 2019. If you have ever attended one of my sessions, you know how passionate I am about this particular topic. So, you also know how very excited I am to have this opportunity to spend a full day talking about SQL Server Performance Tuning and Optimization. Performance Tuning is one of my favorite things to do with SQL Server. There is nothing like seeing performance improvements in an environment as your reward for your hard work.… Continue Reading

What is Implicit Conversion?

  By ,

Another quick post of simple changes you can make to your code to create more optimal execution plans. This one is on implicit conversions. An implicit conversion is when SQL Server must automatically convert a data type from one type to another when comparing values, moving data or combining values with other values. When these values are converted, during the query process, it adds additional overhead and impacts performance. Here is a great chart by Microsoft that shows you conversions and which will cause an implicit or explicit conversion. In this post I will not go into explicit, just know… Continue Reading

What’s a Key Lookup?

  By ,

One of the easiest things to fix when performance tuning queries are Key Lookups or RID Lookups. The key lookup operator occurs when the query optimizer performs an index seek against a specific table and that index does not have all of the columns needed to fulfill the result set. SQL Server is forced to go back to the clustered index using the Primary Key and retrieve the remaining columns it needs to satisfy the request. A RID lookup is the same operation but is performed on a table with no clustered index, otherwise known as a heap.  It uses… Continue Reading

How Your Hypervisor Can Impact Your CPU

  By

Recently I had a client complain of chronic high CPU utilization. The performance of their SQL Server had degraded, and it appeared to be related to higher than normal CPU utilization in conjunction with symptoms of unresponsive user queries.  The root cause was twofold—a third party hosting provider had overallocated virtual processors on the physical host where the virtual machine (VM) running SQL Server was residing, as well as a recent upgrade from a version of VMWare that was not patched for Spectre and Meltdown. The host had 16 physical cores and was hyperthreading (making it effectively 32 cores) until… Continue Reading

SQL Server Data Collection and Management Data Warehouse

  By

We all have the need to collect system and performance information regarding our SQL Servers. Some of us use third-party tools, SQL Trace, or a homegrown solution.  Did you know Microsoft has a built-in tool to help you accomplish this? It’s called Data Collection and the data it collects (collection sets) is stored in a relational Management Data Warehouse. The data collected is used to generate reports giving us in with very readable and useful insights to our servers. Not only can you get performance information, but you can also use SQL Profiler to export trace definition and create custom… Continue Reading