Tag: Azure SQL Database

RunAsRadio- Query Performance Tuning Strategies

  By

Had a great time talking with Richard on RunAsRadio about Query Performance Tuning Strategies, check it out. How do you keep your SQL queries fast? Richard chats with Monica Rathbun about her approaches to SQL Server query tuning. Monica starts with defining the problem – how do we know that the database is the performance bottleneck? The conversation dives into measuring query performance and the power of Query Store, but only on SQL Server 2016 and above, so get upgrading! Entity Framework is a standard tool for developers to automate access to SQL. Still, it can generate some pretty ugly… Continue Reading

Add Azure Cache for Redis to Your Azure SQL Performance Tuning Toolbox

  By ,

One of the biggest impacts on resource consumption for Azure SQL DB are repeated data pulls by the application layer. No matter how fast those queries execute calling the same procedure or issuing the same SQL statements hundreds, thousands, or million times a day can wreak havoc on database performance. Death by a thousand cuts can easily bring a system to its knees. Sometimes it’s hard for DBAs to troubleshoot these actively as the execution of the statements happens so quickly they don’t even show in tools like sp_whoisactive. It’s not until you begin to dive into things like Query… Continue Reading

Add DBCC INPUTBUFFER to Your Performance Tuning Toolbox

  By

A command I like to use when performance tuning is DBCC INPUTBUFFER. If you have ever run sp_whoisactive or sp_who2 to find out what sessions are executing when CPU is high for instance this can be a real quick life saver. At times, for me, those two options do not return enough information for what I’m looking for which is the associated stored procedure or object. Using this little helper along with the session id can easily get you that information. Let’s take a look. First, I will create a simple procedure to generate a workload. CREATE OR ALTER PROCEDURE… Continue Reading

Using Readable Secondary in Azure SQLDB

  By ,

When using a Geo Replicated Azure SQL Database Readable Secondary  there are a few things to consider when it comes to performance tuning. Check out this episode of Data Exposed: MVP Edition as we discuss what you need to keep in mind with Microsoft’s Anna Hoffman, @AnalyticAnna.     Continue Reading

Adaptive Joins in SQL Server

  By ,

SQL Server 2017 (compatibility 140) brought us many Intelligent Query Processing (IQP), formally known as Adaptive Query Processing, features that improve performance on workloads straight out of the box with no code changes to implement. One of those features introduced was Adaptive Joins. In this feature the join operator is dynamically determined at runtime by the optimizer, which defines a threshold number of rows and then chooses between a Nested Loop or Hash Match join operator. This operator switch can produce a better join and improve performance of your queries without you having to lift a finger. Not all queries… Continue Reading

Change Azure SQL Database Service Level Objectives in SSMS

  By

Sometimes as a DBA, I am lazy and want the ability to execute all of my tasks in one place. Lucky for me I discovered the other day that I can change my Azure SQL Database Service Level Object options within SQL Server Management Studio (SSMS) without ever having to go to the Azure Portal. By right clicking on your database properties and choosing the Configure SQL page you can change your Subscription and Azure Location. Even more and what is really cool is I can also scale up and down my Edition (which is generally referred to as Service… Continue Reading

Caution When Dropping Unused Indexes on an Azure SQL Database

  By

Index Maintenance is an important aspect of database health. Above and beyond regular index rebuilds and reorganizations it is important to understand the usage of the indexes on your database. Cleaning up unused indexes can save a lot of overhead on Insert/Update/Delete operations. To achieve that goal, I typically run a script like the one shown below and check out whether or not an index has had any seeks or scans against it as a starting point in my cleanup regiment. SELECT d.name,        OBJECT_NAME(i.[object_id]) AS [ObjectName],        i.[name] AS [IndexName],        s.user_seeks,        s.user_scans FROM sys.indexes AS i    … Continue Reading

What is Azure SQL Database Serverless?

  By

What is Azure SQL Database Serverless? When I hear the term serverless my mind gets confused. How can a database exist without a server? Azure is a cloud platform, is my database just floating in the air? No, I am not really thinking that, but still the word serverless can be hard to understand. So, let’s walk through what it is. Serverless is a term commonly used for function as a service patterns like Amazon Lambda, or Azure Functions, where you have a piece of code that is called and executed without you deploying any infrastructure. Azure Logic Apps are… Continue Reading

Moving Your SQL Workload to the Cloud   

  By

Every day, more IT organizations decide to move their SQL Server databases to Azure. In fact, over a million on-premises SQL Server databases have been moved to Azure. There’s an interesting blog about how Microsoft is faster and cheaper than its competitors that’s worth a read. To assist with your move to Azure Microsoft offers a number of migration tools and services to make this move as smooth as possible which I think attributes to their success. Two of those options are below with some informational links. If you’re migrating a number of large SQL Server instances, Azure Database Migration… Continue Reading

What is Azure Key Vault?

  By

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… Continue Reading