Indexed view table taking long time in performing insertion and deletion

All Replies

• Sunday, February 17, 2013 6:46 AM

  Answerer

   

   
  

  0

  Yes it is possible because the indexed view is materialized and needs to maintain all new/del rows (update the index you created on)

  ---

  Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
  
  MS SQL optimization: MS SQL Development and Optimization
  MS SQL Blog: Large scale of database and data cleansing
  Remote DBA Services: Improves MS SQL Database Performance
  

  • Reply
  • Quote

   
• Sunday, February 17, 2013 7:41 AM

  Moderator

   

   
  

  0
  Article on the topic: "When Not to Use Indexed Views
   
  Do not index views without careful consideration. Remember that indexed views work best when the underlying data is infrequently updated, or when a performance penalty for updates is acceptable. Because the structure of an indexed view is more complex than the structure of an index on a base table, the cost of maintaining an indexed view is higher than the cost of maintaining an index on a base table. Do not use indexed views in the following situations:
   •  An OLTP (Online Transaction Processing) system with many writes

  • Frequently updated databases

  • Queries that do not involve aggregations or joins

  • Aggregate queries where the result set has almost as many rows as the base table

  • A view with expanding joins in which the result set is larger than the original data stored in the base tables "

  http://www.informit.com/articles/article.aspx?p=130855&seqNum=5

  ---

  Kalman Toth Database & OLAP Architect
  Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

  • Reply
  • Quote

   
• Sunday, February 17, 2013 9:55 AM

   

   
  

  0

  If there are a lot of Insert Delete operations
  then you have to update index statistics frequently

  • Reply
  • Quote

   
• Sunday, February 17, 2013 9:56 AM

   

   
  

  0

  Here is a procedure that can help you

  IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE TYPE = N'p' AND NAME = N'RebuildAllOverFragmentedIndexes')
  DROP PROCEDURE dbo.RebuildAllOverFragmentedIndexes
  GO
  CREATE PROCEDURE dbo.RebuildAllOverFragmentedIndexes
  AS
  SET NOCOUNT ON;
  DECLARE @db_id int;
  DECLARE @objectid int;
  DECLARE @indexid int;
  DECLARE @partitioncount bigint;
  DECLARE @schemaname nvarchar(130);
  DECLARE @objectname nvarchar(130);
  DECLARE @indexname nvarchar(130);
  DECLARE @partitionnum bigint;
  DECLARE @partitions bigint;
  DECLARE @frag float;
  DECLARE @command nvarchar(4000);
  DECLARE @allowpagelocks BIT;
  
  SET @db_id = DB_ID();
  -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
  -- and convert object and index IDs to names.
  SELECT
      object_id AS objectid,
      index_id AS indexid,
      partition_number AS partitionnum,
      avg_fragmentation_in_percent AS frag
  INTO #work_to_do
  FROM sys.dm_db_index_physical_stats (@db_id, NULL, NULL , NULL, 'LIMITED')
  WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
  
  -- Declare the cursor for the list of partitions to be processed.
  DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
  
  -- Open the cursor.
  OPEN partitions;
  
  -- Loop through the partitions.
  WHILE (1=1)
      BEGIN;
          FETCH NEXT
             FROM partitions
             INTO @objectid, @indexid, @partitionnum, @frag;
          IF @@FETCH_STATUS < 0 BREAK;
          SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
          FROM sys.objects AS o
          JOIN sys.schemas as s ON s.schema_id = o.schema_id
          WHERE o.object_id = @objectid;
          SELECT @indexname = QUOTENAME(name), @allowpagelocks = allow_page_locks
          FROM sys.indexes
          WHERE  object_id = @objectid AND index_id = @indexid;
          SELECT @partitioncount = count (*)
          FROM sys.partitions
          WHERE object_id = @objectid AND index_id = @indexid;
  
  -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
          IF @allowpagelocks = 0
            BEGIN
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' SET (ALLOW_PAGE_LOCKS = ON)';
            EXEC (@command);
            END
          IF @frag < 30.0
              SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
          IF @frag >= 30.0
              SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
          IF @partitioncount > 1
              SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
          EXEC (@command);
          
          IF @allowpagelocks = 0
            BEGIN
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' SET (ALLOW_PAGE_LOCKS = OFF)';
            EXEC (@command);
            END
          
         print N'Executed: ' + @command;
      END;
  
  -- Close and deallocate the cursor.
  CLOSE partitions;
  DEALLOCATE partitions;
  -- Drop the temporary table.
  DROP TABLE #work_to_do;
  GO

  
  

  • Proposed As Answer by Kostya Khomyakov Friday, February 22, 2013 5:16 PM
  • Marked As Answer by Iric WenModerator Monday, February 25, 2013 9:36 AM
  •  
  • Reply
  • Quote

   
• Sunday, February 17, 2013 10:09 AM

  Answerer

   

   
  

  0

  Kostya, add pagecount parameter >1000 to the main SELECT statement. But I do not think that operation will solve the problem in long term period.

  ---

  Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
  
  MS SQL optimization: MS SQL Development and Optimization
  MS SQL Blog: Large scale of database and data cleansing
  Remote DBA Services: Improves MS SQL Database Performance
  

  • Reply
  • Quote

   
• Monday, February 18, 2013 10:18 AM

   

   
  

  0

   Hi Uri Dimant,

  I am sure that such kind of procedure + SQL JOB(with configured schedule) will help to keep index statistics at an updated state
  

  • Reply
  • Quote