SQL 2005 write performance

Question

Post reply

SQL 2005 write performance

Viewing 15 posts - 16 through 30 (of 35 total)

You must be logged in to reply to this topic. Login to reply

leonp SSCommitted Points: 1962 More actions · Answer 1

I meant to say I am seeing a lot of sessions with a 100% writer thread stats for this DB

noeld SSC Guru Points: 96590 More actions · Answer 2

Thanks for the clarification.

Here are some things to check for:

- check for "blocking" ?

- Do you have an idea for how many indexes you have on the heaviest written table ?

- How about rollbacks, are you experiencing many rollbacks?

* Noel

leonp SSCommitted Points: 1962 More actions · Answer 3

I believe I have just the one Index on the most voluminous tables -

T-DLR-RTC (6.9 million)

Index - Sequence-number (Non Clustered)

T-TRSPL_RCC (6.9 million)

Index - Sequence-number (Non Clustered)

I will check to see if I can get the blocking counter captured

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

leonp (12/22/2008)
I meant to say I am seeing a lot of sessions with a 100% writer thread stats for this DB

Doesn't mean you have IO bottlenecks on the database server. There are a number of reasons why DB inserts could be slow. Can you trace with profiler and see exactly what that database writer is doing?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

leonp SSCommitted Points: 1962 More actions · Answer 5

Gila,

As I mentioned before, not all of the slow performance is attributed to the DB server; in fact it is not being seen as the primary source of slowdown; this architecture was a little different sometime back when the APP was on the same box as the DB server - although that sounds like it would load the box more, the writes were actually happening much faster - commits of 10000 rows were occuring 1 - 2 seconds versus the 6 - 16 seconds now. We conducted a lot of tests as follows to isolate the source of slow down -

1. Changed the Target to Flat file on the APP server while keeping everything else the same - This improved performance a great deal and essentially eliminated all non-write components from the suspect-problem zone such as APP server performance, read performance

2. Changed the Target to Flat file on the DB server - This maintained the level of performance in #1 - This eliminated the network bandwidth and the NIC capacity on the DB server from the suspect problem zone

The only components we could not distinguish between were the -

1. New ODBC (Data Direct) to connect to the DB (initially this was Microsoft's ODBC)

2. The DB performance itself

I will have the lazy writes/sec, checkpoint pages/sec and Available bytes counters today afternoon; the app is running.

Thanks!

Leon

noeld SSC Guru Points: 96590 More actions · Answer 6

You may want to revisit those large tables and cluster them. DML performance change significantly with table size and MS recommends to cluster them

* Noel

leonp SSCommitted Points: 1962 More actions · Answer 7

Gila,

The app is running now, but with the counters gathering stats for more than an hour now, I thought we had a reasonable assessment with us. Here is what I am seeing -

1. The Available MBytes remain steady as the app progresses -> 1.222 GB

2. The checkpoint pages/sec shoots up to an average of 200 per second as soon as the app starts writing back (this is when the PLE plummets)

3. Lazy writes/sec are around 0.155

4. Log flushes/sec reports 26.5

What is this suggesting???? An intensive log operation???

¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢

sys.dm_os_wait_stats

¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢

WAIT_TYPEWAITING_TASKS_COUNTWAIT_TIME_MSMAX_WAIT_TIME_MSSIGNAL_WAIT_TIME_MS

CXPACKET15121263281373203108034535132265

LAZYWRITER_SLEEP25190525107893711714421

SQLTRACE_BUFFER_FLUSH6276825106954640781421

IO_COMPLETION4097621100840609259323703

ASYNC_NETWORK_IO32365439114843217142375

PAGEIOLATCH_SH99714451837522969062

SOS_SCHEDULER_YIELD14277831443953781443421

LATCH_EX1904166056210626015

MSQL_XP257459062859060

SLEEP_TASK14539322235153437828

leonp SSCommitted Points: 1962 More actions · Answer 8

if it helps build the complete picture, the log bytes/sec at this time are 1.8MB - 1.6MB

leonp SSCommitted Points: 1962 More actions · Answer 9

Do you find it interesting that when I switch on the /3GB option, the dm_os_wait_stats states "ASYNC_NETWORK_IO" is the item seeing the largest wait?

Also now, the Log file drive (primary partition) has a lower Idle Time (more busy) and the PLE has managed to stay above 300 (~360) for a while now, the total memory available to SQL is now 2.65 GB......

Does this indicate that more memory is not going to help improve the write performance as with the slight increase in available memory the network inputs are seen to be not keeping up with the DB throughput?

Let me know ..... coz I am still not an expert ......

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Doesn't look like memory bottleneck either.

If the app's inserting a lot of new data, then the PLE will be low because new data's constantly coming in.

Do you have SQL profiler running constantly? The buffer trace wait is awfully high.

I think at this point, you're going to have to post schema and exactly what queries the app is running, because it doesn't appear, from the perfmon stats you've posted, that it's a hardware bottleneck.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

leonp SSCommitted Points: 1962 More actions · Answer 11

We have perfmon running for 3 hours; thrice a day. Sampling once every 75 seconds. Is that a bad thing?

I will get the schema and queries ... although I am think the only queries issued to the DB are -

Select * for the reader (which the APP logs do not point out as a bottleneck)

and

Insert into for the target tables

everything else happens on the APP server

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

leonp (12/24/2008)
We have perfmon running for 3 hours; thrice a day. Sampling once every 75 seconds. Is that a bad thing?

Not perfmon. Profiler.

Select * for the reader (which the APP logs do not point out as a bottleneck)
and
Insert into for the target tables
everything else happens on the APP server

Ok, but to help more I think it's necessary to see what's selected, from where and what's inserted to where and to know which are the bottlenecks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

leonp SSCommitted Points: 1962 More actions · Answer 13

leonp

SSCommitted

Points: 1962

December 24, 2008 at 7:37 am

#917389

i should have that soon

leonp SSCommitted Points: 1962 More actions · Answer 14

Thanks Gila!

Here are the actual queries for this one session I am seeing run now -

¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢

Insert Query

¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢

The application is executing a series of sp_executes with values for one row at a time

¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢

Select Query

¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢

SELECT T_IBNR_INFO_RTC.SEQUENCE_NO, T_IBNR_INFO_RTC.FILE_DESCRIPTION, T_IBNR_INFO_RTC.RC_CODE, T_IBNR_INFO_RTC.RCC_CD, T_IBNR_INFO_RTC.SOURCE_IDENTIFIER, T_IBNR_INFO_RTC.REC_IDENTIFIER, T_IBNR_INFO_RTC.STATORY_CO_CD, T_IBNR_INFO_RTC.SERV_OFF_CD, T_IBNR_INFO_RTC.RPO_CD, T_IBNR_INFO_RTC.INPUT_ACCT_CD, T_IBNR_INFO_RTC.STAT_DIV, T_IBNR_INFO_RTC.MOD_BK_LN, T_IBNR_INFO_RTC.POL_EFF_DT, T_IBNR_INFO_RTC.ACCIDENT_DATE, T_IBNR_INFO_RTC.CLAIM_NUM, T_IBNR_INFO_RTC.NO_ROSTER_REASON, T_IBNR_INFO_RTC.CERTIFICATE_NO, T_IBNR_INFO_RTC.LIFE_CO, T_IBNR_INFO_RTC.RE_COLLAT_PURP, T_IBNR_INFO_RTC.RE_CONSOL_SCHED_F, T_IBNR_INFO_RTC.RE_DOMA_SCHED_F, T_IBNR_INFO_RTC.RE_ALASKA_C, T_IBNR_INFO_RTC.RE_CAL_C, T_IBNR_INFO_RTC.RE_CONNECTICUT_C, T_IBNR_INFO_RTC.RE_DELA_C, T_IBNR_INFO_RTC.RE_FLORIDA_C, T_IBNR_INFO_RTC.RE_ILL_C, T_IBNR_INFO_RTC.RE_MASS_C, T_IBNR_INFO_RTC.RE_MICH_C, T_IBNR_INFO_RTC.RE_NJ_C, T_IBNR_INFO_RTC.RE_NY_ACCR_REINS_C, T_IBNR_INFO_RTC.RE_NY_POOLS_C, T_IBNR_INFO_RTC.RE_NY_FIRE_C, T_IBNR_INFO_RTC.RE_NY_LIFE_C, T_IBNR_INFO_RTC.RE_COLORADO_C, T_IBNR_INFO_RTC.RE_OHIO_C, T_IBNR_INFO_RTC.RE_PENNA_C, T_IBNR_INFO_RTC.RE_TEXAS_C, T_IBNR_INFO_RTC.RE_INDIANA_C, T_IBNR_INFO_RTC.RE_US_TREAS_C, T_IBNR_INFO_RTC.RE_VIRGINIA_C, T_IBNR_INFO_RTC.RE_PUERTO_RICO_C, T_IBNR_INFO_RTC.RE_CANADA_C, T_IBNR_INFO_RTC.RE_SC_C, T_IBNR_INFO_RTC.RE_GEORGIA_C, T_IBNR_INFO_RTC.RE_FST_CD_C, T_IBNR_INFO_RTC.INS_CD, T_IBNR_INFO_RTC.USER_CD, T_IBNR_INFO_RTC.TRANS_CD_ALPHA, T_IBNR_INFO_RTC.EIS_POL_HO, T_IBNR_INFO_RTC.EIS_POL_1_ID, T_IBNR_INFO_RTC.EIS_POL_CK_DGT, T_IBNR_INFO_RTC.EIS_TRN_CD, T_IBNR_INFO_RTC.EIS_TRACE_ID, T_IBNR_INFO_RTC.CREMS_NO_FAULT_IND, T_IBNR_INFO_RTC.CREMS_CLM_CD_A, T_IBNR_INFO_RTC.IBNR_TYPE_IND, T_IBNR_INFO_RTC.RE_CIGNA_POOL, T_IBNR_INFO_RTC.CONTRACT_BAL_IND, T_IBNR_INFO_RTC.RE_BEST_RATE, T_IBNR_INFO_RTC.RE_CAPTIV_IND, T_IBNR_INFO_RTC.RE_CIG_AFIL, T_IBNR_INFO_RTC.RE_FED_EMPL_ID, T_IBNR_INFO_RTC.RE_MAST_RC_CODE, T_IBNR_INFO_RTC.RE_MAST_REIN_TYPE, T_IBNR_INFO_RTC.RE_NAIC_NUMBER, T_IBNR_INFO_RTC.RE_REINS_LOCATION, T_IBNR_INFO_RTC.RE_REINS_NAME, T_IBNR_INFO_RTC.RE_REINS_PARNT_NAME, T_IBNR_INFO_RTC.RE_SUM_LEVL_CD, T_IBNR_INFO_RTC.RE_TYPE_OF_REIN, T_IBNR_INFO_RTC.RE_US_FORGN_IND, T_IBNR_INFO_RTC.ACCT_CD, T_IBNR_INFO_RTC.ACCT_NAME, T_IBNR_INFO_RTC.MGA_POOL_CD, T_IBNR_INFO_RTC.MGA_POOL_NAME, T_IBNR_INFO_RTC.INPUT_ACCT_NAME, T_IBNR_INFO_RTC.BR_TR_NUM, T_IBNR_INFO_RTC.CIG_TR_LAYR, T_IBNR_INFO_RTC.CIG_TR_NAME, T_IBNR_INFO_RTC.CIG_TR_NUM, T_IBNR_INFO_RTC.TR_LOSS_BAS_CD, T_IBNR_INFO_RTC.TR_PARTICI_RATE, T_IBNR_INFO_RTC.TR_PARTICI_COUNT, T_IBNR_INFO_RTC.TR_ROST_SENTL, T_IBNR_INFO_RTC.TR_SCHED_F_SENTL, T_IBNR_INFO_RTC.TR_CONTRCT_BEG_DT, T_IBNR_INFO_RTC.TR_CONTRCT_END_DT, T_IBNR_INFO_RTC.TR_LAYR_INCEPT_DT, T_IBNR_INFO_RTC.TR_PLA_DIV, T_IBNR_INFO_RTC.POL_NUMBER, T_IBNR_INFO_RTC.PAC, T_IBNR_INFO_RTC.PROD_LINE, T_IBNR_INFO_RTC.MGT_RESP, T_IBNR_INFO_RTC.MKT_REGION, T_IBNR_INFO_RTC.PROFIT_CNTR, T_IBNR_INFO_RTC.CONV_TERM_CD, T_IBNR_INFO_RTC.ORIGINAT_DT, T_IBNR_INFO_RTC.COMPANY_CD, T_IBNR_INFO_RTC.POL_YR, T_IBNR_INFO_RTC.POL_MO, T_IBNR_INFO_RTC.BR_OFC_CD, T_IBNR_INFO_RTC.PIIC_CD_CREMS, T_IBNR_INFO_RTC.MANUAL_REC_DATA, T_IBNR_INFO_RTC.ACCT_TEAM, T_IBNR_INFO_RTC.ACCT_DESK, T_IBNR_INFO_RTC.OBO_IAO_IND, T_IBNR_INFO_RTC.MEMO_REG_PYMT_CD, T_IBNR_INFO_RTC.MODE_CD_CREMS, T_IBNR_INFO_RTC.COLLAT_TYPE, T_IBNR_INFO_RTC.COLLAT_TRTY_NUM, T_IBNR_INFO_RTC.COLLAT_DIV, T_IBNR_INFO_RTC.COLLAT_TRTY_LAYER, T_IBNR_INFO_RTC.COLLAT_NUM, T_IBNR_INFO_RTC.ASSIGN_NUM, T_IBNR_INFO_RTC.ISSUER, T_IBNR_INFO_RTC.COLLAT_BRKR_CD, T_IBNR_INFO_RTC.COLLAT_SECTION, T_IBNR_INFO_RTC.OBO_CODE, T_IBNR_INFO_RTC.ST_CD_CREMS, T_IBNR_INFO_RTC.RE_TP_CD_CREMS, T_IBNR_INFO_RTC.REC_LEDGER, T_IBNR_INFO_RTC.REC_INS_NM, T_IBNR_INFO_RTC.POL_EXPIR_YR, T_IBNR_INFO_RTC.POL_EXPIR_MO, T_IBNR_INFO_RTC.POL_EXPIR_DY, T_IBNR_INFO_RTC.REC_AGE_IND, T_IBNR_INFO_RTC.REC_AGE_CTR, T_IBNR_INFO_RTC.ORIG_REC_RC, T_IBNR_INFO_RTC.ACCNT_YR, T_IBNR_INFO_RTC.ACCNT_MO, T_IBNR_INFO_RTC.SCHED_F_ACER, T_IBNR_INFO_RTC.SCHED_F_IBNR, T_IBNR_INFO_RTC.IN_FORCE_PREM, T_IBNR_INFO_RTC.UNEARNED_PREM_RESRVS, T_IBNR_INFO_RTC.OUTSTANDING_RESERVES, T_IBNR_INFO_RTC.CREDIT_PREMS_4033, T_IBNR_INFO_RTC.PD_LOSS_RECOV_4032, T_IBNR_INFO_RTC.DEFRD_PREMS_3067, T_IBNR_INFO_RTC.COLLAT_AMT, T_IBNR_INFO_RTC.PREMIUM_DEPOSITS, T_IBNR_INFO_RTC.ASSUMED_WRITTEN_PREM, T_IBNR_INFO_RTC.ASSUMED_COMMISSION, T_IBNR_INFO_RTC.PAID_LOSS, T_IBNR_INFO_RTC.PAID_EXPENSE, T_IBNR_INFO_RTC.ERR_CD1, T_IBNR_INFO_RTC.ERR_CD2, T_IBNR_INFO_RTC.ERR_CD3, T_IBNR_INFO_RTC.ERR_CD4, T_IBNR_INFO_RTC.ERR_CD5, T_IBNR_INFO_RTC.ERR_CD6, T_IBNR_INFO_RTC.ERR_CD7, T_IBNR_INFO_RTC.ERR_CD8, T_IBNR_INFO_RTC.ERR_CD9, T_IBNR_INFO_RTC.ERR_CD10, T_IBNR_INFO_RTC.ERR_CD14, T_IBNR_INFO_RTC.ERR_CD15, T_IBNR_INFO_RTC.ERR_CD16, T_IBNR_INFO_RTC.ERR_CD17, T_IBNR_INFO_RTC.ERR_CD18, T_IBNR_INFO_RTC.ERR_CD19, T_IBNR_INFO_RTC.ERR_CD20, T_IBNR_INFO_RTC.MSTR_SEQ, T_IBNR_INFO_RTC.PARTIC_SEQ, T_IBNR_INFO_RTC.TR_SPL_DT, T_IBNR_INFO_RTC.TR_SPL_IND, T_IBNR_INFO_RTC.MANUAL_SRC_IND, T_IBNR_INFO_RTC.ESTABLISH_datetime, T_IBNR_INFO_RTC.TRANS_XPIR_DT, T_IBNR_INFO_RTC.TRANS_EFF_DT, T_IBNR_INFO_RTC.WRITTEN_PREM, T_IBNR_INFO_RTC.COMMISSION, T_IBNR_INFO_RTC.REC_ENTRY_datetime, T_IBNR_INFO_RTC.REC_MONEY_CODE, T_IBNR_INFO_RTC.RECEIV_AMT, T_IBNR_INFO_RTC.REC_RECORD_TYPE, T_IBNR_INFO_RTC.REC_COLLECT_CAT_CD, T_IBNR_INFO_RTC.REC_DEBIT_CREDIT_IND, T_IBNR_INFO_RTC.REC_AGE_DAYS, T_IBNR_INFO_RTC.REC_OFFSET_CD, T_IBNR_INFO_RTC.REC_REASON_CD, T_IBNR_INFO_RTC.REC_TRANS_CD_CREM, T_IBNR_INFO_RTC.REC_UNIT_CD, T_IBNR_INFO_RTC.REC_DESK_CD, T_IBNR_INFO_RTC.REC_COLLECTOR, T_IBNR_INFO_RTC.REC_COORDINATOR, T_IBNR_INFO_RTC.REC_NET_AGE_DAYS, T_IBNR_INFO_RTC.REC_INSTALL_CD, T_IBNR_INFO_RTC.REC_AVG_AGE_DAYS, T_IBNR_INFO_RTC.PD_LOSS_RECOV, T_IBNR_INFO_RTC.PD_EXP_RECOV, T_IBNR_INFO_RTC.ASSUMED_UNPD_LOSS, T_IBNR_INFO_RTC.ASSUMED_UNEARNED_PREM

FROM T_IBNR_INFO_RTC

UNION ALL

SELECT T_IBNR_TRSPL_RCC.SEQUENCE_NO, T_IBNR_TRSPL_RCC.FILE_DESCRIPTION, T_IBNR_TRSPL_RCC.RC_CODE, T_IBNR_TRSPL_RCC.RCC_CD, T_IBNR_TRSPL_RCC.SOURCE_IDENTIFIER, T_IBNR_TRSPL_RCC.REC_IDENTIFIER, T_IBNR_TRSPL_RCC.STATORY_CO_CD, T_IBNR_TRSPL_RCC.SERV_OFF_CD, T_IBNR_TRSPL_RCC.RPO_CD, T_IBNR_TRSPL_RCC.INPUT_ACCT_CD, T_IBNR_TRSPL_RCC.STAT_DIV, T_IBNR_TRSPL_RCC.MOD_BK_LN, T_IBNR_TRSPL_RCC.POL_EFF_DT, T_IBNR_TRSPL_RCC.ACCIDENT_DATE, T_IBNR_TRSPL_RCC.CLAIM_NUM, T_IBNR_TRSPL_RCC.NO_ROSTER_REASON, T_IBNR_TRSPL_RCC.CERTIFICATE_NO, T_IBNR_TRSPL_RCC.LIFE_CO, T_IBNR_TRSPL_RCC.RE_COLLAT_PURP, T_IBNR_TRSPL_RCC.RE_CONSOL_SCHED_F, T_IBNR_TRSPL_RCC.RE_DOMA_SCHED_F, T_IBNR_TRSPL_RCC.RE_ALASKA_C, T_IBNR_TRSPL_RCC.RE_CAL_C, T_IBNR_TRSPL_RCC.RE_CONNECTICUT_C, T_IBNR_TRSPL_RCC.RE_DELA_C, T_IBNR_TRSPL_RCC.RE_FLORIDA_C, T_IBNR_TRSPL_RCC.RE_ILL_C, T_IBNR_TRSPL_RCC.RE_MASS_C, T_IBNR_TRSPL_RCC.RE_MICH_C, T_IBNR_TRSPL_RCC.RE_NJ_C, T_IBNR_TRSPL_RCC.RE_NY_ACCR_REINS_C, T_IBNR_TRSPL_RCC.RE_NY_POOLS_C, T_IBNR_TRSPL_RCC.RE_NY_FIRE_C, T_IBNR_TRSPL_RCC.RE_NY_LIFE_C, T_IBNR_TRSPL_RCC.RE_COLORADO_C, T_IBNR_TRSPL_RCC.RE_OHIO_C, T_IBNR_TRSPL_RCC.RE_PENNA_C, T_IBNR_TRSPL_RCC.RE_TEXAS_C, T_IBNR_TRSPL_RCC.RE_INDIANA_C, T_IBNR_TRSPL_RCC.RE_US_TREAS_C, T_IBNR_TRSPL_RCC.RE_VIRGINIA_C, T_IBNR_TRSPL_RCC.RE_PUERTO_RICO_C, T_IBNR_TRSPL_RCC.RE_CANADA_C, T_IBNR_TRSPL_RCC.RE_SC_C, T_IBNR_TRSPL_RCC.RE_GEORGIA_C, T_IBNR_TRSPL_RCC.RE_FST_CD_C, T_IBNR_TRSPL_RCC.INS_CD, T_IBNR_TRSPL_RCC.USER_CD, T_IBNR_TRSPL_RCC.TRANS_CD_ALPHA, T_IBNR_TRSPL_RCC.EIS_POL_HO, T_IBNR_TRSPL_RCC.EIS_POL_1_ID, T_IBNR_TRSPL_RCC.EIS_POL_CK_DGT, T_IBNR_TRSPL_RCC.EIS_TRN_CD, T_IBNR_TRSPL_RCC.EIS_TRACE_ID, T_IBNR_TRSPL_RCC.CREMS_NO_FAULT_IND, T_IBNR_TRSPL_RCC.CREMS_CLM_CD_A, T_IBNR_TRSPL_RCC.IBNR_TYPE_IND, T_IBNR_TRSPL_RCC.RE_CIGNA_POOL, T_IBNR_TRSPL_RCC.CONTRACT_BAL_IND, T_IBNR_TRSPL_RCC.RE_BEST_RATE, T_IBNR_TRSPL_RCC.RE_CAPTIV_IND, T_IBNR_TRSPL_RCC.RE_CIG_AFIL, T_IBNR_TRSPL_RCC.RE_FED_EMPL_ID, T_IBNR_TRSPL_RCC.RE_MAST_RC_CODE, T_IBNR_TRSPL_RCC.RE_MAST_REIN_TYPE, T_IBNR_TRSPL_RCC.RE_NAIC_NUMBER, T_IBNR_TRSPL_RCC.RE_REINS_LOCATION, T_IBNR_TRSPL_RCC.RE_REINS_NAME, T_IBNR_TRSPL_RCC.RE_REINS_PARNT_NAME, T_IBNR_TRSPL_RCC.RE_SUM_LEVL_CD, T_IBNR_TRSPL_RCC.RE_TYPE_OF_REIN, T_IBNR_TRSPL_RCC.RE_US_FORGN_IND, T_IBNR_TRSPL_RCC.ACCT_CD, T_IBNR_TRSPL_RCC.ACCT_NAME, T_IBNR_TRSPL_RCC.MGA_POOL_CD, T_IBNR_TRSPL_RCC.MGA_POOL_NAME, T_IBNR_TRSPL_RCC.INPUT_ACCT_NAME, T_IBNR_TRSPL_RCC.BR_TR_NUM, T_IBNR_TRSPL_RCC.CIG_TR_LAYR, T_IBNR_TRSPL_RCC.CIG_TR_NAME, T_IBNR_TRSPL_RCC.CIG_TR_NUM, T_IBNR_TRSPL_RCC.TR_LOSS_BAS_CD, T_IBNR_TRSPL_RCC.TR_PARTICI_RATE, T_IBNR_TRSPL_RCC.TR_PARTICI_COUNT, T_IBNR_TRSPL_RCC.TR_ROST_SENTL, T_IBNR_TRSPL_RCC.TR_SCHED_F_SENTL, T_IBNR_TRSPL_RCC.TR_CONTRCT_BEG_DT, T_IBNR_TRSPL_RCC.TR_CONTRCT_END_DT, T_IBNR_TRSPL_RCC.TR_LAYR_INCEPT_DT, T_IBNR_TRSPL_RCC.TR_PLA_DIV, T_IBNR_TRSPL_RCC.POL_NUMBER, T_IBNR_TRSPL_RCC.PAC, T_IBNR_TRSPL_RCC.PROD_LINE, T_IBNR_TRSPL_RCC.MGT_RESP, T_IBNR_TRSPL_RCC.MKT_REGION, T_IBNR_TRSPL_RCC.PROFIT_CNTR, T_IBNR_TRSPL_RCC.CONV_TERM_CD, T_IBNR_TRSPL_RCC.ORIGINAT_DT, T_IBNR_TRSPL_RCC.COMPANY_CD, T_IBNR_TRSPL_RCC.POL_YR, T_IBNR_TRSPL_RCC.POL_MO, T_IBNR_TRSPL_RCC.BR_OFC_CD, T_IBNR_TRSPL_RCC.PIIC_CD_CREMS, T_IBNR_TRSPL_RCC.MANUAL_REC_DATA, T_IBNR_TRSPL_RCC.ACCT_TEAM, T_IBNR_TRSPL_RCC.ACCT_DESK, T_IBNR_TRSPL_RCC.OBO_IAO_IND, T_IBNR_TRSPL_RCC.MEMO_REG_PYMT_CD, T_IBNR_TRSPL_RCC.MODE_CD_CREMS, T_IBNR_TRSPL_RCC.COLLAT_TYPE, T_IBNR_TRSPL_RCC.COLLAT_TRTY_NUM, T_IBNR_TRSPL_RCC.COLLAT_DIV, T_IBNR_TRSPL_RCC.COLLAT_TRTY_LAYER, T_IBNR_TRSPL_RCC.COLLAT_NUM, T_IBNR_TRSPL_RCC.ASSIGN_NUM, T_IBNR_TRSPL_RCC.ISSUER, T_IBNR_TRSPL_RCC.COLLAT_BRKR_CD, T_IBNR_TRSPL_RCC.COLLAT_SECTION, T_IBNR_TRSPL_RCC.OBO_CODE, T_IBNR_TRSPL_RCC.ST_CD_CREMS, T_IBNR_TRSPL_RCC.RE_TP_CD_CREMS, T_IBNR_TRSPL_RCC.REC_LEDGER, T_IBNR_TRSPL_RCC.REC_INS_NM, T_IBNR_TRSPL_RCC.POL_EXPIR_YR, T_IBNR_TRSPL_RCC.POL_EXPIR_MO, T_IBNR_TRSPL_RCC.POL_EXPIR_DY, T_IBNR_TRSPL_RCC.REC_AGE_IND, T_IBNR_TRSPL_RCC.REC_AGE_CTR, T_IBNR_TRSPL_RCC.ORIG_REC_RC, T_IBNR_TRSPL_RCC.ACCNT_YR, T_IBNR_TRSPL_RCC.ACCNT_MO, T_IBNR_TRSPL_RCC.SCHED_F_ACER, T_IBNR_TRSPL_RCC.SCHED_F_IBNR, T_IBNR_TRSPL_RCC.IN_FORCE_PREM, T_IBNR_TRSPL_RCC.UNEARNED_PREM_RESRVS, T_IBNR_TRSPL_RCC.OUTSTANDING_RESERVES, T_IBNR_TRSPL_RCC.CREDIT_PREMS_4033, T_IBNR_TRSPL_RCC.PD_LOSS_RECOV_4032, T_IBNR_TRSPL_RCC.DEFRD_PREMS_3067, T_IBNR_TRSPL_RCC.COLLAT_AMT, T_IBNR_TRSPL_RCC.PREMIUM_DEPOSITS, T_IBNR_TRSPL_RCC.ASSUMED_WRITTEN_PREM, T_IBNR_TRSPL_RCC.ASSUMED_COMMISSION, T_IBNR_TRSPL_RCC.PAID_LOSS, T_IBNR_TRSPL_RCC.PAID_EXPENSE, T_IBNR_TRSPL_RCC.ERR_CD1, T_IBNR_TRSPL_RCC.ERR_CD2, T_IBNR_TRSPL_RCC.ERR_CD3, T_IBNR_TRSPL_RCC.ERR_CD4, T_IBNR_TRSPL_RCC.ERR_CD5, T_IBNR_TRSPL_RCC.ERR_CD6, T_IBNR_TRSPL_RCC.ERR_CD7, T_IBNR_TRSPL_RCC.ERR_CD8, T_IBNR_TRSPL_RCC.ERR_CD9, T_IBNR_TRSPL_RCC.ERR_CD10, T_IBNR_TRSPL_RCC.ERR_CD14, T_IBNR_TRSPL_RCC.ERR_CD15, T_IBNR_TRSPL_RCC.ERR_CD16, T_IBNR_TRSPL_RCC.ERR_CD17, T_IBNR_TRSPL_RCC.ERR_CD18, T_IBNR_TRSPL_RCC.ERR_CD19, T_IBNR_TRSPL_RCC.ERR_CD20, T_IBNR_TRSPL_RCC.MSTR_SEQ, T_IBNR_TRSPL_RCC.PARTIC_SEQ, T_IBNR_TRSPL_RCC.TR_SPL_DT, T_IBNR_TRSPL_RCC.TR_SPL_IND, T_IBNR_TRSPL_RCC.MANUAL_SRC_IND, T_IBNR_TRSPL_RCC.ESTABLISH_datetime, T_IBNR_TRSPL_RCC.TRANS_XPIR_DT, T_IBNR_TRSPL_RCC.TRANS_EFF_DT, T_IBNR_TRSPL_RCC.WRITTEN_PREM, T_IBNR_TRSPL_RCC.COMMISSION, T_IBNR_TRSPL_RCC.REC_ENTRY_datetime, T_IBNR_TRSPL_RCC.REC_MONEY_CODE, T_IBNR_TRSPL_RCC.RECEIV_AMT, T_IBNR_TRSPL_RCC.REC_RECORD_TYPE, T_IBNR_TRSPL_RCC.REC_COLLECT_CAT_CD, T_IBNR_TRSPL_RCC.REC_DEBIT_CREDIT_IND, T_IBNR_TRSPL_RCC.REC_AGE_DAYS, T_IBNR_TRSPL_RCC.REC_OFFSET_CD, T_IBNR_TRSPL_RCC.REC_REASON_CD, T_IBNR_TRSPL_RCC.REC_TRANS_CD_CREM, T_IBNR_TRSPL_RCC.REC_UNIT_CD, T_IBNR_TRSPL_RCC.REC_DESK_CD, T_IBNR_TRSPL_RCC.REC_COLLECTOR, T_IBNR_TRSPL_RCC.REC_COORDINATOR, T_IBNR_TRSPL_RCC.REC_NET_AGE_DAYS, T_IBNR_TRSPL_RCC.REC_INSTALL_CD, T_IBNR_TRSPL_RCC.REC_AVG_AGE_DAYS, T_IBNR_TRSPL_RCC.PD_LOSS_RECOV, T_IBNR_TRSPL_RCC.PD_EXP_RECOV, T_IBNR_TRSPL_RCC.ASSUMED_UNPD_LOSS, T_IBNR_TRSPL_RCC.ASSUMED_UNEARNED_PREM

FROM T_IBNR_TRSPL_RCC

leonp SSCommitted Points: 1962 More actions · Answer 15

T_IBNR_INFO_RTC (6.1 million rows) has a non-clustered index on Sequence_no and TRSPL_RCC (6.9 million rows) has no indexes.