Huge cost issue with Temp DB using 2008r2 sp2
-
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.
-
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;-) -
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?
-
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.
-
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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.
-
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff,
I hope so, this one is causing me a few sleepless nights!
-
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
-
Sure here we go
2 files
1. 2005 - fast.sqlplan - this the old 2005 server
2. 2008r2 - Slow.sqlplan - This is the new server, now with TempDB the auto create and update stats now on.
-
my 2 cents...
Estimated and actual rows are significantly different all over the place. I'd re-run an update statistics with full scan.
Estimated IO costs seem significantly higher for TempDB. Have you looked at if there's any contention there - sys.dm_os_waiting_tasks?
-
There should be no contention at all this database server has nothing I mean nothing running on it. The only database is the one that I am providing the details for and its also got no hits, only the ones that I generate on it.
However running:
SELECT dm_ws.wait_duration_ms,dm_ws.wait_type,
dm_es.status,
dm_t.TEXT,
dm_qp.query_plan,
dm_ws.session_ID,
dm_es.cpu_time,
dm_es.memory_usage,
dm_es.logical_reads,
dm_es.total_elapsed_time,
dm_es.program_name,
DB_NAME(dm_r.database_id) DatabaseName,
-- Optional columns
dm_ws.blocking_session_id,
dm_r.wait_resource,
dm_es.login_name,
dm_r.command,
dm_r.last_wait_type
FROM sys.dm_os_waiting_tasks dm_ws
INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id
INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id
CROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t
CROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qp
WHERE dm_es.is_user_process = 1
GO
renders nothing to view.
I have run the update full after the restore, but there is no harm doing that again... So this has been done using maintenance plans doing the following:
Checking DB integrity
Reorg to all tables and views
rebuild - sort results in tempDB also selected
update stats - full
After completion with no faults re-running the query and checking the execution plan, well no joy, its still the same unfortunately, no change from the last posted plan.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply