title: Partition Table Update Slow
body: <p>I have an issue with one of my MSSQL tables.  The table has 1.2 Trillion rows about 1TB of data and growing.   The table is partitioned into 8000 partitions, only about 800 are used.   The others were created for expanded growth.   </p>
  
  <p>Inserts and selects are &lt; 1s
  My problem is Updates are very slow.   To updated 1 record varchar(100) using the exact partition key and Identity Column key, it is 3s.</p>
  
  <p>If I place the update code in a Stored Procedure, goes in less than 1s.  If I add option (recompile), about 1s.   Is there a way to fix this without adding recomile?</p>
  
  <p>Thank you.</p>
  
  <pre><code>CREATE TABLE [dbo].[_tabletest](
      [data_id] [bigint] IDENTITY(10000,1) NOT NULL,
      [idx1_id] [smallint] NOT NULL,
      [idx2_id] [bigint] NOT NULL,
      [idx3_id] [bigint] NOT NULL,
      [template_id] [bigint] NOT NULL,
      [reference_id] [varchar](200) NOT NULL,
      [data] [nvarchar](400) NULL,
      [data_1] [tinyint] NULL,
      [data_2] [varchar](max) NULL,
      [data_3] [real] NULL,
      [data_4] [tinyint] NULL,
      [data_5] [tinyint] NULL,
      [data_6] [bit] NULL,
      [data_7] [nvarchar](50) NULL,
      [data_8] [datetime] NULL,
      [data_9] [nvarchar](50) NULL,
      [data_10] [varchar](100) NULL,
      [data_11] [varchar](100) NULL,
      [data_12] [varchar](100) NULL,
      [data_13] [varchar](300) NULL,
      [data_14] [varchar](200) NULL,
      [data_15] [uniqueidentifier] NULL,
      [data_16] [varchar](600) NULL,
      [data_17] [varchar](100) NULL,
      [data_18] [varchar](100) NULL,
      [data_19] [decimal](10, 5) NULL,
      [data_20] [decimal](10, 5) NULL,
      [data_21] [decimal](10, 5) NULL,
      [data_22] [decimal](10, 5) NULL,
      [data_23] [decimal](10, 5) NULL,
      [data_24] [int] NULL,
      [data_25] [int] NULL,
      [data_26] [int] NULL,
      [data_27] [int] NULL,
      [data_28] [decimal](10, 5) NULL,
      [data_29] [decimal](10, 5) NULL,
      [data_30] [decimal](10, 5) NULL,
      [data_31] [datetime] NULL,
      [data_32] [decimal](10, 5) NULL,
      [data_3] [bit] NULL,
      [data_34] [varchar](max) NULL,
      [data_35] [smallint] NULL,
      [data_36] [bigint] NULL,
      [data_37] [int] NULL,
      [data_38] [real] NULL,
      [data_39] [datetime] NULL,
      [data_40] [varchar](2500) NULL,
   CONSTRAINT [PK_data_id] PRIMARY KEY CLUSTERED 
  (
      [idx1_id] ASC,
      [idx2_id] ASC,
      [idx3_id] ASC,
      [data_id] ASC
  )WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
  )
  GO
  
  ALTER TABLE [dbo].[_tabletest] SET (LOCK_ESCALATION = AUTO)
  GO
  
  ALTER TABLE [dbo].[_tabletest] ADD  CONSTRAINT [DF_1]  DEFAULT ((0)) FOR [data_1]
  GO
  
  ALTER TABLE [dbo].[_tabletest] ADD  CONSTRAINT [DF_2]  DEFAULT (getutcdate()) FOR [data_39]
  GO
  
  ALTER TABLE [dbo].[_tabletest] ADD  DEFAULT (newid()) FOR [data_15]
  GO
  
  
  
  UPDATE [_tabletest] SET [data_40] = 'test-data'
  WHERE [idx1_id] = 1209 AND [idx2_id] = 113795 AND [idx3] = 41195716 AND [data_id] = 1329110156
  ;
  </code></pre>