July 12, 2010 at 4:55 pm
Hi,
We have Share Point 2007 databases on SQL Server 2005 and we are using Spotlight tool for Monitoring.
Very frequently, we are getting blocking from the Content database. Please see the attachment.
Please advice me..
Thanks
July 12, 2010 at 5:46 pm
The CXPacket block is caused by parallel processing. One thread in the process is waiting for another to complete, typically reads. What are the DISK performance counters like?
Some DBAs may recommmend setting MAXDOP = 1 or raising the parallelism cost threshold above 5 (seconds).
I'd check if you are getting SPIDs blocking each other. The CXPacket blocking may not be an issue in itself unless it is causing other issues.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
July 12, 2010 at 6:12 pm
I'd check if you are getting SPIDs blocking each other. The CXPacket blocking may not be an issue in itself unless it is causing other issues
Thanks Miller,
Here the SPID 65 is the blocking and blocked itself..so can we ignore this kind of blocking?
When I saw this blocking in Spotlight, immediately I ran the report Activity - All Blocking Transactions. But I did NOT see any blocked process there!!
Is there any script to continuously run as a SQL Agent job and capture the blocking and send an email when it finds the blocking?
Please advice
July 12, 2010 at 7:07 pm
You could try this
GO
/****** Object: StoredProcedure [dbo].[DBA_checkForBlocks] Script Date: 07/13/2010 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[DBA_checkForBlocks] @wait_threshold as int,
@emailto as varchar(500)
AS
--Last updated 22/07/2008 MG
--Added remove duplicate spids
--Updated 13/05/2009
--Removed dts pacakge. Results now in body of email
declare @sqlstr_checkblocks as nvarchar(3000)
declare @server_name as varchar(50)
declare @blockedusers as varchar(50)
declare @getblockedspids as varchar(50)
declare @cmdStr as varchar(300)
Declare @cmd Varchar(150)
declare @rc as int
declare @output as varchar(1000)
declare @blocked int
declare @checktmpblocks int
declare @msubject as varchar(50)
truncate table tmpblocks
truncate table sqlstatement
create table #temp (x varchar(100), y int, s varchar(1000), id int
identity (1,1))
INSERT tmpblocks(SPID, Status, Login, HostName, BlkBy, DBName, Command, CPUTime, DiskIO, LastBatch, waittime,ProgramName, SPID2,blocked)
select spid,status,loginame as login,hostname,blocked,db_name(dbid) as dbname,cmd as command,cpu as cputime,physical_io as diskio,last_batch as lastbatch,waittime,program_name as programname,spid,blocked
from master.dbo.sysprocesses where spid > 51
select @blocked = count(*) from tmpblocks where
status <> 'background' and command not in
('signal handler', 'lock monitor', 'log writer', 'lazy writer', 'checkpoint sleep', 'awaiting command')
and blocked <>0 and waittime > @wait_threshold
If @blocked <> 0
begin
--Remove spids that are not being blocked and duplicates
delete from tmpblocks where blkby = 0 and
spid not in (select blkby from tmpblocks where blkby <> 0)
or
--Delete duplicate values
--DELETE
-- FROM tmpblocks where
dupID NOT IN
(
SELECT MAX(dupID)
FROM tmpblocks
GROUP BY spid)
Declare @spid varchar(10)
Declare @Statement varchar(8000)
declare @sql varchar(1000)
DECLARE SpidCursor Cursor
FOR Select spid from tmpblocks
OPEN SpidCursor
FETCH NEXT FROM SpidCursor
INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'dbcc inputbuffer (' + @spid + ')'
insert #temp
exec (@sql)
Insert Into SqlStatement
Select @spid, s From #Temp where id = (Select max(id) from #Temp)
FETCH NEXT FROM SpidCursor
INTO @spid
END
Close SpidCursor
Deallocate SpidCursor
--Select B.Statement, A.* from #sp_who2 A Left JOIN
-- #SqlStatement B ON A.spid = B.spid
Drop Table #Temp
if @blocked <> 0
begin
print 'blocks'
print @checktmpblocks
--DTS no longer required
--set @cmdstr = 'dtsrun.exe /S ' + @@servername + ' /n DBA_checkforblocks /e'
--EXEC @rc = master..xp_cmdshell @CmdStr
DECLARE @tableHTML NVARCHAR(MAX) ;
set @msubject = 'Database Blocking Report for ' + @@servername
SET @tableHTML =
N'<H1>Database Blocking Report</H1>' +
N'<table border="1">' +
N'<tr><th>SPID</th><th>STATUS</th>' +
N'<th>LOGIN</th><th>HOST NAME</th><th>BLKBY</th>' +
N'<th>DBNAME</th><th>COMMAND</th><th>CPUTIME</th><th>DISK IO</th><th>LASTBATCH</th><th>PROGRAM NAME</th><th>SPID2</th><th>STATEMENT</th><th>WAIT TIME</th></tr>' +
CAST ( ( SELECT td = A.SPID, '',
td = A.Status, '',
td = A.Login, '',
td = A.HostName, '',
td = A.BlkBy, '',
td = A.DBName, '',
td = A.Command, '',
td = A.CPUTime, '',
td = A.DiskIO, '',
td = A.LastBatch, '',
td = a.programname, '',
td = A.SPID2, '',
td = B.statement, '',
td = a.waittime, ''
FROM
tmpblocks A LEFT OUTER
JOIN
SqlStatement B ON A.SPID = B.spid
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @recipients=@emailto, --'blah@company.com',
@subject = @msubject,
@body = @tableHTML,
@body_format = 'HTML'
end
else
begin
print 'no blocks'
end
end
July 12, 2010 at 7:33 pm
..so can we ignore this kind of blocking?
Is there any script to continuously run as a SQL Agent job and capture the blocking and send an email when it finds the blocking?
You can ignore it if you want to, probably too your peril in the long run. I'd certainly try reduce the amount of this type of blocking if possible. It points to a performance bottleneck that may be impacting users.
In SQL 2005 you can use the DMVs to detect blocked prosesses, it's worth you while to do a bit of reading around these. Sorry I don't have any handy code, but there's bound to be some on the net if you google DMV + Blocking Script.
You may find you get a lot of notifications as (in my opinion) it's almost impossible to remove this blocking completely. To remove it you have to have all parallel threads on a given SPID ending at the same time. The idea is to keep it to a minimum.
As I said some DBAs recommend setting MAXDOP = 1 which eliminates parallelism. The question is; do you really want to do this SQL Instance wide? On an OLTP server it may be nice for all those high performance queries, but on a mixed server where you want some parallelism it may not be a good idea. Other DBAs recommend pushing the Cost Threshold up to 30 seconds (or more) so only long running queries use parallelism. Sorry there are no quick answers here and you need to test everything.
If you can, try improving indexing, use indexes with the INCLUDE clause, but I think you said this was SharePoint, and MS may not like you changing the database. Look at the disk sub-system and see if that is optimal (raid 10?).
What about memory usage, paging and Cache Buffer hit ratio, it is up in the 99,9% range?
Hope this helps.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply