SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


All queries running slow after moving the db from staging server to production Server in SQL Server...


All queries running slow after moving the db from staging server to production Server in SQL Server 2008 R2

Author
Message
Kumar-386306
Kumar-386306
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 246
We have the db in staging server which runs in SQL 2008 R2 RTM version holding 32 GB of RAM. We have build the new SQL SERver 2008R2 SP2 server for production in cluster which has 38 GB RAM allocated.

We have backed up the db from the staging server and restore in production server. The DB size is near 10GB only. After we restore we can see all the queries take mostly 2 or 3 times more than it runs in the staging server. The production server is not moved to Live yet.

we have reindexed all the tables, dbcc checkdb and updated the stats on all the tables but still no improvement. When we run the proc in production db, the SSMS some time freezes not able to take the execution plan as well.

If I run proc including execution plan, it's freezing the SSMS but I'm sure the execution plan is same as for the same proc when it runs in the staging db server.

I have check the Server setting, seems both of them are same in staging & prod. only difference I could see in we have pushed SP2 in prod server.

let me know your suggestions to look into issue.
Abu Dina
Abu Dina
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1239 Visits: 3323
Have you checked wait stats?

I got into the habit of using the below query for initial investigation into performance related prolems:
This is from Paul Randals blog: http://www.sqlskills.com/blogs/paul/

WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP',
N'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH',
N'SLEEP_TASK', N'SLEEP_SYSTEMTASK',
N'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
N'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN',
N'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT',
N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE',
N'TRACEWRITE', N'XE_DISPATCHER_WAIT',
N'BROKER_TO_FLUSH', N'BROKER_EVENTHANDLER',
N'FT_IFTSHC_MUTEX', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP')
)
SELECT
[W1].[wait_type] AS [WaitType],
CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],
CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S],
CAST ([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],
[W1].[WaitCount] AS [WaitCount],
CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
[W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
GO



---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Kumar-386306
Kumar-386306
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 246
I haven't done yet. I'll check it out and update the output to start the investigation.
Kumar-386306
Kumar-386306
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 246
This is what I get as on wait stats output

WaitType Wait_S Resource_S Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S
OLEDB 103923.24 103923.24 0 253419 70.73 0.4101 0.4101 0
CXPACKET 23462.71 22882.71 580 3721674 15.97 0.0063 0.0061 0.0002
LATCH_EX 7059.45 6811.85 247.6 5282981 4.8 0.0013 0.0013 0
PAGEIOLATCH_SH 4573.95 4555.5 18.45 221309 3.11 0.0207 0.0206 0.0001
WRITELOG 1256.89 1212.06 44.83 880483 0.86 0.0014 0.0014 0.0001
emailsv
emailsv
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 12
Where do you keep the database. is it on a SAN or the machine.
It is advised to check on hardware side issues as well.
We recently performed a migration similar to this and observed our HP SAN settings were incorrect due to which the loading process was taking time. We corrected this issue and everything was OK

Venkat
http://sqlblogging.blogspot.in
Kumar-386306
Kumar-386306
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 246
Exactly said. checking SAN is in progress now and expecting our Storage guys to see any abnormal settings done.

BTW. Could you please let me know what SAN Setting was wrong on the machine which you faced issue? so that I could also look into it.

For sure I know, I'm using Iscsi initiator to connect to the target, but I think the target LUN are created in 3rd party storage software where as the LUN are generated as .img files.
BOR15K
BOR15K
Old Hand
Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)

Group: General Forum Members
Points: 386 Visits: 451
Kumar-386306 (6/15/2013)
Exactly said. checking SAN is in progress now and expecting our Storage guys to see any abnormal settings done.

BTW. Could you please let me know what SAN Setting was wrong on the machine which you faced issue? so that I could also look into it.

For sure I know, I'm using Iscsi initiator to connect to the target, but I think the target LUN are created in 3rd party storage software where as the LUN are generated as .img files.



I would like to know as well, please - we are going to move our DB to a server with SAN and local disk and I would also like to ask
where would you suggest to keep data file and where - the log one (e.g. should we place data file on SAN and log file on a local disk?)


Massive thanks in advance!
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