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»»»

Huge cost issue with Temp DB using 2008r2 sp2 Expand / Collapse
Author
Message
Posted Thursday, November 8, 2012 7:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:02 AM
Points: 32, Visits: 132
Hi Guys,

I have an issue that is completely baffling I need a guru's guidance / help.

so a little bit of an over view first.
We currently have a 2005 sp3 server in place at the moment its serving a number of databases, its spec is around 24 gig of RAM, 2 2.6 quad core CPU's and its disk are in a raid 5 config.
this server is has no SQL optimization its almost a default install.


Now we have a new server that is being built it has SQL 2008 r2 Sp2,
2 Latest X series processes (they are far better than that in the Sql 2005) and 48 gig of RAM it has 3 disk sets 1 raid 1 for the OS, raid 10 for both the log and data drives... MUCH better overall

This new server is in its testing phases before its to go to production, but I am in a situation where I can put it into production as it's performance seems dismal in comparison to the SQL 2005 box. I need some understanding as to why.

I can tell you from I/O testing that the new 2008r2 server from a hardware point of view blitz's the old server.

But the odd thing is when hitting a website that is on the same web server, there is a constant 3 second addition when requesting the website.
So to confirm that it is 100%the db that is the cause I did a SQL trace on the new server, I found a query that has a significant duration, I then pulled that query and ran it on both 2005 and 2008

2005 does it a second faster than 2008, If I look at the execution plan I can see that:

On an insert to the temp table on 2005 it has a 0% cost to



But yet on my SQL2008 box its got a 93% cost.



So to me it looks like temp DB is caching this, I need to understand how I can get this same performance if not better from the darn TempDB... I am dying for any help / guidance on this one.
Post #1382797
Posted Friday, November 9, 2012 3:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:01 AM
Points: 2,840, Visits: 3,968
Have you tried to repeat same test in new server? see and paste the result here

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1382928
Posted Friday, November 9, 2012 3:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 5,216, Visits: 5,109
What post-migration steps have you carried out on the new server?

I am guessing you backed up and restored the 2005 database onto the 2008 server?

Did you run DBCC UPDATEUSAGE, rebuild and update all statistics?




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1382932
Posted Sunday, November 11, 2012 1:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:02 AM
Points: 32, Visits: 132
Sorry for the late response guys
Yes I have repeated the execution of the query more than 10+ times with the same % on the new database server.

in terms of the migration to the new server, yes it was a simple backup and restore, I have rebuilt the index several times and also rebuilt the full text index's not that they come into the picture here... Just not getting anywhere with this.

Post #1383463
Posted Sunday, November 11, 2012 3:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750
Estimated and even supposed actual cost suck for these types of estimates. The real problem is likely futher to the right in the execution plan. I can see that the first one has nested loops and that the second has a merge instead.

My recommendation would be to read the article at the second link in my signature line below and post the actual execution plans using the methods from that article.

It may actually turn out to be the insert into the temp table that cause the problem if you're running on a new SAN. It seems there's been a rash of "lemons" hit the streets from a couple of previously trusted vendors, lately. I don't want to mention the name because I don't want to get sued for saying their products have really taken a turn for the worse.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1383473
Posted Sunday, November 11, 2012 3:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:02 AM
Points: 32, Visits: 132
Thanks Jeff,

I have zipped both plans up.

1. Long running Plan.sqlplan - this is the one that is taking longer SQL2008R2 (problematic)

2. Quick plan.sqlplan - This is the 2005 box that is rather quick to execute.


  Post Attachments 
ExecutionPlan.zip (18 views, 66.68 KB)
Post #1383475
Posted Sunday, November 11, 2012 11:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750
I have to admit... I've never seen anything like this before. Hopefully, the resident expert at such things will show up for this one.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1383508
Posted Monday, November 12, 2012 12:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:02 AM
Points: 32, Visits: 132
Thanks Jeff,

I hope so, this one is causing me a few sleepless nights!
Post #1383531
Posted Monday, November 12, 2012 1:46 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: Today @ 2:48 AM
Points: 916, Visits: 2,880
I can see many warnings with your temp tables and column statistics. Is TempDb configured to auto create statistics?

SELECT	name,
is_auto_create_stats_on,
is_auto_update_stats_on,
is_auto_update_stats_async_on
FROM sys.databases
WHERE database_id = 2





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1383546
Posted Monday, November 12, 2012 1:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:02 AM
Points: 32, Visits: 132
Both are set to false or 0. My SQL 2005 has both set to true. Though these settings were also tried and tested before changing them (changing them was a recommendation), It still provided the same outcome with the cost on SLQ2008r2


Post #1383548
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse