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 Monday, November 12, 2012 1:56 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 @ 6:35 AM
Points: 815, Visits: 2,643
I wouldn't recommend them to off on tempdb, at least not the Auto Create. Can you switch them on and try your query again. Please attach the new query plan.



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1383551
Posted Monday, November 12, 2012 2:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:52 PM
Points: 32, Visits: 129
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.


  Post Attachments 
ExecPlans.zip (5 views, 65.96 KB)
Post #1383557
Posted Monday, November 12, 2012 2:33 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: Thursday, July 17, 2014 4:02 AM
Points: 811, Visits: 2,414
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?
Post #1383562
Posted Monday, November 12, 2012 2:53 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 @ 6:35 AM
Points: 815, Visits: 2,643
Your slow query has a clustered index scan on UploadedImage sending 15,152 rows to a merge join. The fast query does a seek, sending 351 rows to a nested loop join.

Do you have the same indexes on this table? Are stats up to date?




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1383570
Posted Monday, November 12, 2012 2:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:52 PM
Points: 32, Visits: 129
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.






Post #1383571
Posted Monday, November 12, 2012 3:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:52 PM
Points: 32, Visits: 129

Artoo22 to answer you, the database have the same indexes. Too prove this to myself today (at a loss with this one), I again backed the database up only 3 hours ago re-restored the database, and rebuilt all index's again.

I see what your saying, But I just don't have a logical explanation to it. They are 100% like for like in terms of data, schema and structure.
Post #1383573
Posted Monday, November 12, 2012 3:19 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 @ 6:35 AM
Points: 815, Visits: 2,643
Can you run this on both servers

SELECT		OBJECT_NAME(s.object_id) AS TableName,
s.name AS StatsName,
s.auto_created,
s.user_created,
STATS_DATE(s.object_id, s.stats_id) AS StatsDate,
c.name AS ColumnName
FROM sys.stats s
INNER JOIN sys.stats_columns sc
ON s.object_id = sc.object_id
INNER JOIN sys.columns c
ON s.object_id = c.object_id
AND sc.column_id = c.column_id
ORDER BY s.object_id,
s.name,
c.column_id





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1383580
Posted Monday, November 12, 2012 3:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:52 PM
Points: 32, Visits: 129
OK run on each database

1. for the SQL 2005
2. for SQL 2008 r2


  Post Attachments 
DB_Stats.zip (3 views, 431.00 KB)
Post #1383598
Posted Monday, November 12, 2012 5:36 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 @ 6:35 AM
Points: 815, Visits: 2,643
Where are your TempDb files located?



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1383640
Posted Monday, November 12, 2012 1:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:52 PM
Points: 32, Visits: 129
I now have 8 of them spread over the log and data drives at the moment.
They were all on the raid 1 OS before, with this configuration I also saw the same sort of issues hence my desperation in spreading them around.
Post #1383855
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse