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 Performance Insights or Query Store that you start to see the real issue.

Azure Portal

SSMS Query Store Top Consuming Queries with Executions Count Metric

The question is how do you combat this issue? The code has been fine-tuned and runs at optimal performance, it’s just the volume of application calls that is causing issues. One answer is Azure Cache for Redis.

What is Azure Cache for Redis?

Simply, it is a dedicated memory cache data store that can be accessed by applications within or outside of Azure based on open source Redis. It enables you to load data into the in-memory data store, read directly from that and reduce the number of calls to your database. Placing the data into this cache layer prevents the application from having to do repeatable data calls over and over again.  This can dramatically improve database performance, reduces latency, and frees up resources for other data requests by shifting the performance load to the cache and away from the database layer. This will require changes to your application code; however, it can potentially really increase database performance. You can see an example of a fairly complex app here in Microsoft docs.

This can be not only a performance gain but a monetary one as well. The result could in fact allow you to scale down you Azure SQL Databases because your resource consumption will be reduced.

Getting Started

Create a Resource, Under Databases choose Azure Cache for Redis

Pay attention to the cache type options. Be sure to click the link to the pricing tiers so you can pick the correct one for your environment. In this case I am choosing the cheapest one, Basic C0, which only gets me 250 MB cache but estimated at $16 per month, larger ones can get a little pricey.

For Networking you will have to choose Public or Private Endpoint the choose Next

Now choose which Redis version you want. Note there is a version 6 in Preview. Next will take you to the Tag options, which I skip, because I have no need to Tag my resources. Lastly, we Review and Create the resource. Now that we have a Redis Cache resource created there are a lot more steps to take to be able to use it, store data in it and access it through your applications. I’ll leave those steps to you, in this post I just wanted to show you where to find it in the portal and how to create it.

Summary

If you work within an environment that has repeated data calls thousands of times an hour, this may be a really great resource for you to look into. I highly suggest you add Azure Cache for Redis to your performance tuning tool kit. You can find all the information you need to continue with the process here. Be sure to also read up on all the security things to consider. There is a lot of useful documentation within Microsoft docs that can be found here as well.

The post Add Azure Cache for Redis to Your Azure SQL Performance Tuning Toolbox appeared first on A Shot of SQLEspresso.

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 KeepRunning
AS
DECLARE @i INT=1
 
WHILE (@i <1000)
BEGIN
select @@servername
WAITFOR DELAY '00:00:30'
select @i=@i+1
END

Now I will execute the procedure and capture what it looks like using first sp_who2 and then sp_whoisactive. Looking at the Sp_who2 screen shot all you can see is the session information including command and status that is being run but have no idea from what procedure it is being run from.

Exec KeepRunning

Now take it a step further and let’s run sp_whoisactive. Here we get more information such as the sql_text being run.

Take note of the session id, displayed with either tool, which in this case is 93. Now run DBCC INPUTBUFFER for that session.

DBCC INPUTBUFFER (93)

BINGO! We’ve now got what we needed which is the name of the store procedure that the statement is associated with.

Now let’s try one last thing. Remember I said sp_whoisactive does not give us the store procedure name, well that wasn’t 100% true. There are fantastic parameter options we can us that can get us even more information. Let’s run sp_whoisactive using the parameter @get_outer_command = 1. Shown in the screenshot you can see here it essentially the same thing as DBCC INPUTBUFFER giving you the sql_command i.e. the store procedure name.

Summary

Quickly knowing the stored procedure associated with the query that is causing issues allows us to easily follow the breadcrumbs to identify the root cause of an issue. If you cannot run third party or community tools like sp_whoisactive, dbcc inputbuffer is an alternative for you. Therefore, I wanted to introduce DBCC INPUTBUFFER. Adding this little tidbit to your performance tuning toolbox can be a real time saver,, you may have other useful ways to use it as well. Be sure to have a look.

The post Add DBCC INPUTBUFFER to Your Performance Tuning Toolbox appeared first on A Shot of SQLEspresso.

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.

The post Using Readable Secondary in Azure SQLDB appeared first on A Shot of SQLEspresso.

Lots of exciting things are happening with Data Saturdays and we want you, the #SQLFamily, involved.

Last week, Rob Sewell (B|T) announced the new automation and setup process for events.  This week I am announcing a new logo and branding initiative. With the generous donation from Denny Cherry and Associates Consulting, DCAC, we have commissioned 99Designs artists to create a new logo for branding. The Data Saturdays Admins reviewed over 220 submissions these were considered the likeability, accessibility, inclusiveness, and ease of multi-use. We have narrowed down the choices to these four.  Please take a moment to click the link below and rate your favorite, we will use your influence to make the final decision.

We’re excited to show off the new branding and deliver to you a full marketing layout to help you with branding your events using the collaborative community owned Data Saturdays resources.

This contest will only stay open for 3 full days ! Please get your votes in soon!!!

CONTEST LINK– https://99designs.com/contests/poll/6f3ab0edcb

Please be sure to click into each option, via the link, and zoom in for detail to see the full layout of design idea.

Feel free to reach out to any of these Data Saturday Admins with questions.

• Rob Swell
• Gianluca Sartori
• Steve Jones
• Monica Rathbun
• Randolph West
• Johan Ludvig Brattås
• Andy Mallon
• Elizabeth Noble
• Warwick Rudd
• Matt Gordon

Contest Closing Friday, March 26th 9pm EST

The post Data Saturdays Logo Contest appeared first on A Shot of SQLEspresso.