December 16, 2020 at 11:41 am
Hi all,
I tried to find different locks in my DB. For this, I use the SQL Server Profiler.
When I launch Profiler I saw a lot of events for several minutes.
Could you help me how correctly choose filters for important information?
Thanks.
December 16, 2020 at 1:00 pm
First, you'll need to detail exactly what it is you're hoping to see, which events you're capturing and how what you want for filtering.
Second, just so you know, Profiler has a very bad filtering mechanism. It captures all events, using the full set of resources needed, and then filters after the capture, using additional resources. Extended Events is a much more efficient tool for something like this as it filters at capture, using radically less resources.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 16, 2020 at 1:03 pm
I saw that my queries and SP with the same execution plan have different time execution.
I would like to investigate maybe something can block it...
December 16, 2020 at 1:31 pm
Ah, so you're not after locks as such, you're looking for blocking and what's causing the blocking?
If so, here is a great article on exactly how to do that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 17, 2020 at 12:14 pm
Unfortunately, I didn't see any blocks. Can it be some locks? Because there is a lot of information and I can't understand what is better to concentrate on. I saw different locks: S, IX, and so on. How I can identify that it can be an impact on my SP?
Thanks.
December 17, 2020 at 12:32 pm
They are only going to affect the query if there is blocking. If there is a lock over on resource X and you're accessing resource Y, it doesn't matter. It's only when you try to access resource X that you have to wait until the first lock clears (depending on the lock of course). That's blocking.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 12, 2021 at 2:29 pm
@Grant
Do you know how I can identify "Parallel query worker thread was involved in a deadlock"? I saw such a message in the SQL Server Profiler but didn't see any information regarding it.
January 12, 2021 at 3:02 pm
It's a parallelism deadlock. So, whichever query was involved, it was the only query involved. If you can't see the query from Profiler, you're stuck. Profiler doesn't have a correlation function like Extended Events. Yet another reason why I prefer them over Profiler.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 12, 2021 at 3:33 pm
Thanks a lot.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply