Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Create index, create table statements are locking

Author  Topic 

Gvarol
Starting Member

3 Posts
Posted - 2009-06-22 : 16:14:00
Our database has quite a few partitioned tables. Our data load and updates
are done through ssis packages. The packages would create tables with same
partition logic without indexes first, and then load the data and then create
the clustered index, nonclustered index(es) if there is any and the check
constraint in a batch. It seems to be if there are multiple create table or
create index batches are initiated at the same time blocking occurs. Create
table blockings do not create a large delay but create index statements might
take up to 20-30 minutes some time and more and more blocks are collected
during the duration. We are running sql 2008 enterprise. SSIS packages do
asynchronous data loads and after the data load create indexes for the
finished data loads to speed up the processing time. I pasted the session
information and the batch statements below.

Stat blocking_session_id session_id reads writes
logical_reads
-------- ------------------- ---------- --------------------
-------------------- --------------------
Blocked 93 90 0 0
129

ALTER TABLE dbo.T_37015_Trans ADD CONSTRAINT T_37015_Trans_KEY PRIMARY KEY
CLUSTERED
(
CNTY_CD ASC,
BATCH_DT ASC,
BATCH_SEQ ASC,
MUNC_CD ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_DATA (CNTY_CD)

CREATE NONCLUSTERED INDEX DOC_NBR_KEY ON dbo.T_37015_Trans
(
CNTY_CD ASC,
DOC_YY_DT ASC,
DOC_NBR ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_INDEX (CNTY_CD)

CREATE NONCLUSTERED INDEX PRI_CAT_CD_KEY ON dbo.T_37015_Trans
(
PRI_CAT_CD ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_INDEX (CNTY_CD)

CREATE NONCLUSTERED INDEX PROPERTY_key ON dbo.T_37015_Trans
(
CNTY_CD ASC,
PCL_ID ASC,
PCL_SEQ_NBR ASC,
MUNC_CD ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_INDEX (CNTY_CD)

CREATE NONCLUSTERED INDEX REC_BKPG_KEY ON dbo.T_37015_Trans
(
CNTY_CD ASC,
SLS_REC_BK_NBR ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_INDEX (CNTY_CD)

CREATE NONCLUSTERED INDEX SALE_POS_KEY ON dbo.T_37015_Trans
(
SALE_POS ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_INDEX (CNTY_CD)

CREATE NONCLUSTERED INDEX VEST_POS_KEY ON dbo.T_37015_Trans
(
VEST_POS ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_INDEX (CNTY_CD)
ALTER TABLE dbo.T_37015_Trans WITH CHECK ADD CONSTRAINT
T_37015_Trans_CntyCodeInState CHECK ((CNTY_CD>='35000' AND CNTY_CD<='44999'))

Stat blocking_session_id session_id reads writes
logical_reads
-------- ------------------- ---------- --------------------
-------------------- --------------------
Blocked 97 93 0 0
7

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'dbo.T_31113_PROPERTY_TRANS_NAME_ADDRESS') AND type in (N'U'))
DROP TABLE dbo.T_31113_PROPERTY_TRANS_NAME_ADDRESS
CREATE TABLE dbo.T_31113_PROPERTY_TRANS_NAME_ADDRESS(
ADDR_APT_NBR varchar(6) NULL,
ADDR_CARRT_CD char(4) NULL,
ADDR_CITY_NM varchar(40) NULL,
ADDR_CMRA_CD char(2) NULL,
ADDR_CNTRY_NM varchar(30) NULL,
ADDR_CSZ_ID varchar(60) NULL,
ADDR_DIR_CD char(2) NULL,
ADDR_DPV_NOTE1 char(3) NULL,
ADDR_DPV_NOTE2 char(3) NULL,
ADDR_HSE1_NBR varchar(10) NULL,
ADDR_HSE2_NBR varchar(10) NULL,
ADDR_LACS_CD char(2) NULL,
ADDR_MODE_CD varchar(5) NULL,
ADDR_OPT_IND char(1) NULL,
ADDR_OPT_IND_2 char(1) NULL,
ADDR_PFX1_CD varchar(5) NULL,
ADDR_PFX2_CD varchar(5) NULL,
ADDR_QDRNT_CD char(2) NULL,
ADDR_SFX1_CD varchar(10) NULL,
ADDR_SFX2_CD varchar(10) NULL,
ADDR_ST_CD char(2) NULL,
ADDR_STRT_NM varchar(30) NULL,
ADDR_ZIP_CD char(9) NULL,
AKA_NM varchar(60) NULL,
AKA_NM_TYP_CD char(4) NULL,
CD char(1) NULL,
CENS_ID varchar(10) NULL,
DBA_NM varchar(60) NULL,
MATCH_CD char(4) NULL,
NM1 varchar(30) NULL,
NM2 varchar(30) NULL,
PHONE_NBR bigint NULL,
PHONE_OPT_IND char(1) NULL,
ADDR_OPT_SRC char(1) NULL,
PHONE_OPT_SRC char(1) NULL,
PHONE_UNLIST_IND char(1) NULL,
NAME_TYPE varchar(8) NOT NULL,
CNTY_CD char(5) NOT NULL,
PCL_ID varchar(45) NOT NULL,
PCL_SEQ_NBR smallint NOT NULL,
MUNC_CD char(3) NOT NULL
) ON psFIPS_GR21_PROP_DATA (CNTY_CD) WITH (DATA_COMPRESSION = PAGE)
ALTER TABLE dbo.T_31113_PROPERTY_TRANS_NAME_ADDRESS SET (LOCK_ESCALATION =
AUTO)

Stat blocking_session_id session_id reads writes
logical_reads
-------- ------------------- ---------- --------------------
-------------------- --------------------
Blocking NULL 93 0 0
7

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'dbo.T_31113_PROPERTY_TRANS_NAME_ADDRESS') AND type in (N'U'))
DROP TABLE dbo.T_31113_PROPERTY_TRANS_NAME_ADDRESS
CREATE TABLE dbo.T_31113_PROPERTY_TRANS_NAME_ADDRESS(
ADDR_APT_NBR varchar(6) NULL,
ADDR_CARRT_CD char(4) NULL,
ADDR_CITY_NM varchar(40) NULL,
ADDR_CMRA_CD char(2) NULL,
ADDR_CNTRY_NM varchar(30) NULL,
ADDR_CSZ_ID varchar(60) NULL,
ADDR_DIR_CD char(2) NULL,
ADDR_DPV_NOTE1 char(3) NULL,
ADDR_DPV_NOTE2 char(3) NULL,
ADDR_HSE1_NBR varchar(10) NULL,
ADDR_HSE2_NBR varchar(10) NULL,
ADDR_LACS_CD char(2) NULL,
ADDR_MODE_CD varchar(5) NULL,
ADDR_OPT_IND char(1) NULL,
ADDR_OPT_IND_2 char(1) NULL,
ADDR_PFX1_CD varchar(5) NULL,
ADDR_PFX2_CD varchar(5) NULL,
ADDR_QDRNT_CD char(2) NULL,
ADDR_SFX1_CD varchar(10) NULL,
ADDR_SFX2_CD varchar(10) NULL,
ADDR_ST_CD char(2) NULL,
ADDR_STRT_NM varchar(30) NULL,
ADDR_ZIP_CD char(9) NULL,
AKA_NM varchar(60) NULL,
AKA_NM_TYP_CD char(4) NULL,
CD char(1) NULL,
CENS_ID varchar(10) NULL,
DBA_NM varchar(60) NULL,
MATCH_CD char(4) NULL,
NM1 varchar(30) NULL,
NM2 varchar(30) NULL,
PHONE_NBR bigint NULL,
PHONE_OPT_IND char(1) NULL,
ADDR_OPT_SRC char(1) NULL,
PHONE_OPT_SRC char(1) NULL,
PHONE_UNLIST_IND char(1) NULL,
NAME_TYPE varchar(8) NOT NULL,
CNTY_CD char(5) NOT NULL,
PCL_ID varchar(45) NOT NULL,
PCL_SEQ_NBR smallint NOT NULL,
MUNC_CD char(3) NOT NULL
) ON psFIPS_GR21_PROP_DATA (CNTY_CD) WITH (DATA_COMPRESSION = PAGE)
ALTER TABLE dbo.T_31113_PROPERTY_TRANS_NAME_ADDRESS SET (LOCK_ESCALATION =
AUTO)

Stat blocking_session_id session_id reads writes
logical_reads
-------- ------------------- ---------- --------------------
-------------------- --------------------
Blocked 96 96 0 0
7

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'dbo.T_31185_PROPERTY_TRANS_NAME_ADDRESS') AND type in (N'U'))
DROP TABLE dbo.T_31185_PROPERTY_TRANS_NAME_ADDRESS
CREATE TABLE dbo.T_31185_PROPERTY_TRANS_NAME_ADDRESS(
ADDR_APT_NBR varchar(6) NULL,
ADDR_CARRT_CD char(4) NULL,
ADDR_CITY_NM varchar(40) NULL,
ADDR_CMRA_CD char(2) NULL,
ADDR_CNTRY_NM varchar(30) NULL,
ADDR_CSZ_ID varchar(60) NULL,
ADDR_DIR_CD char(2) NULL,
ADDR_DPV_NOTE1 char(3) NULL,
ADDR_DPV_NOTE2 char(3) NULL,
ADDR_HSE1_NBR varchar(10) NULL,
ADDR_HSE2_NBR varchar(10) NULL,
ADDR_LACS_CD char(2) NULL,
ADDR_MODE_CD varchar(5) NULL,
ADDR_OPT_IND char(1) NULL,
ADDR_OPT_IND_2 char(1) NULL,
ADDR_PFX1_CD varchar(5) NULL,
ADDR_PFX2_CD varchar(5) NULL,
ADDR_QDRNT_CD char(2) NULL,
ADDR_SFX1_CD varchar(10) NULL,
ADDR_SFX2_CD varchar(10) NULL,
ADDR_ST_CD char(2) NULL,
ADDR_STRT_NM varchar(30) NULL,
ADDR_ZIP_CD char(9) NULL,
AKA_NM varchar(60) NULL,
AKA_NM_TYP_CD char(4) NULL,
CD char(1) NULL,
CENS_ID varchar(10) NULL,
DBA_NM varchar(60) NULL,
MATCH_CD char(4) NULL,
NM1 varchar(30) NULL,
NM2 varchar(30) NULL,
PHONE_NBR bigint NULL,
PHONE_OPT_IND char(1) NULL,
ADDR_OPT_SRC char(1) NULL,
PHONE_OPT_SRC char(1) NULL,
PHONE_UNLIST_IND char(1) NULL,
NAME_TYPE varchar(8) NOT NULL,
CNTY_CD char(5) NOT NULL,
PCL_ID varchar(45) NOT NULL,
PCL_SEQ_NBR smallint NOT NULL,
MUNC_CD char(3) NOT NULL
) ON psFIPS_GR21_PROP_DATA (CNTY_CD) WITH (DATA_COMPRESSION = PAGE)
ALTER TABLE dbo.T_31185_PROPERTY_TRANS_NAME_ADDRESS SET (LOCK_ESCALATION =
AUTO)

Stat blocking_session_id session_id reads writes
logical_reads
-------- ------------------- ---------- --------------------
-------------------- --------------------
Blocking NULL 96 0 0
7

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'dbo.T_31185_PROPERTY_TRANS_NAME_ADDRESS') AND type in (N'U'))
DROP TABLE dbo.T_31185_PROPERTY_TRANS_NAME_ADDRESS
CREATE TABLE dbo.T_31185_PROPERTY_TRANS_NAME_ADDRESS(
ADDR_APT_NBR varchar(6) NULL,
ADDR_CARRT_CD char(4) NULL,
ADDR_CITY_NM varchar(40) NULL,
ADDR_CMRA_CD char(2) NULL,
ADDR_CNTRY_NM varchar(30) NULL,
ADDR_CSZ_ID varchar(60) NULL,
ADDR_DIR_CD char(2) NULL,
ADDR_DPV_NOTE1 char(3) NULL,
ADDR_DPV_NOTE2 char(3) NULL,
ADDR_HSE1_NBR varchar(10) NULL,
ADDR_HSE2_NBR varchar(10) NULL,
ADDR_LACS_CD char(2) NULL,
ADDR_MODE_CD varchar(5) NULL,
ADDR_OPT_IND char(1) NULL,
ADDR_OPT_IND_2 char(1) NULL,
ADDR_PFX1_CD varchar(5) NULL,
ADDR_PFX2_CD varchar(5) NULL,
ADDR_QDRNT_CD char(2) NULL,
ADDR_SFX1_CD varchar(10) NULL,
ADDR_SFX2_CD varchar(10) NULL,
ADDR_ST_CD char(2) NULL,
ADDR_STRT_NM varchar(30) NULL,
ADDR_ZIP_CD char(9) NULL,
AKA_NM varchar(60) NULL,
AKA_NM_TYP_CD char(4) NULL,
CD char(1) NULL,
CENS_ID varchar(10) NULL,
DBA_NM varchar(60) NULL,
MATCH_CD char(4) NULL,
NM1 varchar(30) NULL,
NM2 varchar(30) NULL,
PHONE_NBR bigint NULL,
PHONE_OPT_IND char(1) NULL,
ADDR_OPT_SRC char(1) NULL,
PHONE_OPT_SRC char(1) NULL,
PHONE_UNLIST_IND char(1) NULL,
NAME_TYPE varchar(8) NOT NULL,
CNTY_CD char(5) NOT NULL,
PCL_ID varchar(45) NOT NULL,
PCL_SEQ_NBR smallint NOT NULL,
MUNC_CD char(3) NOT NULL
) ON psFIPS_GR21_PROP_DATA (CNTY_CD) WITH (DATA_COMPRESSION = PAGE)
ALTER TABLE dbo.T_31185_PROPERTY_TRANS_NAME_ADDRESS SET (LOCK_ESCALATION =
AUTO)

Stat blocking_session_id session_id reads writes
logical_reads
-------- ------------------- ---------- --------------------
-------------------- --------------------
Blocked 100 95 0 0
7

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'dbo.T_31175_PROPERTY_TRANS_NAME_ADDRESS') AND type in (N'U'))
DROP TABLE dbo.T_31175_PROPERTY_TRANS_NAME_ADDRESS
CREATE TABLE dbo.T_31175_PROPERTY_TRANS_NAME_ADDRESS(
ADDR_APT_NBR varchar(6) NULL,
ADDR_CARRT_CD char(4) NULL,
ADDR_CITY_NM varchar(40) NULL,
ADDR_CMRA_CD char(2) NULL,
ADDR_CNTRY_NM varchar(30) NULL,
ADDR_CSZ_ID varchar(60) NULL,
ADDR_DIR_CD char(2) NULL,
ADDR_DPV_NOTE1 char(3) NULL,
ADDR_DPV_NOTE2 char(3) NULL,
ADDR_HSE1_NBR varchar(10) NULL,
ADDR_HSE2_NBR varchar(10) NULL,
ADDR_LACS_CD char(2) NULL,
ADDR_MODE_CD varchar(5) NULL,
ADDR_OPT_IND char(1) NULL,
ADDR_OPT_IND_2 char(1) NULL,
ADDR_PFX1_CD varchar(5) NULL,
ADDR_PFX2_CD varchar(5) NULL,
ADDR_QDRNT_CD char(2) NULL,
ADDR_SFX1_CD varchar(10) NULL,
ADDR_SFX2_CD varchar(10) NULL,
ADDR_ST_CD char(2) NULL,
ADDR_STRT_NM varchar(30) NULL,
ADDR_ZIP_CD char(9) NULL,
AKA_NM varchar(60) NULL,
AKA_NM_TYP_CD char(4) NULL,
CD char(1) NULL,
CENS_ID varchar(10) NULL,
DBA_NM varchar(60) NULL,
MATCH_CD char(4) NULL,
NM1 varchar(30) NULL,
NM2 varchar(30) NULL,
PHONE_NBR bigint NULL,
PHONE_OPT_IND char(1) NULL,
ADDR_OPT_SRC char(1) NULL,
PHONE_OPT_SRC char(1) NULL,
PHONE_UNLIST_IND char(1) NULL,
NAME_TYPE varchar(8) NOT NULL,
CNTY_CD char(5) NOT NULL,
PCL_ID varchar(45) NOT NULL,
PCL_SEQ_NBR smallint NOT NULL,
MUNC_CD char(3) NOT NULL
) ON psFIPS_GR21_PROP_DATA (CNTY_CD) WITH (DATA_COMPRESSION = PAGE)
ALTER TABLE dbo.T_31175_PROPERTY_TRANS_NAME_ADDRESS SET (LOCK_ESCALATION =
AUTO)

Stat blocking_session_id session_id reads writes
logical_reads
-------- ------------------- ---------- --------------------
-------------------- --------------------
Blocking NULL 95 0 0
7

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'dbo.T_31175_PROPERTY_TRANS_NAME_ADDRESS') AND type in (N'U'))
DROP TABLE dbo.T_31175_PROPERTY_TRANS_NAME_ADDRESS
CREATE TABLE dbo.T_31175_PROPERTY_TRANS_NAME_ADDRESS(
ADDR_APT_NBR varchar(6) NULL,
ADDR_CARRT_CD char(4) NULL,
ADDR_CITY_NM varchar(40) NULL,
ADDR_CMRA_CD char(2) NULL,
ADDR_CNTRY_NM varchar(30) NULL,
ADDR_CSZ_ID varchar(60) NULL,
ADDR_DIR_CD char(2) NULL,
ADDR_DPV_NOTE1 char(3) NULL,
ADDR_DPV_NOTE2 char(3) NULL,
ADDR_HSE1_NBR varchar(10) NULL,
ADDR_HSE2_NBR varchar(10) NULL,
ADDR_LACS_CD char(2) NULL,
ADDR_MODE_CD varchar(5) NULL,
ADDR_OPT_IND char(1) NULL,
ADDR_OPT_IND_2 char(1) NULL,
ADDR_PFX1_CD varchar(5) NULL,
ADDR_PFX2_CD varchar(5) NULL,
ADDR_QDRNT_CD char(2) NULL,
ADDR_SFX1_CD varchar(10) NULL,
ADDR_SFX2_CD varchar(10) NULL,
ADDR_ST_CD char(2) NULL,
ADDR_STRT_NM varchar(30) NULL,
ADDR_ZIP_CD char(9) NULL,
AKA_NM varchar(60) NULL,
AKA_NM_TYP_CD char(4) NULL,
CD char(1) NULL,
CENS_ID varchar(10) NULL,
DBA_NM varchar(60) NULL,
MATCH_CD char(4) NULL,
NM1 varchar(30) NULL,
NM2 varchar(30) NULL,
PHONE_NBR bigint NULL,
PHONE_OPT_IND char(1) NULL,
ADDR_OPT_SRC char(1) NULL,
PHONE_OPT_SRC char(1) NULL,
PHONE_UNLIST_IND char(1) NULL,
NAME_TYPE varchar(8) NOT NULL,
CNTY_CD char(5) NOT NULL,
PCL_ID varchar(45) NOT NULL,
PCL_SEQ_NBR smallint NOT NULL,
MUNC_CD char(3) NOT NULL
) ON psFIPS_GR21_PROP_DATA (CNTY_CD) WITH (DATA_COMPRESSION = PAGE)
ALTER TABLE dbo.T_31175_PROPERTY_TRANS_NAME_ADDRESS SET (LOCK_ESCALATION =
AUTO)

