Sql-server – High I/O for part of query

execution-planperformanceperformance-tuningquery-performancesql-server-2005

I have a large query that I'm trying to tune. I write lots of queries but haven't done a lot of tuning. I've included a screenshot from SQL Sentry Plan Explorer Free (SSPEF):

High I/O

In the above part of the plan the table pb_WorkRquestLog contains 229,001 rows. However, the query plan is showing approx. 348 million rows (229,001 x 1,520 iterations):

enter image description here

There is no where clause so the query is using a Clustered Index Scan. I've rebuild all indexes with FULLSCAN and updated all statistics.

The code that this part of the plan is executing is:

select distinct 
            wrs.ServiceKey 
            , owner.DepartmentName AS GroupName
            , owner.UserName AS UserId
            , owner.WRLCreateDateTime
            , owner.WRLNotes
            , wrx.CreatedDate as WRCreateDateTime
            , wrx.Id
            , wrx.Description 
            , cast(wrx.Notes as varchar(2500)) as Notes
        from    
            prism72ext.dbo.pb_WorkRequestService wrs 
            Join prism72ext.dbo.pb_WorkRequest wrx on (wrs.WorkRequestId = wrx.Id and wrx.Status = 'Incomplete')
            left join (
                select
                    wl.WorkRequestId
                    ,   d.Name AS DepartmentName
                    ,   u.UserName
                    , wl.CreatedDate as WRLCreateDateTime
                    , cast(wl.Notes as varchar(2500)) as WRLNotes
                from
                    (
                        SELECT     
                            MAX( Id ) AS Id
                        FROM          
                                prism72ext.dbo.pb_WorkRequestLog WITH(INDEX(0))
                        GROUP BY 
                            WorkRequestId
                    ) mwl
                    join prism72ext.dbo.pb_WorkRequestLog wl on mwl.Id = wl.Id
                    join prism72Ext.dbo.pb_Department d ON wl.DepartmentId = d.DepartmentId
                    left join prism72Ext.dbo.pb_User u on wl.UserId = u.UserId
                ) owner on wrs.WorkRequestId = owner.WorkRequestId

SSPEF is reporting the Actual Data Size is nearly 5GB, with 3 million Logical Reads! SSMS reports the table to be only 16 MB in size.

I've tuned the query down from 4m28s to 1m35s, but now I'm stuck. I'd be grateful if anyone could point me in the right direction to deal with this.

Edit: Someone suggested trying "OPTION (HASH JOIN, MERGE JOIN)". This made a dramatic difference. I/O was massively reduced and the query ran in 13 seconds.

Does anyone see any issues with this solution?

Best Answer

If you don't already have one, create a composite nonclustered index on prism72ext.dbo.pb_WorkRequestLog(WorkRequestId, Id DESC) and run the query without the index hint.

Also, have your inner select for the owner derived table return mwl.WorkRequestId instead of ml.WorkRequestId.

Required Software Updates

The first is SQL Server. You are running SQL Server 2008 Service Pack 1 (build 2531). You ought to be patched up to at least the current Service Pack (SQL Server 2008 Service Pack 3 - build 5500). The most recent build of SQL Server 2008 at the time of writing is Service Pack 3, Cumulative Update 12 (build 5844).

The second piece of software is SQL Sentry Plan Explorer. The latest versions have significant new features and fixes, including the ability to directly upload a query plan for expert analysis (no need to paste XML anywhere!)

Query Plan Analysis

The cardinality estimate for the table variable is exactly right, thanks to a statement-level recompilation:

table variable estimate

Unfortunately, table variables do not maintain distribution statistics, so all the optimizer knows is that there are six rows; it knows nothing of the values that might be in those six rows. This information is crucial given that the next operation is a join to another table. The cardinality estimate from that join is based on a wild guess by the optimizer:

first join estimate

From that point on, the plan chosen by the optimizer is based on incorrect information, so it's no wonder really that performance is so poor. In particular, the memory set aside for sorts and hash tables for hash joins will be far too small. At execution time, the overflowing sorts and hashing operations will be spilled out to physical tempdb disk.

SQL Server 2008 does not highlight this in execution plans; you can monitor the spills using Extended Events or Profiler Sort Warnings and Hash Warnings. Memory is reserved for sorts and hashes based on cardinality estimates before execution starts, and cannot be increased during execution regardless of how much spare memory your SQL Server may have. Accurate row count estimates are therefore crucial for any execution plan that involves workspace memory consuming operations.

Your query is also parameterized. You should consider adding OPTION (RECOMPILE) to the query if different parameter values affect the query plan. You should probably consider using it anyway, so the optimizer can see the value of @Param1 at compilation time. If nothing else, this may help the optimizer produce a more reasonable estimate for the index seek shown above, given that the table is very large, and partitioned. It may also enable static partition elimination.

Try the query again with a temporary table instead of the table variable and OPTION (RECOMPILE). You should also try materializing the result of the first join into another temporary table, and run the rest of the query against that. The number of rows is not all that large (3,285,620) so this should be reasonably quick. The optimizer will then have an exact cardinality estimate and distribution statistics for the result of the join. With luck, the rest of the plan will fall into place nicely.

Working from the properties shown in the plan, the materializing query would be:

SELECT
    A.A_Var7_FK_LK,
    A.A_Var4_FK_LK,
    A.A_Var6_FK_LK, 
    A.A_Var5_FK_LK,
    A.A_Var1,
    A.A_Var2,
    A.A_Var3_FK_LK
INTO #AnotherTempTable
FROM @MyTableVar AS B
JOIN TableA AS A
    ON A.Job = B.B_Var1_PK
WHERE
    A_Var8_FK_LK = @Param1;

You could also INSERT into a predefined temporary table (the correct data types are not shown in the plan, so I cannot do that part). The new temporary table may or may not benefit from clustered and nonclustered indexes.

Best Answer

Related Solutions

Sql-server – Forcing Join Order

Sql-server – How to optimise T-SQL query using Execution Plan

Required Software Updates

Query Plan Analysis

Related Question