August 30, 2002 at 6:24 am
I started seeing a problem with our production system a few months back. It seems we keep getting timeouts reported by the users on one specific table. NOTE: This table only has inserts performed on it(history table). It is up around the 10 million record count at this point and we have a primary key that is non-clustered with a fill factor of 0%. The timeout occurance is very sporadic. It happens now about 7-10 times a day, but not for all of the users. I know there is no dead-locking going on because the time between user's inserts into the database is spread out at least 2 minutes in length.
I had posed a question to our DBA about dropping and recreating the indexes(I don't know whether or not this means dropping the primary key and re-adding it?) with a fill factor of 90%.
Can someone tell me if this probably is going to fix our problem?
Thank you!
David Wiesner
August 30, 2002 at 7:32 am
If there is a clustered index than it is better to have 15-20% fillfactor.....
Non clustered index dosn't affects that much....
you can use dbcc dbreindex to specify and recreate index with fill factor
Deadlocking is there otherwise timeout won't happen.....
anyway can you put the exact error message and which is the application platform you are using?
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
August 30, 2002 at 8:04 am
-2147217871 Timeout Expired is the error message. We are running on a Windows NT platform with SQL Server 7.0.
Here is the table structure:
The problem is with the auddiofields table.
We are successfully inserting into the other 2 tables first with 1 row each, but when we insert into the fields table with just 1 row, we are seeing the timeout occur.
If deadlocking is possibly occuring, then are there any specific log files that SQL Server maintains that we should be able to see these problems in?
I really, really appreciate any insite on this.
CREATE TABLE [xpress].[auddiofields] (
[wp_oid] [char] (15) NOT NULL ,
[ref_oid] [char] (15) NOT NULL ,
[executiontimestamp] [datetime] NOT NULL ,
[recordsequence] [int] NOT NULL ,
[fieldsequence] [int] NOT NULL ,
[maxsize] [int] NOT NULL ,
[name] [varchar] (255) NULL ,
[value] [varchar] (2500) NULL
) ON [PRIMARY]
GO
CREATE TABLE [xpress].[auddiorecords] (
[wp_oid] [char] (15) NOT NULL ,
[ref_oid] [char] (15) NOT NULL ,
[executiontimestamp] [datetime] NOT NULL ,
[recordsequence] [int] NOT NULL ,
[direction] [char] (1) NOT NULL ,
[name] [varchar] (255) NULL ,
[description] [varchar] (2000) NULL
) ON [PRIMARY]
GO
CREATE TABLE [xpress].[auddioreferences] (
[wp_oid] [char] (15) NOT NULL ,
[ref_oid] [char] (15) NOT NULL ,
[applicationname] [char] (30) NOT NULL ,
[defaultquantity] [int] NOT NULL ,
[actualquantity] [int] NOT NULL ,
[executinguser_oid] [char] (15) NOT NULL ,
[executiontimestamp] [datetime] NOT NULL ,
[family] [char] (50) NOT NULL ,
[initiatinguser_oid] [char] (15) NOT NULL ,
[initiationtimestamp] [datetime] NOT NULL ,
[creditedtouser_oid] [char] (15) NULL ,
[name] [char] (45) NOT NULL ,
[priority] [int] NOT NULL ,
[reftype] [char] (30) NOT NULL ,
[haserrors] [char] (1) NOT NULL ,
[title] [varchar] (2000) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [xpress].[auddiofields] WITH NOCHECK ADD
CONSTRAINT [auddiofields_pk] PRIMARY KEY NONCLUSTERED
(
[ref_oid],
[executiontimestamp],
[recordsequence],
[fieldsequence]
) WITH FILLFACTOR = 0 ON [PRIMARY]
GO
ALTER TABLE [xpress].[auddiorecords] WITH NOCHECK ADD
CONSTRAINT [auddiorecords_pk] PRIMARY KEY NONCLUSTERED
(
[ref_oid],
[executiontimestamp],
[recordsequence]
) WITH FILLFACTOR = 0 ON [PRIMARY]
GO
ALTER TABLE [xpress].[auddioreferences] WITH NOCHECK ADD
CONSTRAINT [auddioreferences_pk] PRIMARY KEY NONCLUSTERED
(
[ref_oid],
[executiontimestamp]
) WITH FILLFACTOR = 0 ON [PRIMARY]
GO
CREATE INDEX [auddiofields_x1] ON [xpress].[auddiofields]([wp_oid]) WITH FILLFACTOR = 0 ON [PRIMARY]
GO
CREATE INDEX [auddiorecords_x1] ON [xpress].[auddiorecords]([wp_oid]) WITH FILLFACTOR = 0 ON [PRIMARY]
GO
CREATE INDEX [auddioreferences_x1] ON [xpress].[auddioreferences]([wp_oid]) WITH FILLFACTOR = 0 ON [PRIMARY]
GO
ALTER TABLE [xpress].[auddiofields] ADD
CONSTRAINT [auddiofields_fk1] FOREIGN KEY
(
[ref_oid],
[executiontimestamp],
[recordsequence]
) REFERENCES [xpress].[auddiorecords] (
[ref_oid],
[executiontimestamp],
[recordsequence]
)
GO
ALTER TABLE [xpress].[auddiorecords] ADD
CONSTRAINT [auddiorecords_fk1] FOREIGN KEY
(
[ref_oid],
[executiontimestamp]
) REFERENCES [xpress].[auddioreferences] (
[ref_oid],
[executiontimestamp]
)
GO
August 30, 2002 at 10:01 am
How big is your audioreferences table? It could be the foreign key lookup from audiofields to audioreferences that's slowing you down.
- Troy King
- Troy King
September 2, 2002 at 3:16 pm
The only problem is that it is sporadic, so it doesn't always happen. I am guessing that since this is the case, it can't be because of the foreign key reference or everyone would be getting this problem.
However, other clients of ours with less records in their database don't seem to have this problem.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply