December 30, 2015 at 10:53 am
I'm looking to see if there is a way to further optimize the query below.
-- Start query
DECLARE @sql NVarChar(500),
@p0 SmallInt,
@p1 SmallInt,
@p2 DateTime,
@p3 SmallInt,
@p4 Int,
@p5 DateTime
SET @sql = N'
; WITH T AS
(SELECT TOP 100 ProcessStatus, ProcessId, ProcessDate
FROM SMSOutgoing sso WITH (ROWLOCK, READPAST, UPDLOCK)
WHERE
( ProcessStatus = @p0
OR (ProcessStatus < @p1 AND (ProcessDate < @p2)))
ORDER BY SOID)
UPDATE T
SET ProcessStatus = @p3,
ProcessID = @p4,
ProcessDate = @p5'
SET @p0 = 0
SET @p1 = 299
SET @p2 = DATEADD(mm,-15,GETDATE())
SET @p3 = 223
SET @p4 = 999999
SET @p5 = GETDATE()
exec sp_executesql @sql, N'@p0 smallint, @p1 smallint, @p2 datetime, @p3 smallint, @p4 int, @p5 datetime', @p0, @p1, @p2, @p3, @p4, @p5
-- end query
The table involved in the query, SMSOutgoing, is a queue table that will contain outgoing SMS messages. The query above is for the inertial batch update that preps the records for sending out. The next process will grab all records where processid = 999999 send out the messages. Note, multiple processes/threads hit this table which is why we are processing them in batches.
The SMSOutgoing table has a clustered index on "SOID". No other indexes currently exist on the table.
The execution plan for this query "Clustered Index Scan" and I'm wondering if anything can be done to change it to a "Clustered Index Seek" to further improve performance.
Attached is:
- a text file that includes the table script, clustered index script and sample data if needed.
- a jpg of the execution plan that I'm seeing.
Any input would be appreciated. This request is as much for a learning experience as it is anything else.
Thanks
Lee
December 30, 2015 at 1:48 pm
You should cluster this table on ( ProcessDate ) or ( ProcessDate, SOID ) if you want to insure the index is unique. You can create a separate nonclustered index on SOID if you need it.
That will solve this problem and any time you need to read from the table for a specific datetime range.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 4, 2016 at 12:52 pm
Scott
I created the clustered index on ProcessDate/SOID as you stated and also created a NC index on SOID.
Doing so has made the execution plan much more complicated and increased the overall cost of the query.
I also tried ProcessStatus/ProcessDate/SOID as the clustered index and that resulted in a similar similar execution plan/cost.
Did I miss something?
Thanks
Lee
ScottPletcher (12/30/2015)
You should cluster this table on ( ProcessDate ) or ( ProcessDate, SOID ) if you want to insure the index is unique. You can create a separate nonclustered index on SOID if you need it.That will solve this problem and any time you need to read from the table for a specific datetime range.
January 4, 2016 at 1:06 pm
I just noticed the OR condition on only status. That will force a table scan regardless. But I'd still like to see the actual query plan.
Btw, are you going by an actual execution or by the "estimated" numbers? The estimates are actually useless for performance comparison, you need the real numbers.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 4, 2016 at 1:35 pm
Scott
I've attached the "Actual" execution plan. Attached zip contains the XML output of the execution plan. Hopefully this will work for you.
Thanks
Lee
ScottPletcher (1/4/2016)
I just noticed the OR condition on only status. That will force a table scan regardless. But I'd still like to see the actual query plan.Btw, are you going by an actual execution or by the "estimated" numbers? The estimates are actually useless for performance comparison, you need the real numbers.
January 4, 2016 at 2:47 pm
As Scott mentions, an OR in a query makes it almost impossible to get a seek. But a rewrite can help you get two seeks instead of one scan - which may or may not be better, depending on lots of factors.
So you might try rewrting this part:
WITH T AS
(SELECT TOP 100 ProcessStatus, ProcessId, ProcessDate
FROM SMSOutgoing sso WITH (ROWLOCK, READPAST, UPDLOCK)
WHERE
( ProcessStatus = @p0
OR (ProcessStatus < @p1 AND (ProcessDate < @p2)))
ORDER BY SOID)
To:
WITH T AS
(SELECT TOP (100) ProcessStatus, ProcessId, ProcessDate
FROM
(SELECT ProcessStatus, ProcessId, ProcessDate, SOID
FROM SMSOutgoing sso WITH (ROWLOCK, READPAST, UPDLOCK)
WHERE ProcessStatus = @p0
UNION ALL
SELECT ProcessStatus, ProcessId, ProcessDate, SOID
FROM SMSOutgoing sso WITH (ROWLOCK, READPAST, UPDLOCK)
WHERE ProcessStatus <> @p0
AND ProcessStatus < @p1
AND ProcessDate < @p2) AS d
ORDER BY SOID)
Note that the ProcessStatus <> @p0 in the second part is very important to prevent duplicates. (You can also prevent them with UNION instead of UNION ALL, but that is almost certainly slower).
If there is a suitable index, then this form of the query can be compiled into a plan with two index seeks. But it's not guaranteed to happen. If it doesn't, then one possible explanation is that the index is not covering and the associated lookup makes the query too expensive. Another possible (and in my opinion more likely) reason is that when an index seek is used, the data retrieved is not in order of SOID, so now SQL Server has to retrieve all rows that match the WHERE clause, then sort all of them to find the first 100 that match. Depending on the amount of rows in the table and the amount of rows that SQL Server estimates to be a match, this can be expensive but still better, or it can be way more expensive than using a scan on an index that is already ordered on SOID so that no sort is needed and the process can simply stop reading after the 100th match.
Can you post the amount of rows (approximately) in the table? And can you as an experiment remove the TOP clause from the query, then request an estimated execution plan (DO NOT ACTUALLY RUN THE MODIFIED QUERY!!) and tell us what the estimated number of rows is?
January 5, 2016 at 11:15 am
Hugo
Per your test recommendation I reworked the query as you described below. However, I did need to change the query to a direct update instead of an update via a CTE due to the "UNION ALL" causing SQL to consider the query a view.
The modified query is below. I've also replaced the params with direct values for simplicity sake:
UPDATE T
SET ProcessStatus = 223,
ProcessID = 999999,
ProcessDate = GETDATE()
FROM dbo.SMSOutgoing t
WHERE t.SOID IN
(SELECT TOP 100 SOID
FROM
(SELECT SOID
FROM SMSOutgoing sso WITH (ROWLOCK, READPAST, UPDLOCK)
WHERE ProcessStatus = 0
UNION ALL
SELECT SOID
FROM SMSOutgoing sso WITH (ROWLOCK, READPAST, UPDLOCK)
WHERE ProcessStatus <> 0
AND ProcessStatus < 299
AND ProcessDate < DATEADD(mm,-15,GETDATE())) AS d
ORDER BY SOID)
Note: I have on Clustered index on the SMSOutgoing table on "SOID" and 1 non clustered index on the SMSOutgoing table on ProcessStatus, ProcessDate and covers SOID.
Currently in my testing we are dealing with 100k records in the table. Once in production it will probably be around 200k records due to purging of older records.
The attached HugoExecutoinPlan.zip file contains 2 execution plans
- HugoActualExecutionPlan.xml contains the "Actual" execution plan of the update query.
- HugoExtimatedExecutionPlan.xml contains the "Estimated" execution plan of the update query with the "Top 100" removed.
Thanks for the assistance
Lee
January 5, 2016 at 2:03 pm
Hi Lee,
Based on the plans you posted and the query text (thanks for catching my mistake with the update / union clash!), I can see the following:
Based on the available statistics, SQL Server thinks that there are 100,000 rows in the table (which matches your count). It also thinks that 97,900 of them have ProcessStatus = 0 - whether or not this estimate is accurate is impossible for me to say. And it finally thinks that just one row matches the combined predicate of ProcessStatus < 299 but not zero and ProcessDate in the requested range - only one off rerality because the actual plan with TOP shows that there are no rows at all that match this.
If you manage to force a plan that seeks the index twice, you will not be happy with the performance. It will have a seek for the "1 expected row" which is very fast indeed. But it will also have a seek for the 97,900 rows with status 0 - so that seek processes almost the entire table! Both seek results will then have to be sorted on SOID in order to find the TOP(100) rows (because the index used returns data in order of ProcessStatus and perhaps more columns, but not in SOID order). Especially that sort of 97,900 rows will be very costly.
After sorting the rows and reducing the results to the first 100, the plan will then still have to go back to the clustered index to get some additional data that is needed in the quuery but not included in the index, so the already high cost will be increased by 100 lookups.
Compare that to your original plan. Sure, it has a scan which has a bad name - but that repuration is not correct. Based on the estimates, I can see that SQL Server thinks that almost all rows in the table have ProcessStatus zero. So statistically, it will probably have to read less than 120 rows from the table before it has found 100 matches. And because execution plans execute from left to right (that is, each operator calls the operator to the right to request a row), the Top operator will actually cut the execution short at that time - after receiving row #100 it will not call the scan operator again, so the index scan will stop after processing slightly over 100 rows. (The Top operator in the plan created for my rewrite and in the theoretical double-seek plan will do the same, but they will not have the same effect because by then the Sort operator has already inputted all rows - it has to because the last row received might be the first it has to produce).
If you look at the SET STASTISTICS IO results of the queries, you will probably see that your original query reads just a handful of rows; that my rewrite attempt bumps this to a few thousand, and that if you actually manage to force the double-seek plan you will add another 300 reads, plus a big sort - which will introduce a large memory grant for the query, and it might even spill to tempdb if you are unlucky.
My rewrite did half of what you asked (it introduced a single seek but left a scan for the rest). You can add hints to force the second seek as well (I don't think you'll ever get SQL Server to produce the double-seek plan without hints, because it's ridiculously expensive).
However, I think your actual goal was not to get seeks or to eliminate the scan, but to get the fastest possible execution plan. And for that, you will have to return to your original query.
I hope this helps!
January 6, 2016 at 8:33 am
Hugo
Thanks for your time and the detail explanation.
Lee
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply