Showing posts with label Performance tuning. Show all posts
Showing posts with label Performance tuning. Show all posts

Tuesday, May 2, 2023

Monitoring Deadlocks in Azure SQL Managed Instance

 Hello Dear Reader!  My lastest blog on our Azure FastTrack blog for Microsoft is live, Monitoring Deadlocks in Azure SQL Managed Instance. 

Here's a little from the blog:

To paraphrase Annie, Deadlocks ARE JUST AWFUL!  When they occur, it means one transaction was the victim and rolled back, the other succeeded.  You have a couple different ways to monitor them in Azure SQL Managed Instance.  Let's review those together. 

 

What is a deadlock?  To quote our MS Learn Documentation, "Deadlocks occur when two or more processes or transactions block each other from continuing because each has locked a database resource that the other transaction needs".  

In layman's terms a deadlock occurs when two queries are blocking one another and neither could ever complete its transaction.  Pretend two people need to use the same door.  They cannot fit through at the same time.  One must stop and let the other proceeded first.  Now add on top of that they both reached for the door knob and grabbed it at the same time, both pulling the door in an opposite direction.   

 

Instead of simply waiting, one must let go of the door knob.  In this case the door knob is a table, the hands reaching out are attempting to gain a lock.   In SQL Server, all versions of the database engine, this is accomplished by the Lock Manager it uses a process called FIFO, first in first out, to determine who has to let go.

 

In the case of SQL one transaction is a deadlock victim, it is killed and the transaction is rolled back.  So if this is occurring on your system fixing it is very important.  First you have to find out if they are occurring, and that's what we will cover in this blog post today!


To read the rest here is the link: Monitoring Deadlocks in Azure SQL Managed Instance


As always Dear Reader, Thank You for stopping by.


Thanks,


Brad




Monday, December 5, 2016

Optimize For Unknown

Hello Dear Reader!  It's been a while.  I was working with a friend and we came across an interesting problem.  They had a large amount of skewness/data skew.  This led to some performance issues for them.  The way this manifested itself was in a set of queries that ran quickly, normally within seconds.  Then occasionally they ran much longer.  To be precise, they ran about x800 times longer.


As you can imagine this is a less than ideal situation for a production environment.  Their solution was to add OPTION (RECOMPILE)  to all of their stored procedures.  This solved the issue with their data skew.  It caused additional side effects.  Increased CPU as every stored procedure now had to recompile before execution.  No stored procedure could experience plan reuse.  Using DMV's to track stored procedure utilization and statistics no longer worked.


"So Balls", you say, "What is the alternative?  Is there an alternative?  And what in the name of King and Country is skewness/data skew!"

Ahh great question Dear Reader!  Dear Reader why are you English today?

"Because you watched the Imitation Game last night, and Benedict Cumberbatch's voice is still stuck in your head."

Right as always Dear Reader!  Good point let's explain it and then do a Demo!


SKEWNESS/DATA SKEW


Skewness is a term from statistics and probability theory that refers to the asymmetry on the probability distribution of a real valued random variable about its mean.   This could get complicated quickly.  In simpler terms that I can understand it means that there are patterns based on variables with an assigned real value.  Based on those variables skewness can be determined and it is the
difference of the normal.

How does this effect our query plans.  With data skew we have a over abundance of data that fits one statistical model and it does not fit for others.  This means the way the SQL Server Cardinality Estimator estimates for one may be different for another based on statistics.

Here's a quick example.  I have a school with 100,000 students.  Every student has a combination of 10 different last names.  On average one could assume that every 10,000 students will have different last names.  If we randomly assign these values, there will be a slight skewness.  Most of the ranges will be similar.  For this example I'll use my students table from my college database.

       select
              lastname, count(*)
       from
              dbo.students
       group by lastname

       order by count(*) desc;



Now we move a new student to the area.  This one student will give us quite a bit of data skew, and will be extremely asymmetrical to the other results.


In order to show this in action we'll make a stored procedure that returns our First Name, Last Name, and the Course Name of students by last name.  Remember some students will have multiple courses.  This means we will have more results than we do last names.


