August 8, 2008 at 10:40 am
I have this query that my boss is trying to run and it
kills transaction log. It grows to 14GB and then it just
throws an error no disk space for LOG file.
SELECT
TIM.time_key
,CUS.cust_key
,CAC.cur_acct_key
,CRN.currency_key
,SRV.service_type_key
,BRA.branch_key
,AUD.audit_key
,CAG.cust_agrmnt_key
,FAC.trn_metric
,FAC.rate_used
,FAC.billed_revenue_orgnl_crncy_amt
,FAC.billed_revenue_amt
,current_timestamp
,NULL
,NULL
,NULL
,NULL
,FAC.STD_FEE_AMT_CALCULATION
FROM
STAGE_REVENUE_FACT_CCP AS FAC --Table scan; no PK; no unique indexes; existing indexes useless
INNER JOIN
AUDIT_DIM AS AUD ON FAC.RUN_NUM = AUD.RUN_NUM
INNER JOIN
TIME_DIM AS TIM ON FAC.PROCESSING_MONTH = TIM.CALENDAR_MONTH_NUM AND FAC.PROCESSING_YEAR = TIM.CALENDAR_YEAR_NUM
INNER JOIN --Table scan; no indexes at all; 26 million records
BAP_PROD_2006.dbo.cust_agrmnt_dim AS CAG ON FAC.CUST_AGRMNT_NUM = CAG.CUST_AGRMNT_NUM AND TIM.TIME_KEY = CAG.TIME_KEY
--INNER JOIN --Table scan; no indexes at all; 22 million records
--BAP_PROD_2006.dbo.cis_customer_Dim AS CUS ON FAC.CIS_KEY = CUS.CIS_KEY AND TIM.TIME_KEY = CUS.TIME_KEY
--INNER JOIN
--CURRENCY_DIM AS CRN ON FAC.CURRENCY_CODE = CRN.CURRENCY_CODE AND FAC.CURRENCY_END_DATE = CRN.CURRENCY_END_DATE
--INNER JOIN
--BAP_PROD_2006.dbo.service_type_dim AS SRV ON FAC.SERVICE_TYPE_CODE = SRV.SERVICE_TYPE_CODE AND TIM.TIME_KEY = SRV.TIME_KEY
--INNER JOIN
--BAP_PROD_2006.dbo.current_account_dim AS CAC ON FAC.CUR_ACCT_NUM = CAC.CUR_ACCT_NUM AND TIM.TIME_KEY = CAC.TIME_KEY
--INNER JOIN
--BAP_PROD_2006.dbo.BRANCH_DIM AS BRA ON FAC.BRANCH_TRANSIT_NUM = BRA.BRANCH_TRANSIT_NUM AND TIM.TIME_KEY = BRA.TIME_KEY
WHERE
--(CAG.CUST_AGRMNT_TYPE = 'CCP') AND
--(CAG.ACTIVE_ROW_IND = 'Y') AND
--(SRV.SERVICE_TYPE_REC_END_DATE = '12/31/9999') AND
--(CAC.ACTIVE_ROW_IND = 'Y') AND
--(BRA.BRANCH_REC_END_DATE = '12/31/9999') AND
(TIM.TIME_KEY BETWEEN 123 AND 125)
The problem is all joins with
BAP_PROD_2006 database tables.
These tables don't have PK,indexes. Nothing.
So I did a test.
I commented out all joins with BAP_PROD_2006
except the first one - BAP_PROD_2006.dbo.cis_customer_Dim (22 million rows)
and ran
SELECT COUNT(FAC.RATE_USED) AS Expr1 FROM ....
It took 40 sec to execute and returned 222,200 number.
So I thought if I add an index it would speed up the query a bit.
I added NONCLUSTERED index on CUST_AGRMNT_NUM column.
Now it takes 2.14 min to run the same
SELECT COUNT(FAC.RATE_USED) AS Expr1 FROM ....
Index made it worse?
August 8, 2008 at 10:49 am
Oops!
Actually the second run took only 6 sec.
So it probably built an execution plan and now it's faster.
So I'll try to add indexes to
all BAP_PROD_2006 tables and see what I get.
Am I doing the right thing?
I just add an index on the JOIN column.
August 8, 2008 at 11:04 am
An index that covers the join columns (and any where columns) will definitely speed things up. Even better yet, if you have the disk space and time to do it, would be an appropriate clustered index also. (Don't just do a clustered index, also do a "covering index".)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 8, 2008 at 11:08 am
Sorry GSquared,
Are you suggesting to have CLUSTERED not NONCLUSTERED index
on the same columns I did? Or use a different column?
And what is "covering index" ?
How do you do that?
August 8, 2008 at 11:14 am
No. Definitely not.
I'm suggesting that, if an appropriate column or set of columns exists, a clustered index could be created on those. That would probably be something that is either commonly used in range scans of the table (for example, get all the records between these two dates), or something that increases every time you add data to the table (for example, an Identity row or DateAdded row). That would be for the clustered index. If there is no such column/set of columns, don't add this. If you can add it, it generally results in much better performance for the whole table.
A covering index is an index that has all the columns you use in your query. In your case, that would be all the ones in the Join statements. You can also include any columns that are in your Select statement from that table. If you had any columns from that table in your Where statement, you would include those, too.
These are two separate things.
What you're planning, a regular index on all the columns in the Join, is a covering index for this query, and is a good idea. I'm just suggesting also taking a look to see if there could be an appropriate clustered index. "Also", not, "instead of".
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 8, 2008 at 11:20 am
It's clear about CLUSTERED index.
But I's still confused about "covering index".
Let's go back to my scenario.
BAP_PROD_2006.dbo.cust_agrmnt_dim.CUST_AGRMNT_NUM
is now has an index
Is this enough for this particular BAP_PROD_2006.dbo.cust_agrmnt_dim table
or I need some additional "covering index" in this table?
August 8, 2008 at 11:25 am
If you just need that one column, that's fine.
If you need more columns, like if you uncomment some of your joins, you'll need to add more columns to the index.
A "covering" index simply means the index has all the columns in it that you are using, for a particular table.
If you have a table with ColA, ColB and ColC as columns, and you only need ColA, a covering index would be:
create index IDX_Table_Covering on dbo.Table (ColA)
If you need ColA and ColB, the covering index would be:
create index IDX_Table_Covering on dbo.Table (ColA, ColB)
(Of course, I'm naming the index "IDX_Table_Covering", but you can name it whatever you want. I just like to use that pattern for my indexes.)
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 8, 2008 at 11:26 am
Are you working from the execution plan? You need to understand how SQL Server is accessing the data. The execution plan will tell you what's going on. It's less accurate, but if it takes to long to generate an actual execution plan, just generate an estimated. They're usually close enough.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 8, 2008 at 12:53 pm
Oh-oh...
Now my SELECT COUNT(*)
with just one JOIN to external table
hangs up. It takes more than 4 min.
I cancelled query. I actually have two indexes on this table:
cust_agrmnt_dim
1. [CUST_AGRMNT_NUM]
2. [TIME_KEY]
[TIME_KEY] was pretty useless. Maybe it's casuing a problem?
I am dropping it...
Done.
Didn't help.
Shoot! Now my query with one index on CUST_AGRMNT_NUM
that ran 6 sec an hour ago runs 22 sec.
What if somebody is doing something on this SQL box that's causing
problems?
sp_who2 output:
SPIDStatusLoginHostNameBlkByDBNameCommandCPUTimeDiskIOLastBatchProgramNameSPIDREQUESTID
1 BACKGROUND sa . . NULLRESOURCE MONITOR46008/08 10:32:53 1 0
2 BACKGROUND sa . . NULLLAZY WRITER 1593008/08 10:32:53 2 0
3 SUSPENDED sa . . NULLLOG WRITER 140008/08 10:32:53 3 0
4 BACKGROUND sa . . NULLLOCK MONITOR 46008/08 10:32:53 4 0
5 BACKGROUND sa . . masterSIGNAL HANDLER 0008/08 10:32:53 5 0
6 sleeping sa . . masterTASK MANAGER 0008/08 10:32:53 6 0
7 BACKGROUND sa . . masterTRACE QUEUE TASK140008/08 10:32:53 7 0
8 sleeping sa . . NULLUNKNOWN TOKEN 0008/08 10:32:53 8 0
9 BACKGROUND sa . . masterBRKR TASK 0008/08 10:32:53 9 0
10 BACKGROUND sa . . masterTASK MANAGER 0008/08 10:32:53 10 0
11 SUSPENDED sa . . masterCHECKPOINT 39013008/08 10:32:53 11 0
12 BACKGROUND sa . . masterBRKR EVENT HNDLR153208/08 10:32:53 12 0
13 BACKGROUND sa . . masterBRKR TASK 0008/08 10:32:53 13 0
14 sleeping sa . . masterTASK MANAGER 086608/08 10:32:53 14 0
15 sleeping sa . . masterTASK MANAGER 0008/08 10:32:53 15 0
16 sleeping sa . . masterTASK MANAGER 0008/08 10:32:53 16 0
17 sleeping sa . . masterTASK MANAGER 0008/08 10:32:53 17 0
18 sleeping sa . . masterTASK MANAGER 0008/08 10:32:53 18 0
19 sleeping sa . . masterTASK MANAGER 0008/08 10:32:53 19 0
20 sleeping sa . . masterTASK MANAGER 0008/08 10:32:53 20 0
21 sleeping sa . . masterTASK MANAGER 0008/08 10:32:53 21 0
51 sleeping sqladminBNSLKZGH4W . masterAWAITING COMMAND3283208/08 14:46:32Microsoft SQL Server Management Studio 51 0
52 sleeping cognos_adminsbuatsvr601 . CRNAWAITING COMMAND12981408/08 14:50:11JSQLConnect 52 0
53 sleeping cognos_adminsbuatsvr601 . CRNAWAITING COMMAND8931008/08 14:50:10JSQLConnect 53 0
54 sleeping cognos_adminsbuatsvr601 . CRNAWAITING COMMAND3952216008/08 14:49:57JSQLConnect 54 0
55 sleeping sqladminBNSLKZGH4W . masterAWAITING COMMAND15008/08 14:18:34Microsoft SQL Server Management Studio - Query55 0
56 RUNNABLE sqladminBNSLKZGH4W . BAP_PRODSELECT INTO 1561108/08 14:25:46Microsoft SQL Server Management Studio - Query56 0
57 sleeping APA_USERsbuatsvr601 . APAAWAITING COMMAND81215908/08 11:20:26 57 0
58 sleeping APA_USERsbuatsvr601 . APAAWAITING COMMAND64026408/08 11:20:26 58 0
59 sleeping cognos_adminsbuatsvr601 . CRNAWAITING COMMAND1019108/08 14:50:12JSQLConnect 59 0
60 sleeping BNS\KCheungBNSLKARH7X . masterAWAITING COMMAND32308/08 13:14:06Microsoft SQL Server Management Studio 60 0
61 sleeping TORFDMART1UNT\sqldmartusrvTORFDMART1UNT . msdbAWAITING COMMAND7812008/08 14:50:07SQLAgent - Alert Engine 61 0
62 sleeping gsmap_userBNSLKARH7X . masterAWAITING COMMAND317008/08 13:13:45Microsoft SQL Server Management Studio 62 0
63 sleeping APA_USERBNSLKYTV07 . APAAWAITING COMMAND0008/08 10:54:48Microsoft SQL Server Management Studio - Query63 0
64 SUSPENDED sqladminBNSLKZGH4W . BAP_PRODSELECT 6103314425208/08 14:49:56Microsoft SQL Server Management Studio - Query64 0
64 SUSPENDED BNSLKZGH4W . BAP_PRODSELECT 364022508/08 14:49:56Microsoft SQL Server Management Studio - Query64 0
64 SUSPENDED BNSLKZGH4W . BAP_PRODSELECT 351617208/08 14:49:56Microsoft SQL Server Management Studio - Query64 0
64 SUSPENDED BNSLKZGH4W . BAP_PRODSELECT 334422808/08 14:49:56Microsoft SQL Server Management Studio - Query64 0
64 SUSPENDED BNSLKZGH4W . BAP_PRODSELECT 3078008/08 14:49:56Microsoft SQL Server Management Studio - Query64 0
65 sleeping BNS\RAlilainGTBKA7PZW4 . masterAWAITING COMMAND232914708/08 14:44:00Microsoft SQL Server Management Studio 65 0
66 sleeping APA_USERBNSLKYTV07 . APAAWAITING COMMAND0008/08 10:56:05Microsoft SQL Server Management Studio - Query66 0
67 sleeping TORFDMART1UNT\sqldmartusrvTORFDMART1UNT . msdbAWAITING COMMAND0008/08 14:46:08SQLAGENT90 - Id 67 0
69 sleeping TORFDMART1UNT\sqldmartusrvTORFDMART1UNT . msdbAWAITING COMMAND3437408/08 13:53:26SQLAgent - Generic Refresher 69 0
70 sleeping TORFDMART1UNT\sqldmartusrvTORFDMART1UNT . msdbAWAITING COMMAND0008/08 13:31:05SQLAgent - Email Logger 70 0
71 sleeping APA_USERBNSLKYTV07 . APAAWAITING COMMAND0008/08 11:54:12Microsoft SQL Server Management Studio - Query71 0
August 8, 2008 at 1:08 pm
So I added an index on the second "external" table - BAP_PROD_2006.dbo.cis_customer_Dim,
uncommented second JOIN with this table.
Ran the query... and it's hanging...more than 10 min and still running....
August 8, 2008 at 1:29 pm
Index on the second external table
is on CHAR(17) column. And the values are like
"0002!E!ESYSTE0001"
It's probably not very effective.
The first index on the first external table
BAP_PROD_2006.dbo.cust_agrmnt_dim.CUST_AGRMNT_NUM
is VARCHAR(22) but it's actually a number (integer) if SQL converts it.
The values are like
"0000067","0000554"
Probably SQL Engine uses this index more effectively?
I start to doubt we will ever be able to run this query successfully..
August 8, 2008 at 1:30 pm
Can you post the table definitions and execution plan?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 8, 2008 at 1:40 pm
Table definitions:
---------------
/*
cust_agrmnt_dim
cis_customer_Dim
service_type_dim
current_account_dim
BRANCH_DIM
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cis_customer_Dim]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[cis_customer_Dim](
[TIME_KEY] [int] NOT NULL,
[CUST_KEY] [int] NOT NULL,
[CIS_KEY] [char](17) NOT NULL,
[CUST_TYPE_CODE] [char](2) NOT NULL,
[COMMRCL_CORP_TYPE] [char](10) NOT NULL,
[LAST_UPD_TS] [datetime] NOT NULL,
[CIS_ID] [char](15) NULL,
[CUST_NAME] [varchar](81) NULL,
[CUST_TYPE_NAME] [varchar](40) NULL,
[SCOTIA_CARD_NUM] [char](13) NULL,
[SIC_CODE] [char](4) NULL,
[CUST_SOURCE_NAME] [varchar](30) NULL,
[EMPLOYEE_QTY] [int] NULL,
[PREF_LANG_NAME] [varchar](20) NULL,
[ANNUAL_SALE_AMT] [money] NULL,
[ADDR_STREET] [varchar](81) NULL,
[ADDR_CITY_NAME] [varchar](25) NULL,
[ADDR_PROV_NAME] [varchar](20) NULL,
[ADDR_POST_CODE] [char](9) NULL,
[ADDR_CNTRY_CODE] [char](2) NULL,
[ADDR_CNTRY_NAME] [varchar](40) NULL,
[CARDS_NUM] [char](6) NULL,
[MASTER_CIS] [char](15) NULL,
[BRANCH_REP] [char](5) NULL,
[BRANCH_REP2] [char](5) NULL,
[CONTACT_NAME] [char](40) NULL,
[CONTACT_TITLE] [char](40) NULL,
[CONTACT_PHONE] [char](10) NULL,
[ADVERTIS_CODE] [char](1) NULL,
[ADVERTIS_DESC] [char](40) NULL,
[DATE_ESTABLISH] [datetime] NULL,
[CUST_START_DATE] [datetime] NULL,
[CUST_CLOSE_DATE] [datetime] NULL,
[BRANCH_TRANSIT_NUM] [char](5) NULL,
[BRANCH_NAME] [varchar](45) NULL,
[IG_CODE] [char](3) NULL,
[BRANCH_MANAGER_CODE] [char](2) NULL,
[ACTIVE_ROW_IND] [char](1) NULL,
[CBI] [char](9) NULL,
[SC_REPORTING_FLAG] [bit] NULL,
[MASTER_OR_CIS_ID] [char](15) NULL,
[EMP_NUM] [char](10) NULL,
[DM_CIS_STATUS] [char](6) NULL,
[ESM_MANAGER] [nvarchar](50) NULL,
[SC_REPORT_FLAG] [char](1) NULL,
[BORROWER] [char](1) NULL,
[Business_Unit] [varchar](25) NULL,
[Portfolio_Segment] [char](4) NULL,
[ANNUAL_SALE_LAST_UPD_DATE] [datetime] NULL,
[GTB_ON_FLAG] [char](1) NULL,
[GTB_ON_DATE] [datetime] NULL,
[GTB_ON_PRODUCT] [char](3) NULL,
[DUNS] [char](11) NULL,
[BUSINESS_LINE_SEGMENT] [nvarchar](25) NULL
) ON [cis_dim]
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[cis_customer_Dim]') AND name = N'IX_cis_customer_Dim')
CREATE NONCLUSTERED INDEX [IX_cis_customer_Dim] ON [dbo].[cis_customer_Dim]
(
[CIS_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [FG_Index]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BRANCH_DIM]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BRANCH_DIM](
[TIME_KEY] [int] NOT NULL,
[BRANCH_KEY] [int] NOT NULL,
[BRANCH_TRANSIT_NUM] [char](5) NOT NULL,
[BRANCH_REC_EFF_DATE] [datetime] NOT NULL,
[BRANCH_REC_END_DATE] [datetime] NOT NULL,
[BRANCH_NAME] [varchar](45) NOT NULL,
[VPO_CODE] [char](2) NOT NULL,
[VPO_NAME] [varchar](45) NOT NULL,
[REGION_CODE] [char](2) NOT NULL,
[REGION_NAME] [varchar](45) NOT NULL,
[LAST_UPD_TS] [datetime] NOT NULL,
[BRANCH_OPEN_DATE] [datetime] NULL,
[BRANCH_CLOSE_DATE] [datetime] NULL,
[BRANCH_STREET_ADDR] [varchar](45) NULL,
[BRANCH_CITY_NAME] [varchar](25) NULL,
[BRANCH_PROV_NAME] [varchar](20) NULL,
[BRANCH_POST_CODE] [char](9) NULL,
[CAU_TRANSIT_NUM] [char](5) NULL,
[CAU_DESCRIPTION] [char](42) NULL
) ON [DIM]
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[BRANCH_DIM]') AND name = N'BRN_BRANCH_RECORD_END_DATE')
CREATE NONCLUSTERED INDEX [BRN_BRANCH_RECORD_END_DATE] ON [dbo].[BRANCH_DIM]
(
[BRANCH_REC_END_DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [IDX]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[BRANCH_DIM]') AND name = N'BRN_BRANCH_TRANSIT_NUM')
CREATE NONCLUSTERED INDEX [BRN_BRANCH_TRANSIT_NUM] ON [dbo].[BRANCH_DIM]
(
[BRANCH_TRANSIT_NUM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [IDX]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[BRANCH_DIM]') AND name = N'BRN_TIME_KEY')
CREATE NONCLUSTERED INDEX [BRN_TIME_KEY] ON [dbo].[BRANCH_DIM]
(
[TIME_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [IDX]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[current_account_dim]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[current_account_dim](
[TIME_KEY] [int] NOT NULL,
[CUR_ACCT_KEY] [int] NOT NULL,
[CUR_ACCT_NUM] [char](12) NOT NULL,
[CUR_ACCT_NAME] [varchar](75) NOT NULL,
[CUR_ACCT_CRNCY_CODE] [char](3) NOT NULL,
[INTEREST_PAYING_IND] [char](1) NOT NULL,
[FLAT_FEE_IND] [char](1) NOT NULL,
[ACTIVE_ROW_IND] [char](1) NOT NULL,
[LAST_UPD_TS] [datetime] NOT NULL,
[CUR_ACCT_CLOSE_DATE] [datetime] NULL,
[STATEMENT_CYCLE_DAY_NUM] [smallint] NULL,
[BRANCH_MANAGER_CODE] [char](2) NULL,
[BUS_ACCT_PLAN_TYPE] [char](2) NULL,
[CUR_ACCT_OPEN_DATE] [datetime] NULL,
[DO_NOT_GEN_SRV_CHRG] [char](19) NULL,
[CA_STATUS] [char](8) NULL,
[CHQ_REC_FLAG] [char](11) NULL,
[MMS_ARRANGEMENT] [char](15) NULL,
[INTEREST_BASE_TYPE] [char](15) NULL,
[ARRANGEMENT_CLASSIFICATION] [char](15) NULL,
[IG_CODE] [char](2) NULL,
[EMP_NUM] [char](10) NULL,
[CREDIT_FAC_REVIEW_DATE] [datetime] NULL,
[STD_IPA_CHANGED_IND] [char](1) NULL,
[ASSOCIATION_CODE] [char](4) NULL,
[DOMICILE_BRANCH] [char](5) NULL,
[VOL_DISCOUNT_PERCENT] [decimal](6, 2) NULL,
[DAYS_IN_STATMT_CYCLE] [int] NULL
) ON [DIM]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cust_agrmnt_dim]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[cust_agrmnt_dim](
[CUST_AGRMNT_KEY] [int] NOT NULL,
[CUST_AGRMNT_NUM] [varchar](22) NOT NULL,
[CUST_AGRMNT_TYPE] [varchar](3) NOT NULL,
[SPECIAL_PRICING_IND] [char](1) NOT NULL,
[LAST_UPD_TS] [datetime] NOT NULL,
[CUST_AGRMNT_DATE] [datetime] NULL,
[SPECIAL_PRICING_EXPRY_DATE] [datetime] NULL,
[MMS_ACCT_CURR_QTY] [int] NULL,
[MMS_ACCT_PREV_QTY] [int] NULL,
[CCP_AGENT_CURR_QTY] [int] NULL,
[CCP_AGENT_PREV_QTY] [int] NULL,
[CUST_AGRMNT_TERM_DATE] [datetime] NULL,
[CUST_AGRMNT_TERM_REASON_DESC] [varchar](30) NULL,
[PRICING_POLICY] [char](12) NULL,
[ACTIVE_ROW_IND] [char](1) NULL,
[STD_PRICING_CHANGED_IND] [char](1) NULL,
[agrmnt_ind] [char](1) NULL,
[TIME_KEY] [int] NULL
) ON [DIM]
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[cust_agrmnt_dim]') AND name = N'IX_cust_agrmnt_dim')
CREATE NONCLUSTERED INDEX [IX_cust_agrmnt_dim] ON [dbo].[cust_agrmnt_dim]
(
[CUST_AGRMNT_NUM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [FG_Index]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[service_type_dim]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[service_type_dim](
[TIME_KEY] [int] NOT NULL,
[SERVICE_TYPE_KEY] [numeric](8, 3) NOT NULL,
[SERVICE_TYPE_CODE] [smallint] NOT NULL,
[SERVICE_TYPE_NAME] [char](60) NOT NULL,
[SERVICE_NAME] [char](60) NOT NULL,
[PRODUCT_CODE] [char](3) NOT NULL,
[PRODUCT_NAME] [varchar](50) NOT NULL,
[SERVICE_TYPE_REC_EFF_DATE] [datetime] NOT NULL,
[SERVICE_TYPE_REC_END_DATE] [datetime] NOT NULL,
[LAST_UPD_TS] [datetime] NOT NULL,
[SERVICE_LAUNCH_DATE] [datetime] NULL,
[SERVICE_TERM_DATE] [datetime] NULL,
[PRODUCT_LAUNCH_DATE] [datetime] NULL,
[PRODUCT_TERM_DATE] [datetime] NULL,
[FEE_TYPE_NAME] [varchar](30) NULL,
[FEE_UOM_DESC] [varchar](50) NULL,
[FEE_CURRENCY_CODE] [char](3) NULL,
[STD_FEE_AMT] [numeric](11, 6) NOT NULL,
[DIRECT_COST_AMT] [numeric](11, 6) NOT NULL,
[INDIRECT_COST_AMT] [numeric](11, 6) NOT NULL,
[COST_UOM_DESC] [varchar](50) NULL,
[COST_CURRENCY_CODE] [char](3) NULL,
[FULL_COST_AMT] [numeric](11, 6) NOT NULL,
[CORE_OR_NONCORE] [char](2) NOT NULL
) ON [DIM]
END
Execution Plan (only with first two "external" tables)
-------------------------------------------------
Actually I don't know how to save it in TXT format..?
I just use "SET SHOWPLAN_TEXT ON"
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1013]=CONVERT_IMPLICIT(int,[globalagg1017],0)))
|--Stream Aggregate(DEFINE:([globalagg1017]=SUM([partialagg1016])))
|--Parallelism(Gather Streams)
|--Filter(WHERE:([BAP_PROD_2006].[dbo].[cust_agrmnt_dim].[TIME_KEY] as [CAG].[TIME_KEY]=[BAP_PROD_2006].[dbo].[cis_customer_Dim].[TIME_KEY] as [CUS].[TIME_KEY] AND [BAP_PROD_2006].[dbo].[cust_agrmnt_dim].[TIME_KEY] as [CAG].[TIME_KEY]>=(12
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1007], [Expr1024]) WITH UNORDERED PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES:([FAC].[CUST_AGRMNT_NUM], [Expr1023]) OPTIMIZED WITH UNORDERED PREFETCH)
| |--Stream Aggregate(GROUP BY:([TIM].[TIME_KEY], [FAC].[CUST_AGRMNT_NUM]) DEFINE:([partialagg1016]=Count(*)))
| | |--Sort(ORDER BY:([TIM].[TIME_KEY] ASC, [FAC].[CUST_AGRMNT_NUM] ASC))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([CUS].[TIME_KEY], [FAC].[CUST_AGRMNT_NUM]))
| | |--Hash Match(Inner Join, HASH:([AUD].[RUN_NUM])=([FAC].[RUN_NUM]))
| | |--Parallelism(Distribute Streams, Broadcast Partitioning)
| | | |--Index Scan(OBJECT:([BAP_PROD].[dbo].[AUDIT_DIM].[AUDIT_DIM_RUN_NUM] AS [AUD]))
| | |--Filter(WHERE:([BAP_PROD].[dbo].[TIME_DIM].[TIME_KEY] as [TIM].[TIME_KEY]=[BAP_PROD_2006].[dbo].[cis_customer_Dim].[TIME_KEY] as [CUS].[TIME_KEY] AND [BAP_PROD_2006].[dbo].[cis_customer_Dim].[TIME_KEY]
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1010], [Expr1022]) WITH UNORDERED PREFETCH)
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([FAC].[CIS_KEY], [Expr1021]) OPTIMIZED WITH UNORDERED PREFETCH)
| | | |--Hash Match(Inner Join, HASH:([TIM].[CALENDAR_MONTH_NUM], [TIM].[CALENDAR_YEAR_NUM])=([Expr1014], [Expr1015]))
| | | | |--Parallelism(Distribute Streams, Broadcast Partitioning)
| | | | | |--Clustered Index Seek(OBJECT:([BAP_PROD].[dbo].[TIME_DIM].[PK__TIME_DIM__2003926C] AS [TIM]), SEEK:([TIM].[TIME_KEY] >= (123) AND [TIM].[TIME_KEY] <= (125)) ORDERED FORWARD)
| | | | |--Compute Scalar(DEFINE:([Expr1014]=CONVERT_IMPLICIT(smallint,[BAP_PROD].[dbo].[STAGE_REVENUE_FACT_CCP].[PROCESSING_MONTH] as [FAC].[PROCESSING_MONTH],0), [Expr1015]=CONVERT_IMPLICIT(
| | | | |--Table Scan(OBJECT:([BAP_PROD].[dbo].[STAGE_REVENUE_FACT_CCP] AS [FAC]))
| | | |--Index Seek(OBJECT:([BAP_PROD_2006].[dbo].[cis_customer_Dim].[IX_cis_customer_Dim] AS [CUS]), SEEK:([CUS].[CIS_KEY]=[BAP_PROD].[dbo].[STAGE_REVENUE_FACT_CCP].[CIS_KEY] as [FAC].[CIS_KEY])
| | |--RID Lookup(OBJECT:([BAP_PROD_2006].[dbo].[cis_customer_Dim] AS [CUS]), SEEK:([Bmk1010]=[Bmk1010]) LOOKUP ORDERED FORWARD)
| |--Index Seek(OBJECT:([BAP_PROD_2006].[dbo].[cust_agrmnt_dim].[IX_cust_agrmnt_dim] AS [CAG]), SEEK:([CAG].[CUST_AGRMNT_NUM]=[BAP_PROD].[dbo].[STAGE_REVENUE_FACT_CCP].[CUST_AGRMNT_NUM] as [FAC].[CUST_AGRMNT_NUM]) ORDERED FORW
|--RID Lookup(OBJECT:([BAP_PROD_2006].[dbo].[cust_agrmnt_dim] AS [CAG]), SEEK:([Bmk1007]=[Bmk1007]) LOOKUP ORDERED FORWARD)
August 8, 2008 at 3:00 pm
If you right-click on the execution plan, you'll have an option to save it. Save it, zip it, upload it to the forum.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 8, 2008 at 9:13 pm
So this Execution Plan I posted is not good enough?
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply