SQL Clone
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
Sean Pearce
Sean Pearce
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

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

Group: General Forum Members
Points: 34 Visits: 132
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.
Attachments
ExecPlans.zip (7 views, 65.00 KB)
adb2303
adb2303
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1294 Visits: 3150
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?
Sean Pearce
Sean Pearce
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

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

Group: General Forum Members
Points: 34 Visits: 132
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.
Brad Marsh
Brad Marsh
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 132
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.
Sean Pearce
Sean Pearce
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

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

Group: General Forum Members
Points: 34 Visits: 132
OK run on each database

1. for the SQL 2005
2. for SQL 2008 r2
Attachments
DB_Stats.zip (9 views, 430.00 KB)
Sean Pearce
Sean Pearce
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1802 Visits: 3432
Where are your TempDb files located?



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Brad Marsh
Brad Marsh
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 132
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.
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