Tag: SQL Server

Understanding Columnstore Indexes in SQL Server Part 3

  By ,

My last two blogs were on understanding columnstore and creating columnstore indexes. In this post, I will touch on one last topic, when to use columnstore indexes. As noted in my prior posts these indexes are designed for large data warehouse workloads, not normal OLTP workload tables. As with any index design it is important to know your data and what you will be using the data for. Be sure to look to what types of queries you will be running against it, as well as how the data is loaded and maintained. Let’s look at a few questions you… Continue Reading

Understanding Columnstore Indexes in SQL Server Part 2

  By ,

In my last blog I explained what a columnstore index is, in this blog, we will dive into creating a clustered columnstore index and look at the performance differences the index can make. Let’s get started. Using AdventureworksDW2016CTP3 we will work with the FactResellerSalesXL table which has 11.6 million rows in it. The simple query we will use as a demo just selects the ProductKey and returns some aggregations grouping them by the different product keys. First, we will run the query with no existing columnstore index and only using the current clustered rowstore (normal) index. Note that I turned… Continue Reading

Understanding Columnstore Indexes in SQL Server Part 1

  By ,

Recently I reviewed filtered indexes, this time let’s look at columnstore indexes. These indexes are very useful for data warehouse workloads and large tables. They can improve query performance by a factor of 10 in some cases, so knowing and understanding how they work is important if you work in an environment with larger scaled data. Now, I admit when these first were introduced in SQL Server 2012 I found them very intimidating (additionally, you couldn’t update them directly). For me, anytime you say columnstore, my mind tends to set off alarms saying wait stay away, this is too complicated.… Continue Reading

Comparing Execution Plans

  By ,

When you run a query twice, and get dramatically different run times, your first step can be to try to identify the differences in execution plans. For many that means running  the two different queries (before & after) and splitting you screen in order to visually compare the plans. Did you know SQL Server Management Studio gives you the option to compare to different execution plans? It makes it easy not only to visualize the differences, but it also shows you detail properties that allow you to dive into the numbers. This functionality was introduced with SQL Server 2016, and… 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

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

Enable Mail Profile – Back to Basics

  By

I’ve seen many people go through the trouble of setting up database mail and configuring SQL Agent Alerts only to realize it’s not working. The reason in the cases I’ve seen is because they have simply neglected to assign a mail profile to SQL Agent. This is way more common than you would think, thus I wrote this quick blog. This check box and drop down can sometimes lead to hours of troubleshooting if you don’t know where to look. In order to receive the alerts, you must enable a mail profile. This would be the profile  created during the… 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

Azure Key Vault Logging

  By

Following up from last week’s post on Azure Key Vault in this blog I will show you how to the setup Key Vault logging I mentioned for auditing access and usage of your key vault. Once we walk through the process of enabling your logging, we will configure Azure Log Analytics as a way to analyze that data. Azure Log Analytics uses advanced analytics and machine learning to analyze your azure log files. It adds intelligent insights to your monitored data such as Key Vault usage and access  as well as latency in key retrieval from your Audit Event Logs.… Continue Reading