if exists(select name from sys.procedures where name='p_sel_get_stu_name')
begin
       drop procedure p_sel_get_stu_name
end
go
create procedure p_sel_get_stu_name(@lname varchar(50))
as
begin
      
       select
              s.FirstName
              ,s.LastName
              ,c.courseName
       from
              dbo.students s
              left join enrollment e
              on s.studentID=e.studentid
              left join courses c
              on e.courseid = c.courseid
       where
                     lastname=@lname

end

So now we will execute this query and see the difference between our query plans and benchmark  the performance.

exec p_sel_get_stu_name 'Bradley' with recompile;



exec p_sel_get_stu_name 'Segarra' with recompile;



The first query took a little over a second to return two rows.  The second query was sub-second and returned 13,843 rows.  Each execution plan was different.  One was parallel, the other was serial.  That makes sense Parallel returned over 13,000 rows, serial only returned 2 row.  The statistical variance is different.  The cardinality estimate gave us different results.

Now let's make this bad.  I'm going to run the first query and second query again.  This time I'm removing the with recompile.

exec p_sel_get_stu_name 'Bradley';
exec p_sel_get_stu_name 'Segarra';

The first query did not change.   The second one did.


We used the cached plan.  Because of data skew we forced 13,843 rows through the serial execution plan.  The result was 8 minutes and 42 seconds instead of a sub-second query.


This is data skew.  We've shown that recompiling the query forces both to execute with their least cost plan.  Is there another option?  In this case we could use the query hint OPTIMIZE FOR UNKNOWN.

The benefit of OPTIMIZE FOR UNKNOWN is that we can remove the recompile.  This will allow us to get the best/least cost plan based on data skewness of the statistics.


if exists(select name from sys.procedures where name='p_sel_get_stu_name')
begin
       drop procedure p_sel_get_stu_name
end
go
create procedure p_sel_get_stu_name(@lname varchar(50))
as
begin
      
       select
              s.FirstName
              ,s.LastName
              ,c.courseName
       from
              dbo.students s
              left join enrollment e
              on s.studentID=e.studentid
              left join courses c
              on e.courseid = c.courseid
       where
                     lastname=@lname
       option (optimize for unknown)
end

exec p_sel_get_stu_name 'Bradley';
exec p_sel_get_stu_name 'Segarra';

Now we execute our procedures and we get our execution plans.  Here are our new query plans.




You'll notice that the execution plan based on statistical variance was parallel plan.  Both queries executed sub-second.  This is not the least cost plan for the first query.  In case you were curious here is a look at the histogram.


WRAP IT UP

So what does this mean?  For the business purpose of  speeding up a query option recompile is completely valid.  

It comes at a cost.  Recompilations, increased CPU utilization, and you loose the history of the execution of the stored procedure from DMVs.  

If those costs do not effect you, or effect the system less than the fluctuation of query performance then it is valid.  

There is also another alternative to use in your tool belt.  That is what we used today.  Like all things in computers use it judiciously.  Test, test, and retest before deploying into production.  As always Dear Reader, Thanks for stopping by.


Thanks,
Brad 

Thursday, June 26, 2014

Deck & Demo's Live & Thank You AZSSUG & OPASS!

Hello Dear Reader!  Just a quick post to say Thank You to the Arizona SQL Server User Group and to my home town nortth Orlando user group OPASS!

This week I was very lucky to present Inside the Query Optimizer to the AZ SSUG and Performance Tuning, NOW! to OPASS.  I had promised to get my decks and demo's live and I wanted to do that.

Click Here for the Deck for Inside the Query Optimizer, and here for Demo's.

Click Here for the Deck for Performance Tuning, NOW!, and here for Demo's.

AZ to FL and back again.  I believe next week I'll just rest :).

Seriously Thank you to the wonderful SSUG leaders, Matt & Amy in AZ, and Shawn, Karla, and Rodney in my home town.

Without you this isn't possible!  And Dear Attendee's Thank You, if you have any questions please feel free to shoot me an email.

As always Thanks for stopping by.

