June 13, 2013 at 12:32 am
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.
June 13, 2013 at 1:59 am
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[/url]
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
June 13, 2013 at 2:35 am
I haven't done yet. I'll check it out and update the output to start the investigation.
June 14, 2013 at 9:31 pm
This is what I get as on wait stats output
WaitTypeWait_SResource_SSignal_SWaitCountPercentageAvgWait_SAvgRes_SAvgSig_S
OLEDB103923.24103923.24025341970.730.41010.41010
CXPACKET23462.7122882.71580372167415.970.00630.00610.0002
LATCH_EX7059.456811.85247.652829814.80.00130.00130
PAGEIOLATCH_SH4573.954555.518.452213093.110.02070.02060.0001
WRITELOG1256.891212.0644.838804830.860.00140.00140.0001
June 14, 2013 at 11:31 pm
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
June 15, 2013 at 3:11 am
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.
June 26, 2013 at 1:14 pm
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!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply