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

All queries running slow after moving the db from staging server to production Server in SQL Server 2008 R2 Expand / Collapse
Author
Message
Posted Thursday, June 13, 2013 12:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 26, 2013 3:12 AM
Points: 233, 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.

Post #1462912
Posted Thursday, June 13, 2013 1:59 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:54 AM
Points: 708, Visits: 3,290
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
Post #1462927
Posted Thursday, June 13, 2013 2:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 26, 2013 3:12 AM
Points: 233, Visits: 246
I haven't done yet. I'll check it out and update the output to start the investigation.
Post #1462937
Posted Friday, June 14, 2013 9:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 26, 2013 3:12 AM
Points: 233, 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
Post #1463809
Posted Friday, June 14, 2013 11:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 16, 2013 4:35 AM
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
Post #1463817
Posted Saturday, June 15, 2013 3:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 26, 2013 3:12 AM
Points: 233, 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.
Post #1463836
Posted Wednesday, June 26, 2013 1:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 120, Visits: 296
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!
Post #1467831
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse