August 19, 2011 at 3:16 am
Hi,
I have noticed one of the production database server, it is using 80 to 90 % CPU consumption, due to blocking of SPID.
Application is called PC-DASHBOARD and in-house development and they are lots of stored procedure.
There is one main SP called dbo.DesktopRunProcedure, its contain multiple SP executed with one SP.
create proc [dbo].[DesktopRunProcedure]
as
begin
exec Delete_Dummydata
exec symantec_AV
exec desktop_PC_status
exec desktop_domain
exec Desktop_DomainPolicy
exec Desktop_MoveChange
exec Desktop_UserPrivilege
exec Desktop_WSUS
exec Desktop_Printers
exec ServiceInfo_Detail
exec SoftwareDetails
exec HardwareDetails
exec Desktop_Server_printers
end
it was schedule at SQL Jobs at every 3 hours and fetching data from over 5000 PC, hardly 45 mints completed once starting this SP. after completing CPU is not decrease from 80 to 90 %. still consuming same percentage.
also find the SP_WH02, there are 40 to 45 SPID was blocked due to 45 mints running SP. after that blocking was not clear once completed SP.
For clear the blocking restarted SQL service but not hope for reduce CPU percentage and again CPU was same percentage, because uncommitted transaction SPID. another way for killing spid is not good method and SQL engine will be affected.
Can you give me suggestion, how to clear these blocking SPID without go for application side(stop & start)? Or Can I run this command for (ALTER DATABASE <DBNAME> SET SINGLE_USER WITH ROLLBACK IMMEDIATE. Does clear the blocking SPID by using this command?
Thanks
August 19, 2011 at 3:57 am
putting the database into single-user that way is a very brutal, but effective method to kill off connections. But you really need to fix the problem rather than just killing processes. You need to follow the blocking chain and resolve the problem. I'd also point out that not qualifying object names may lead to contention.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 19, 2011 at 6:12 am
Stolen from activity monitor (shows the blocking chains and waits really well)
--Borrowed from the activity monitor
SELECT
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = ISNULL(db_name(p.dbid), N''),
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024,
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0)
-- [Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
--LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)--TAKE THIS dmv OUT TO WORK IN 2005
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id;
August 19, 2011 at 6:14 am
I think you really need to read this as well because just killing the app that needs teh db ain't the solution :
Free book on perf tuning (excellent) : http://www.sqlservercentral.com/articles/books/65831/
August 19, 2011 at 12:36 pm
We had a similar situation in our environment. There were a lot if spids getting blocked and CPU was around 95% all the time. A lot of these queries were accesing one table.
All we had to do was create one non clustered index on this table and boom.....that made a huge improvement.
Check the indexes on the tables involved.
August 20, 2011 at 1:34 pm
I agree with the Ninja, killing the process is a bandaid at best. You need to address the issue and fix that set of procs so that they're not stepping all over the system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply