Huge cost issue with Temp DB using 2008r2 sp2

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Huge cost issue with Temp DB using 2008r2 sp2...

31 posts, Page 1 of 4

1 2 3 4 »»»

Huge cost issue with Temp DB using 2008r2 sp2

Rate Topic

Display Mode

Topic Options

Author

Message

Brad Marsh

Posted Thursday, November 08, 2012 7:55 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 20, 2013 6:24 PM
Points: 32, Visits: 123

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

Bhuvnesh

Posted Friday, November 09, 2012 3:49 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

Have you tried to repeat same test in new server? see and paste the result here

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1382928

anthony.green

Posted Friday, November 09, 2012 3:56 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

Brad Marsh

Posted Sunday, November 11, 2012 1:41 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 20, 2013 6:24 PM
Points: 32, Visits: 123

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

Jeff Moden

Posted Sunday, November 11, 2012 3:33 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 32,906, Visits: 26,793

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1383473

Brad Marsh

Posted Sunday, November 11, 2012 3:51 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 20, 2013 6:24 PM
Points: 32, Visits: 123

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

Jeff Moden

Posted Sunday, November 11, 2012 11:33 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 32,906, Visits: 26,793

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

Post #1383508

Brad Marsh

Posted Monday, November 12, 2012 12:55 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 20, 2013 6:24 PM
Points: 32, Visits: 123

Thanks Jeff,

I hope so, this one is causing me a few sleepless nights!

Post #1383531

Sean Pearce

Posted Monday, November 12, 2012 1:46 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 350, Visits: 1,334

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

http://thesqlguy.blogspot.com/

Post #1383546

Brad Marsh

Posted Monday, November 12, 2012 1:52 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 20, 2013 6:24 PM
Points: 32, Visits: 123

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 »

31 posts, Page 1 of 4

1 2 3 4 »»»

Permissions