Has anyone had thsi issue, if so what was the fix?
We installed service pack2, the day after 2 of our production jobs started taking a long time to complete and causing a ton of blocking.
it went from running in 2 minutes to now taking 3 hours and 29 minutes to run. Can someone help?
Is it only certain queries running slower or is it all queries running slower? What high-level symptoms are you seeing? Does it look like CPU pressure, memory pressure, I/O pressure, blocking, etc.?
What kind of results do you get when you run these DMV queries?
-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)
SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)
,'%signal (cpu) waits' = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
,resource_wait_time_ms=SUM(wait_time_ms - signal_wait_time_ms)
,'%resource waits'= CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
FROM sys.dm_os_wait_stats
-- Check SQL Server Schedulers to see if they are waiting on CPU
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
-- Isolate top waits
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 LIKE '%SLEEP%'
-- filter out additional irrelevant waits
)
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 < 90 -- percentage threshold
ORDER BY W1.rn;
-- Detect blocking
SELECT blocked_query.session_id AS blocked_session_id,
blocking_query.session_id AS blocking_session_id,
sql_text.text AS blocked_text,
sql_btext.text AS blocking_text,
waits.wait_type AS blocking_resource
FROM sys.dm_exec_requests AS blocked_query
INNER JOIN sys.dm_exec_requests AS blocking_query
ON blocked_query.blocking_session_id = blocking_query.session_id
CROSS APPLY
(SELECT *
FROM sys.dm_exec_sql_text(blocking_query.sql_handle)
) sql_btext
CROSS APPLY
(SELECT *
FROM sys.dm_exec_sql_text(blocked_query.sql_handle)
) sql_text
INNER JOIN sys.dm_os_waiting_tasks AS waits
ON waits.session_id = blocking_query.session_id
|||After the SP2 Upgrade did you update the stats and index rebuilds on the tables?. You might want to perform some optimization and see if the problem still persists.|||
If you have not done so already, try running sp_UpdateStats on your database(s). This will run UPDATE STATISTICS with the default 10% sample rate on every table in the database (which may take a while, depending on your hardware and db size).
You should always do that after upgrading to SQL Server 2005
Rebuilding (as opposed to reorganizing) indexes will also automatically update statistics.
|||Hi,
We have experienced many slowness and trouble since the SP2 was installed.
Have a look at theses 2 KB:
http://support.microsoft.com/default.aspx/kb/937745
And
http://support.microsoft.com/default.aspx/kb/933564/en-us
Last point, run the following query when your server is slow:
select type, sum(single_pages_kb+multi_pages_kb) 'total memory' from sys.dm_os_memory_clerks ORDER by 2 DESC
This will help you to know what cache type is getting most of your SQL Ram.
If it's USERSTORE_TOKENPERM, consider using Trace flag 4618. It has had good impact on our system, but still not correct the case. I have conf call with MS Support on Monday about that point and will answer in this post if I have anymore informations.
Regards,
Jeremy
|||
Jeremy - Just wondering if you had any updates. We are currently hitting this issue. The clearing of ('TokenAndPermUserStore') does not work 100% of the time. We've experienced times when the system is so busy that we can not clear it fast enough.
We are considering using the trace flag.
Thanks -
Sam.
