Index on two columns doesnt allow NULL in both - HELP!

Table DDL below:

The tables I have contain Timesheet information. Each row in the
tblTSCollected table contains an entry for an employee into the
timesheet system, specifically by scanning the barcode on their badge.

A whole bunch of business logic periodically attempts to "pair" these
into logically matched scans. For example, some employees will scan in
and out of a single place of work. For these there will be a row
written to the tblTSRuleApplied table which contains, inter alia and
some redundant data, the fldCollectedID for the two rows. The earlier
will be put into the fldStartTimeCollectedID, and the later into the
fldEndTimeCollectedID. Some employees will clock on at their base,
then perform sub-duties at different locations during the day, and
clock off at their home base at the end of their shift. For these, the
system would identify the outer records as a matching pair, and then
pair up inner records by location.

However, if the employee fails to enter a valid "clocking in and out"
pair (for example, if they clock in at the wrong location) the system
needs to generate a "dummy" "clocking in and out" record for the
payroll department. Ideally, this would have NULL values in the
fldStartTimeCollectedID and fldEndTimeCollectedID columns. This would
alert a user in a different part of the system, where missing
timesheets were being arbitrated, that an employee appeared to have
failed to clock in for that day. Of course, the user could see
on-screen that they had clocked in, but at an incorrect location.

Unfortunately, the database designer is not here for the moment (he was
knocked off his bicycle recently), but he put a unique index on the
tblTSRuleApplied table that prevents the same value being entered into
the fldStartTimeCollectedID and fldEndTimeCollectedID columns. This is
generally A Good Thing, since we don't want the same timesheet scan to
form both a "clocking on" event and a "clocking off" event.

So, is there any way of retaining the requirement that the
fldStartTimeCollectedID and the fldEndTimeCollectedID columns may not
contain the same value in a single row, UNLESS that value is NULL in
which case all is hunky dory. I should add that the clients don't much
care for Triggers (and neither do I for that matter).

Many thanks if you are able to help.

Edward

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINT
FK_tblTSRuleApplied_tblTSCollected
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected1]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINT
FK_tblTSRuleApplied_tblTSCollected1
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSArbAccept_tblTSRuleApplied]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSArbAccept] DROP CONSTRAINT
FK_tblTSArbAccept_tblTSRuleApplied
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSCollected_tblTSRuleApplied]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSCollected] DROP CONSTRAINT
FK_tblTSCollected_tblTSRuleApplied
GO

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

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

