October 19, 2011 at 9:20 am
I have faced with a problem,that I thought probably you have faced it before.I have two tables(one has 30,000,000 Rows,and the other one has 60,000,000),both tables have Identity Columns as Primary key.Now user (through an application) is trying to insert(insert is being done through Stored Procedure call) 2000-3000 rows to these two tables.But occasionally these inserts take 15-16 sec and sometimes only 2sec ,When I ran the sQL profiler,I noticed that each individual SQL statement (in SP)runs very fast,but randomly there is a delay for 10ms-800ms between SQL statement in SP.the position of this delay vary.the first thing that I checked was the Fragmentation of indexes in these two tables,which we did not have any fragmentation.then I check Auto file growth of data and log file and I noticed that database has fixed length.also,during the test period there was no traffic on Data.Also, there was no (SP) recompile process during the delay period.I checked the size of tempdb and there was no problem with that.
Thanks
October 19, 2011 at 9:25 am
1st guess would be blocking.
What does this return?
/*
Microsoft White Paper on waits http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
Next query by Paul Randal http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx
*/
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 (
'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', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'BROKER_RECEIVE_WAITFOR')
)
SELECT
W1.wait_type AS WaitType,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (W1.WaitS * 1000 / W1.WaitCount AS DECIMAL(14, 2)) AS AvgWait_MS,
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
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 < 97 -- percentage threshold
AND W1.WaitCount > 0;
GO
October 19, 2011 at 9:33 am
Thanks for the script,should I run this after we ran our batch(that try to insert 1000 -2000 rows) or I should run them at the same time.
October 19, 2011 at 9:35 am
This data is since last server recycle so it makes no difference at all.
I might post a script later about task waits and that one would be during. Which is hard when it runs in a few ms!
October 19, 2011 at 3:05 pm
This is our wait list after running the script:
WaitType Wait_S AvgWait_MSResource_SSignal_SWaitCountPercentage
ONDEMAND_TASK_QUEUE8339.42 44358.62 8339.41 0.02 188 82.39
LCK_M_X 1179.17 951.71 1178.99 0.18 1239 11.65
ASYNC_NETWORK_IO219.69 4.73 219.17 0.52 46464 2.17
October 19, 2011 at 3:10 pm
I attached the image that is more readable.
October 20, 2011 at 6:03 am
The first wait is not an issue as by this (unless edge case I don't know about) : => http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx
Put it in the exclusion list and run again plz.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply