Table insert issue ( Only once in a while)

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  October 28, 2005 at 4:10 pm

  #601134

  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