CREATE TABLE [dbo].[tblTSCollected] (
[fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,
[fldEmployeeID] [int] NULL ,
[fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldTimeStamp] [datetime] NULL ,
[fldRuleAppliedID] [int] NULL ,
[fldBarCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldProcessed] [smallint] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTSRuleApplied] (
[fldEmpRuleID] [int] NOT NULL ,
[fldRuleAppliedID] [int] IDENTITY (1, 1) NOT NULL ,
[fldStartTime] [datetime] NULL ,
[fldEndTime] [datetime] NULL ,
[fldStartTimeCollectedID] [int] NULL ,
[fldEndTimeCollectedID] [int] NULL ,
[fldStartArbStatus] [smallint] NULL ,
[fldEndArbStatus] [smallint] NULL ,
[fldDurationArbStatus] [smallint] NULL ,
[fldPrimary] [smallint] NOT NULL ,
[fldDateEntered] [datetime] NULL ,
[fldEnteredBy] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSCollected] WITH NOCHECK ADD
CONSTRAINT [DF_tblTSCollected_fldProcessed] DEFAULT (0) FOR
[fldProcessed],
CONSTRAINT [PK_tblTimesheetCollected] PRIMARY KEY CLUSTERED
(
[fldCollectedID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSRuleApplied] WITH NOCHECK ADD
CONSTRAINT [DF_tblTSRuleApplied_fldPrimary] DEFAULT (1) FOR
[fldPrimary],
CONSTRAINT [PK_tblTSRuleApplied] PRIMARY KEY CLUSTERED
(
[fldRuleAppliedID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_tblTSRuleApplied_1] UNIQUE NONCLUSTERED
(
[fldStartTimeCollectedID],
[fldEndTimeCollectedID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSCollected] ADD
CONSTRAINT [FK_tblTSCollected_tblEmployee1] FOREIGN KEY
(
[fldEmployeeID]
) REFERENCES [dbo].[tblEmployee] (
[fldEmployeeID]
),
CONSTRAINT [FK_tblTSCollected_tblLocation] FOREIGN KEY
(
[fldLocationCode]
) REFERENCES [dbo].[tblLocation] (
[fldLocationCode]
),
CONSTRAINT [FK_tblTSCollected_tblTSRuleApplied] FOREIGN KEY
(
[fldRuleAppliedID]
) REFERENCES [dbo].[tblTSRuleApplied] (
[fldRuleAppliedID]
)
GO

ALTER TABLE [dbo].[tblTSRuleApplied] ADD
CONSTRAINT [FK_tblTSRuleApplied_tblTSCollected] FOREIGN KEY
(
[fldStartTimeCollectedID]
) REFERENCES [dbo].[tblTSCollected] (
[fldCollectedID]
),
CONSTRAINT [FK_tblTSRuleApplied_tblTSCollected1] FOREIGN KEY
(
[fldEndTimeCollectedID]
) REFERENCES [dbo].[tblTSCollected] (
[fldCollectedID]
),
CONSTRAINT [FK_tblTSRuleApplied_tblTSDurationStatus] FOREIGN KEY
(
[fldDurationArbStatus]
) REFERENCES [dbo].[tblTSDurationStatus] (
[fldStatus]
),
CONSTRAINT [FK_tblTSRuleApplied_tblTSEmpRules] FOREIGN KEY
(
[fldEmpRuleID]
) REFERENCES [dbo].[tblTSEmpRules] (
[fldEmpRuleID]
),
CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus] FOREIGN KEY
(
[fldStartArbStatus]
) REFERENCES [dbo].[tblTSTimeStatus] (
[fldStatus]
),
CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus1] FOREIGN KEY
(
[fldEndArbStatus]
) REFERENCES [dbo].[tblTSTimeStatus] (
[fldStatus]
)
GO> So, is there any way of retaining the requirement that the
> fldStartTimeCollectedID and the fldEndTimeCollectedID columns may not
> contain the same value in a single row, UNLESS that value is NULL in
> which case all is hunky dory. I should add that the clients don't much
> care for Triggers (and neither do I for that matter).

There are a couple of methods to accomplish this. One method is with a
trigger. Another, with SQL 2000 and above, is using an index view including
non-null values instead of a unique constraint:

CREATE VIEW v_tblTSRuleApplied
WITH SCHEMABINDING
AS
SELECT fldStartTimeCollectedID, fldEndTimeCollectedID
FROM dbo.tblTSRuleApplied
WHERE fldStartTimeCollectedID IS NOT NULL AND
fldEndTimeCollectedID IS NOT NULL
GO

CREATE UNIQUE CLUSTERED INDEX v_tblTSRuleApplied_cdx
ON v_tblTSRuleApplied(fldStartTimeCollectedID, fldEndTimeCollectedID)
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

<teddysnips@.hotmail.com> wrote in message
news:1135265109.464713.76030@.f14g2000cwb.googlegro ups.com...
> Table DDL below:
> The tables I have contain Timesheet information. Each row in the
> tblTSCollected table contains an entry for an employee into the
> timesheet system, specifically by scanning the barcode on their badge.
> A whole bunch of business logic periodically attempts to "pair" these
> into logically matched scans. For example, some employees will scan in
> and out of a single place of work. For these there will be a row
> written to the tblTSRuleApplied table which contains, inter alia and
> some redundant data, the fldCollectedID for the two rows. The earlier
> will be put into the fldStartTimeCollectedID, and the later into the
> fldEndTimeCollectedID. Some employees will clock on at their base,
> then perform sub-duties at different locations during the day, and
> clock off at their home base at the end of their shift. For these, the
> system would identify the outer records as a matching pair, and then
> pair up inner records by location.
> However, if the employee fails to enter a valid "clocking in and out"
> pair (for example, if they clock in at the wrong location) the system
> needs to generate a "dummy" "clocking in and out" record for the
> payroll department. Ideally, this would have NULL values in the
> fldStartTimeCollectedID and fldEndTimeCollectedID columns. This would
> alert a user in a different part of the system, where missing
> timesheets were being arbitrated, that an employee appeared to have
> failed to clock in for that day. Of course, the user could see
> on-screen that they had clocked in, but at an incorrect location.
> Unfortunately, the database designer is not here for the moment (he was
> knocked off his bicycle recently), but he put a unique index on the
> tblTSRuleApplied table that prevents the same value being entered into
> the fldStartTimeCollectedID and fldEndTimeCollectedID columns. This is
> generally A Good Thing, since we don't want the same timesheet scan to
> form both a "clocking on" event and a "clocking off" event.
> So, is there any way of retaining the requirement that the
> fldStartTimeCollectedID and the fldEndTimeCollectedID columns may not
> contain the same value in a single row, UNLESS that value is NULL in
> which case all is hunky dory. I should add that the clients don't much
> care for Triggers (and neither do I for that matter).
> Many thanks if you are able to help.
> Edward
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINT
> FK_tblTSRuleApplied_tblTSCollected
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected1]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINT
> FK_tblTSRuleApplied_tblTSCollected1
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblTSArbAccept_tblTSRuleApplied]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblTSArbAccept] DROP CONSTRAINT
> FK_tblTSArbAccept_tblTSRuleApplied
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblTSCollected_tblTSRuleApplied]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblTSCollected] DROP CONSTRAINT
> FK_tblTSCollected_tblTSRuleApplied
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblTSCollected]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblTSCollected]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblTSRuleApplied]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblTSRuleApplied]
> GO
> CREATE TABLE [dbo].[tblTSCollected] (
> [fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,
> [fldEmployeeID] [int] NULL ,
> [fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [fldTimeStamp] [datetime] NULL ,
> [fldRuleAppliedID] [int] NULL ,
> [fldBarCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [fldProcessed] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblTSRuleApplied] (
> [fldEmpRuleID] [int] NOT NULL ,
> [fldRuleAppliedID] [int] IDENTITY (1, 1) NOT NULL ,
> [fldStartTime] [datetime] NULL ,
> [fldEndTime] [datetime] NULL ,
> [fldStartTimeCollectedID] [int] NULL ,
> [fldEndTimeCollectedID] [int] NULL ,
> [fldStartArbStatus] [smallint] NULL ,
> [fldEndArbStatus] [smallint] NULL ,
> [fldDurationArbStatus] [smallint] NULL ,
> [fldPrimary] [smallint] NOT NULL ,
> [fldDateEntered] [datetime] NULL ,
> [fldEnteredBy] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblTSCollected] WITH NOCHECK ADD
> CONSTRAINT [DF_tblTSCollected_fldProcessed] DEFAULT (0) FOR
> [fldProcessed],
> CONSTRAINT [PK_tblTimesheetCollected] PRIMARY KEY CLUSTERED
> (
> [fldCollectedID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblTSRuleApplied] WITH NOCHECK ADD
> CONSTRAINT [DF_tblTSRuleApplied_fldPrimary] DEFAULT (1) FOR
> [fldPrimary],
> CONSTRAINT [PK_tblTSRuleApplied] PRIMARY KEY CLUSTERED
> (
> [fldRuleAppliedID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [IX_tblTSRuleApplied_1] UNIQUE NONCLUSTERED
> (
> [fldStartTimeCollectedID],
> [fldEndTimeCollectedID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblTSCollected] ADD
> CONSTRAINT [FK_tblTSCollected_tblEmployee1] FOREIGN KEY
> (
> [fldEmployeeID]
> ) REFERENCES [dbo].[tblEmployee] (
> [fldEmployeeID]
> ),
> CONSTRAINT [FK_tblTSCollected_tblLocation] FOREIGN KEY
> (
> [fldLocationCode]
> ) REFERENCES [dbo].[tblLocation] (
> [fldLocationCode]
> ),
> CONSTRAINT [FK_tblTSCollected_tblTSRuleApplied] FOREIGN KEY
> (
> [fldRuleAppliedID]
> ) REFERENCES [dbo].[tblTSRuleApplied] (
> [fldRuleAppliedID]
> )
> GO
> ALTER TABLE [dbo].[tblTSRuleApplied] ADD
> CONSTRAINT [FK_tblTSRuleApplied_tblTSCollected] FOREIGN KEY
> (
> [fldStartTimeCollectedID]
> ) REFERENCES [dbo].[tblTSCollected] (
> [fldCollectedID]
> ),
> CONSTRAINT [FK_tblTSRuleApplied_tblTSCollected1] FOREIGN KEY
> (
> [fldEndTimeCollectedID]
> ) REFERENCES [dbo].[tblTSCollected] (
> [fldCollectedID]
> ),
> CONSTRAINT [FK_tblTSRuleApplied_tblTSDurationStatus] FOREIGN KEY
> (
> [fldDurationArbStatus]
> ) REFERENCES [dbo].[tblTSDurationStatus] (
> [fldStatus]
> ),
> CONSTRAINT [FK_tblTSRuleApplied_tblTSEmpRules] FOREIGN KEY
> (
> [fldEmpRuleID]
> ) REFERENCES [dbo].[tblTSEmpRules] (
> [fldEmpRuleID]
> ),
> CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus] FOREIGN KEY
> (
> [fldStartArbStatus]
> ) REFERENCES [dbo].[tblTSTimeStatus] (
> [fldStatus]
> ),
> CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus1] FOREIGN KEY
> (
> [fldEndArbStatus]
> ) REFERENCES [dbo].[tblTSTimeStatus] (
> [fldStatus]
> )
> GO|||Dan Guzman wrote:
> > So, is there any way of retaining the requirement that the
> > fldStartTimeCollectedID and the fldEndTimeCollectedID columns may not
> > contain the same value in a single row, UNLESS that value is NULL in
> > which case all is hunky dory. I should add that the clients don't much
> > care for Triggers (and neither do I for that matter).
> There are a couple of methods to accomplish this. One method is with a
> trigger. Another, with SQL 2000 and above, is using an index view including
> non-null values instead of a unique constraint:
[snip]

Many thanks - I'll put this to the vote just after the holidays.

I *love* usenet.

Edward|||I don't think Mr. Guzman's solution will work for the business problem
you are trying to solve. It does allow the index, but you will never be
able to retrieve any of the data where EITHER start OR end time is
null.

I guess I'm trying to understand when a record would be created when
both entries are null?|||On 22 Dec 2005 11:26:28 -0800, Doug wrote:

>I don't think Mr. Guzman's solution will work for the business problem
>you are trying to solve. It does allow the index, but you will never be
>able to retrieve any of the data where EITHER start OR end time is
>null.

Hi Doug,

Not from the indexed view, but you can still get this data from the
table itself.

The view suggested by Dan is intended merely to enforce the constraint,
not to replace the table in queries.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hmmmm......

I don't think the view forces the constraint onto the table. Is this
correct?|||On 22 Dec 2005 16:06:57 -0800, Doug wrote:

>Hmmmm......
>I don't think the view forces the constraint onto the table. Is this
>correct?

Hi Doug,

Have you tried it?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||> I don't think the view forces the constraint onto the table. Is this
> correct?

SQL Server automatically maintains the view index to reflect underlying
table changes. This will have the effect of a unique constraint that
ignores null values. Duplicate non-null values will not be allowed in the
underlying table.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Doug" <drmiller100@.hotmail.com> wrote in message
news:1135296417.507570.119610@.f14g2000cwb.googlegr oups.com...
> Hmmmm......
> I don't think the view forces the constraint onto the table. Is this
> correct?sql

Index on result of function

I have a table with about 28 million records in it. Each row has an ID (PK), logged (datetime), IP varchar(15)

The data grows at about 14 million records per year. I'm going to be running queries on the table that extract the MONTH or YEAR from the logged column. In Foxpro tables I would have created indexes on YEAR(logged) and MONTH(logged) so my queries would run faster. Is this possible/necessary in SQL Server?

Yes. You can achive this using the indexed views.

Create different views for each year & index it.

|||Bes, this table sounds like a good candidate for the new table partitioning method of SQL 2005. You could partition by the year and month... There would be separate indexes on each partition slice and SQL Server would direct a query to just the partition needed and the query would run much faster... but... you need Enterprise Edition for paritioning. If you have Enterprise, then it's something to check out... Bruce|||

Bruce,

It's good to know there is another way to do it. The little I've read about Indexed Views indicates they'll increase my maintenance and I should only use them in special cases.

We're not running Enterprise (too much $ for dual CPUs), but if depending on how we use this data maybe we'll be able to justify it.

Thanks!

Brian

|||

I think creating a couple of computed column(s) and creating an index on those field(s) will give you the best combination of query performance and maintenance. Lots of modifications to data in the base table in an indexed view could cause a server to grind to a halt. The index maintenance on the computed columns should be minimal.

alter table MyTable add MyDateYear AS YEAR(MyDate)

alter table MyTable add MyDateMonth AS Month(MyDate)

CREATE INDEX IX_MyTable_Year_Month ON MyTable(MyDateYear, MyDateMonth)

index keeps going bad

Hi All
I am running on SQL Server 2000 SP3.
I have a deduction table which might have a row added here and there
throughout a month and all is fine. But once a month we run a process
which inserts 25,000 rows. For the last five months, every time that
process was run, a select which normally takes less than a second
would take a minute and a half. If I recreate the index on the table
the select goes back down to less than a second.
That is my fix for it but I don't know if that is just inadvertently
fixing it or it is truly the index which is the problem. Checkdb and
checktable show no problems before I recreate the index.
This is the result of showcontig while slow:
- Pages Scanned........................: 68066
- Extents Scanned.......................: 8600
- Extent Switches.......................: 27218
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 31.26% [8509:27219]
- Logical Scan Fragmentation ..............: 18.59%
- Extent Scan Fragmentation ...............: 15.12%
- Avg. Bytes Free per Page................: 1198.4
- Avg. Page Density (full)................: 85.19%
This is the result of showcontig when good:
- Pages Scanned........................: 58283
- Extents Scanned.......................: 7334
- Extent Switches.......................: 7336
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.30% [7286:7337]
- Logical Scan Fragmentation ..............: 2.19%
- Extent Scan Fragmentation ...............: 8.36%
- Avg. Bytes Free per Page................: 40.5
- Avg. Page Density (full)................: 99.50%
Any suggestions on what might be causing this?
Or what I might do to find the problem?
Thanks,
MGB
In message <7341bcd.0409291012.4746ecdc@.posting.google.com> , MGB
<groups@.beu.hm> writes
>Scan Density [Best Count:Actual Count]......: 31.26% [8509:27219]
This would suggest to me that the table in question has a Primary Key
which is Clustered and you have left no space in the table for normal
growth through your import of 25000 transactions.
This typically happens when you use a GUID column as a Clustered Primary
Key. As a GUID is randomly created it could appear anywhere in the index
and hence more Extents and Pages are created in order to keep the
Clustered Index ordered properly (remember that a Clustered index means
the data is also physically stored in the same order - witch in turn
increases the load on the server). If the Primary Key was an AutoNumber
column then the Insert would always be on the last page of the last
extent, in laymans terms.
There are several solutions:
1) Never create a Clustered Index on a GUID column unless the data in
that table is almost static.
2) After step 1 adjust the index's Fill Factor to create space for your
25000 Inserts.
3) Schedule a rebuild of the index's once a month to keep things sweet.
You don't need to keep Droping the index, try Scheduleing the function
below.
CREATE PROC TWRebuild AS
DECLARE @.TableName VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE @.Command VARCHAR(255)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @.TableName
DBCC DBREINDEX(@.TableName)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
If you provide more information about the Table structure, Index's and
Constraints we could be a little more accurate about the problem.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

index keeps going bad

Hi All
I am running on SQL Server 2000 SP3.
I have a deduction table which might have a row added here and there
throughout a month and all is fine. But once a month we run a process
which inserts 25,000 rows. For the last five months, every time that
process was run, a select which normally takes less than a second
would take a minute and a half. If I recreate the index on the table
the select goes back down to less than a second.
That is my fix for it but I don't know if that is just inadvertently
fixing it or it is truly the index which is the problem. Checkdb and
checktable show no problems before I recreate the index.
This is the result of showcontig while slow:
- Pages Scanned........................: 68066
- Extents Scanned.......................: 8600
- Extent Switches.......................: 27218
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 31.26% [8509:27219]
- Logical Scan Fragmentation ..............: 18.59%
- Extent Scan Fragmentation ...............: 15.12%
- Avg. Bytes Free per Page................: 1198.4
- Avg. Page Density (full)................: 85.19%
This is the result of showcontig when good:
- Pages Scanned........................: 58283
- Extents Scanned.......................: 7334
- Extent Switches.......................: 7336
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.30% [7286:7337]
- Logical Scan Fragmentation ..............: 2.19%
- Extent Scan Fragmentation ...............: 8.36%
- Avg. Bytes Free per Page................: 40.5
- Avg. Page Density (full)................: 99.50%
Any suggestions on what might be causing this?
Or what I might do to find the problem?
Thanks,
MGBIn message <7341bcd.0409291012.4746ecdc@.posting.google.com>, MGB
<groups@.beu.hm> writes
>Scan Density [Best Count:Actual Count]......: 31.26% [8509:27219]
This would suggest to me that the table in question has a Primary Key
which is Clustered and you have left no space in the table for normal
growth through your import of 25000 transactions.
This typically happens when you use a GUID column as a Clustered Primary
Key. As a GUID is randomly created it could appear anywhere in the index
and hence more Extents and Pages are created in order to keep the
Clustered Index ordered properly (remember that a Clustered index means
the data is also physically stored in the same order - witch in turn
increases the load on the server). If the Primary Key was an AutoNumber
column then the Insert would always be on the last page of the last
extent, in laymans terms.
There are several solutions:
1) Never create a Clustered Index on a GUID column unless the data in
that table is almost static.
2) After step 1 adjust the index's Fill Factor to create space for your
25000 Inserts.
3) Schedule a rebuild of the index's once a month to keep things sweet.
You don't need to keep Droping the index, try Scheduleing the function
below.
--
CREATE PROC TWRebuild AS
DECLARE @.TableName VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE @.Command VARCHAR(255)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @.TableName
DBCC DBREINDEX(@.TableName)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
--
If you provide more information about the Table structure, Index's and
Constraints we could be a little more accurate about the problem.
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

Index Fragementing Rapidly

I'm a little confused over something that keeps happinging to my database.
I have a web app that insert a row into a SS2K table every time a page is viewed. It's an intranet site, and I maybe average one page a second, no big deal. THe table uses an Indentity field, and has a clustered index on that Indenty Field alone.
Most of the time this works fine, absolutely no problems, no slow down, etc. However, from time to time the insert into this table will "lock up" - take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
When I look at the tables, using DBCC SHOWCONTIG, the logical fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that generally takes care of the problem. I've setup a job to run nightly that runs the following sql command: DBCC DBREINDEX (
'Activity','',70).
Here's my question: This will happen suddenly (i.e. one page request, no problem, the next, lock up). Why does my index fragment so rapidly? I would expect a "build up". Could something else be going on that I'm missing?
Hi,
Info from Books online:-
Table fragmentation occurs through the process of data modifications
(INSERT, UPDATE, and DELETE statements) made against the table.
Because these modifications are not usually distributed equally among the
rows of the table, the fullness of each page can vary over
time causing fragments.For queries that scan part or all of a table, such
table fragmentation can cause additional page reads,
which hinders parallel scanning of data.
Thanks
Hari
MCDBA
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> I'm a little confused over something that keeps happinging to my database.
> I have a web app that insert a row into a SS2K table every time a page is
viewed. It's an intranet site, and I maybe average one page a second, no
big deal. THe table uses an Indentity field, and has a clustered index on
that Indenty Field alone.
> Most of the time this works fine, absolutely no problems, no slow down,
etc. However, from time to time the insert into this table will "lock up" -
take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> When I look at the tables, using DBCC SHOWCONTIG, the logical
fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
generally takes care of the problem. I've setup a job to run nightly that
runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> Here's my question: This will happen suddenly (i.e. one page request, no
problem, the next, lock up). Why does my index fragment so rapidly? I
would expect a "build up". Could something else be going on that I'm
missing?
|||Not sure if that is his problem. He says that he has an identity column,
which is clustered. This means that each insert will be added to the end of
the table. Maybe the hang is occurring when the database needs to grow.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u2mp%23t$bEHA.212@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi,
> Info from Books online:-
> Table fragmentation occurs through the process of data modifications
> (INSERT, UPDATE, and DELETE statements) made against the table.
> Because these modifications are not usually distributed equally among the
> rows of the table, the fullness of each page can vary over
> time causing fragments.For queries that scan part or all of a table, such
> table fragmentation can cause additional page reads,
> which hinders parallel scanning of data.
> Thanks
> Hari
> MCDBA
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
database.[vbcol=seagreen]
is
> viewed. It's an intranet site, and I maybe average one page a second, no
> big deal. THe table uses an Indentity field, and has a clustered index on
> that Indenty Field alone.
> etc. However, from time to time the insert into this table will "lock
up" -[vbcol=seagreen]
> take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
> generally takes care of the problem. I've setup a job to run nightly that
> runs the following sql command: DBCC DBREINDEX ('Activity','',70).
no
> problem, the next, lock up). Why does my index fragment so rapidly? I
> would expect a "build up". Could something else be going on that I'm
> missing?
>
|||But why does my table fragment so quickly? I have approx. 400,000 records in the table, with, on average, about 1 row being added a second and it appears to go from being < 1% fragment to > 90% in a matter of seconds (all after running fine for hours / d
ays without reindexing).
THe problem is that all of the sudden the website goes from working fine to completely shut down in a matter of seconds.
"Hari Prasad" wrote:

> Hi,
> Info from Books online:-
> Table fragmentation occurs through the process of data modifications
> (INSERT, UPDATE, and DELETE statements) made against the table.
> Because these modifications are not usually distributed equally among the
> rows of the table, the fullness of each page can vary over
> time causing fragments.For queries that scan part or all of a table, such
> table fragmentation can cause additional page reads,
> which hinders parallel scanning of data.
> Thanks
> Hari
> MCDBA
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> viewed. It's an intranet site, and I maybe average one page a second, no
> big deal. THe table uses an Indentity field, and has a clustered index on
> that Indenty Field alone.
> etc. However, from time to time the insert into this table will "lock up" -
> take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
> generally takes care of the problem. I've setup a job to run nightly that
> runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> problem, the next, lock up). Why does my index fragment so rapidly? I
> would expect a "build up". Could something else be going on that I'm
> missing?
>
>
|||looks like your fill factor is set to 70% ? knock it down to say 50% and see
if that helps.
Greg Jackson
PDX, Oregon
|||Perhaps it has less to do with the fragmentation than when
your pad index/fill factor gets to 100%. When you hit
100% one add'l row will cause a cascade, won't it?
>--Original Message--
>But why does my table fragment so quickly? I have
approx. 400,000 records in the table, with, on average,
about 1 row being added a second and it appears to go from
being < 1% fragment to > 90% in a matter of seconds (all
after running fine for hours / days without reindexing).
>THe problem is that all of the sudden the website goes
from working fine to completely shut down in a matter of
seconds.[vbcol=seagreen]
>"Hari Prasad" wrote:
modifications[vbcol=seagreen]
the table.[vbcol=seagreen]
equally among the[vbcol=seagreen]
over[vbcol=seagreen]
all of a table, such[vbcol=seagreen]
<KarlPierburg@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
3BD40FE07664@.microsoft.com...[vbcol=seagreen]
happinging to my database.[vbcol=seagreen]
every time a page is[vbcol=seagreen]
page a second, no[vbcol=seagreen]
clustered index on[vbcol=seagreen]
problems, no slow down,[vbcol=seagreen]
table will "lock up" -[vbcol=seagreen]
shuts down my website.[vbcol=seagreen]
logical[vbcol=seagreen]
INDEXDEFRAG, and that[vbcol=seagreen]
to run nightly that[vbcol=seagreen]
('Activity','',70).[vbcol=seagreen]
one page request, no[vbcol=seagreen]
fragment so rapidly? I[vbcol=seagreen]
going on that I'm
>.
>
|||Karl,
Are you sure there isn't a shrink operation going on? DO you have a Job
scheduled to do a shrink or worse yet is AutoShrink turned on? A clustered
index on an Identity column will not cause splits or fragmentation with just
inserts. Do you update these rows after they are inserted?
Andrew J. Kelly SQL MVP
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:1671C010-B598-4EC4-8225-0FBAE90CB611@.microsoft.com...
> But why does my table fragment so quickly? I have approx. 400,000 records
in the table, with, on average, about 1 row being added a second and it
appears to go from being < 1% fragment to > 90% in a matter of seconds (all
after running fine for hours / days without reindexing).
> THe problem is that all of the sudden the website goes from working fine
to completely shut down in a matter of seconds.[vbcol=seagreen]
> "Hari Prasad" wrote:
the[vbcol=seagreen]
such[vbcol=seagreen]
message[vbcol=seagreen]
database.[vbcol=seagreen]
is[vbcol=seagreen]
no[vbcol=seagreen]
on[vbcol=seagreen]
down,[vbcol=seagreen]
up" -[vbcol=seagreen]
website.[vbcol=seagreen]
that[vbcol=seagreen]
no[vbcol=seagreen]
|||Decreasing fill factor will not help at all, since all new inserts are going
to the last page...
Do as Andew says, a shrink will definitely frag up a table...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> I'm a little confused over something that keeps happinging to my database.
> I have a web app that insert a row into a SS2K table every time a page is
viewed. It's an intranet site, and I maybe average one page a second, no
big deal. THe table uses an Indentity field, and has a clustered index on
that Indenty Field alone.
> Most of the time this works fine, absolutely no problems, no slow down,
etc. However, from time to time the insert into this table will "lock up" -
take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> When I look at the tables, using DBCC SHOWCONTIG, the logical
fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
generally takes care of the problem. I've setup a job to run nightly that
runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> Here's my question: This will happen suddenly (i.e. one page request, no
problem, the next, lock up). Why does my index fragment so rapidly? I
would expect a "build up". Could something else be going on that I'm
missing?
|||Andrew-
Actually, I do do a DBCC SHRINKDATABASE every night as part of my nightly processing. I just read a post that you were involved with that basically said "don't do that".
I will take it out. It's just a weird problem that happens with no regularity.
These rows are NEVER updated or deleted. Some basic read-only reporting is all that takes place.
Could you comment on 2 things:
1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on INDENTITY fields. It seems to me that I would want to speficy a fill factor of 100%, since I'll never do any inserts earlier in the page / extent?
"Andrew J. Kelly" wrote:

> Karl,
> Are you sure there isn't a shrink operation going on? DO you have a Job
> scheduled to do a shrink or worse yet is AutoShrink turned on? A clustered
> index on an Identity column will not cause splits or fragmentation with just
> inserts. Do you update these rows after they are inserted?
> --
> Andrew J. Kelly SQL MVP
>
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:1671C010-B598-4EC4-8225-0FBAE90CB611@.microsoft.com...
> in the table, with, on average, about 1 row being added a second and it
> appears to go from being < 1% fragment to > 90% in a matter of seconds (all
> after running fine for hours / days without reindexing).
> to completely shut down in a matter of seconds.
> the
> such
> message
> database.
> is
> no
> on
> down,
> up" -
> website.
> that
> no
>
>
|||> 1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
To shrink the file it must move any pages at the end of the physical file to
someplace near the beginning since the shrink happens from the end inward.
This for one is a very expensive operation in terms of resources and
logging. But chances are after the move the data that was so nicely
defragged and contiguous earlier (by the reindexing) is now spread all over
the file where ever SQL Server had a place to put the extents. This is
usually mixed in amongst all the other extents and causes extent
fragmentation. But then later that night you reindex the tables again and
this forces the database to grow and starts the whole process all over
again. Put lots of free space in the data files and leave it there.

