Query Performance Issue

TomLelli 2015-02-13 19:09:23

We are looking for some assistance in improving the performance of this sql query. It looks like it is performing a Index scan on some tables. link text

/storage/attachments/1130-poorperformingquery.pesession
Aaron Bertrand 2015-03-10 14:18:54
Some of the scans seem to be caused by a vast overestimate in rows. I would start by ensuring statistics have been updated on all of the tables so that SQL Server has a chance to make decent estimates. You might also try fiddling with MAXDOP to match the number of cores per NUMA node, if that number is not 4. You're getting decent parallel distribution but if you can use more cores on that NUMA node some of these operations may process faster. Still, I think that stats will be the key, and don't always assume that a scan is bad – it can be better to scan hundreds of thousands of rows than to perform hundreds of thousands of individual seek operations.
SQLkiwi 2015-03-18 05:19:48
There isn't a single obvious reason in the session file for that query to run for over a minute, but there are a number of smaller things that might add up:

I would eliminate the splitting operation from your enquiries by saving the result of dbo.split to a temporary table with the matching data type (int) and suitable indexing.

Run the query with OPTION (RECOMPILE) to enable the parameter-embedding optimization. This may well help by compiling a plan for the specific runtime values of @GroupID and @CourseIDs. The existing general plan shape for all possible variable values does result in a moderately high number of executions for some plan operators.

Check the sys.dm_os_waiting_tasks DMV during execution, paying particular attention to the node ID in the resource_description for CXPACKET waits. These waits seem on the high side, and it may be that the DMV can help identify where this is occurring. Further analysis would be required to determine any deeper cause here.

The difference is estimate vs actual rows for the scans is probably not cause for concern. Most of the scans have pushed-down static bitmap filters, which are added after query optimization (so the estimates do not account for their effects). On the face of it, the bitmap + scan strategy seems a reasonable one.

Aside from the above, I would also break this query up a bit, along the natural transitions in the query plan. This may help improve performance, but it is more aimed at understanding where the performance problem is occurring. Depending on what you find by doing that, you may choose to leave it that way, or rebuild the monolithic query incorporating any corrective measures.

The spilling hash joins and sort does not appear to be causing a performance problem, but eliminating these may be a handy side-effect of the measures outlined above.

There are also a number of small implicit conversions due to typing mismatches. I doubt these are having a major performance effect, but again, it is worth correcting these just in case.

Sorry there isn't a single obvious cause (to me, anyway).

February 13, 2015
No Comments