Stat blocking_session_id session_id reads writes
logical_reads
-------- ------------------- ---------- --------------------
-------------------- --------------------
Blocked 96 97 1 0
129

ALTER TABLE dbo.T_37015_Mortgage ADD CONSTRAINT PK_T_37015_Mortgage PRIMARY
KEY CLUSTERED
(
CNTY_CD ASC,
BATCH_DT ASC,
BATCH_SEQ ASC,
MUNC_CD ASC,
MTG_SEQ ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_DATA (CNTY_CD)

CREATE NONCLUSTERED INDEX MTG_DOC_DT_NBR_KEY ON dbo.T_37015_Mortgage
(
CNTY_CD ASC,
MTG_DOC_YY_DT ASC,
MTG_DOC_NBR ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_INDEX (CNTY_CD)

CREATE NONCLUSTERED INDEX MTG_LIEN_POS_KEY ON dbo.T_37015_Mortgage
(
MTG_LIEN_POS ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_INDEX (CNTY_CD)

CREATE NONCLUSTERED INDEX PROPERTY_KEY ON dbo.T_37015_Mortgage
(
CNTY_CD ASC,
PCL_ID ASC,
PCL_SEQ_NBR ASC,
MUNC_CD ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_INDEX (CNTY_CD)

CREATE NONCLUSTERED INDEX MTG_REC_BKPG_NBR_KEY ON dbo.T_37015_Mortgage
(
CNTY_CD ASC,
MTG_REC_BK_NBR ASC,
MTG_REC_PG_NBR ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_INDEX (CNTY_CD)

CREATE NONCLUSTERED INDEX PRI_CAT_CD_KEY ON dbo.T_37015_Mortgage
(
PRI_CAT_CD ASC,
MTG_AMT ASC,
MTG_DT_DER ASC,
PCL_ID ASC,
PCL_SEQ_NBR ASC,
CNTY_CD ASC,
BATCH_DT ASC,
BATCH_SEQ ASC,
MTG_SEQ ASC,
MUNC_CD ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_INDEX (CNTY_CD)ALTER
TABLE dbo.T_37015_Mortgage WITH CHECK ADD CONSTRAINT
T_37015_Mortgage_CntyCodeInState CHECK ((CNTY_CD>='35000' AND
CNTY_CD<='44999'))

Stat blocking_session_id session_id reads writes
logical_reads
-------- ------------------- ---------- --------------------
-------------------- --------------------
Blocking NULL 97 1 0
129

ALTER TABLE dbo.T_37015_Mortgage ADD CONSTRAINT PK_T_37015_Mortgage PRIMARY
KEY CLUSTERED
(
CNTY_CD ASC,
BATCH_DT ASC,
BATCH_SEQ ASC,
MUNC_CD ASC,
MTG_SEQ ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_DATA (CNTY_CD)

CREATE NONCLUSTERED INDEX MTG_DOC_DT_NBR_KEY ON dbo.T_37015_Mortgage
(
CNTY_CD ASC,
MTG_DOC_YY_DT ASC,
MTG_DOC_NBR ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_INDEX (CNTY_CD)

CREATE NONCLUSTERED INDEX MTG_LIEN_POS_KEY ON dbo.T_37015_Mortgage
(
MTG_LIEN_POS ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_INDEX (CNTY_CD)

CREATE NONCLUSTERED INDEX PROPERTY_KEY ON dbo.T_37015_Mortgage
(
CNTY_CD ASC,
PCL_ID ASC,
PCL_SEQ_NBR ASC,
MUNC_CD ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_INDEX (CNTY_CD)

CREATE NONCLUSTERED INDEX MTG_REC_BKPG_NBR_KEY ON dbo.T_37015_Mortgage
(
CNTY_CD ASC,
MTG_REC_BK_NBR ASC,
MTG_REC_PG_NBR ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_INDEX (CNTY_CD)

CREATE NONCLUSTERED INDEX PRI_CAT_CD_KEY ON dbo.T_37015_Mortgage
(
PRI_CAT_CD ASC,
MTG_AMT ASC,
MTG_DT_DER ASC,
PCL_ID ASC,
PCL_SEQ_NBR ASC,
CNTY_CD ASC,
BATCH_DT ASC,
BATCH_SEQ ASC,
MTG_SEQ ASC,
MUNC_CD ASC
)WITH (DATA_COMPRESSION = PAGE) ON psFIPS_GR35_TRANS_INDEX (CNTY_CD)ALTER
TABLE dbo.T_37015_Mortgage WITH CHECK ADD CONSTRAINT
T_37015_Mortgage_CntyCodeInState CHECK ((CNTY_CD>='35000' AND
CNTY_CD<='44999'))

Stat blocking_session_id session_id reads writes
logical_reads
-------- ------------------- ---------- --------------------
-------------------- --------------------
Blocked 95 100 0 0
7

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'dbo.T_31181_PROPERTY_TRANS_NAME_ADDRESS') AND type in (N'U'))
DROP TABLE dbo.T_31181_PROPERTY_TRANS_NAME_ADDRESS
CREATE TABLE dbo.T_31181_PROPERTY_TRANS_NAME_ADDRESS(
ADDR_APT_NBR varchar(6) NULL,
ADDR_CARRT_CD char(4) NULL,
ADDR_CITY_NM varchar(40) NULL,
ADDR_CMRA_CD char(2) NULL,
ADDR_CNTRY_NM varchar(30) NULL,
ADDR_CSZ_ID varchar(60) NULL,
ADDR_DIR_CD char(2) NULL,
ADDR_DPV_NOTE1 char(3) NULL,
ADDR_DPV_NOTE2 char(3) NULL,
ADDR_HSE1_NBR varchar(10) NULL,
ADDR_HSE2_NBR varchar(10) NULL,
ADDR_LACS_CD char(2) NULL,
ADDR_MODE_CD varchar(5) NULL,
ADDR_OPT_IND char(1) NULL,
ADDR_OPT_IND_2 char(1) NULL,
ADDR_PFX1_CD varchar(5) NULL,
ADDR_PFX2_CD varchar(5) NULL,
ADDR_QDRNT_CD char(2) NULL,
ADDR_SFX1_CD varchar(10) NULL,
ADDR_SFX2_CD varchar(10) NULL,
ADDR_ST_CD char(2) NULL,
ADDR_STRT_NM varchar(30) NULL,
ADDR_ZIP_CD char(9) NULL,
AKA_NM varchar(60) NULL,
AKA_NM_TYP_CD char(4) NULL,
CD char(1) NULL,
CENS_ID varchar(10) NULL,
DBA_NM varchar(60) NULL,
MATCH_CD char(4) NULL,
NM1 varchar(30) NULL,
NM2 varchar(30) NULL,
PHONE_NBR bigint NULL,
PHONE_OPT_IND char(1) NULL,
ADDR_OPT_SRC char(1) NULL,
PHONE_OPT_SRC char(1) NULL,
PHONE_UNLIST_IND char(1) NULL,
NAME_TYPE varchar(8) NOT NULL,
CNTY_CD char(5) NOT NULL,
PCL_ID varchar(45) NOT NULL,
PCL_SEQ_NBR smallint NOT NULL,
MUNC_CD char(3) NOT NULL
) ON psFIPS_GR21_PROP_DATA (CNTY_CD) WITH (DATA_COMPRESSION = PAGE)
ALTER TABLE dbo.T_31181_PROPERTY_TRANS_NAME_ADDRESS SET (LOCK_ESCALATION =
AUTO)

Stat blocking_session_id session_id reads writes
logical_reads
-------- ------------------- ---------- --------------------
-------------------- --------------------
Blocking NULL 100 0 0
7

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'dbo.T_31181_PROPERTY_TRANS_NAME_ADDRESS') AND type in (N'U'))
DROP TABLE dbo.T_31181_PROPERTY_TRANS_NAME_ADDRESS
CREATE TABLE dbo.T_31181_PROPERTY_TRANS_NAME_ADDRESS(
ADDR_APT_NBR varchar(6) NULL,
ADDR_CARRT_CD char(4) NULL,
ADDR_CITY_NM varchar(40) NULL,
ADDR_CMRA_CD char(2) NULL,
ADDR_CNTRY_NM varchar(30) NULL,
ADDR_CSZ_ID varchar(60) NULL,
ADDR_DIR_CD char(2) NULL,
ADDR_DPV_NOTE1 char(3) NULL,
ADDR_DPV_NOTE2 char(3) NULL,
ADDR_HSE1_NBR varchar(10) NULL,
ADDR_HSE2_NBR varchar(10) NULL,
ADDR_LACS_CD char(2) NULL,
ADDR_MODE_CD varchar(5) NULL,
ADDR_OPT_IND char(1) NULL,
ADDR_OPT_IND_2 char(1) NULL,
ADDR_PFX1_CD varchar(5) NULL,
ADDR_PFX2_CD varchar(5) NULL,
ADDR_QDRNT_CD char(2) NULL,
ADDR_SFX1_CD varchar(10) NULL,
ADDR_SFX2_CD varchar(10) NULL,
ADDR_ST_CD char(2) NULL,
ADDR_STRT_NM varchar(30) NULL,
ADDR_ZIP_CD char(9) NULL,
AKA_NM varchar(60) NULL,
AKA_NM_TYP_CD char(4) NULL,
CD char(1) NULL,
CENS_ID varchar(10) NULL,
DBA_NM varchar(60) NULL,
MATCH_CD char(4) NULL,
NM1 varchar(30) NULL,
NM2 varchar(30) NULL,
PHONE_NBR bigint NULL,
PHONE_OPT_IND char(1) NULL,
ADDR_OPT_SRC char(1) NULL,
PHONE_OPT_SRC char(1) NULL,
PHONE_UNLIST_IND char(1) NULL,
NAME_TYPE varchar(8) NOT NULL,
CNTY_CD char(5) NOT NULL,
PCL_ID varchar(45) NOT NULL,
PCL_SEQ_NBR smallint NOT NULL,
MUNC_CD char(3) NOT NULL
) ON psFIPS_GR21_PROP_DATA (CNTY_CD) WITH (DATA_COMPRESSION = PAGE)
ALTER TABLE dbo.T_31181_PROPERTY_TRANS_NAME_ADDRESS SET (LOCK_ESCALATION =
AUTO)

Stat blocking_session_id session_id reads writes
logical_reads
-------- ------------------- ---------- --------------------
-------------------- --------------------
Blocked 93 102 0 0
7

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'dbo.T_31101_PROPERTY_TRANS_NAME_ADDRESS') AND type in (N'U'))
DROP TABLE dbo.T_31101_PROPERTY_TRANS_NAME_ADDRESS
CREATE TABLE dbo.T_31101_PROPERTY_TRANS_NAME_ADDRESS(
ADDR_APT_NBR varchar(6) NULL,
ADDR_CARRT_CD char(4) NULL,
ADDR_CITY_NM varchar(40) NULL,
ADDR_CMRA_CD char(2) NULL,
ADDR_CNTRY_NM varchar(30) NULL,
ADDR_CSZ_ID varchar(60) NULL,
ADDR_DIR_CD char(2) NULL,
ADDR_DPV_NOTE1 char(3) NULL,
ADDR_DPV_NOTE2 char(3) NULL,
ADDR_HSE1_NBR varchar(10) NULL,
ADDR_HSE2_NBR varchar(10) NULL,
ADDR_LACS_CD char(2) NULL,
ADDR_MODE_CD varchar(5) NULL,
ADDR_OPT_IND char(1) NULL,
ADDR_OPT_IND_2 char(1) NULL,
ADDR_PFX1_CD varchar(5) NULL,
ADDR_PFX2_CD varchar(5) NULL,
ADDR_QDRNT_CD char(2) NULL,
ADDR_SFX1_CD varchar(10) NULL,
ADDR_SFX2_CD varchar(10) NULL,
ADDR_ST_CD char(2) NULL,
ADDR_STRT_NM varchar(30) NULL,
ADDR_ZIP_CD char(9) NULL,
AKA_NM varchar(60) NULL,
AKA_NM_TYP_CD char(4) NULL,
CD char(1) NULL,
CENS_ID varchar(10) NULL,
DBA_NM varchar(60) NULL,
MATCH_CD char(4) NULL,
NM1 varchar(30) NULL,
NM2 varchar(30) NULL,
PHONE_NBR bigint NULL,
PHONE_OPT_IND char(1) NULL,
ADDR_OPT_SRC char(1) NULL,
PHONE_OPT_SRC char(1) NULL,
PHONE_UNLIST_IND char(1) NULL,
NAME_TYPE varchar(8) NOT NULL,
CNTY_CD char(5) NOT NULL,
PCL_ID varchar(45) NOT NULL,
PCL_SEQ_NBR smallint NOT NULL,
MUNC_CD char(3) NOT NULL
) ON psFIPS_GR21_PROP_DATA (CNTY_CD) WITH (DATA_COMPRESSION = PAGE)
ALTER TABLE dbo.T_31101_PROPERTY_TRANS_NAME_ADDRESS SET (LOCK_ESCALATION =
AUTO)

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-06-26 : 06:27:00
If you have your tempdb on a different lun, you can always try some WITH options.

CREATE INDEX...
WITH sort_in_tempdb = on, online = on


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site