> 2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on
INDENTITY fields. It seems to me that I would want to specify a fill factor
of 100%, since I'll never do any inserts earlier in the page / extent?
Yes in your case you probably do want 100%. The new rows will be appended
and will never grow. This is an ideal situation for keeping the
fragmentation and reads to a minimum. I have to believe Autoshrink was
kicking in and not only freezing your database (so it seemed) but
fragmenting the tables as well.
Andrew J. Kelly SQL MVP
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:852300A1-859C-41DC-A047-F12C87F61D11@.microsoft.com...
> Andrew-
> Actually, I do do a DBCC SHRINKDATABASE every night as part of my nightly
processing. I just read a post that you were involved with that basically
said "don't do that".
> I will take it out. It's just a weird problem that happens with no
regularity.
> These rows are NEVER updated or deleted. Some basic read-only reporting
is all that takes place.
> Could you comment on 2 things:
> 1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
> 2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on INDEN
TITY fields. It seems to me that I would want to speficy a fill factor of
100%, since I'll never do any inserts earlier in the page / extent?[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
clustered[vbcol=seagreen]
just[vbcol=seagreen]
message[vbcol=seagreen]
records[vbcol=seagreen]
(all[vbcol=seagreen]
fine[vbcol=seagreen]
among[vbcol=seagreen]
page[vbcol=seagreen]
second,[vbcol=seagreen]
index[vbcol=seagreen]
"lock[vbcol=seagreen]
that[vbcol=seagreen]
nightly[vbcol=seagreen]
request,[vbcol=seagreen]
rapidly? I[vbcol=seagreen]
I'm[vbcol=seagreen]

Index Fragementing Rapidly

I'm a little confused over something that keeps happinging to my database.
I have a web app that insert a row into a SS2K table every time a page is vi
ewed. It's an intranet site, and I maybe average one page a second, no big
deal. THe table uses an Indentity field, and has a clustered index on that
Indenty Field alone.
Most of the time this works fine, absolutely no problems, no slow down, etc.
However, from time to time the insert into this table will "lock up" - tak
e 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
When I look at the tables, using DBCC SHOWCONTIG, the logical fragmentation
is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that generally takes care
of the problem. I've setup a job to run nightly that runs the following sq
l command: DBCC DBREINDEX (
'Activity','',70).
Here's my question: This will happen suddenly (i.e. one page request, no pr
oblem, the next, lock up). Why does my index fragment so rapidly? I would
expect a "build up". Could something else be going on that I'm missing?Hi,
Info from Books online:-
Table fragmentation occurs through the process of data modifications
(INSERT, UPDATE, and DELETE statements) made against the table.
Because these modifications are not usually distributed equally among the
rows of the table, the fullness of each page can vary over
time causing fragments.For queries that scan part or all of a table, such
table fragmentation can cause additional page reads,
which hinders parallel scanning of data.
Thanks
Hari
MCDBA
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> I'm a little confused over something that keeps happinging to my database.
> I have a web app that insert a row into a SS2K table every time a page is
viewed. It's an intranet site, and I maybe average one page a second, no
big deal. THe table uses an Indentity field, and has a clustered index on
that Indenty Field alone.
> Most of the time this works fine, absolutely no problems, no slow down,
etc. However, from time to time the insert into this table will "lock up" -
take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> When I look at the tables, using DBCC SHOWCONTIG, the logical
fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
generally takes care of the problem. I've setup a job to run nightly that
runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> Here's my question: This will happen suddenly (i.e. one page request, no
problem, the next, lock up). Why does my index fragment so rapidly? I
would expect a "build up". Could something else be going on that I'm
missing?|||Not sure if that is his problem. He says that he has an identity column,
which is clustered. This means that each insert will be added to the end of
the table. Maybe the hang is occurring when the database needs to grow.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u2mp%23t$bEHA.212@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Info from Books online:-
> Table fragmentation occurs through the process of data modifications
> (INSERT, UPDATE, and DELETE statements) made against the table.
> Because these modifications are not usually distributed equally among the
> rows of the table, the fullness of each page can vary over
> time causing fragments.For queries that scan part or all of a table, such
> table fragmentation can cause additional page reads,
> which hinders parallel scanning of data.
> Thanks
> Hari
> MCDBA
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
database.[vbcol=seagreen]
is[vbcol=seagreen]
> viewed. It's an intranet site, and I maybe average one page a second, no
> big deal. THe table uses an Indentity field, and has a clustered index on
> that Indenty Field alone.
> etc. However, from time to time the insert into this table will "lock
up" -
> take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
> generally takes care of the problem. I've setup a job to run nightly that
> runs the following sql command: DBCC DBREINDEX ('Activity','',70).
no[vbcol=seagreen]
> problem, the next, lock up). Why does my index fragment so rapidly? I
> would expect a "build up". Could something else be going on that I'm
> missing?
>|||But why does my table fragment so quickly? I have approx. 400,000 records i
n the table, with, on average, about 1 row being added a second and it appea
rs to go from being < 1% fragment to > 90% in a matter of seconds (all after
running fine for hours / d
ays without reindexing).
THe problem is that all of the sudden the website goes from working fine to
completely shut down in a matter of seconds.
"Hari Prasad" wrote:

> Hi,
> Info from Books online:-
> Table fragmentation occurs through the process of data modifications
> (INSERT, UPDATE, and DELETE statements) made against the table.
> Because these modifications are not usually distributed equally among the
> rows of the table, the fullness of each page can vary over
> time causing fragments.For queries that scan part or all of a table, such
> table fragmentation can cause additional page reads,
> which hinders parallel scanning of data.
> Thanks
> Hari
> MCDBA
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> viewed. It's an intranet site, and I maybe average one page a second, no
> big deal. THe table uses an Indentity field, and has a clustered index on
> that Indenty Field alone.
> etc. However, from time to time the insert into this table will "lock up"
-
> take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
> generally takes care of the problem. I've setup a job to run nightly that
> runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> problem, the next, lock up). Why does my index fragment so rapidly? I
> would expect a "build up". Could something else be going on that I'm
> missing?
>
>|||looks like your fill factor is set to 70% ? knock it down to say 50% and see
if that helps.
Greg Jackson
PDX, Oregon|||Perhaps it has less to do with the fragmentation than when
your pad index/fill factor gets to 100%. When you hit
100% one add'l row will cause a cascade, won't it?
>--Original Message--
>But why does my table fragment so quickly? I have
approx. 400,000 records in the table, with, on average,
about 1 row being added a second and it appears to go from
being < 1% fragment to > 90% in a matter of seconds (all
after running fine for hours / days without reindexing).
>THe problem is that all of the sudden the website goes
from working fine to completely shut down in a matter of
seconds.
>"Hari Prasad" wrote:
>
modifications[vbcol=seagreen]
the table.[vbcol=seagreen]
equally among the[vbcol=seagreen]
over[vbcol=seagreen]
all of a table, such[vbcol=seagreen]
<KarlPierburg@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
3BD40FE07664@.microsoft.com...[vbcol=seagreen]
happinging to my database.[vbcol=seagreen]
every time a page is[vbcol=seagreen]
page a second, no[vbcol=seagreen]
clustered index on[vbcol=seagreen]
problems, no slow down,[vbcol=seagreen]
table will "lock up" -[vbcol=seagreen]
shuts down my website.[vbcol=seagreen]
logical[vbcol=seagreen]
INDEXDEFRAG, and that[vbcol=seagreen]
to run nightly that[vbcol=seagreen]
('Activity','',70).[vbcol=seagreen]
one page request, no[vbcol=seagreen]
fragment so rapidly? I[vbcol=seagreen]
going on that I'm[vbcol=seagreen]
>.
>|||Karl,
Are you sure there isn't a shrink operation going on? DO you have a Job
scheduled to do a shrink or worse yet is AutoShrink turned on? A clustered
index on an Identity column will not cause splits or fragmentation with just
inserts. Do you update these rows after they are inserted?
Andrew J. Kelly SQL MVP
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:1671C010-B598-4EC4-8225-0FBAE90CB611@.microsoft.com...
> But why does my table fragment so quickly? I have approx. 400,000 records
in the table, with, on average, about 1 row being added a second and it
appears to go from being < 1% fragment to > 90% in a matter of seconds (all
after running fine for hours / days without reindexing).
> THe problem is that all of the sudden the website goes from working fine
to completely shut down in a matter of seconds.[vbcol=seagreen]
> "Hari Prasad" wrote:
>
the[vbcol=seagreen]
such[vbcol=seagreen]
message[vbcol=seagreen]
database.[vbcol=seagreen]
is[vbcol=seagreen]
no[vbcol=seagreen]
on[vbcol=seagreen]
down,[vbcol=seagreen]
up" -[vbcol=seagreen]
website.[vbcol=seagreen]
that[vbcol=seagreen]
no[vbcol=seagreen]|||Decreasing fill factor will not help at all, since all new inserts are going
to the last page...
Do as Andew says, a shrink will definitely frag up a table...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:A957CF4F-0820-4628-84D6-3BD40FE07664@.microsoft.com...
> I'm a little confused over something that keeps happinging to my database.
> I have a web app that insert a row into a SS2K table every time a page is
viewed. It's an intranet site, and I maybe average one page a second, no
big deal. THe table uses an Indentity field, and has a clustered index on
that Indenty Field alone.
> Most of the time this works fine, absolutely no problems, no slow down,
etc. However, from time to time the insert into this table will "lock up" -
take 1 - 1 1/2 minutes to perform, which obviously shuts down my website.
> When I look at the tables, using DBCC SHOWCONTIG, the logical
fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and that
generally takes care of the problem. I've setup a job to run nightly that
runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> Here's my question: This will happen suddenly (i.e. one page request, no
problem, the next, lock up). Why does my index fragment so rapidly? I
would expect a "build up". Could something else be going on that I'm
missing?|||Andrew-
Actually, I do do a DBCC SHRINKDATABASE every night as part of my nightly pr
ocessing. I just read a post that you were involved with that basically sai
d "don't do that".
I will take it out. It's just a weird problem that happens with no regular
ity.
These rows are NEVER updated or deleted. Some basic read-only reporting is
all that takes place.
Could you comment on 2 things:
1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on INDENTI
TY fields. It seems to me that I would want to speficy a fill factor of 100
%, since I'll never do any inserts earlier in the page / extent?
"Andrew J. Kelly" wrote:

