Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Archive for August, 2011

sys.dm_exec_requests error for “blocking_session_id”

Posted by Simon Cho on 08/24/2011

Recently, I found that one of the DMV return error data when specific case.

sys.dm_exec_requests return error result of “blocking_session_id” data when CXPACKET is blocked by other session.

Here is the simulation

1. Create test data (if your server isn’t working as parallelism on #3, you need to increase top count)

use test

if object_id(‘test1’) is not null
drop table test1

select top 1000000 identity(int, 1,1) idx, c1.*

into test1

from sys.syscolumns c1

cross join sys.syscolumns c2

cross join sys.syscolumns c3


ALTER TABLE dbo.test1



2. Open new Query on your SSMS and Update one record to make lock processing without close the transaction. (Session 1)

Use test
begin transaction

update test1
set name=’test update’
where idx=25000

–rollback transaction

3. Open another new Query on your SSMS and run parallelism query.(Session 2)

set transaction isolation level read committed
exec sp_configure ‘show advanced options’, 1
reconfigure with override
exec sp_configure ‘max degree of parallelism’,0

reconfigure with override

select @@SPID


select COUNT(*) from test1

where name like ‘%object%’

option (maxdop 0)

Before run the query, please see the execution plan to make sure this query is working as parallelism.

And run the query. It should keep running due to open transaction

4. Compare sp_who2 result, sys.sysprocesses and sys.dm_exec_requests result.

— 53 is the session id of #3
exec sp_who2 53
select spid, blocked from sys.sysprocesseswhere spid=53

select session_id, blocking_session_id, wait_type

from sys.dm_exec_requests

where session_id=53

Result of sys.dm_exec_requets

The reason is that #2 just blocked 1 record to update.

#3 query is heavy, so it’s running as parallelism.

But, one of parallelism query can’t finish it due to blocking process of update lock.

So, it’s waiting for updating.

In this case, the sys.dm_exec_requests should return blocking session id as 59.

But, it returns as 0.

It’s error data.


Simon Cho

Posted in Common | 3 Comments »

Replication status check

Posted by Simon Cho on 08/16/2011

, rm.publication as pub_name
, rm.publisher_db as pub_db
, a.publisher_database_id as pub_db_id
, a.subscriber_db as sub_db
, comments
, h.time
, isnull(rm.cur_latency, 0) as cur_latency
, as pub_srv
, as sub_srv
distribution_svg011.dbo.msdistribution_agents a with(nolock)
join distribution_svg011.dbo.MSreplication_monitordata rm with(nolock) on
join master.sys.servers pub_srv with(nolock) on a.publisher_id = pub_srv.server_id
join master.sys.servers sub_srv with(nolock) on a.subscriber_id= sub_srv.server_id
cross apply
select top 1 *
From distribution_SVG011.dbo.msdistribution_history with(nolock)
order by timestamp desc
) h
order by pub_db, pub_name

Posted in Common | Tagged: , | 1 Comment »

Available File group size

Posted by Simon Cho on 08/04/2011

SELECT ISNULL(b.groupname, 'LOG') AS 'File Group'
, Name
, [Filename]
, CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space(MB)]
, CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)]
, CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)]
, CONVERT(Decimal(15,2),CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2))/CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2))*100) FreeSpace_Ratio
FROM dbo.sysfiles a (NOLOCK)
left outer JOIN sysfilegroups b (NOLOCK)
ON a.groupid = b.groupid
--ORDER BY b.groupname

Posted in Common | Tagged: , , | Leave a Comment »

object size each table and index (update for partitioning table)

Posted by Simon Cho on 08/04/2011

This query can check each table and index
And also, if it is partitioning table, it will show up partition # and each partition # size.

select FILEGROUP_NAME(au.data_space_id) as file_group
      , i.index_id
      , as index_name
      , au.partition_number
      , au.size_MB
      , case when i.data_space_id > 65000 then 'Y' else 'N' end IsPartitionedTable
  from 	(
		select object_id, index_id, partition_number, data_space_id
			 , convert(numeric(10,2)
			 , SUM(in_row_data_page_count
				+ lob_used_page_count
				+ row_overflow_used_page_count) * 8/1024.0) size_MB 
		  from sys.allocation_units au
		  join sys.dm_db_partition_stats  ps
			on au.container_id = ps.partition_id and au.type=1
		 group by object_id, index_id, partition_number, data_space_id
		) au
  join sys.indexes i 
    on i.object_id = au.object_id and i.index_id = au.index_id
  join sys.all_objects o
    on i.[object_id] = o.[object_id] and o.type='U'
 order by 1,2,3,4

Posted in Common | Tagged: , , , | 1 Comment »

Disk IO response time check.

Posted by Simon Cho on 08/04/2011

select convert(varchar(19), getdate(), 121) as date_time
, db_name(df.database_id) as DatabaseName
, as FileLogicalName
, f.filename
, case when num_of_reads+num_of_writes>0 then io_stall/(num_of_reads+num_of_writes) else 0 end as ‘ms/io’
, case when num_of_reads>0 then io_stall_read_ms/num_of_reads else 0 end as ‘ms/Read io’
, case when num_of_writes>0 then io_stall_write_ms/num_of_writes else 0 end as ‘ms/Write io’
from sys.dm_io_virtual_file_stats(null,null) as df
join sys.sysaltfiles as f
on f.dbid= df.database_id
and f.fileid = df.file_id
–where df.database_id in(DB_ID(‘db_passport’), DB_ID(‘TempDB’))
where case when num_of_reads+num_of_writes>0 then io_stall/(num_of_reads+num_of_writes) else 0 end >50
or case when num_of_reads>0 then io_stall_read_ms/num_of_reads else 0 end >50
or case when num_of_writes>0 then io_stall_write_ms/num_of_writes else 0 end >50
order by 5 desc

Posted in Common | Leave a Comment »

sp_now : rewrite sp_who2

Posted by Simon Cho on 08/03/2011

This SP is very helpful to troubleshooting concurrent issue.

use master

if object_id(‘sp_now’) is not null
drop procedure sp_now

create procedure [dbo].[sp_now]
@session_id int = null
,@xml_on bit = 0
,@text nvarchar(500) =”
,@back_on bit = 0
**Object Name: dbo.sp_now
**Description: Revise sp_who2
**Input Parameters:
@session_id int = null
, @text nvarchar(500) =”
, @back_on bit = 0
**Return Value: N/A
**Return Result Set: session info
**Creator: Simon Cho
**Example: exec [sp_now]
set nocount on;
set transaction isolation level read uncommitted;

sp.spid as s_id
, max(sp.blocked) as BlkBy
, substring(max(convert(varchar(23),r.start_time,121)+sp.status), 24, 50) status
, rtrim(max(sp.loginame)) as Login
, rtrim(ltrim(max(sp.hostname))) as hostname
, min(r.start_time) as st_time
, convert(numeric(10,2),max(r.total_elapsed_time/1000)/60.0) [run_time(min)]
–, r.total_elapsed_time
, GETDATE() as current_datetime
, max(sp.waittime) as waittime
, substring(max(convert(varchar(23),r.start_time,121)+r.wait_type), 24, 50) wait_type
, db_name(max(sp.dbid)) as db
, substring(max(convert(varchar(23),r.start_time,121)+command), 24, 50) command
, max(r.cpu_time) as cpu_time
, max(r.reads) as reads
, max(r.writes) as writes
, max(r.logical_reads) as logical_reads
, rtrim(max(sp.program_name)) as program_name
, max(c.client_net_address) ip
, min(sp.login_time) as login_time
, convert(numeric(10,2),max(r.granted_query_memory)/128.0) as granted_query_memory_MB
–, t.objectid
, OBJECT_NAME(t.objectid, t.dbid) objname
, convert(xml,'<?Q–‘+CHAR(13)+CHAR(10)+substring(max(case when text like ‘Fetch%’ then substring(text, 1,32) else text end)
, (case when (statement_start_offset/2)+1′) as text_stmt_xml
–, max(t.text) as text
, case when @xml_on = 1 then (select query_plan from sys.dm_exec_query_plan(max(r.plan_handle)))
else convert(xml, ”)
end as query_plan
from sys.sysprocesses sp with(nolock)
join sys.dm_exec_requests r with(nolock) on r.session_id=sp.spid
left join sys.dm_exec_connections c with(nolock) on r.connection_id=c.connection_id
outer apply sys.dm_exec_sql_text(r.sql_handle) t
where 1=1
— and sp.spid!=@@SPID
and case when @session_id is null then 1 else sp.spid end
= case when @session_id is null then 1 else @session_id end
and case when @text=” then ‘1’ else text end
like case when @text=” then ‘1’ else ‘%’+@text+’%’ end
and case when @back_on = 0 then sp.spid else 51 end > 50
and case when @back_on = 0 then sp.status else ‘all’ end != ‘background’
group by sp.spid
, sp.dbid, t.dbid
, t.objectid
, r.statement_start_offset
, r.statement_end_offset
, r.transaction_id
, c.connection_id
, sp.blocked


–exec sys.sp_MS_marksystemobject ‘sp_now’

Posted in Common | Tagged: , , , , | 1 Comment »