Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Query Plans migrating from 2005 to 2008R2 Expand / Collapse
Author
Message
Posted Wednesday, September 4, 2013 6:39 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:19 AM
Points: 28, Visits: 319
Yes sir, the Current production server does it in about 1 min. The new one takes from 4 to 7 mins. I did use the hint which I need to do again and go back and do again and look at the plan because I cannot remember the details, but it still took much longer. I have also tried limiting the columns. I see disk latencies up to 2000 ms during this so it make us wary of migrating until we are sure. That is why we have double checked all the hardware. I just don't get why this server is choosing such a slow plan. I also tried using a plan guide but for some reason I couldn't get it to use the guide. I couldn't get much info about why but I did the profiler to see if it was used or not.

A bit of interesting info my reporting server which is a replicated copy of many of the tables can do a loop 600 times of this query using a random claimid seeded in 60 seconds. If your wondering how that's possible the server has a PCI SSD card that uses some software called Velobit for disk read caching. It uses the same plan as the Production Server and is 2008R2





I don't always test my SQL scripts, but when I do, I test in Production.
Post #1491598
Posted Wednesday, September 4, 2013 7:34 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:19 AM
Points: 28, Visits: 319
Plan using the hint still slow and very high latencies. So I guess I have achieved my objective to test the new hardware.


http://www.evernote.com/shard/s272/sh/7212dbd2-aeb3-423f-b5aa-0485027757e2/2d85a33fca98e3c0df2c41e4783f93d0


I don't always test my SQL scripts, but when I do, I test in Production.
Post #1491611
Posted Thursday, September 5, 2013 1:24 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:24 PM
Points: 805, Visits: 722
You still have a different plan than on SQL 2005.

The plans posted to evernote are difficult to read. The best is if you can attach them as .sqlplan files.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1491644
Posted Thursday, September 5, 2013 6:48 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:19 AM
Points: 28, Visits: 319
Here they are

I don't always test my SQL scripts, but when I do, I test in Production.

  Post Attachments 
Production.sqlplan (11 views, 109.22 KB)
NewServer_with_Hint.sqlplan (4 views, 103.01 KB)
NewServerPlan.sqlplan (9 views, 188.47 KB)
Post #1491758
Posted Thursday, September 5, 2013 3:52 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:24 PM
Points: 805, Visits: 722
Thanks for the plans!

I note that the table is partitioned. What is the partitioning column? ClaimID?

It appears that you have mixed up the plans a bit. The one you call NewServerPlan.sqlplan is in fact from you old server. (I can see the version number in the XML plan.)

There is something fishy with the plan from the new server. It first sorts the data from the Index Scan on ClaimId to make the Key Lookup faster, and then it performs a TOP N Sort on the fat result set - it should be able to run the TOP before the Key Lookup.

From a more practical perspective: is this a critical query for you, or just something you found vile enough to test the hardware of the new server?


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1491979
Posted Thursday, September 5, 2013 8:21 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:07 PM
Points: 9,926, Visits: 11,183
KTD (9/4/2013)
However the new server creates massive IO and doesn't make use of some indexes that it should be using.

The first thing to say is that your index on RecordDate does not, in fact, support ordering by RecordDate. This might seem counter-intuitive, but the index is partitioned by ClaimID (just like the base table is).

The nonclustered index records are in RecordDate key order, but only within each partition. Effectively, the nonclustered index key is (PartitionID, RecordDate) because of the partitioning.

This explains why you see an expensive sort on 194,656,980 rows even on SQL Server 2005. The partitioned nonclustered index cannot return rows in RecordDate order when more than one partition is involved, so SQL Server has to perform a full sort.

The situation is logically the same in SQL Server 2005 and SQL Server 2008 R2, but the physical implementation is different. SQL Server 2005 used an APPLY implementation model; it processes one partition at a time in the query plan. The Constant Scan you see in the SQL Server 2005 plan is a list of partition IDs. The Index Scan operation on the inner side of the Nested Loops Join following the Constant Scan operates on a single partition at a time, with the current partition ID as the correlated join parameter.

One problem with the 2005 implementation was that it did not always distribute work across parallel threads very well. You can see an example of this in the plan you submitted using the @Random value. For that particular test run, all the qualifying rows happened to be in a single partition. If you look at the per-thread actual rows, you will see that one thread did all the work. The plan is effectively a serial plan, just with some extra overhead for the additional unused threads and the parallelism operators!

The physical implementation for partitioned tables completely changed in SQL Server 2008. The APPLY model (Constant Scan + Nested Loops Join, per-partition processing) is no longer used. Instead, the effective index keys (PartitionID, RecordDate) are processed directly, exactly as if that were the real index definiton. You can find more details about the implementation and "enhancements" for SQL Server 2008 in the following TechNet article (please do read this):

Query Processing Enhancements on Partitioned Tables and Indexes

Unfortunately, dramatic changes to query processing always mean significant query plan changes. SQL Server 2008 (and all later versions) can no longer use the same query plans for partitioned tables that SQL Server 2005 did, because the internal implementation is so radically different.

You will see all-new query plans for partitioned table queries when upgrading from SQL Server 2005. Many of these changes will result in improved performance, but some will not, and some will even be much worse. This means that real, detailed testing is needed before upgrading from 2005. As a result of that, you will end up rewriting some partitioned queries to produce good query plans.

The particular case here is one example of a performance regression. Without an index hint, SQL Server seeks to the partitions and ClaimIDs using the clustered index and then sorts by RecordDate. The clustered index is much, much, wider than the nonclustered index, so you see a huge amount of I/O for this compared with the 2005 plan.

When the nonclustered index is forced, an unfortunate side-effect results in two sorts being needed. As Erland mentioned, a general-purpose index optimization pre-sorts rows on ClaimID keys for the lookup into the clustered index. The set is then re-sorted on RecordDate later on.

This is a performance regression, and undoubtedly unfortunate, but the Top operation cannot be performed earlier because the rows are not in RecordDate order before the explicit sort (they are in PartitionID, RecordDate order). The SQL Server 2005 plan just decided to sort on RecordDate alone, and take the expected random-I/O hit when performing lookups into the base table.

One way to rewrite the test query for better performance is:

WITH QualifyingRows AS
(
SELECT TOP (10000)
C.ClaimID,
C.RecordDate
FROM dbo.Claims AS C
WHERE
C.ClaimID > 100781325
ORDER BY
C.RecordDate ASC
)
SELECT C.*
FROM dbo.Claims AS C
WHERE
EXISTS
(
SELECT 1
FROM QualifyingRows
WHERE
QualifyingRows.ClaimID = C.ClaimID
AND QualifyingRows.RecordDate = C.RecordDate
)
ORDER BY
C.RecordDate;

The query plan produced uses a single Top-N Sort on RecordDate, while still making effective use a parallelism:



You are very likely to encounter similar query plan issues with some types of aggregate query:

http://sqlmag.com/t-sql/max-and-min-aggregates-against-partitioned-tables




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi


  Post Attachments 
ssc.png (148 views, 35.89 KB)
Post #1492028
Posted Friday, September 6, 2013 1:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:24 PM
Points: 805, Visits: 722
Paul White (9/5/2013)

When the nonclustered index is forced, an unfortunate side-effect results in two sorts being needed. As Erland mentioned, a general-purpose index optimization pre-sorts rows on ClaimID keys for the lookup into the clustered index. The set is then re-sorted on RecordDate later on.


I am stunned... Thanks Paul for stepping in and providing a great answer. I should have realised that it could not use the index directly because of the partitioning, but a question: shouldn't the optimizer be able to come with the same plan as for your rewritten query? That is, it could first to a TOP N sort on the small set, and then sort for the key lookup?

I noticed that in the SQL 2005 plan it does something like this, but the first sort produces some 15000 rows and not 10000 rows, which I guess because there are rows with the same RecordDate.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1492061
Posted Friday, September 6, 2013 3:22 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:07 PM
Points: 9,926, Visits: 11,183
Erland Sommarskog (9/6/2013)
a question: shouldn't the optimizer be able to come with the same plan as for your rewritten query? That is, it could first to a TOP N sort on the small set, and then sort for the key lookup?

Yes, it could, and I would argue it should. Conor would say the optimizer can't consider every possible transformation, there haven't been enough customer requests for it, etc. etc.

More to the point, MIN and MAX are hard work on 2008+ partitioned indexes too, and that is an even more obvious optimization that is still missing.

By the by, the idea of manual rewrites to select keys first and then perform a small number of expensive lookups is a good general technique. I used the same basic idea for my SQL Server Central paging article:

http://www.sqlservercentral.com/articles/paging/69892/

I noticed that in the SQL 2005 plan it does something like this, but the first sort produces some 15000 rows and not 10000 rows, which I guess because there are rows with the same RecordDate.

The extra rows are due to nested loops prefetching, something I blogged about just a few days ago:

bit.ly/Prefetch




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1492105
Posted Friday, September 6, 2013 6:19 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:19 AM
Points: 28, Visits: 319
Wow I am so impressed. This is of great help for me in knowing what is going on. I really need to read this forum more I have learned a great deal just searching for information on this issue. I thank you both so much for taking the time to help.



I don't always test my SQL scripts, but when I do, I test in Production.
Post #1492187
Posted Friday, September 6, 2013 6:39 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:19 AM
Points: 28, Visits: 319
Your version of the query ran in 30 seconds without any high disk latencies observed.

I don't always test my SQL scripts, but when I do, I test in Production.
Post #1492195
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse