Extremely large db and blocking

Hi-
I'm a developer on a team with a 200GB db. We inherited it, it definitely
has design problems. About 3 weeks ago we started seeing a lot of blocking
on our largest table which has affected our systems. We made no changes at
the time the blocking started occurring. The db has been growing about 20
GB/month, and we are re-indexing every weekend. We have significant hardware
to support the database, and our DBAs do not believe it to be
hardware-related.
Is it possible that the SQL Server engine has problems when the db reaches
such a large size? Has anyone else experienced a similar situation or could
offer up ideas?
Thx,
JanHi
How are your indexes laid out compared to the database design? Is there a
clustered index on a very selectable column? Maybe posting the DDL of the
table would help us define a solution.
On large tables, a wrong clustered index can cuase havoc with blocking.
I am running multiple 5Tb databases, on 8Gb RAM servers, with no issues.
Regards
Mike
"Jan" wrote:
> Hi-
> I'm a developer on a team with a 200GB db. We inherited it, it definitely
> has design problems. About 3 weeks ago we started seeing a lot of blocking
> on our largest table which has affected our systems. We made no changes at
> the time the blocking started occurring. The db has been growing about 20
> GB/month, and we are re-indexing every weekend. We have significant hardware
> to support the database, and our DBAs do not believe it to be
> hardware-related.
> Is it possible that the SQL Server engine has problems when the db reaches
> such a large size? Has anyone else experienced a similar situation or could
> offer up ideas?
> Thx,
> Jan|||Thanks for the reply. Wow- 5TB, very impressive!
We do have clustered indexes. Though not exactly the script (security
reasons), this might give you an idea:
CREATE TABLE [dbo].[PROBTBL] (
[RNO] [int] IDENTITY (1, 1) NOT NULL ,
[KNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ANOTHKNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ATYPEKIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LNITEMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACUSTID] [int] NULL ,
[VID] [int] NULL ,
[VNUM] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BR1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BR2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CTR] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CNTY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LTYPE] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OP] [datetime] NULL ,
[CLS] [datetime] NULL ,
[CLSCODE] [int] NULL ,
[PRN] [decimal](11, 2) NULL ,
[MNAMOUNT] [decimal](11, 2) NULL ,
[LTCHG] [decimal](11, 2) NULL ,
[LTPCT] [decimal](5, 2) NULL ,
[CANCL] [datetime] NULL ,
[TERM] [datetime] NULL ,
[STS] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[S1] [int] NULL ,
[S2] [int] NULL ,
[INTPRD] [decimal](7, 2) NULL ,
[SRVPRD] [decimal](7, 2) NULL ,
[ORIG] [decimal](11, 2) NULL ,
[LDESC] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MLADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MLADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MLCTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MLST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MLZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IRTE] [decimal](6, 3) NULL ,
[DEF] [datetime] NULL ,
[RFL] [datetime] NULL ,
[INVID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INVNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IVPRCT] [decimal](7, 3) NULL ,
[INRID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INSNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INSPCTT] [decimal](7, 3) NULL ,
[INSCRTT] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRTY] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRCSSR] [int] NULL ,
[NXTCD] [int] NULL ,
[NXTDT] [datetime] NULL ,
[LASTACC] [datetime] NULL ,
[ONHLD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ONHLD__4301EA8F] DEFAULT (0),
[IVFEE] [decimal](9, 2) NULL ,
[ISFEE] [decimal](9, 2) NULL ,
[ADDFEEREQ] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADDFEE__43F60EC8]
DEFAULT (0),
[ADFE] [decimal](9, 2) NULL ,
[IVCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ISCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HLDFRBK] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__HOLDFO__44EA3301] DEFAULT
(0),
[CNTSTD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__CONTES__45DE573A] DEFAULT
(0),
[VNDPRD] [int] NULL ,
[SVNXTCDE] [int] NULL ,
[SVNXTDT] [datetime] NULL ,
[DJSTBL] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADJUST__46D27B73] DEFAULT
(0),
[XTCHNG] [datetime] NULL ,
[SCRWNL] [datetime] NULL ,
[BKNTFY] [datetime] NULL ,
[PRPTND] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RGNT] [datetime] NULL ,
[MTRT] [datetime] NULL ,
[SRVCLS] [datetime] NULL ,
[NTS] [int] NULL ,
[NVSTD] [int] NULL ,
[PTYP] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PSN] [int] NULL ,
[MRSN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LstMdfd] [datetime] NULL ,
[LstMdfdDTTM] [datetime] NULL ,
[Lngnd] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_PROBTBL] PRIMARY KEY CLUSTERED
(
[KNO]
) WITH FILLFACTOR = 40 ON [PRIMARY] ,
CONSTRAINT [FK_Lngnd] FOREIGN KEY
(
[Lngnd]
) REFERENCES [LngndDefinition] (
[Lngnd]
)
) ON [PRIMARY] TEXTIMAGE_ON [TEXT3]
GO
CREATE INDEX [IXC_PROBTBL] ON [dbo].[PROBTBL]([KNO], [VID]) WITH
FILLFACTOR = 90 ON [INDEXES2]
GO
CREATE INDEX [RNO_IDX] ON [dbo].[PROBTBL]([RNO]) WITH FILLFACTOR = 90 ON
[INDEXES2]
GO
CREATE INDEX [Clt_IDX] ON [dbo].[PROBTBL]([ACUSTID]) WITH FILLFACTOR = 90
ON [INDEXES]
GO
CREATE INDEX [LNITEMNO_IDX] ON [dbo].[PROBTBL]([LNITEMNO]) WITH
FILLFACTOR = 90 ON [INDEXES]
GO
CREATE INDEX [[tIDX_PROBTBL_COMPOSITE] ON [dbo].[PROBTBL]([ACUSTID],
[VNUM], [KNO], [ATYPEKIND], [LNITEMNO]) WITH FILLFACTOR = 90 ON [INDEXES2]
GO
CREATE INDEX [Vdors_IDX] ON [dbo].[PROBTBL]([VID]) WITH FILLFACTOR = 90
ON [INDEXES2]
GO
CREATE INDEX [SRVCLS_IDX] ON [dbo].[PROBTBL]([SRVCLS]) WITH FILLFACTOR =90 ON [INDEXES]
GO
CREATE INDEX [VNUM_IDX] ON [dbo].[PROBTBL]([VNUM]) WITH FILLFACTOR = 90
ON [INDEXES]
GO
CREATE INDEX [ADDRESS1_IDX] ON [dbo].[PROBTBL]([ADDRESS1]) WITH
FILLFACTOR = 90 ON [INDEXES2]
GO
CREATE INDEX [NVSTD_IDX] ON [dbo].[PROBTBL]([NVSTD]) WITH FILLFACTOR = 90
ON [INDEXES]
GO
CREATE INDEX [tIDX_PROBTBL_KNO_ACUSTID] ON [dbo].[PROBTBL]([KNO],
[ACUSTID]) WITH FILLFACTOR = 90 ON [INDEXES]
GO
CREATE INDEX [INVNMNO_IDX] ON [dbo].[PROBTBL]([INVNMNO]) WITH FILLFACTOR
= 90 ON [INDEXES2]
GO
CREATE INDEX [CTR_IDX] ON [dbo].[PROBTBL]([CTR]) WITH FILLFACTOR = 90 ON
[INDEXES2]
GO
CREATE INDEX [LastModified_IDX] ON [dbo].[PROBTBL]([LastModified]) WITH
FILLFACTOR = 90 ON [INDEXES]
GO
CREATE INDEX [IDX_TSBodyAtt] ON [dbo].[PROBTBL]([VID], [NVSTD],
[ATYPEKIND]) WITH FILLFACTOR = 90 ON [INDEXES]
GO
CREATE INDEX [tIDX_PROBTBL_ST_CLS] ON [dbo].[PROBTBL]([ST], [CLS]) WITH
FILLFACTOR = 90 ON [INDEXES2]
GO
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> How are your indexes laid out compared to the database design? Is there a
> clustered index on a very selectable column? Maybe posting the DDL of the
> table would help us define a solution.|||You should really find the source of the block. For example, is it a query
an update or an insert? Is the block caused by (say) small "X" locks, or an
"S" lock at the table level. One bad query plan can cause an index scan
which in turn can cause an "S" lock on the table; disaster!
There is some information on obtaining the "block chain" that you could find
using a Google search. I found a script which identifies the blocking
connection, all blocked connections, and query buffer contents for the
blocker (no always helpfull, but sometimes) and I have found this informatin
indespensible.
Without knowing the real cause and nature of the blocking, you might miss
the best solution.|||Jan wrote:
> Thanks for the reply. Wow- 5TB, very impressive!
> We do have clustered indexes. Though not exactly the script (security
> reasons), this might give you an idea:
> CREATE TABLE [dbo].[PROBTBL] (
> [RNO] [int] IDENTITY (1, 1) NOT NULL ,
> [KNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ANOTHKNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ATYPEKIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LNITEMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ACUSTID] [int] NULL ,
> [VID] [int] NULL ,
> [VNUM] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BR1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BR2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CTR] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CNTY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LTYPE] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [OP] [datetime] NULL ,
> [CLS] [datetime] NULL ,
> [CLSCODE] [int] NULL ,
> [PRN] [decimal](11, 2) NULL ,
> [MNAMOUNT] [decimal](11, 2) NULL ,
> [LTCHG] [decimal](11, 2) NULL ,
> [LTPCT] [decimal](5, 2) NULL ,
> [CANCL] [datetime] NULL ,
> [TERM] [datetime] NULL ,
> [STS] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [S1] [int] NULL ,
> [S2] [int] NULL ,
> [INTPRD] [decimal](7, 2) NULL ,
> [SRVPRD] [decimal](7, 2) NULL ,
> [ORIG] [decimal](11, 2) NULL ,
> [LDESC] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MLADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> , [MLADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL , [MLCTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL , [MLST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MLZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [IRTE] [decimal](6, 3) NULL ,
> [DEF] [datetime] NULL ,
> [RFL] [datetime] NULL ,
> [INVID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [INVNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [IVPRCT] [decimal](7, 3) NULL ,
> [INRID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [INSNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [INSPCTT] [decimal](7, 3) NULL ,
> [INSCRTT] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PRTY] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PRCSSR] [int] NULL ,
> [NXTCD] [int] NULL ,
> [NXTDT] [datetime] NULL ,
> [LASTACC] [datetime] NULL ,
> [ONHLD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ONHLD__4301EA8F]
> DEFAULT (0), [IVFEE] [decimal](9, 2) NULL ,
> [ISFEE] [decimal](9, 2) NULL ,
> [ADDFEEREQ] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADDFEE__43F60EC8]
> DEFAULT (0),
> [ADFE] [decimal](9, 2) NULL ,
> [IVCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ISCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HLDFRBK] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__HOLDFO__44EA3301]
> DEFAULT (0),
> [CNTSTD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__CONTES__45DE573A]
> DEFAULT (0),
> [VNDPRD] [int] NULL ,
> [SVNXTCDE] [int] NULL ,
> [SVNXTDT] [datetime] NULL ,
> [DJSTBL] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADJUST__46D27B73]
> DEFAULT (0),
> [XTCHNG] [datetime] NULL ,
> [SCRWNL] [datetime] NULL ,
> [BKNTFY] [datetime] NULL ,
> [PRPTND] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RGNT] [datetime] NULL ,
> [MTRT] [datetime] NULL ,
> [SRVCLS] [datetime] NULL ,
> [NTS] [int] NULL ,
> [NVSTD] [int] NULL ,
> [PTYP] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PSN] [int] NULL ,
> [MRSN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LstMdfd] [datetime] NULL ,
> [LstMdfdDTTM] [datetime] NULL ,
> [Lngnd] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_PROBTBL] PRIMARY KEY CLUSTERED
> (
> [KNO]
> ) WITH FILLFACTOR = 40 ON [PRIMARY] ,
> CONSTRAINT [FK_Lngnd] FOREIGN KEY
> (
> [Lngnd]
> ) REFERENCES [LngndDefinition] (
> [Lngnd]
> )
> ) ON [PRIMARY] TEXTIMAGE_ON [TEXT3]
> GO
> CREATE INDEX [IXC_PROBTBL] ON [dbo].[PROBTBL]([KNO], [VID]) WITH
> FILLFACTOR = 90 ON [INDEXES2]
> GO
> CREATE INDEX [RNO_IDX] ON [dbo].[PROBTBL]([RNO]) WITH FILLFACTOR => 90 ON [INDEXES2]
> GO
> CREATE INDEX [Clt_IDX] ON [dbo].[PROBTBL]([ACUSTID]) WITH
> FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [LNITEMNO_IDX] ON [dbo].[PROBTBL]([LNITEMNO]) WITH
> FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [[tIDX_PROBTBL_COMPOSITE] ON [dbo].[PROBTBL]([ACUSTID],
> [VNUM], [KNO], [ATYPEKIND], [LNITEMNO]) WITH FILLFACTOR = 90 ON
> [INDEXES2] GO
> CREATE INDEX [Vdors_IDX] ON [dbo].[PROBTBL]([VID]) WITH FILLFACTOR
> = 90
> ON [INDEXES2]
> GO
> CREATE INDEX [SRVCLS_IDX] ON [dbo].[PROBTBL]([SRVCLS]) WITH
> FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [VNUM_IDX] ON [dbo].[PROBTBL]([VNUM]) WITH FILLFACTOR
> = 90
> ON [INDEXES]
> GO
> CREATE INDEX [ADDRESS1_IDX] ON [dbo].[PROBTBL]([ADDRESS1]) WITH
> FILLFACTOR = 90 ON [INDEXES2]
> GO
> CREATE INDEX [NVSTD_IDX] ON [dbo].[PROBTBL]([NVSTD]) WITH
> FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [tIDX_PROBTBL_KNO_ACUSTID] ON [dbo].[PROBTBL]([KNO],
> [ACUSTID]) WITH FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [INVNMNO_IDX] ON [dbo].[PROBTBL]([INVNMNO]) WITH
> FILLFACTOR = 90 ON [INDEXES2]
> GO
> CREATE INDEX [CTR_IDX] ON [dbo].[PROBTBL]([CTR]) WITH FILLFACTOR => 90 ON [INDEXES2]
> GO
> CREATE INDEX [LastModified_IDX] ON [dbo].[PROBTBL]([LastModified])
> WITH FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [IDX_TSBodyAtt] ON [dbo].[PROBTBL]([VID], [NVSTD],
> [ATYPEKIND]) WITH FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [tIDX_PROBTBL_ST_CLS] ON [dbo].[PROBTBL]([ST], [CLS])
> WITH FILLFACTOR = 90 ON [INDEXES2]
> GO
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> How are your indexes laid out compared to the database design? Is
>> there a clustered index on a very selectable column? Maybe posting
>> the DDL of the table would help us define a solution.
I would recommend some performance tuning on the server. There are a
number of products out there that may be able to point you to the
problem. You could also try using Profiler to see what SQL is taking a
lot of CPU or has long durations.
That looks like a large table with very wide rows. If you are reindexing
weekly, you may consider changing your fill factor so you don't leave
10% free space. That is, if you really don't require that much free
space for the inserts each week.
Plus your PK has a fill factor of 40!!!. That's means you are severly
degrading read performance. Probably more than 2X. You are in effect
increasing the size of the table by about 225%, causing all reads to
lock more than twice as many pages, causing updates to do the same, and
making your disks work so much harder to get at the data.
If you are reindexing every week, then you may want to leave the fill
factor at the default settings, or at least fill the pages 95% full.
Only highly transactional tables with clustered indexes that force page
breaks should really be using a lower fill factor.
I suspect that's a large portion of the problem.
David Gugick
Imceda Software
www.imceda.com|||To add to what David wrote.
You have a lot of indexes, the index maintenance overhead is big in such a
case. This can have horrible effects on blocking. Do you really need all
those indexes?
The databases we hit with such big volumes are fully normalized, with no
table exceeding 20 columns.
Your maximum row width would hit 600 bytes, without the TEXT datatype, so
having a fill factor of 40% on your clustered index really wastes space in
the cache and results in more I/O than necessary.
Also check that Auto update statistics is on for the DB (and especially on
big DB's like this, Torn Page Detection On)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OzZllPJ3EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Jan wrote:
> > Thanks for the reply. Wow- 5TB, very impressive!
> >
> > We do have clustered indexes. Though not exactly the script (security
> > reasons), this might give you an idea:
> >
> > CREATE TABLE [dbo].[PROBTBL] (
> > [RNO] [int] IDENTITY (1, 1) NOT NULL ,
> > [KNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [ANOTHKNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ATYPEKIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [LNITEMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ACUSTID] [int] NULL ,
> > [VID] [int] NULL ,
> > [VNUM] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [BR1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [BR2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [CTR] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [CTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [CNTY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [LTYPE] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [OP] [datetime] NULL ,
> > [CLS] [datetime] NULL ,
> > [CLSCODE] [int] NULL ,
> > [PRN] [decimal](11, 2) NULL ,
> > [MNAMOUNT] [decimal](11, 2) NULL ,
> > [LTCHG] [decimal](11, 2) NULL ,
> > [LTPCT] [decimal](5, 2) NULL ,
> > [CANCL] [datetime] NULL ,
> > [TERM] [datetime] NULL ,
> > [STS] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [S1] [int] NULL ,
> > [S2] [int] NULL ,
> > [INTPRD] [decimal](7, 2) NULL ,
> > [SRVPRD] [decimal](7, 2) NULL ,
> > [ORIG] [decimal](11, 2) NULL ,
> > [LDESC] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [MLADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > , [MLADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL , [MLCTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL , [MLST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [MLZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [PPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [BPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [IRTE] [decimal](6, 3) NULL ,
> > [DEF] [datetime] NULL ,
> > [RFL] [datetime] NULL ,
> > [INVID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [INVNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [IVPRCT] [decimal](7, 3) NULL ,
> > [INRID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [INSNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [INSPCTT] [decimal](7, 3) NULL ,
> > [INSCRTT] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [PRTY] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [PRCSSR] [int] NULL ,
> > [NXTCD] [int] NULL ,
> > [NXTDT] [datetime] NULL ,
> > [LASTACC] [datetime] NULL ,
> > [ONHLD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ONHLD__4301EA8F]
> > DEFAULT (0), [IVFEE] [decimal](9, 2) NULL ,
> > [ISFEE] [decimal](9, 2) NULL ,
> > [ADDFEEREQ] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADDFEE__43F60EC8]
> > DEFAULT (0),
> > [ADFE] [decimal](9, 2) NULL ,
> > [IVCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ISCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [HLDFRBK] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__HOLDFO__44EA3301]
> > DEFAULT (0),
> > [CNTSTD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__CONTES__45DE573A]
> > DEFAULT (0),
> > [VNDPRD] [int] NULL ,
> > [SVNXTCDE] [int] NULL ,
> > [SVNXTDT] [datetime] NULL ,
> > [DJSTBL] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADJUST__46D27B73]
> > DEFAULT (0),
> > [XTCHNG] [datetime] NULL ,
> > [SCRWNL] [datetime] NULL ,
> > [BKNTFY] [datetime] NULL ,
> > [PRPTND] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [RGNT] [datetime] NULL ,
> > [MTRT] [datetime] NULL ,
> > [SRVCLS] [datetime] NULL ,
> > [NTS] [int] NULL ,
> > [NVSTD] [int] NULL ,
> > [PTYP] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [PSN] [int] NULL ,
> > [MRSN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [LstMdfd] [datetime] NULL ,
> > [LstMdfdDTTM] [datetime] NULL ,
> > [Lngnd] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > CONSTRAINT [PK_PROBTBL] PRIMARY KEY CLUSTERED
> > (
> > [KNO]
> > ) WITH FILLFACTOR = 40 ON [PRIMARY] ,
> > CONSTRAINT [FK_Lngnd] FOREIGN KEY
> > (
> > [Lngnd]
> > ) REFERENCES [LngndDefinition] (
> > [Lngnd]
> > )
> > ) ON [PRIMARY] TEXTIMAGE_ON [TEXT3]
> > GO
> >
> > CREATE INDEX [IXC_PROBTBL] ON [dbo].[PROBTBL]([KNO], [VID]) WITH
> > FILLFACTOR = 90 ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [RNO_IDX] ON [dbo].[PROBTBL]([RNO]) WITH FILLFACTOR => > 90 ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [Clt_IDX] ON [dbo].[PROBTBL]([ACUSTID]) WITH
> > FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [LNITEMNO_IDX] ON [dbo].[PROBTBL]([LNITEMNO]) WITH
> > FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [[tIDX_PROBTBL_COMPOSITE] ON [dbo].[PROBTBL]([ACUSTID],
> > [VNUM], [KNO], [ATYPEKIND], [LNITEMNO]) WITH FILLFACTOR = 90 ON
> > [INDEXES2] GO
> >
> > CREATE INDEX [Vdors_IDX] ON [dbo].[PROBTBL]([VID]) WITH FILLFACTOR
> > = 90
> > ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [SRVCLS_IDX] ON [dbo].[PROBTBL]([SRVCLS]) WITH
> > FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [VNUM_IDX] ON [dbo].[PROBTBL]([VNUM]) WITH FILLFACTOR
> > = 90
> > ON [INDEXES]
> > GO
> >
> > CREATE INDEX [ADDRESS1_IDX] ON [dbo].[PROBTBL]([ADDRESS1]) WITH
> > FILLFACTOR = 90 ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [NVSTD_IDX] ON [dbo].[PROBTBL]([NVSTD]) WITH
> > FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [tIDX_PROBTBL_KNO_ACUSTID] ON [dbo].[PROBTBL]([KNO],
> > [ACUSTID]) WITH FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [INVNMNO_IDX] ON [dbo].[PROBTBL]([INVNMNO]) WITH
> > FILLFACTOR = 90 ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [CTR_IDX] ON [dbo].[PROBTBL]([CTR]) WITH FILLFACTOR => > 90 ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [LastModified_IDX] ON [dbo].[PROBTBL]([LastModified])
> > WITH FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [IDX_TSBodyAtt] ON [dbo].[PROBTBL]([VID], [NVSTD],
> > [ATYPEKIND]) WITH FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [tIDX_PROBTBL_ST_CLS] ON [dbo].[PROBTBL]([ST], [CLS])
> > WITH FILLFACTOR = 90 ON [INDEXES2]
> > GO
> >
> >
> >
> >
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> >> Hi
> >>
> >> How are your indexes laid out compared to the database design? Is
> >> there a clustered index on a very selectable column? Maybe posting
> >> the DDL of the table would help us define a solution.
> I would recommend some performance tuning on the server. There are a
> number of products out there that may be able to point you to the
> problem. You could also try using Profiler to see what SQL is taking a
> lot of CPU or has long durations.
> That looks like a large table with very wide rows. If you are reindexing
> weekly, you may consider changing your fill factor so you don't leave
> 10% free space. That is, if you really don't require that much free
> space for the inserts each week.
> Plus your PK has a fill factor of 40!!!. That's means you are severly
> degrading read performance. Probably more than 2X. You are in effect
> increasing the size of the table by about 225%, causing all reads to
> lock more than twice as many pages, causing updates to do the same, and
> making your disks work so much harder to get at the data.
> If you are reindexing every week, then you may want to leave the fill
> factor at the default settings, or at least fill the pages 95% full.
> Only highly transactional tables with clustered indexes that force page
> breaks should really be using a lower fill factor.
> I suspect that's a large portion of the problem.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||You have many DateTime fields. You can cut the size of these in half by
converting to SmallDateTime. See BOL.
Paul.
"Jan" <Jan@.discussions.microsoft.com> wrote in message
news:5C690F4A-5E37-4927-9FCC-827D7C412469@.microsoft.com...
> Hi-
> I'm a developer on a team with a 200GB db. We inherited it, it definitely
> has design problems. About 3 weeks ago we started seeing a lot of
> blocking
> on our largest table which has affected our systems. We made no changes
> at
> the time the blocking started occurring. The db has been growing about 20
> GB/month, and we are re-indexing every weekend. We have significant
> hardware
> to support the database, and our DBAs do not believe it to be
> hardware-related.
> Is it possible that the SQL Server engine has problems when the db reaches
> such a large size? Has anyone else experienced a similar situation or
> could
> offer up ideas?
> Thx,
> Jan