2

For background, I am on an instance of SQL Server 2012 Enterprise Edition.

I have three tables that join on the same set of three keys - all of them are INT. All tables have their keys clustered in the following order:

  • Key-Time (represents a week)
  • Key-Product
  • Key-Location

The main table left joins to the other two tables, and the result is fed into a set of windowing functions. The purpose is to sum a specific number of weeks' data based on Product-Location, like so:

SUM(Metric) OVER (PARTITION BY Key-Product, Key-Location ORDER BY Key-Time ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS Result

The where clause limits the time frame by Key-Time, and an outer query discards any results that would not have 3 preceding rows. There are 15 windowing functions calculating different metrics.

The issue comes when sorting for the partitioning function. This is the first part of my query plan:

Query Plan for Windowing Function

When the tables are joined, the results are already sorted by Time-Product-Location (the key). The windowing function needs to order by Product-Location then Time, hence the second sort. This query takes just over two minutes to execute for the 22 million rows shown, but in production the tables are much larger.

We have other queries that depend on the Time-Location-Product ordering, so changing the order of the keys won't be an option. Also, the two left join tables contain the metrics data, but the main table must be present to ensure accuracy.

How would I go about speeding this up? Is there room for improvement here?

UPDATE: Here is the information when I hover over the sort: Sort Information

  • What are the estimated and actual rows from the operator before the sort? – James Anderson Oct 10 '14 at 14:59
  • I updated the question with the sort operation information - 12.8 million estimated with 20 million actual. – lucrativelucas Oct 10 '14 at 15:12
  • That sounds like an issue with statistics. Do you rebuild stats and perform regular index maintenance? – James Anderson Oct 10 '14 at 15:26
  • We rebuild the indexes regularly and let the engine take care of the stats, but I guess I don't see how estimating 13mil vs 20mil rows would affect sorting performance. – lucrativelucas Oct 10 '14 at 17:04
  • 1
    It can matter because you have a spill to tempdb. Estimated Data Size is 993 MB and Actual Data Size is 1581 MB and that is a pretty good match to the difference between estimated and actual rows. How about creating non-clustered indexes on Product-Location-Time? That could also give you merge joins instead of hash joins. – Mikael Eriksson Oct 10 '14 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.