> Karl,
> Are you sure there isn't a shrink operation going on? DO you have a Job
> scheduled to do a shrink or worse yet is AutoShrink turned on? A clustere
d
> index on an Identity column will not cause splits or fragmentation with ju
st
> inserts. Do you update these rows after they are inserted?
> --
> Andrew J. Kelly SQL MVP
>
> "Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
> news:1671C010-B598-4EC4-8225-0FBAE90CB611@.microsoft.com...
> in the table, with, on average, about 1 row being added a second and it
> appears to go from being < 1% fragment to > 90% in a matter of seconds (al
l
> after running fine for hours / days without reindexing).
> to completely shut down in a matter of seconds.
> the
> such
> message
> database.
> is
> no
> on
> down,
> up" -
> website.
> that
> no
>
>|||> 1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
To shrink the file it must move any pages at the end of the physical file to
someplace near the beginning since the shrink happens from the end inward.
This for one is a very expensive operation in terms of resources and
logging. But chances are after the move the data that was so nicely
defragged and contiguous earlier (by the reindexing) is now spread all over
the file where ever SQL Server had a place to put the extents. This is
usually mixed in amongst all the other extents and causes extent
fragmentation. But then later that night you reindex the tables again and
this forces the database to grow and starts the whole process all over
again. Put lots of free space in the data files and leave it there.

> 2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on
INDENTITY fields. It seems to me that I would want to specify a fill factor
of 100%, since I'll never do any inserts earlier in the page / extent?
Yes in your case you probably do want 100%. The new rows will be appended
and will never grow. This is an ideal situation for keeping the
fragmentation and reads to a minimum. I have to believe Autoshrink was
kicking in and not only freezing your database (so it seemed) but
fragmenting the tables as well.
Andrew J. Kelly SQL MVP
"Karl Pierburg" <KarlPierburg@.discussions.microsoft.com> wrote in message
news:852300A1-859C-41DC-A047-F12C87F61D11@.microsoft.com...
> Andrew-
> Actually, I do do a DBCC SHRINKDATABASE every night as part of my nightly
processing. I just read a post that you were involved with that basically
said "don't do that".
> I will take it out. It's just a weird problem that happens with no
regularity.
> These rows are NEVER updated or deleted. Some basic read-only reporting
is all that takes place.
> Could you comment on 2 things:
> 1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
> 2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on INDEN
TITY fields. It seems to me that I would want to speficy a fill factor of
100%, since I'll never do any inserts earlier in the page / extent?[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
>
clustered[vbcol=seagreen]
just[vbcol=seagreen]
message[vbcol=seagreen]
records[vbcol=seagreen]
(all[vbcol=seagreen]
fine[vbcol=seagreen]
among[vbcol=seagreen]
page[vbcol=seagreen]
second,[vbcol=seagreen]
index[vbcol=seagreen]
"lock[vbcol=seagreen]
that[vbcol=seagreen]
nightly[vbcol=seagreen]
request,[vbcol=seagreen]
rapidly? I[vbcol=seagreen]
I'm[vbcol=seagreen]