I just got back from the cinema where I watched The Lost City with Sandra Bullock and Channing Tatum.
Yess, a cool film! But now it is the time to turn on the laptop and, as promised in the last post, click on the publish button for a special post!
A day at work with me!
Remember, when a day starts before 8:00 with a business phone call it's usually never a good sign!
And if an important customer is blocked and you start the day without coffee, this is all ... confirmed!
Ok, let's be more precise! When the call from your customer "X" arrives, whose procedure for calculating the stocks of his material is no longer successful!
Enjoy the reading!
Deadlock & warehouse stock
It's urgent! Let's connect with the customer to see what's happening!
What to say ...often an error message can tell us a lot:
Well, yes (houston!) we have a deadlock problem!
And now, how to proceed? We activate a couple of trace flags:
DBCC TRACEON (1204,-1)
GO
DBCC TRACEON (1222,-1)
These trace flags allow us to log deadlocks to the SQL Server error log as well
Very good!
Let us now launch the procedure that goes into deadlock again and let's see what the SQL Server error log contains.
You can find the log here:
Now click on the Current item and look at the Log:
We can see a lot of information, but the most important is the collision of the two queries.
In the green box at the top you can see on which table the two queries collide.
Now some time has passed! ... and I know that sometimes the customer would have already called to find out how to proceed with the resolution ... it takes a cool head but we already have the most important information!
What can we do now?
Profiler
We have to run the procedure again!This time, however, we will put it under profiler!
How should you set up the profiler?
I usually always select check to track stored procedures and triggers.
This time I need to know also when a rollback occurs:
I need to see if recompilation occur:
And I need to see also the execution plan:
Now run the procedure again and take a look to the profiler.
The same Query executed 4 times, reads twice a number of pages and twice a different and much greater number of pages.
Furthermore, not seeing any recompilation from the profiler, we come to understand that we have a sniffing problem!
We have a so called sniffing problem!
We have talked about sniffing many times on this blog, if you want to know more you can search here.
Just two words to say where this problem arose:
When SQL Server solve a Query generate an execution plan and this this operation is quite heavy SQL Server store the execution plan in the cache plan.
As said this happens for performance reason.
In fact if I run the same query SQL Server will reuse the same execution plan and the execution time will be less.
However if my query has parameters a problem arose: the execution plan has been generated with some parameters while I execute the query with other parameters.
The execution plan may be not optimal.
This happens furthermore when the Query extract data distributed unevenly. For example, when I ask for the number of orders a customer has placed, I might have a customer who has placed many orders while others have not placed any.
Solve a sniffing problem
Today we need to solve this problem.
What could we do? We could create an adhoc index.
I created a custom index.This index will allow to the Query to read less rows and thus have less probability to read rows readed or updated by other Queries.
Some tests usually need to be done.
In the end I created a covering index and I verified that it is used by my SELECT and not by the UPDATE
.... et voilà the deadlock is disappeared and the procedure is working again!
This time it was simple but we made it friends! We made it!
And now a good coffee!
That's all for today!
