August 22, 2011 at 10:04 am
I cannot copy the table structures..but below is the scenario
I have a query which runs fine when one of the table in the query is mapped to remote server database.
But same query if the same table is mapped to same database then it takes 2 hours 35 minutes...
Why is this difference could someone please help me...
August 22, 2011 at 10:06 am
can you at least post the actual execution plan?
When was the last time you updated the stats on those tables?
August 22, 2011 at 10:08 am
It doesn't matter because I created a new temp table with the same structure and load the same 456 rows and it takes the same time....
August 22, 2011 at 10:08 am
There is no way we can provide a sensible answer with that little information. Need the execution plan at a minimum.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2011 at 10:11 am
Oh ok, But am sorry i cannot even post the execution plan....
But i see execution plan is same
August 22, 2011 at 10:14 am
You're telling your mechaninc that you have a problem with you car, without any more details and without letting if see the car or run any diagnostics.
Good luck fixing your own problem, we can't help with what you've given us.
August 22, 2011 at 10:14 am
P.S. when something runs 10X slower than something else, the plan is NOT the same... or should not be the same.
August 22, 2011 at 10:15 am
Are both databases on identically specced servers?
Do both databases have identical storage systems?
Are both databases using the same edition of SQL Server?
Are both servers under a similar load....
August 22, 2011 at 10:17 am
Ninja's_RGR'us (8/22/2011)
P.S. when something runs 10X slower than something else, the plan is NOT the same... or should not be the same.
Unless there's major blocking/waits on the slow one.
But this is guessing, without some data it's going to remain a guess.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2011 at 10:18 am
Assuming the plan is absolutely identical, then it sounds like resource contention or blocking. No way to know anything though based on the information supplied. These are just guesses.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 22, 2011 at 10:18 am
GilaMonster (8/22/2011)
Ninja's_RGR'us (8/22/2011)
P.S. when something runs 10X slower than something else, the plan is NOT the same... or should not be the same.Unless there's major blocking/waits on the slow one.
But this is guessing, without some data it's going to remain a guess.
Agreed, but we'd still see that in the actual plans :-D.
I'm getting tired of writing full blog posts for every answers I answer simple questions here so I try to cut to the chase without leaving something crucial out.
August 22, 2011 at 10:20 am
I am sorry everyone... I cannot post more Information...
I know it is hard to reply with answer without proper information....but i cannot post more Information...
If it's related to blocking..Do you mean table access blocking?
If yes how can i find out if it's blocking...
August 22, 2011 at 10:21 am
Lucky9 (8/22/2011)
I am sorry everyone... I cannot post more Information...I know it is hard to reply with answer without proper information....but i cannot post more Information...
In which case we cannot help you. Sorry, but diagnosing performance problems with no access to anything is near-impossible
If it's related to blocking..Do you mean table access blocking?
If yes how can i find out if it's blocking...
Query sys.dm_exec_requests while the query is running
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2011 at 10:22 am
Ninja's_RGR'us (8/22/2011)
GilaMonster (8/22/2011)
Ninja's_RGR'us (8/22/2011)
P.S. when something runs 10X slower than something else, the plan is NOT the same... or should not be the same.Unless there's major blocking/waits on the slow one.
But this is guessing, without some data it's going to remain a guess.
Agreed, but we'd still see that in the actual plans :-D.
Errr... See waits and blocks from the execution plan (actual or estimated)????
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2011 at 10:23 am
So you got the next E = MC2 blatantly visible in your query (not the output, just the query)?
I've never seen a query so special that it couldn't not be shared but anywho.
Like Gail said for blocking + maybe sp_WhoIsActive from Adam Machanic
or this stolen from the activity monitor :
--Borrowed from the activity monitor
SELECT
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = ISNULL(db_name(p.dbid), N''),
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024,
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0)
-- [Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
--LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)--TAKE THIS dmv OUT TO WORK IN 2005
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id;
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply