July 16, 2009 at 9:47 am
Hello, Folks,
We have a main table like this, with a little more than 200 million rows:
CREATE TABLE dbo.MainTable
(
DateHourStamp DATETIME NOT NULL,
RecordIdentifier NCHAR(17) NOT NULL,
LatestStatusDateTime DATETIME NULL,
LatestStatusValue NCHAR(2) NULL,
...
)
ALTER TABLE dbo.MainTable
ADD CONSTRAINT PK_MainTable
PRIMARY KEY CLUSTERED
(
DateHourStamp ASC,
RecordIdentifier ASC
)
CREATE UNIQUE NONCLUSTERED INDEX IX_MainTable_RecordIdentifier
ON dbo.MainTable
(
RecordIdentifier ASC
)
We have a status table like this, with a little more than 2 million rows:
CREATE TABLE dbo.StatusTable
(
RecordIdentifier NCHAR(17) NOT NULL,
StatusDateTime DATETIME NOT NULL,
StatusRank TINYINT NOT NULL,
StatusValue NCHAR(2) NOT NULL
)
ALTER TABLE dbo.StatusTable
ADD CONSTRAINT PK_StatusTable
PRIMARY KEY CLUSTERED
(
RecordIdentifier ASC,
StatusDateTime DESC,
StatusRank DESC
)
The main table is populated once an hour with one row per record identifier, and the distribution of record identifiers is pretty even and mostly ever increasing.
The status table is populated once an hour (but later than the main table) with multiple rows per record identifier, and the distribution of record identifiers could be pretty random.
We use the following statement to update the main table with the latest status for a record identifier:
WITH NewStatuses AS
(
SELECT
RecordIdentifier,
StatusDateTime,
StatusValue,
ROW_NUMBER() OVER
(
PARTITION BY
RecordIdentifier
ORDER BY
StatusDateTime DESC,
StatusRank DESC
)
AS RowNumber
FROM
dbo.StatusTable
)
UPDATE
dbo.MainTable
SET
LatestStatusDateTime = NewStatuses.StatusDateTime,
LatestStatusValue = NewStatuses.StatusValue
FROM
dbo.MainTable
INNER JOIN
NewStatuses
ON (MainTable.RecordIdentifier = NewStatuses.RecordIdentifier)
WHERE
NewStatuses.RowNumber = 1;
MainTable and StatusTable have a one-to-many relationship. The ROW_NUMBER() function allows us to filter the StatusTable to make a pseudo one-to-one relationship.
Here is the problem. Even though we make that pseudo one-to-one relationship, SQL Server still treats it like a one-to-many, and throws a Hash Match Aggregate in the plan to eliminate possible duplicates before the update.
The number of rows input to the Hash Match Aggregate is always equal to the number of rows output. And because the StatusTable and Hash create a pretty random mess, the actual update to the table causes a ton of random disk access.
We tried adding OPTION (ORDER GROUP) to the end, but that just changed the Hash Match Aggregate into a Sort Distinct. Granted, the random disk access was reduced. The hash takes about 90 minutes, the sort takes about 45 minutes.
My question is, how can we eliminate the Hash Match Aggregate or Sort Distinct all together, when we know that the number of rows input to the operator is always equal to the number of rows output? Thanks.
July 16, 2009 at 10:00 am
Please post index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2009 at 10:16 am
Thank you, Gail.
The index definitions are in the above post, and the SQL plans are attached. In the HashMatch plan, the DateHourStamp was a calculated column, so that's why you see that extra Key Lookup. In the SortDistinct plan, we made DateHourStamp persisted to eliminate the Key Lookup. Otherwise, the only difference between the plans is the Hash Match vs. Sort Distinct.
Kind Regards,
Eric Hand-Smith
July 17, 2009 at 6:05 am
Hey Eric,
A few observations:
Using the ROW_NUMBER = 1 trick is a handy one, but the optimizer cannot use it to infer group-level uniqueness - the hash aggregate or sort distinct is therefore necessary, at least from the optimizer's point of view.
It is interesting that the row number estimates for both the hash aggregate and sort distinct are around 60K. In fact, 2.4M rows flow through those operators. Since both require a memory grant (which is determined at compile-time, not run-time) it is highly likely that both operations overflow the memory allocated to them. This will result in spills to tempdb - which you can see using Profiler: look for the Hash and Sort Warning events.
It is unfortunate that both tables have wide composite primary keys - a single integer row identifier is often useful in these situations (even more so, in some circumstances, if a parallel plan can be used).
It looks as if almost all rows of the main table end up being updated. If this is the case (or even if it is just a significant fraction) it is likely that the wide (per-index) write-cursor side of the update plan is generating most of the I/O you see - even if the hash or sort is spilling to disk.
If you have exclusive access to the table at the time the update runs, you should seriously consider disabling the non-clustered indexes on the updated table for the duration of the update, and then rebuilding them afterward. This obviously has the side-benefit of giving you nicely contiguous non-clustered indexes too.
Another idea worth considering would be to use a trigger on the status table to maintain a new table containing just the latest values, as inserts, updates, and deletions occur. If a unique index were created on that new table which matches the primary key of the main table, that would likely remove the hash/sort and make life considerably less difficult for the optimizer.
If you simply wish to remove the hash/sort at any price (to see if it is responsible for the I/O), you could insert the results of your ROW_NUMBER = 1 trick into a temporary table (probably not a table variable given the number of rows involved) with the appropriate primary key, and then update the main table from there.
Given the differences between the execution plans submitted and the original query, it is difficult to provide more specific advice.
Paul
July 17, 2009 at 9:14 am
Hello, Paul,
Given the differences between the execution plans submitted and the original query, you still provide fantastic advice. Thank you.
We will test these ideas and post the results. Thanks, again.
Kind Regards,
Eric Hand-Smith
July 17, 2009 at 9:39 am
phlogiston (7/17/2009)
Given the differences between the execution plans submitted and the original query, you still provide fantastic advice. Thank you.
Very amusing Eric! :laugh: ...and thanks. It would be interesting to hear how you get on.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply