sql-server - 分区表更新慢

Question

我的一个 MSSQL 表有问题。该表有 1.2 万亿行，大约 1TB 的数据并且还在增长。该表被划分为 8000 个分区，仅使用了大约 800 个。其他的都是为了扩大增长而创建的。

插入和选择 < 1s 我的问题是更新非常慢。要使用精确的分区键和标识列键更新 1 条记录 varchar(100)，它是 3s。

如果我将更新代码放在存储过程中，则不到 1 秒。如果我添加选项（重新编译），大约 1s。有没有办法在不添加重新编译的情况下解决这个问题？

谢谢你。

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
;

标签： sql-serverpartition