June 6, 2012 at 11:52 am
Hi,
We moved the database to new server and after move , we the server is slow.
So i create a same environment in a PC and and test PC is 2 time faster than the server.
Following are the details of the OS and SQL Server
OS: Windows 2003 R2 Sp2
SQL : Microsoft SQL Server 2000 - 8.00.2249 (Intel X86)
Following are the details of hardware
Server : Intel Xeon CPU 5650, Ram : 16 Gb
Test PC : Intel Core 15 CPU, Ram : 2 Gb.
I check with the execution plan and it is almost ok. But not 100 % same.
Please advice, how i can improve the performance of the server.
Thanking you
Regards
Mathew
June 6, 2012 at 1:28 pm
Have you checked for blocking? The issue could be due to concurrency.
Is there anything else running on the server? You could be hitting memory or CPU constraints based on other usage.
June 6, 2012 at 1:33 pm
did you rebuild the indexes or update statistics on the new server yet?
this would be especially true if the database you restored came from a different version of SQL...the engine uses statistics differently, so exisitng "old style" statistics cause a performance problem until they get rebuilt.
DECLARE @Exec VARCHAR(MAX)
SELECT @Exec = ''
SELECT @Exec = 'UPDATE STATISTICS dbo.[' + CONVERT(VARCHAR(200),name) + '] WITH FULLSCAN ; ' + CHAR(13) + CHAR(10) + @Exec FROM sys.tables ORDER BY name DESC
PRINT LEN(@Exec)
PRINT @Exec
EXEC(@Exec)
Lowell
June 7, 2012 at 1:08 am
Lowell (6/6/2012)
Yes.. I did re-index and update statistics with fullscan to all the tables.
Pls note: i did not perform re-index in the test Pc. But still that pc is faster.
Mathew
June 7, 2012 at 1:56 am
cfradenburg (6/6/2012)
Have you checked for blocking? The issue could be due to concurrency.Is there anything else running on the server? You could be hitting memory or CPU constraints based on other usage.
Following are the outpat of DBCC SQLPERF(WAITSTATS) . This is the data after DBCC SQLPERF(WAITSTATS,clear)
MISCELLANEOUS3.00.00.0
LCK_M_SCH_S0.00.00.0
LCK_M_SCH_M0.00.00.0
LCK_M_S0.00.00.0
LCK_M_U0.00.00.0
LCK_M_X0.00.00.0
LCK_M_IS0.00.00.0
LCK_M_IU0.00.00.0
LCK_M_IX0.00.00.0
LCK_M_SIU0.00.00.0
LCK_M_SIX0.00.00.0
LCK_M_UIX0.00.00.0
LCK_M_BU0.00.00.0
LCK_M_RS_S0.00.00.0
LCK_M_RS_U0.00.00.0
LCK_M_RIn_NL0.00.00.0
LCK_M_RIn_S0.00.00.0
LCK_M_RIn_U0.00.00.0
LCK_M_RIn_X0.00.00.0
LCK_M_RX_S0.00.00.0
LCK_M_RX_U0.00.00.0
LCK_M_RX_X0.00.00.0
SLEEP2146.02199686.02164101.0
IO_COMPLETION5787.0858.00.0
ASYNC_IO_COMPLETION0.00.00.0
RESOURCE_SEMAPHORE0.00.00.0
DTC0.00.00.0
OLEDB286.04.2125696E+90.0
FAILPOINT0.00.00.0
RESOURCE_QUEUE10461.02991921.0735047.0
ASYNC_DISKPOOL_LOCK0.00.00.0
UMS_THREAD0.00.00.0
PIPELINE_INDEX_STAT0.00.00.0
PIPELINE_LOG0.00.00.0
PIPELINE_VLM0.00.00.0
WRITELOG4952.01032.047.0
LOGBUFFER4.00.00.0
PSS_CHILD0.00.00.0
EXCHANGE173.095.00.0
XCB0.00.00.0
DBTABLE0.00.00.0
EC0.00.00.0
TEMPOBJ0.00.00.0
XACTLOCKINFO0.00.00.0
LOGMGR0.00.00.0
CMEMTHREAD230.00.00.0
CXPACKET54926.04654360.01650.0
PAGESUPP5119.0735.015.0
SHUTDOWN0.00.00.0
WAITFOR0.00.00.0
CURSOR0.00.00.0
EXECSYNC9.00.00.0
LATCH_NL0.00.00.0
LATCH_KP0.00.00.0
LATCH_SH0.00.00.0
LATCH_UP9.00.00.0
LATCH_EX176264.0230072.02698.0
LATCH_DT0.00.00.0
PAGELATCH_NL0.00.00.0
PAGELATCH_KP0.00.00.0
PAGELATCH_SH957.00.00.0
PAGELATCH_UP1407.00.00.0
PAGELATCH_EX1002.0125.00.0
PAGELATCH_DT0.00.00.0
PAGEIOLATCH_NL0.00.00.0
PAGEIOLATCH_KP0.00.00.0
PAGEIOLATCH_SH2740.014664.031.0
PAGEIOLATCH_UP2.00.00.0
PAGEIOLATCH_EX1052.06974.00.0
PAGEIOLATCH_DT0.00.00.0
TRAN_MARK_NL0.00.00.0
TRAN_MARK_KP0.00.00.0
TRAN_MARK_SH0.00.00.0
TRAN_MARK_UP0.00.00.0
TRAN_MARK_EX0.00.00.0
TRAN_MARK_DT0.00.00.0
NETWORKIO26623.058541.00.0
Total294152.04.2227284E+92903589.0
June 7, 2012 at 5:58 am
You should check out Glenn Berry's Diagnostic Queries. The following is pulled from that and provides an easier view of the wait stats:
-- Clear Wait Stats
-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',
'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT',
'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'ONDEMAND_TASK_QUEUE', 'BROKER_EVENTHANDLER', 'SLEEP_BPOOL_FLUSH'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage threshold
It gives you a percent, the time, and excludes several waits that are known to not be an issue. Looking at what you posted it looks like CXPACKET waits are highest which is an indication that queries are going parallel and not getting benefit from it. You may want to modify the max degree of parallelism to see if that helps. If this is a product you got from a vendor reach out to them and see if they have a recommended setting.
June 7, 2012 at 6:05 am
cfradenburg (6/7/2012)
You should check out Glenn Berry's Diagnostic Queries. The following is pulled from that and provides an easier view of the wait stats:
-- Clear Wait Stats-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',
'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT',
'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'ONDEMAND_TASK_QUEUE', 'BROKER_EVENTHANDLER', 'SLEEP_BPOOL_FLUSH'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage threshold
It gives you a percent, the time, and excludes several waits that are known to not be an issue. Looking at what you posted it looks like CXPACKET waits are highest which is an indication that queries are going parallel and not getting benefit from it. You may want to modify the max degree of parallelism to see if that helps. If this is a product you got from a vendor reach out to them and see if they have a recommended setting.
This won't work here as the OP stated SQL Server 2000 in use, you'll need to look at sysprocesses
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 7, 2012 at 6:12 am
Forgot about that. The CTE and row_number won't work either. The list of wait types that he excludes should still vaguely apply but there were undoubtably changes between SQL 2000 and SQL 2008 for those so it won't apply exactly.
June 7, 2012 at 8:00 am
Hi All,
Thanks for your support.
my DB version : SQL : Microsoft SQL Server 2000 - 8.00.2249 (Intel X86)
Device manager processor shows 24 processers.
So can i change the Max Degree of Parallelism to 24.
Regards
Mathew
June 7, 2012 at 8:37 am
Mathew M.Varghese (6/7/2012)
Device manager processor shows 24 processers.So can i change the Max Degree of Parallelism to 24.
If parallelism is part of the problem you want to decrease the degree, not increase it. I'm assuming it's set to 4 right now which means using up to the available number of cores. If this is an OLTP system, which I'm assuming it is, you probably want to change it to somewhere between 2-8. You'll have to do monitoring to see how it's performing, if it helps, if parallelism is still an issue, etc. The exact number you settle on will depend on a number of factors and may be outside the range I gave to initially try. If parallelism never helps you would want it at 1 but I wouldn't start there.
June 7, 2012 at 8:46 am
Are you sure it's SQL Server that's the problem?
Have you run some simple benchmarks on the new server vs the PC?
June 7, 2012 at 8:52 am
Ian Scarlett (6/7/2012)
Are you sure it's SQL Server that's the problem?Have you run some simple benchmarks on the new server vs the PC?
Can you please explain, how to do the benchmark?
June 7, 2012 at 10:40 am
Mathew M.Varghese (6/7/2012)
Can you please explain, how to do the benchmark?
I'm not talking about anything overly scientific... even a simple copy of small and large files should be way quicker on the server than the PC. If they aren't, then maybe a deeper benchmark will be required
There are plenty of free benchmarking tools available. Google should find you some (SiSoft Sandra is one I've used before).
June 7, 2012 at 10:41 am
hi,
One more thing...
Test PC and server execution plan icons are almost same. But in the row count and No of execution have big differance.Some sql take 1 second in test pc, it took around 37 second in production server..
Following is the new findings
Execution plan output : test PC
Physical operaton : Index scan
logical operation : index scan
row count : 950053
est row size : 112
1/o cost : 5.13
cpu : 1.05
number exectuve : 1
cost : 6.17
sub tree : 6.17
est row coun : 950053
Execution plan output : server
Physical operaton : Index scan
logical operation : index scan
row count : 165,331,842
est row size : 112
1/o cost : 5.13
cpu : 1.05
number exectuve : 174
cost : 8.26
sub tree : 8.26
est row coun : 950182
Regards
Mathew
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply