March 14, 2012 at 9:36 am
Hello,
I have a simple two table join query that runs much slower in a new SQL 2008 R2 than in SQL 2005 SP4.I'd love to find out why and how to tune it.
SQL 2008 server is on a much more powerful hardware w/ terabyte of memory.
The databases, two tables, indexes are identical in both environments.
2005 tables have a few more rows (101,195 as versus to 101,184)
The identical query is issued from the same client PC against both 2005 and 2008 servers with warm cache.
select * from tblvendor v inner join tblreferral r on v.intvendorid = r.intvendorid
Execution plans as well as Text plans show proper indexex were used.
Statistics show comparable logical/physical/lob reads.
The big difference is Scan Count and CPU and elapsed time.
It takes 2 to 3 times longer when running against SQL 2008.
SQL2008 stats
============
|--Parallelism(Gather Streams)
|--Hash Match(Inner Join, HASH:([v].[intVendorID])=([r].[intVendorID]))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([v].[intVendorID]))
| |--Clustered Index Scan(OBJECT:([1032Automation].[dbo].[tblVendor].[PK_tblVendor] AS [v]))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([r].[intVendorID]))
|--Clustered Index Scan(OBJECT:([1032Automation].[dbo].[tblReferral].[PK_tblCase] AS [r]))
Table 'tblVendor'. Scan count 65, logical reads 5407, physical reads 0, read-ahead reads 0, lob logical reads 259084, lob physical reads 0, lob read-ahead reads 2.
Table 'tblReferral'. Scan count 65, logical reads 2803, physical reads 0, read-ahead reads 0, lob logical reads 414, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 19051 ms, elapsed time = 24499 ms.
SQL2005 stats
============
|--Parallelism(Gather Streams)
|--Hash Match(Inner Join, HASH:([v].[intVendorID])=([r].[intVendorID]))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([v].[intVendorID]))
| |--Clustered Index Scan(OBJECT:([1032Automation].[dbo].[tblVendor].[PK_tblVendor] AS [v]))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([r].[intVendorID]))
|--Clustered Index Scan(OBJECT:([1032Automation].[dbo].[tblReferral].[PK_tblCase] AS [r]))
Table 'tblVendor'. Scan count 17, logical reads 5407, physical reads 0, read-ahead reads 0, lob logical reads 259057, lob physical reads 0, lob read-ahead reads 2.
Table 'tblReferral'. Scan count 17, logical reads 2809, physical reads 0, read-ahead reads 0, lob logical reads 542, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 11251 ms, elapsed time = 7219 ms.
I've noticed, select * from tblVendor or select * from tblReferral yield comparable result.
When select * from both tables, the estimate row size is 1288bytes.
Does this have to do w/ buffer cache page size? 8K page?
TIA
March 14, 2012 at 9:59 am
Have you checked index fragmentation? Have you updated statistics?
How many CPUs/cores on the new box?
Can you see what happens if you set MAXDOP = 1 to the query? Perhaps the query doesn't need to be parallelized?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 14, 2012 at 3:17 pm
Hi,
The 2008 server has 4 Xeon X7560@2.26GHz /16 cores.
Statistics were updated.
I did not check index fragmentation previously.
So I went ahead and rebuild the complete indexes.
Tested with both MAXDOP=1 and MAXDOP=0. This option does not seem to have a significant impact (MAXDOP=1 yielded a slight lower number of logical reads, no difference in CPU/Elapsed time)
Overall, after the index rebuilt, the total Elapsed time went down from 24s to 17s, CPU time went up slightly from 19s to 22s.
This is an improvement, but still roughly twice as slow as what I was getting from the SQL 2005 test (elapsed 8s).
Thx
March 14, 2012 at 3:21 pm
Please read the 2nd article I reference in my signature block below regarding ask for help with performance issues. Follow the instructions regarding what to post and how. With that information you will get better answers to your question.
March 14, 2012 at 3:29 pm
What about storage? What changed?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 14, 2012 at 3:55 pm
We are migrating our SQL 2005 cluster to the new 2008 cluster which is running on brand new hardware, HP ProLiant DL980 G7, database devices are on EMC Enterprise SANS system with Multiple Quad Gigabit adapters.
This new 2008 Server instance is a test instance, with 10GB RAM allocated.
Very light load at this point.
We've tested other applications without much issue.
This query is the only one so far that kind of jump out in terms of performance degradation.
March 15, 2012 at 1:15 am
Whats the size of the TempDB and is it in the SAN Disk?
Can you please try dropping and recreating the indexes?
March 15, 2012 at 2:00 pm
Hi, tempdb is at 500MB with over 100MB free consistently test after test, so it doesn't appear to me that tempdb was starving.
And I did rebuild the indexes completely, after which, the Elapsed time was lower to 17s from 24s.
However, 17s is still twice as long as I was getting from a SQL 2005 server on a much older hardware, which is ~ 7s.
March 15, 2012 at 2:05 pm
Did you migrated to 64bit environment from 32?
March 15, 2012 at 2:09 pm
THANG HOANG (3/15/2012)
Hi, tempdb is at 500MB with over 100MB free consistently test after test, so it doesn't appear to me that tempdb was starving.And I did rebuild the indexes completely, after which, the Elapsed time was lower to 17s from 24s.
However, 17s is still twice as long as I was getting from a SQL 2005 server on a much older hardware, which is ~ 7s.
Looking back through the posts I don't see anything like I suggested. Please read and follow the instructions given in the 2nd article I have referenced below in my signature block regarding asking for help with performance problems.
If you post the information it states you will probably get much better answers to your problem.
March 15, 2012 at 2:09 pm
Both or our SQL 2005 and 2008 Enterprise Edition are 64bit.
The one difference is SQL 2005 runs on Windows Server 2003,
and SQL 2008 runs on Windows Server 2008.
March 15, 2012 at 2:13 pm
THANG HOANG (3/15/2012)
Both or our SQL 2005 and 2008 Enterprise Edition are 64bit.The one difference is SQL 2005 runs on Windows Server 2003,
and SQL 2008 runs on Windows Server 2008.
Doesn't help fulfull the request. Please read my previous post.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply