February 11, 2008 at 3:38 pm
I run dbcc showcontig as part of my weekly maint plan to see how fragmented the indexes are. Every once in a while, it will run for over 5 hours. From what I understand, it only puts a shared lock on the index which it is actually working on at the time.
I do run this process during off peak hours - but, there are still users accessing theses indexes (24x7 application). So, my understanding is that dbcc showcontig will wait for an index to become available, and then put a shared lock on it, and then release it. It shouldn't hold on to it for very long.
My problem is while dbcc showcontig was running, the application got hung up. It looked like dbcc showcontig was being blocked by one process, and then dbcc showcontig blocked multiple other processes.
I plan to create an alert that will just kill my job if it runs for more than an hour ... I need to research how to do that as well.
Has anyone else run into this problem? Does anyone have any suggestions?
Thank you in advance.
February 11, 2008 at 3:42 pm
Note: This is occurring in a SQL Server 2000 environment.
February 11, 2008 at 7:18 pm
May try "with fast" option, or schedule it while no much activities.
Here is an example of collecting fragmentation data via a scheduled job.
February 12, 2008 at 10:04 am
Vivien,
Thanks for the tips. I do run dbcc showcontig along with the "with fast" options.
Here is the full command I run:
dbcc showcontig with fast, tableresults, all_indexes
The command averages out to run in about 10 minutes when it doesn't get hung up for 5 hours. I do my best to determine our least activity time and run it then, but there are always users on the system.
I haven't been able to determine exactly what is causing this process become hung. My next step is to go on the system around the time this has happened in the past and run profiler. Does anyone have any words of advice?
February 12, 2008 at 11:00 am
you may want to set the deadlockpriority for your job to low:
SET DEADLOCKPRIORITY LOW
So if a job is canceled, it will be yours.
Best is to start a trace (sql profiler) before your job starts, and stop that trace afterward, so that if it gets halted, you'll have all running sqlstatements.
there are many sp_who variants that also show locking parties...
e.g.
-- ALZ install sp_DBA_WhoBlocks.sql
-----------------------------------
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_DBA_WhoBlocks] ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBA_WhoBlocks]') AND type in (N'P', N'PC'))
begin
DROP PROCEDURE [dbo].[sp_DBA_WhoBlocks]
end
GO
CREATE PROCEDURE sp_DBA_WhoBlocks
@Login_Name sysname = NULL
,@Host_Name sysname = NULL
,@DbName sysname = NULL
,@Program_Name sysname = NULL
,@ExtraInfo char(1) = 'N'
-- WITH EXECUTE AS 'myuser'
-- with encryption
as
BEGIN
-- JOBI dd 2006/06/07
-- Verzicht bestaande connecties en blockinginfo
--
print '-- Extra Parameters :'
print '-- @Login_Name sysname = ''' + isnull(@Login_Name,'') + ''''
print '-- ,@Host_Name sysname = ''' + isnull(@Host_Name,'') + ''''
print '--,@DbName sysname = ''' + isnull(@DbName,'') + ''''
print '-- ,@Program_Name sysname = ''' + isnull(@Program_Name,'') + ''''
print '-- ,@ExtraInfo char(1) = ''' + isnull(@ExtraInfo,'') + ''''
set nocount on
if upper(@ExtraInfo) = 'Y'
begin
SELECT
-- ES.*
ES.session_id
, ISNULL(ER.blocking_session_id, 0) as BlockedBy
, ES.login_name
, ES.last_request_start_time
, ES.last_request_end_time
, ES.cpu_time
, ES.[host_name]
, db_name(ER.database_id) as DbName
, ES.program_name
, case ES.transaction_isolation_level
when 0 then 'Unspecified'
when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted'
when 3 then 'RepeatableRead'
when 4 then 'Serializable'
when 5 then 'Snapshot'
else cast(ES.transaction_isolation_level as char(14))
end as TRX_isolation_level
, ES.nt_domain
, ES.nt_user_name
--, ES.login_time
--, ES.host_process_id
, ES.client_interface_name
--, ES.client_version
--, ES.security_id
, ES.status
--, ES.context_info
, ES.memory_usage
, ES.total_scheduled_time
, ES.total_elapsed_time
--, ES.endpoint_id
, ES.reads
, ES.writes
, ES.logical_reads
, ES.is_user_process
--, ES.text_size
--, ES.[language]
--, ES.date_format
--, ES.date_first
--, ES.[quoted_identifier]
--, ES.[arithabort]
--, ES.[ansi_null_dflt_on]
--, ES.[ansi_defaults]
--, ES.[ansi_warnings]
--, ES.[ansi_padding]
--, ES.[ansi_nulls]
--, ES.[concat_null_yields_null]
--, ES.transaction_isolation_level
--, ES.[lock_timeout]
--, ES.[deadlock_priority]
, ES.row_count
, ES.prev_error
-- , ER.*
--, ER.session_id
--, ER.request_id
, ER.start_time
, ER.status
, ER.command
--, ER.sql_handle
--, ER.statement_start_offset
--, ER.statement_end_offset
--, ER.plan_handle
, ER.[user_id]
--, ER.connection_id
--, ER.blocking_session_id
, ER.wait_type
, ER.wait_time
, ER.last_wait_type
, ER.wait_resource
, ER.open_transaction_count
, ER.open_resultset_count
--, ER.transaction_id
--, ER.context_info
--, ER.percent_complete
--, ER.estimated_completion_time
, ER.cpu_time
, ER.total_elapsed_time
--, ER.scheduler_id
--, ER.task_address
, ER.reads
, ER.writes
, ER.logical_reads
--, ER.text_size
--, ER.[language]
--, ER.date_format
--, ER.date_first
--, ER.[quoted_identifier]
--, ER.[arithabort]
--, ER.[ansi_null_dflt_on]
--, ER.[ansi_defaults]
--, ER.[ansi_warnings]
--, ER.[ansi_padding]
--, ER.[ansi_nulls]
--, ER.[concat_null_yields_null]
, ER.transaction_isolation_level
, ER.[lock_timeout]
, ER.[deadlock_priority]
, ER.row_count
--, ER.prev_error
, ER.nest_level
--, ER.granted_query_memory
, ER.executing_managed_code
--, EST.* --dbid objectid number encrypted text
, EST.dbid
, EST.objectid
, EST.number
, EST.encrypted
, EST.[text]
-- , EQP.* --dbid objectid number encrypted query_plan
-- , EQP.dbid
-- , EQP.objectid
--, EQP.number
--, EQP.encrypted
, EQP.query_plan
-- Blocked by info
, ESBlk.session_id as Blk_session_id
, ESBlk.login_name as Blk_login_name
, ESBlk.nt_domain as Blk_nt_domain
, ESBlk.last_request_start_time as Blk_last_request_start_time
, ESBlk.last_request_end_time as Blk_last_request_end_time
, ESBlk.[host_name] as Blk_host_name
, ESBlk.program_name as Blk_program_name
, ESBlk.status as Blk_status
, ESBlk.cpu_time as Blk_cpu_time
FROM sys.dm_exec_sessions ES
left join sys.dm_exec_requests ER
on ER.session_id = ES.session_id
OUTER Apply sys.dm_exec_sql_text(ER.sql_handle) EST
OUTER Apply sys.dm_exec_query_plan(ER.plan_handle) EQP
left join sys.dm_exec_sessions ESBlk
on ESBlk.session_id = ER.blocking_session_id
-- where ER.session_id = @session_id
Where case when @Login_Name is null then 1
else case when ES.login_name like @Login_Name + '%' then 1 else 0 end
end = 1
and case when @Host_Name is null then 1
else case when ES.[host_name] like @Host_Name + '%' then 1 else 0 end
end = 1
and case when @DbName is null then 1
else case ER.database_id when db_id(@DbName) then 1 else 0 end
end = 1
and case when @Program_Name is null then 1
else case when ES.program_name like @Program_Name + '%' then 1 else 0 end
end = 1
order by case when isnull(ER.blocking_session_id,0)=0 then 1 else 0 end, ES.last_request_start_time desc, ES.last_request_end_time desc , ES.login_time desc
END
ELSE
BEGIN
SELECT
-- ES.*
ES.session_id
, ISNULL(ER.blocking_session_id, 0) as BlockedBy
, ES.login_name
, ES.last_request_start_time
, ES.last_request_end_time
, ES.cpu_time
, ES.[host_name]
, db_name(ER.database_id) as DbName
, ES.program_name
, case ES.transaction_isolation_level
when 0 then 'Unspecified'
when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted'
when 3 then 'RepeatableRead'
when 4 then 'Serializable'
when 5 then 'Snapshot'
else cast(ES.transaction_isolation_level as char(14))
end as TRX_isolation_level
, ES.nt_domain
, ES.nt_user_name
--, ES.login_time
--, ES.host_process_id
, ES.client_interface_name
--, ES.client_version
--, ES.security_id
, ES.status
--, ES.context_info
, ES.memory_usage
, ES.total_scheduled_time
, ES.total_elapsed_time
--, ES.endpoint_id
, ES.reads
, ES.writes
, ES.logical_reads
, ES.is_user_process
--, ES.text_size
--, ES.[language]
--, ES.date_format
--, ES.date_first
--, ES.[quoted_identifier]
--, ES.[arithabort]
--, ES.[ansi_null_dflt_on]
--, ES.[ansi_defaults]
--, ES.[ansi_warnings]
--, ES.[ansi_padding]
--, ES.[ansi_nulls]
--, ES.[concat_null_yields_null]
--, ES.transaction_isolation_level
--, ES.[lock_timeout]
--, ES.[deadlock_priority]
, ES.row_count
, ES.prev_error
-- , ER.*
--, ER.session_id
--, ER.request_id
, ER.start_time
, ER.status
, ER.command
--, ER.sql_handle
--, ER.statement_start_offset
--, ER.statement_end_offset
--, ER.plan_handle
, ER.[user_id]
--, ER.connection_id
--, ER.blocking_session_id
, ER.wait_type
, ER.wait_time
, ER.last_wait_type
, ER.wait_resource
, ER.open_transaction_count
, ER.open_resultset_count
--, ER.transaction_id
--, ER.context_info
--, ER.percent_complete
--, ER.estimated_completion_time
, ER.cpu_time
, ER.total_elapsed_time
--, ER.scheduler_id
--, ER.task_address
, ER.reads
, ER.writes
, ER.logical_reads
--, ER.text_size
--, ER.[language]
--, ER.date_format
--, ER.date_first
--, ER.[quoted_identifier]
--, ER.[arithabort]
--, ER.[ansi_null_dflt_on]
--, ER.[ansi_defaults]
--, ER.[ansi_warnings]
--, ER.[ansi_padding]
--, ER.[ansi_nulls]
--, ER.[concat_null_yields_null]
, ER.transaction_isolation_level
, ER.[lock_timeout]
, ER.[deadlock_priority]
, ER.row_count
--, ER.prev_error
, ER.nest_level
--, ER.granted_query_memory
, ER.executing_managed_code
-- Blocked by info
, ESBlk.session_id as Blk_session_id
, ESBlk.login_name as Blk_login_name
, ESBlk.nt_domain as Blk_nt_domain
, ESBlk.last_request_start_time as Blk_last_request_start_time
, ESBlk.last_request_end_time as Blk_last_request_end_time
, ESBlk.[host_name] as Blk_host_name
, ESBlk.program_name as Blk_program_name
, ESBlk.status as Blk_status
, ESBlk.cpu_time as Blk_cpu_time
FROM sys.dm_exec_sessions ES
left join sys.dm_exec_requests ER
on ER.session_id = ES.session_id
left join sys.dm_exec_sessions ESBlk
on ESBlk.session_id = ER.blocking_session_id
-- where ER.session_id = @session_id
Where case when @Login_Name is null then 1
else case when ES.login_name like @Login_Name + '%' then 1 else 0 end
end = 1
and case when @Host_Name is null then 1
else case when ES.[host_name] like @Host_Name + '%' then 1 else 0 end
end = 1
and case when @DbName is null then 1
else case ER.database_id when db_id(@DbName) then 1 else 0 end
end = 1
and case when @Program_Name is null then 1
else case when ES.program_name like @Program_Name + '%' then 1 else 0 end
end = 1
order by case when isnull(ER.blocking_session_id,0) = 0 then 1 else 0 end, ES.last_request_start_time desc, ES.last_request_end_time desc , ES.login_time desc
END
END
GO
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 12, 2008 at 12:36 pm
How about check table by table instead of checking the whole database in one batch?
Refer to Books online "DBCC SHOWCONTIG" example E, you may add your debug code to find out which table is not proceeding.
Or get the recent backup and restore, work on the restored database, if it is ok with your environment.
Just my 2 cents.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply