Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Huge cost issue with Temp DB using 2008r2 sp2


Huge cost issue with Temp DB using 2008r2 sp2

Author
Message
Brad Marsh
Brad Marsh
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
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.
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
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;-)
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6091 Visits: 6069
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
When a question, really isn't a question - Jeff Smith
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


Brad Marsh
Brad Marsh
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
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.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44990 Visits: 39875
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Brad Marsh
Brad Marsh
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
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.
Attachments
ExecutionPlan.zip (20 views, 66.00 KB)
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44990 Visits: 39875
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Brad Marsh
Brad Marsh
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 132
Thanks Jeff,

I hope so, this one is causing me a few sleepless nights!
Sean Pearce
Sean Pearce
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 3432
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
Brad Marsh
Brad Marsh
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search