Strate SQL

I got to work with deadlocks quite a bit recently.  There were quite a few interesting ones that came up that I had the chance to research.  Since I like easy, I’ll start with the one that I forgot to grab the deadlock details for.

Well, maybe not all of the details… in this case as the title states I was looking at deadlocks with the events exchangeEvent and threadpool.  I managed to come across a post from Bart Duncan that went through and deciphered this deadlock.  The long and the short of it… parallelism deadlocks.

Bart does a better job explaining this than I can do here, especially since I didn’t take the time to grab the deadlock details for review.  Maybe I’ll have that one the next one…

Fortunately, a large part of the issue that I was reviewing for the client had to do with parallelism and so solving this issue actually occurred as a side effect of dealing with parallelism issues.  But I will share my little secret that I used to resolve this and most of the parallelism…

Indexes!!

There I said it.  True, you can have too many indexes.  But no indexes is too few.  No clustered indexes can lead to too many scans.  I could pulpit here on indexes and making sure that you have them, but I’ll save that for another time.

Overall, I used Bart’s Workaround #1.  Hopefully this helps… direct you to a post that is more prescriptive.

It’s quite often at clients that I spend time working on issues revolving around parallelism.  In fact, just this week it’s been the single most irritating performance problem that I’ve had to deal with.

If you aren’t up to snuff on parallelism there are a couple places I would start with getting up to speed:

• Case Study: Part 1: CXPACKET Wait Stats & ‘max degree of parallelism’ Option: Introduction to Using Wait Stats to Identify & Remediate Query Parallelism Bottlenecks
• Case Study: Part 2: CXPACKET Wait Stats & ‘max degree of parallelism’ Option: Suppressing Query Parallelism Eliminated CXPACKET Waits & Liberated 30% of CPU

Both articles are from Jimmy May (@aspiringgeek), who writes some great articles on SQL Server performance and scalability.

Two days ago, I was working on trying to determine which queries were causing the most trouble when it came to parallelism.  That was when I modified the current queries executing with parallelism query to make it more useful.  And a few days before I posted some queries that I also use to determine plans that may utilize parallelism.

While working on these parallelism issues, I started wondering if identifying query plans utilizing parallelism by CPU was the correct approach.  Just because an execution plan has high CPU doesn’t necessarily mean that it is the biggest contributor or source of parallelism on the server.

Instead, I started looking at execution counts of the plans.  The query that I’ll be listing below provided a stark indication of the highest contributor to parallelism in the issues I was working on by identify a single query plan that was utilizing parallelism and had been executed nearly a 100 thousand times since I’d cleared the procedure cache that morning.

SELECT TOP 50

    CASE WHEN p.dbid = 32767 THEN '{RESOURCE}' ELSE DB_NAME(p.dbid) END as database_name

    ,OBJECT_NAME(p.objectid, p.dbid) as [object_name]

    ,qs.execution_count

    ,qs.total_worker_time

    ,qs.total_logical_reads

    ,CONVERT(decimal(12,3),qs.total_elapsed_time/1000000./qs.execution_count) as avg_elapsed_time_sec

    ,CASE statement_end_offset WHEN -1 THEN q.text

        ELSE SUBSTRING(q.text, statement_start_offset/2, (statement_end_offset-statement_start_offset)/2) END as sql_statement

    ,p.query_plan

    ,q.text

    ,cp.plan_handle

FROM sys.dm_exec_query_stats qs

    INNER JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as q

WHERE cp.cacheobjtype = 'Compiled Plan' 

AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)', 'float') > 0

ORDER BY qs.execution_count DESC

Now that I’ve shared this, what do you the reader use to identify queries that are utilizing parallelism in your environments?

While working on some parallelism issues today I started using the query that detects whether there are queries currently running utilizing parallelism.  It’s the same query that can be found in the white paper Troubleshooting Performance Problems in SQL Server 2005.

Which I’ve included below:

SELECT 

    r.session_id,

    r.request_id,

    MAX(ISNULL(exec_context_id, 0)) as number_of_workers,

    r.sql_handle,

    r.statement_start_offset,

    r.statement_end_offset,

    r.plan_handle

FROM 

    sys.dm_exec_requests r

    JOIN sys.dm_os_tasks t ON r.session_id = t.session_id

    JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id

WHERE 

    s.is_user_process = 0x1

GROUP BY 

    r.session_id, r.request_id, 

    r.sql_handle, r.plan_handle, 

    r.statement_start_offset, r.statement_end_offset

HAVING MAX(ISNULL(exec_context_id, 0)) > 0

It sure is to know the queries currently executing using parallelism.  But the query doesn’t provide enough information when it executes to take some immediate action without further research.  The query below provides this additional information.

WITH CurrentParallelism

AS (

    SELECT 

        r.session_id

        ,r.request_id

        ,r.database_id

        ,MAX(ISNULL(exec_context_id, 0)) as number_of_workers

        ,r.sql_handle

        ,r.statement_start_offset

        ,r.statement_end_offset

        ,r.plan_handle

    FROM sys.dm_exec_requests r

        INNER JOIN sys.dm_os_tasks t on r.session_id = t.session_id

        INNER JOIN sys.dm_exec_sessions s on r.session_id = s.session_id

    WHERE s.is_user_process = 0x1

    GROUP BY 

        r.session_id, r.request_id, r.database_id,

        r.sql_handle, r.plan_handle, 

        r.statement_start_offset, r.statement_end_offset

    HAVING MAX(ISNULL(exec_context_id, 0)) > 0

)

SELECT

    cp.session_id

    ,cp.request_id

    ,db_name(cp.database_id) AS database_name

    ,cp.number_of_workers

    ,CASE statement_end_offset WHEN -1 THEN q.text

        ELSE SUBSTRING(q.text, statement_start_offset/2, (statement_end_offset-statement_start_offset)/2) END as sql_statement

    ,p.query_plan

    ,q.text

    ,cp.plan_handle

FROM CurrentParallelism cp

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as q

The information that I like to have on hand is the exact statement and query plan that is executing so that I know what it is that I am dealing with.  Hopefully others feel the same and find some use in this.