Thanks,

Brad

Thursday, March 21, 2013

SQL in Chicago

Hello Dear Reader!  I've got some very exciting news!  Pragmatic Works is holding a Two Day Performance Tuning Workshop in Chicago Illinois on Wednesday April 3rd and Thursday April 4th.  Headlining this act will be the wonderful Kimberly Hathaway(Linked-In), and lil old me!

I was born and lived most of my young life in Peoria Illinois.  The last time I was in Chicago was for a field trip to the Museum of Natural History, aka the Field Museum, with my Boy Scout Troop.  I was one of the Boy Scouts and it wasn't a reunion.

Chicago is home to some of the best resturants in the country, a main spot on shows like Man vs. Food, Diners Drive-Ins' and Dives, and Anthony Bourdain have made multiple episodes on the Windy City.

I've grown up a life long Cubs fan who has watched them in Atlanta, Washington D.C., and Miami, never in the Mecca of Wrigley Field.  Sadly the Cubs are out of town, heck even the Wrigley Field tours don't start until the week after I'm gone. I'm still hoping to stop by Wrigley and at least get a photo.

"So Balls", you say, "There was a Performance Tuning Workshop?"

Thank You Dear Reader.  My love for Chi-Town aside, I cannot wait for this workshop.  Kimberly and I are going to working very hard for you Dear Attendee, to make sure that in two days we can paint a picture and bring a lot of complex topics home.  Here's a link to the class if you're in town and would like to sign up (Some changes are coming to the outline, content will stay the same, but the flow will be streamlined)!   We are already 75% sold out, and space has been filling up fast. Space is limited so get your seat while you still can!


BUILD IT THE RIGHT WAY


A lot of the problems I see in servers deal with them not being set up correctly.  Most of the time it is a simple oversight.  When troubleshooting there are things that we will check for, and some of them are simple fixes.

Did you use SQLIO to baseline you're hard drives?  Did you take a perfmon baseline?  Do you know the correct calculations for PLE? Cough cough *hint*!  What about the best ways to set up and things to consider when using Virtualization?  Are your BIOS driver's up to date?  Do you have Green Driver Settings on, what is your Windows Performance Plan set to? All this and we haven't even installed SQL Server!

Our Configuration are only just beginning.  Once we install SQL Server there is more to do.  Have we set up Instant Database File Initialization, based on our security can we?   Max Memory, Lock Pages in memory, Max Worker Threads, Maxdop, Cost Threshold for Parallelism, and many other settings need to be considered.  We will cover each of these and what the best practices are for each.


Next up we will cover the Internals of how a Query flows through SQL Server.  We'll discuss the Optimizer, Statistics, and plan costing before diving into the Plan Cache and Execution plans.  We'll round things out by a full discussion on Indexes and end the day with a dive into Locking/Blocking and Deadlocks.

TROUBLESHOOTING SHARPSHOOTING


Anybody can run a DMV, (anybody with the right permissions on a SQL Instance that is).  Interpreting the results is what turns Troubleshooting into Sharpshooting.

To fine tune the process we will cover Waits and Queues, making sure we understand Preemptive vs. Cooperative Processing and how that helps us get Wait Stats.  We'll troubleshoot various types and discuss what we've seen.


We'll go back to our Baselines and talk about what to look for when things go south and how to start diving into the problem.  We'll use Extended Events to single out specific data and show how you can use them and powerful tools in your arsenal.

We will discuss some of the free tools, our SQL Community, and of course #SQLHelp.

We'll step into the BI world to discuss some performance issues you will see there, and how to troubleshoot them as well. Finally we'll get a hands on lab that we can all really get into.



THE WHOLE D@MN TEAM


One of the best things about the class?  When you get one of us from Pragmatic Works you get the whole crew. ( I call dips on being Nick Fury)

They might not be there in the class with us but great SQL Community members and teammates SQL MVP's Jorge  Segarra (@SQLChicken | Blog) and Jason Strate (@StrateSQL | Blog), .

Former Microsof-ties like Kathi Kellenberger (@AuntKathi | Blog) and Roger Wolter (@rwolter50 | Linked-In).

Finally Tremendous Sr. Consultants for Pragmatic works such as  Gareth Swanepoel (@GarethSwan | Blog), Chad Churchwell (@ChadChurchwell | Blog), Kimberly, and myself all had are input on the class and worked on making the demos.

For $300 it's a can't miss!  Hope to see you there! (Click Here to Sign Up while Seats are still available!)

As always Thanks for stopping by!

Thanks,


Brad

Tuesday, January 22, 2013

Pro SQL Server 2012 Practices Chapter 18 Review Tuning for Peak Load



I'm on a Book!

Hello Dear Reader, early last year I was asked if I would like to contribute to a book.  The concept was get a lot of really great SQL People together and let them write a chapter on a subject that they were passionate about.  Eighteen different top SQL professional’s, at least two MCM’s, many MVP’s, Shake Stir and you get our book, Pro SQL Server 2012 Practices.

Having only written once chapter I hadn’t read the others.  I had an idea I’d read the chapters and then blog reviews.  I shared this idea with Mr. Grant Fritchey (@GrantFritchey|Blog), check out his review of Chapter 12 yesterday,  and he suggested that we get all of the authors to blog reviews.  A lot of people signed on and we’re releasing our reviews one at a time. 

My first review is on Ben Debow’s (@BBQSQL | Website) chapter Tuning for Peak Load.  Ben is a co-founder of SQLHA with MVP Alan Hirt(@SQLHA | Blog).  Ben is a speaker, very active in the SQL Community, and an all around expert.

“So Balls,” you say, “Get to the review all ready!”

Alright Dear Reader, away we go!

TUNING FOR PEAK LOAD

Ben does an amazing job of stepping through many different tools that you can use to assess your environment.  First Ben identifies what Peak Load is.  He talks about the people that should be involved in the process of identifying and tuning for this period, and really steps through the business logic of why these different people should be involved.  Your mileage may vary based on how large or small your shop is, but in bigger shops he is spot on.  He goes on to talk about how you identify where you are today.

This is an important concept.  You cannot measure improvements, or if changes were detrimental, without first knowing as much as possible about your current environment.  We start at a 10,000 foot view of a setting up a topology diagram, Ben also lists a detailed table of Attributes to gather on your servers.  Next up we begin doing a performance assessment.

Ben weaves his way through Perfmon, gives you counters to monitor, and reasons for why you would want to collect them.  We move into a discussion of how to gather profiler data and recommendations what counters you would want to collect.

We move next into Observations.  Ben walks through metric’s he has collected and what they tell him.  This is invaluable to a DBA.  You often hear professionals say “Collect this data” if you’re lucky you hear them say “You want these numbers”, in this case he tells you how he interprets the numbers and what they could mean.  I’m stressing could, because this will help you in diagnosing your server, but each environment will be different.

We move into using PAL, http://pal.codeplex.com, to interpret and report on our Perfmon counters we’ve been collecting.  A quick aside if you want to set up PAL and get it working  read the documentation, there are two add ins.  One is tools for Office 2013 Web Components, another is Microsoft Log Parser, not listed but required as well is Microsoft Chart Controls for Microsoft.NET Framework 3.5.  If you do not have that last one you’ll get a nice little .NET error when trying to generate the report.

From there we move onto DMV’s and gathering index statistics.  Ben discusses gathering Index Usage Statistics from sys.dm_db_index_operational_stats, no script is listed to verify, but the columns discussed are from sys.dm_db_index_usage_stats.  As long as you get the right DMV the content is solid and I found the Costly Indexes description very interesting!

Finally Ben helps you devise a plan to actually implement the analysis into a plan you can implement.  The thing I love is that you can ask 20 brilliant SQL minds to do the same thing, and you’ll get 20 different variations of the same thing.  Ben looks for some things that I had not considered.  I enjoyed the chapter immensely and look forward to implementing what I’ve learned.

If you pick up the book please feel free to drop me a line and tell me what you think!

As always Thank You for stopping by!

Thanks,

Brad