Fragmentation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Fragmentation

Author

Topic

debug
Starting Member

29 Posts

Posted - 2003-06-05 : 18:51:21

Hi ALL,

I have recently joined this forum...and I have recently started my carrer as a SQL / ORACLE DBA.

I am facing a strange problem everyday that one of the important tables in our OLTP database shows a very high level of fragmentation as the production progresses, even when we have scheduled a task for the reindexing of entire database and we have scheduled it to run after and before the production starts. Each day we get a call and blocking is observed in that particular table.

Please suggest some measures that I can take to solve this problem.

DU

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-05 : 20:13:31

Blocking doesn't necessarily have to do with fragmentation. Blocking can be attributed to slow server performance, bad code, etc... You have to analyze what is causing the blocking in order to determine a solution. Run SQL Profiler along with Performance Monitor when the blocking occurs to determine the cause.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-05 : 20:44:28

Blocking is more likely to be caused by poor application design than fragmentation.
Here is an SP you can run to see what is blocking and being blocked and the commands that they were executed from (run it with 'blk').
I'm going to put it on my web site (www.nigelrivett.com) when I get a chance.

if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_nrSpidByStatus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_nrSpidByStatus]
GO

Create procedure sp_nrSpidByStatus
@status varchar(20) = 'runnable'
as
/*
exec sp_nrSpidByStatus -- all spids whith status runnable
exec sp_nrSpidByStatus 'sleeping' -- all spids whith status sleeping
exec sp_nrSpidByStatus 'background' -- all spids whith status background
exec sp_nrSpidByStatus 'sleeping' -- all spids whith status sleeping
exec sp_nrSpidByStatus 'all' -- all spids
exec sp_nrSpidByStatus 'blk' -- all blocked or blocking spids
exec sp_nrSpidByStatus '113' -- an individual spid - also gives subthreads
select * from master..sysprocesses where spid = 113
*/
set transaction isolation level read uncommitted
set nocount on

declare @cmd varchar(1000)

declare @buf varchar(1000) ,
@id int ,
@spid int ,
@maxSpid int
create table #spid (spid int, command varchar(1000) null)
create table #temp (x varchar(100), y int, s varchar(1000), id int identity (1,1))
create table #spids (spid int)
if isnumeric(@status) = 1
begin
insert #spids select @status
end
else if @status = 'blk'
begin
insert #spids
select spid from master..sysprocesses where blocked <> 0
union
select blocked from master..sysprocesses where blocked <> 0
end
else
begin
insert #spids select spid from master..sysprocesses where (status = @status or @status = 'all') and ecid = 0
end
select @spid = 0 ,
@maxSpid = max(spid)
from #spids

while @spid < @maxSpid
begin
select @spid = min(spid) from #spids where spid > @spid

select @cmd = 'dbcc inputbuffer (' + convert(varchar(10),@spid) + ')'

delete #temp

insert #temp
exec (@cmd)

select @id = 0 ,
@buf = ''
select @buf = @buf + replace(replace(s,char(10),'|'),char(13),'|')

from #temp

insert #spid
select @spid, @buf
end

select blk = case when s.blocked <> 0 then convert(varchar(3),s.blocked) else ' ' end ,
spid = convert(varchar(4),#spid.spid) ,
s.physical_io ,
status = left(s.status,12) ,
last_batch = convert(varchar(23),s.last_batch,121) ,
s.cmd ,
#spid.command ,
login_time = convert(varchar(23),s.login_time,121) ,
s.HostName
from #spid ,
master..sysprocesses s
where s.spid = #spid.spid
and (ecid = 0 or isnumeric(@status) = 1)
order by s.status, #spid.spid

drop table #spid
drop table #temp

go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

debug
Starting Member

29 Posts

Posted - 2003-06-05 : 20:49:46

Thanks a ton for the kind advice.

Its just fragmentation which is the cause of worry here

Please check out the DBCC Showcontig results for the table :

DBCC SHOWCONTIG scanning 'IDSImage' table...
Table: 'IDSImage' (885578193); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 755
- Extents Scanned..............................: 101
- Extent Switches..............................: 336
- Avg. Pages per Extent........................: 7.5
- Scan Density [Best Count:Actual Count].......: 28.19% [95:337]
- Logical Scan Fragmentation ..................: 40.40%
- Extent Scan Fragmentation ...................: 42.57%
- Avg. Bytes Free per Page.....................: 1562.0
- Avg. Page Density (full).....................: 80.70%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Please suggest on this ground.

DU

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-06-05 : 21:04:22

Just out of curiosity, what is the structure of the IDSImage table? Can you post the DDL (CREATE TABLE statement) and list any and all indexes on that table? If you have large varchar/nvarchar columns, with widely varying lengths in the data values, these values can cause a great deal of fragmentation due to their size. Considering how much average free space there is per page you probably have a fairly large row size. Fragementation also happens when updates, deletes, and inserts occur frequently and which cause page splits (aggravated even further by large row sizes and/or varchar columns)

I would not entirely dismiss the application as the cause of the fragmentation. If there is a lot of transactional activity that cause data to be written and rewritten, you may need to look at modifying it to reduce the amount of that kind of work. In doing so you can reduce the need to defragment or reindex substantially, or at least reduce the amount of work it needs to do. Additionally, you should not be scheduling a reindexing procedure during any kind of moderate or heavy activity. Try moving the schedule to a time when there is zero or extremely little activity on the server.

debug
Starting Member

29 Posts

Posted - 2003-06-06 : 20:12:54

Hi ,

Please find the TABLE DESIGN as requested :
CREATE TABLE [dbo].[IDSImage] (
[ImageId] [varchar] (20) NOT NULL ,
[PolicyNo] [varchar] (20) NOT NULL ,
[ProcessId] [varchar] (10) NOT NULL ,
[WorkTypeId] [int] NOT NULL ,
[DateIndexUpload] [datetime] NOT NULL ,
[DateEntry] [datetime] NULL ,
[DateDownLoad] [datetime] NULL ,
[DateUpLoad] [datetime] NULL ,
[DwnldUserId] [varchar] (10) NULL ,
[DwnldMfUserid] [varchar] (10) NULL ,
[DEUserId] [varchar] (10) NULL ,
[DEMfUserid] [varchar] (10) NULL ,
[StateName] [varchar] (20) NOT NULL ,
[IndexWorkType] [varchar] (10) NULL ,
[AuditDone] [char] (1) NOT NULL ,
[ProcState] [varchar] (15) NULL ,
[Custom1] [numeric](28, 2) NULL ,
[Custom2] [varchar] (50) NULL ,
[DwnldCycle] [numeric](28, 0) NULL ,
[UpldUserId] [varchar] (10) NULL ,
[UpldMfUserid] [varchar] (10) NULL ,
[Custom3] [numeric](28, 2) NULL ,
[Custom4] [varchar] (50) NULL ,
[GeneralDate] [datetime] NULL
)
GO

Today same this happened, inspite of Scheduling the Reindexing job 3 times before the start of the production and once immediately after the production hours.

DU

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources