March 18, 2013 at 12:52 pm
So, I ran into a nifty little "bug" I'll call it. From what I can find this was an issue in SQL Server 2005 but supposedly addressed in SQL Server 2008. I'm running Microsoft SQL Server 2008 R2 (SP2) - 10.50.4260.0 (X64). Here is my scenario:
View is defined as follows:
CREATE VIEW dbo.vw_BatchItems
SELECT
BatchID,
FieldA,
FieldB,
CAST(ISNULL(ROW_NUMBER() OVER (PARTITION BY BatchID ORDER BY DateEntered), 0) AS INT) AS CheckOrder
FROM <myDatabase>.<mySchema>.<myTable>;
Now when I code a procedure to query the view and add a WHERE clause it looks like this:
CREATE VIEW dbo.usp_sel_BatchItems (@BatchID INT)
AS
SELECT *
FROM <myDatabase>.<mySchema>.vw_BatchItems
WHERE BatchID = @BatchID;
We found that this procedure now is in our top 10 (well, the top 1 of the top 10) I/O consuming procedures (the underlying tables are large). While the query still runs fast we found that the ROW_NUMBER is causing an index scan on the index over BatchID. When we remove the ranking function from the view the SELECT statement now does an index seek on the index over BatchID. And after reviewing both execution plans it clearly shows that the predicate is not pushed to the view when the ROW_NUMBER() function is in the view. Without it the predicate is pushed to the view.
Any thoughts on this one? Is there a way to force SQL server to perform the WHERE condition first instead of scanning the entire index to resolve the ROW_NUMBER() function? In our case, we only need the function to return results based on the filtered criteria anyway.
March 18, 2013 at 1:43 pm
What happens if you take this out of the call to ROW_NUMBER(): PARTITION BY BatchID. Seem unnecessary since you are calling it with a specific BatchID, unless you also need the the view for other things with all the BatchD's present.
You are probably getting the scan because of the PARTITION BY clause.
March 18, 2013 at 3:42 pm
What about taking the row_number out of vw_BatchItems completely and putting row_number() over (order by DateEntered) in the usp_sel_BatchItems view?
March 19, 2013 at 8:14 am
Erin Ramsay (3/18/2013)
What about taking the row_number out of vw_BatchItems completely and putting row_number() over (order by DateEntered) in the usp_sel_BatchItems view?
SQL Server 2008R2 build 4266
USE AdventureWorks
go
CREATE VIEW test1 as
SELECT
ProductID,
CarrierTrackingNumber,
UnitPrice,
CAST(ISNULL(ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ModifiedDate), 0) AS INT) AS rownum
FROM Sales.SalesOrderDetail
2 records, INDEX seek, KEY lookup AS expected
SELECT *
FROM test1
WHERE ProductID = 897 --707
3083 records, table scan AS expected
SELECT *
FROM test1
WHERE ProductID = 707
Why are you doing the CAST ISNULL thing? Seems unnecessary to me, and this view avoids that "unnecessary" ComputeScalar in the plan:
CREATE VIEW test2 as
SELECT
ProductID,
CarrierTrackingNumber,
UnitPrice,
ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ModifiedDate) AS rownum
FROM Sales.SalesOrderDetail
Given the above, it is possible your build of SQL Server has a bug/regression in it (in which case you could try taking out the CAST and see if that makes a difference and also eliminate the view and see if you get a seek). MUCH more likely IMHO is that the number of rows you get out of the query simply make the optimizer pick a scan as the most efficient access mechanism. Try forcing the index seek and see if it is more efficient. I note this could also be a simple case of parameter sniffing, where the first batchid called has a high estimated rows and gets the scan? Maybe not given your comment about with and without the ROW_NUMBER. Again please try without the CAST. Also, if you put the ROW_NUMBER in the sproc, what effect do you get? Also try OPTION(RECOMPILE on the statement in the sproc to see effect...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 19, 2013 at 8:54 am
Lynn Pettis (3/18/2013)
What happens if you take this out of the call to ROW_NUMBER(): PARTITION BY BatchID. Seem unnecessary since you are calling it with a specific BatchID, unless you also need the the view for other things with all the BatchD's present.You are probably getting the scan because of the PARTITION BY clause.
Lynn,
Unfortunately this isn't the case. Even with the PARTITION BY clause, SQL Server is still performing an index scan over the index and returning all the records in the table (as evidenced in the execution plan). I think this might be a bad usage of a ranking function (or possibly a bug in SQL Server for not pushing the predicate down into the view).
March 19, 2013 at 8:56 am
Erin Ramsay (3/18/2013)
What about taking the row_number out of vw_BatchItems completely and putting row_number() over (order by DateEntered) in the usp_sel_BatchItems view?
In this case that will not work as the code base uses this same view to provide an interface for the entity framework. The developer team uses this view in this context even though they don't call it directly. Otherwise that would work perfectly as we've even tested that. This is the primary reason why we are most likely going to just maintain a column to contain this information and scrap the usage of ROW_NUMBER() altogether in this case.
March 19, 2013 at 9:03 am
Why the developer used ISNULL / CAST - not sure. Didn't make sense to me but even without those the execution plan remains the same. I'm also leary of suggesting using the RECOMPILE option a solution as that just reinforces bad coding practices IMO - unless it is the only justifiable option. But with that said, I did try this already 🙂 and did not witness any changes to the execution plan.
And just to set the stage - I'm approaching this from an I/O perspective. We're moving to a new data center and one of the things we identified is we have a large volume of I/O. After researching our sql and reviewing various metrics we indeed to have some inefficient queries contributing to unnecessary I/O. So I'm simply looking at ways to cut this down.
Also, just to set the perspective, this table contains currently around 60,000 records and grows every day. The distribution of data based on the BatchID is between 1 and 180ish records (per BatchID). I would expect in all cases an index seek and not a scan through every single record in the table.
March 19, 2013 at 10:21 am
1) what is estimated and actual row counts from sproc calls?
2) can you post both actual query plans - the one that gets the seek and the one that gets the scan? This one has definitely piqued my curiosity.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 19, 2013 at 1:28 pm
Since the best plan will vary based on the selectivity of the specific @BatchID passed in, try it with the RECOMPILE option:
SELECT *
FROM <myDatabase>.<mySchema>.vw_BatchItems
WHERE BatchID = @BatchID
OPTION ( RECOMPILE );
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 19, 2013 at 1:29 pm
tafountain (3/19/2013)
@TheSQLGuru,Why the developer used ISNULL / CAST - not sure. Didn't make sense to me but even without those the execution plan remains the same. I'm also leary of suggesting using the RECOMPILE option a solution as that just reinforces bad coding practices IMO - unless it is the only justifiable option. But with that said, I did try this already 🙂 and did not witness any changes to the execution plan.
And just to set the stage - I'm approaching this from an I/O perspective. We're moving to a new data center and one of the things we identified is we have a large volume of I/O. After researching our sql and reviewing various metrics we indeed to have some inefficient queries contributing to unnecessary I/O. So I'm simply looking at ways to cut this down.
Also, just to set the perspective, this table contains currently around 60,000 records and grows every day. The distribution of data based on the BatchID is between 1 and 180ish records (per BatchID). I would expect in all cases an index seek and not a scan through every single record in the table.
It's not outside the realm of possibility that the query optimizer decided that it's more efficient to scan a 60,000 row table than to perform a seek. The tipping point for that sort of thing tends to be much lower than people may think.
Also, you didn't post the definition of the index used by the seek when the ROW_NUMBER() function is removed from the view definition. I noticed that the ORDER BY argument for the ROW_NUMBER() function includes the column DateEntered that does not appear anywhere else in the query. Does the index on BatchID cover the query when this column is referenced (i.e., is DateEntered part of the key or an included column in that index)? If not, I think it is very likely that the optimizer would choose to scan a 60,000-row table rather than perform an index seek followed by a key lookup to get the DateEntered value.
Jason Wolfkill
March 19, 2013 at 6:08 pm
try to run
SELECT
BatchID,
FieldA,
FieldB,
CAST(ISNULL(ROW_NUMBER() OVER (PARTITION BY BatchID ORDER BY DateEntered), 0) AS INT) AS CheckOrder
FROM <myDatabase>.<mySchema>.<myTable>;
WHERE BatchID = @BatchID
and check the execution plan.
It goes for Index Seek, and then for Key Lookup.
That's where is your problem.
Key Lookup is extremely expensive operation and optimizer will avoid such a plan if there is a possibility of any more or less significant number of rows to lookup for.
During my my testing on Tally table optimizer switched from Key Lookup to Index Scan somewhere around 150 rows filtered by the WHERE clause.
_____________
Code for TallyGenerator
March 20, 2013 at 10:03 am
Sergiy (3/19/2013)
try to run
SELECTBatchID,
FieldA,
FieldB,
CAST(ISNULL(ROW_NUMBER() OVER (PARTITION BY BatchID ORDER BY DateEntered), 0) AS INT) AS CheckOrder
FROM <myDatabase>.<mySchema>.<myTable>;
WHERE BatchID = @BatchID
and check the execution plan.
It goes for Index Seek, and then for Key Lookup.
That's where is your problem.
Key Lookup is extremely expensive operation and optimizer will avoid such a plan if there is a possibility of any more or less significant number of rows to lookup for.
During my my testing on Tally table optimizer switched from Key Lookup to Index Scan somewhere around 150 rows filtered by the WHERE clause.
Agreed - especially if your index doesn't include BatchID AND dateEntered. I'm frankly surprised it uses a BatchID index at all, since it need BOTH columns to determine the row_number value. I think you're seeing a clustered index scan (i.e. a table scan) and it's not trying to rely on ANY index you have.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 21, 2013 at 1:00 pm
@matt-2 & Sergiy - I'll have to repost a better example. I think the point was missed. The predicate applied by the WHERE clause is not pushed down to the view at runtime. This is evidenced by reviewing the execution plan and seeing that a "clustered index scan" is being performed on the underlying table *and* returning all the rows in the table. A few operations later the predicate is applied. My whole point is the ranking function should only execute over the returned data (i.e. the predicate being applied first), not all of the data in the table. This is further illustrated by 2 things - first I removed the ranking function from the view and second, putting the ranking function in the actual select statement over the view. Both examples show the appropriate index seek and only one record is returned.
I'll post better and more elaborate examples later.
March 21, 2013 at 1:27 pm
tafountain (3/21/2013)
@Matt & Sergiy - I'll have to repost a better example. I think the point was missed. The predicate applied by the WHERE clause is not pushed down to the view at runtime. This is evidenced by reviewing the execution plan and seeing that a "clustered index scan" is being performed on the underlying table *and* returning all the rows in the table. A few operations later the predicate is applied. My whole point is the ranking function should only execute over the returned data (i.e. the predicate being applied first), not all of the data in the table. This is further illustrated by 2 things - first I removed the ranking function from the view and second, putting the ranking function in the actual select statement over the view. Both examples show the appropriate index seek and only one record is returned.I'll post better and more elaborate examples later.
I'm not sure why you say "the ranking function should only execute over the returned data (i.e., the predicate being applied first), not all of the data in the table." The optimizer can rearrange queries into any logically equivalent form using a number of rules. Predicate pushdown is one way the optimizer can simplify a query, but the optimizer may still estimate the cost of a plan that doesn't push the predicate down to be less than one that does. As I mentioned above, the optimizer likely would estimate the cost of scanning a 60,000-row table to be very low - low enough, in fact, that it might decide that it would cost more to generate and evaluate additional execution plans than to just execute the query with the scan.
Could you post actual execution plans for both queries - with and without the ROW_NUMBER() function call?
Jason Wolfkill
March 21, 2013 at 1:36 pm
wolfkillj (3/21/2013)
Could you post actual execution plans for both queries - with and without the ROW_NUMBER() function call?
Wolf, I will do that with the other examples. I guess another piece of the puzzle is this, we resolved this by removing the ranking function and simply maintaining a column to support this concept. In doing so the cost of the query was lowered from just under 400 to less than .01 - quite a difference - and the I/O generated was greatly reduced. This particular view is called from a very active web interface by our users constantly throughout the day.
But I will post more in the next day or two to provide a better example.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply