July 30, 2012 at 9:27 am
I'm creating a set of tables to model performance for several development options. One of those tables is intentionally large: 23 million records, lots of fields with varying datatypes to force 1 row per page (just over 4K per row). Each row in the table is identical except for an identity primary key created via default, so it is also the clustered index for the table (bad design, but important for the test). I ran out of disk space while creating some of the other tables, so I decided to delete out some of the rows. I restarted the service because everything was bogged down but couldn't get the delete to run at all. Even hitting "display estimated execution plan" on DELETE TOP(1) FROM Table_36 WHERE table_36PK > 6000000 didn't return an execution plan after almost 8 minutes. I know the table is bad, it was intentionally set up that way, but why won't SQL Server even give me the plan in a reasonable timeframe? Any ideas?
Here are the specifics for the table:
CREATE TABLE Table_36 (
Table_36PK int IDENTITY(1,1) PRIMARY KEY,
Column_1 datetime NULL,
Column_2 varchar(801) NULL,
Column_3 varchar(50) NULL,
Column_4 varchar(50) NULL,
Column_5 datetime NULL,
Column_6 datetime NULL,
Column_7 varchar(50) NULL,
Column_8 int NULL,
Column_9 varchar(50) NULL,
Column_10 int NULL,
Column_11 int NULL,
Column_12 int NULL,
Column_13 datetime NULL,
Column_14 varchar(50) NULL,
Column_15 numeric(19, 0) NULL,
Column_16 datetime NULL,
Column_17 varchar(50) NULL,
Column_18 varchar(1634) NULL,
Column_19 int NULL,
Column_20 varchar(1869) NULL,
Column_21 varchar(50) NULL,
Column_22 numeric(19, 0) NULL,
Column_23 varchar(1981) NULL,
Column_24 varchar(50) NULL,
Column_25 varchar(50) NULL,
Column_26 int NULL,
Column_27 varchar(50) NULL,
Column_28 int NULL,
Column_29 datetime NULL,
Column_30 varchar(255) NULL,
Column_31 varchar(50) NULL,
Column_32 varchar(369) NULL,
Column_33 varchar(177) NULL,
Column_34 int NULL,
Column_35 datetime NULL,
Column_36 datetime NULL,
Column_37 varchar(50) NULL,
Column_38 int NULL,
Column_39 varchar(50) NULL,
Column_40 datetime NULL,
Column_41 datetime NULL,
Column_42 varchar(50) NULL,
Column_43 int NULL,
Column_44 int NULL,
Column_45 varchar(50) NULL)
--Somewhat random values, fill varchars 50%
INSERT INTO Table_36 (Column_1,Column_2,Column_3,Column_4,Column_5,Column_6,Column_7,Column_8,Column_9,Column_10,Column_11,Column_12,Column_13,Column_14,Column_15,Column_16,Column_17,Column_18,Column_19,Column_20,Column_21,Column_22,Column_23,Column_24,Column_25,Column_26,Column_27,Column_28,Column_29,Column_30,Column_31,Column_32,Column_33,Column_34,Column_35,Column_36,Column_37,Column_38,Column_39,Column_40,Column_41,Column_42,Column_43,Column_44,Column_45)
VALUES ('2012-07-30 09:10:17.250','abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:','abcdefghijklmnopqrstuvwxy','abcdefghijklmnopqrstuvwxy','2012-07-30 09:10:17.250','2012-07-30 09:10:17.250','abcdefghijklmnopqrstuvwxy',563729467,'abcdefghijklmnopqrstuvwxy',563729467,563729467,563729467,'2012-07-30 09:10:17.250','abcdefghijklmnopqrstuvwxy',6578984621,'2012-07-30 09:10:17.250','abcdefghijklmnopqrstuvwxy','abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopq',563729467,'abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz01234567','abcdefghijklmnopqrstuvwxy',6578984621,'abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$','abcdefghijklmnopqrstuvwxy','abcdefghijklmnopqrstuvwxy',563729467,'abcdefghijklmnopqrstuvwxy',563729467,'2012-07-30 09:10:17.250','abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0','abcdefghijklmnopqrstuvwxy','abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz01234567','abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()<>?:abcdefghijklmnopqrstuvwxyz0123456789!@',563729467,'2012-07-30 09:10:17.250','2012-07-30 09:10:17.250','abcdefghijklmnopqrstuvwxy',563729467,'abcdefghijklmnopqrstuvwxy','2012-07-30 09:10:17.250','2012-07-30 09:10:17.250','abcdefghijklmnopqrstuvwxy',563729467,563729467,'abcdefghijklmnopqrstuvwxy')
--At this point, run this command multiple times, doubling the rows in the table each time, until you have the number of rows desired:
INSERT INTO Table_36 (Column_1,Column_2,Column_3,Column_4,Column_5,Column_6,Column_7,Column_8,Column_9,Column_10,Column_11,Column_12,Column_13,Column_14,Column_15,Column_16,Column_17,Column_18,Column_19,Column_20,Column_21,Column_22,Column_23,Column_24,Column_25,Column_26,Column_27,Column_28,Column_29,Column_30,Column_31,Column_32,Column_33,Column_34,Column_35,Column_36,Column_37,Column_38,Column_39,Column_40,Column_41,Column_42,Column_43,Column_44,Column_45)
SELECT Column_1,Column_2,Column_3,Column_4,Column_5,Column_6,Column_7,Column_8,Column_9,Column_10,Column_11,Column_12,Column_13,Column_14,Column_15,Column_16,Column_17,Column_18,Column_19,Column_20,Column_21,Column_22,Column_23,Column_24,Column_25,Column_26,Column_27,Column_28,Column_29,Column_30,Column_31,Column_32,Column_33,Column_34,Column_35,Column_36,Column_37,Column_38,Column_39,Column_40,Column_41,Column_42,Column_43,Column_44,Column_45
FROM Table_36
Thanks,
Chad
July 30, 2012 at 9:44 am
Alright - updating statistics gave me a query plan. I figured the uniqueness constraint on the primary key and TOP (1) would have shortcutted some of the need for stats, but I guess not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply