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 looking to any performance issues or I need to know…
-
-
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 that is what you explicitly tell SQL Server to CAST…
-
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 a row id instead of a primary key to do…
-
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 Database Mail configuration process. To configure SQL Server Agent to…
-
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 the hosting provider patched from VMWare 5.5 to a newer…
-
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. Here is the PowerShell Script you can use to enable…
-
Keys and secrets (AKA passwords) are an essential part of data protection management not only on-premises, but within the cloud as well. One of the many advantages of cloud is the ability to have a secure, persisted key store. If you have used a password manager like Keepass or 1Password, you can consider Azure Key Vault to be an enterprise level password manager, but also a lot more. One of the functions that Azure Key Vault supports is for you to keep small secrets such as passwords, tokens, connection strings, API keys as well as encryption keys and certificates in a safe tightly controlled secure location in the cloud. It…
-
Azure offers a lot of value-added services included with the price of what you pay . One of the things really, I like is Azure Advisor. this service provides information about your entire Azure landscape. It gives you a complete overview of your system needs including possible ways to save money. High Availability shows you ways to improve the continuity of your business-critical applications. Security detects threats and vulnerabilities that might lead to security breaches. Performance shows you ways to speed up your applications. Cost give you ways to reduce your overall Azure spreading. As you can see from the above screenshot, we have several recommendations on how we can…
-
A key part of the SQL Server Agent is the ability to schedule jobs. While you can create one schedule for each agent job, frequently with applications like Reporting Services, users use Shared Schedules across multiple jobs. For instance, you can set a schedule to run at 8 am on week days or run every 2 hours or pick from a predefined list of schedules that already exist in the MSDB database. These are very convenient. However, if you choose to use these be sure you are keeping track of what is running for each of these shared schedules. You should not have everything running at once. This is an…
-
We all have written queries that use COUNT DISTINCT to get the unique number of non-NULL values from a table. This process can generate a noticeable performance hit especially for larger tables with millions of rows. Many times, there is no way around this. To help mitigate this overhead SQL Server 2019 introduces us to approximating the distinct count with the new APPROX_COUNT_DISTINCT function. The function approximates the count within a 2% precision to the actual answer at a fraction of the time. Let’s see this in action. In this example, I am using the AdventureworksDW2016CTP3 sample database which you can download here. SET STATISTICS IO ON SELECT COUNT(DISTINCT([SalesOrderNumber])) as DISTINCTCOUNT…
