Table insert issue ( Only once in a while)
-
What was the error encountered when the inserts failed?
-
could this be related to an index with a high fill factor? if an index is created with a high fill factor, say 90%, but you have a lot of inserts into the table with the index, when the pages reserved for inserted data are filled, won't they hit a certain point and the index needs to be rebuilt, I would suspect that the table would be locked while that operation is occuring, and then of course it goes away/automatically corrects itself after SQL Server has rebuilt the index.
why it works in QA and not a web page/application command? i'd bet that a page times out after 30 seconds of waiting, but QA waits forever for the operation to complete.
just a guess, but HTH
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell,
Thanks for the feedback
We do have a couple of indexes on the table and indexes are being rebuilt on a weekly basis and I thought that they might somehow play into this but I didn't think that indexes would just recreate themselves without explicitly requesting them to do so through reindexing commands. Is that an incorrect assumption?
As far as the application having problems, I agree that it could be a time out issue but the time is set to 90 seconds and it still doesn't answer as to why having a transaction complete, even though it's through QA would cause everything to start working fine again. That's probably the most perplexing issues for me at this point.
-
I am confused! You are guessing it is timing out, is there no log or run time error being thrown? For all you know you are inserting duplicate records and a PK violation is being thrown.
-
He didn't specifically mention any errors, so i jumped to the conclusion tha that it must be a hardware issue;Kory is right, are you getting any errors back before we assume something more exotic?
an index(or multiple indexes) on a 4.6 gig table would be pretty big, and if it caused a page split on the table so that it could organize/structure the data for availability(due to the high insert volume), i figured that was a reasonable guess as to why he might have have to wait a long time for the server to respond again.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
How large is the table (#rows) ? could you post the DDL for the table and indexes ? It is possible that the index(es) were 'splitting' ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Kory, we aren't getting any errors being thrown at least none that are being noticed or captured that I am aware of.
The table is about 350,000 rows
Here's the ddl for the table and indexes
CREATE TABLE [dbo].[SERVICE_QUEUE] (
[Service_Queue_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Data1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Data2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VBComponent] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ResponseText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CurrentStatus] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateAdded] [datetime] NULL ,
[AddedBy] [numeric](18, 0) NULL ,
[DateCompleted] [datetime] NULL ,
[DateProcessed] [datetime] NULL ,
[MachineName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UUID] [uniqueidentifier] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE INDEX [IX_SERVICE_QUEUE] ON [dbo].[SERVICE_QUEUE]([Service_Queue_ID], [CurrentStatus]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_CurrentStatus] ON [dbo].[SERVICE_QUEUE]([CurrentStatus]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_UUID] ON [dbo].[SERVICE_QUEUE]([UUID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
-
Kory,
I just wanted to add some info about the other part of your response. The primary key field is an incremented id field and none of our indexes are concerned about uniqueness so this shouldn't be a problem should it?
-
All, my PK violation was just an example to get us thinking! I think we shouold take a moment and step through this and not get ahead of ourselves by guessing what may be taking place with the table (that doesn't mean it is not the table)
Lets establish some facts:
1) We have applications calling a service 2) The service call as stored procedure 3) No blocking locks are present 4) No lingering locks are present during an insert, but we do see some I/O wait at times 5) Your quote "As soon as the problem was encountered, all insert attempts by the applications were failing" or maybe they were never attempted 6) We have the table structure and alot of info about what may be happening with the table 7)Contains 350,000 rows. The table is small as far as rows, but each row can be large do to 3 text data types.
Questions:
How do we know the inserts are failing or even being attempted? It may be the service calling the proc! Do you have a log for the service?
If we know the service is calling the proc with out flaw then why are the inserts failing? We should get an error? Deadlock, PK violation , FK violation, Invalid syntax! something (assuming you are testing for and raising errors '@@error' in the proc)
Lets see the proc?
-
Index splitting anybody ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Rudy,
Could you elaborate on that? When an index splits, my understanding is that it only creates 1 additonal page at a time and only moves some of the rows from that the page that's "full" to the newly created page? It doesn't physically move all the data in the entire index around on disk
Kory,
I have attached the sp code. The service has no logs available, but I know that attempts were being made because our monitoring solution was able to capture that this sp had been attempted many times after the incident started. Sadly, I don't have any logs for the service
CREATE Procedure sp_ADD_SERVICE_QUEUE
(
@Data1 text,
@Data2 text,
@VBComponent varchar (256),
@AddedBy numeric,
@Service_Queue_ID numeric OUTPUT,
@UUID uniqueidentifier = null
 
As
declare @LastError int
INSERT INTO
Service_Queue
(
Data1,
Data2,
VBComponent,
AddedBy,
DateAdded,
UUID
 
values
(
@Data1,
@Data2,
@VBComponent,
@AddedBy,
GetDate(),
@UUID
 
select @LastError = @@ERROR
If @LastError = 0
SELECT @Service_Queue_ID = @@IDENTITY
Return @LastError
GO
-
If there is no blocking locks or lingering locks (during insert/indexing) what is happening? It took two minutes to run this procedure manually? Is the database db_option 'auto close' set and the service is timing out before the database can open (long shot)?
Questions about the service:
Does it process the inserts to the queue serially?
What is the timeout?
Dreams for how the service uses the below procedure code? Per process log Success/Failure a) Success with output variable (identity) and return status (obviously 0 for no error) b) Failuer with timeout/runtime error or Failure with Return status (obviously non 0)
select @LastError = @@ERROR
If @LastError = 0
SELECT @Service_Queue_ID = @@IDENTITY
Return @LastError
-
We don't have auto close set on any of our databases so not that
Yeah, the inserts are processed by the service serially. The timeout is set to 90 seconds.
-
My issues are I don't see an insert taking more than 90 seconds (causing the service to time out) based on all the previos info about the table.
Your sure the service does nothing with the procedures output value or return value below?
select @LastError = @@ERROR
If @LastError = 0
SELECT @Service_Queue_ID = @@IDENTITY
Return @LastError
The only thing I can think of would be to add logging to the service with proc call info, start times, end time, output parm, and return val. This would definitly allow you to monitor the loading of the queue.
-
You can also get these symptons when autogrow is enabled, so that when the file needs to increase in size, everything stops.
See http://support.microsoft.com/kb/305635/EN-US/ for more details on your cause and the workarounds. Some of the workarounds are expand the database manually or set the autogrow factor to a smaller amount such as 1024 extents which is 64Mb.
Also regarding your table indicies:
Since the first column of the index Service_Queue_ID has the identity property, each inserted row will always have a value greater than any existing row and therefore would always be appended to the index structure. Free space within the index would therefore never be used. Recommend changing the fill factor to 100.
Additionally, since the first column in the table is unique, having the CurrentStatus column as the second column in the index is useless and may hinder performance by making the index wider than is needed.
There also has been a history with MS SQL Server of performance problems with tables that do not have a unique clustered index.
Recommend removing CurrentStatus from the index and then changing the organization to unique clustered.
See BOL regarding Create index and "with drop_existing"
CREATE UNIQUE CLUSTERED INDEX IX_SERVICE_QUEUE
ON SERVICE_QUEUE (Service_Queue_ID) WITH FILLFACTOR = 100
, DROP_EXISTING
As UUID has a datatype of uniqueidentifier i.e. a globally unique identifier and is valued from a parameter passed from the stored procedure. UniqueIdentifiers tend to have random values which could cause index page splitting. They also consume alot of space (4 times that of an integer). If at all possible, do not index this column. If it is necessary to index this column, first capture the index fragementation with DBCC DBCC SHOWCONTIG with TABLERESULTS and increase/decrease the fill factor appropriately. A fill factor of 75% would not be unusual.
How many values are there for CurrentStatus ? What SQL uses Current Status ? If this is acting like a queue, then you have some "get next" SQL like:
SELECT top 1 Service_Queue_ID
from SERVICE_QUEUE
where CurrentStatus = 'OPEN'
ORDER BY Service_Queue_ID
If so, indexed views will probably be a good solution - Within the last month, I made such a change on a queue with 1.5 million rows and performance improved dramatically.
Here are the IO statistics
Before with Base table only, each "get next" SQL needed about 59,000 logic reads.
Table 'Task_Privilege_Type'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'User_Action_Task'. Scan count 11277, logical reads 23326, physical reads 0, read-ahead reads 0.
Table 'Contact_Incident'. Scan count 8, logical reads 35455, physical reads 0, read-ahead reads 0.
After adding the materialized view and changing the "get next" SQL, logical reads dropped to 4600 or a 1/12 reduction.
Table 'User_Action_Task'. Scan count 2229, logical reads 4610, physical reads 0, read-ahead reads 0.
Table 'Contact_Incident_Unassigned'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0.
Create view SERVICE_QUEUE_NextOpen AS
SELECT Service_Queue_ID
from SERVICE_QUEUE
where CurrentStatus = 'OPEN'
go
Create unique clustered index SERVICE_QUEUE_NextOpen_P on SERVICE_QUEUE_NextOpen ( Service_Queue_ID)
go
Your "GET NEXT" SQL then becomes
SELECT Service_Queue_ID from SERVICE_QUEUE
Top 1 is not needed since the data is in physical order by Service_Queue_ID.
if your "get next" is slightly more complicated, as was the example where the requesting user could only work on certain types of requests, the materialized view solution works well but the filter columns need to be first and Service_Queue_ID last in the view index.
SQL = Scarcely Qualifies as a Language
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply