May 6, 2016 at 10:21 am
Hello
I have a query:
select count(*)
FROM F_POLICY_PREMIUM POLICY_DATA
INNER JOIN D_PRODUCT
ON POLICY_DATA.PRODUCT_SKEY = D_PRODUCT.PRODUCT_SKEY
INNER JOIN D_BUSINESS_AREA
ON POLICY_DATA.BUSINESS_AREA_SKEY = D_BUSINESS_AREA.BUSINESS_AREA_SKEY
INNER JOIN D_POLICY
ON POLICY_DATA.POLICY_SKEY = D_POLICY.POLICY_SKEY
INNER JOIN D_CALENDAR AS COVER_START_CALENDAR
ON POLICY_DATA.COVER_START_DATE_SKEY = COVER_START_CALENDAR.CALENDAR_SKEY
INNER JOIN D_CALENDAR AS COVER_EXPIRY_CALENDAR
ON POLICY_DATA.COVER_EXPIRY_DATE_SKEY = COVER_EXPIRY_CALENDAR.CALENDAR_SKEY
INNER JOIN D_CALENDAR AS LAPSED_CALENDAR
ON POLICY_DATA.LAPSED_DATE_SKEY = LAPSED_CALENDAR.CALENDAR_SKEY
INNER JOIN D_POLICY_POSTING AS POSTING
ON POLICY_DATA.POLICY_POSTING_SKEY = POSTING.POLICY_POSTING_SKEY
WHERE D_PRODUCT.PRODUCT_DESC <> N'Claims Dummy Product'
This works fine and responds quickly
However, if I add ANY of the following filters there's an issue
and POSTING.POLICY_POSTING_LEVEL_1 = 'Posted to Accounts'
and POLICY_DATA.TRANSACTION_DATE_SKEY > 20000101
and BUSINESS_AREA_NAME in (N'Commercial', N'Online-Commercial', N'Commercial - DA')
It takes longer to respond and when I look at the execution plan, suddenly the estimated and actual rows differ dramatically
There's an Hash Join with an exclamation mark against it
Upon investigation, it as 'Operator used tempdb to spill data during execution with spill level 1' in the Warning property
Stats are up to date
I've also performed with fullscan (researched suggestion)
It's not huge (around 2.5 million rows )
I think my indexes are ok
Does anybody have a suggestion?
Note, this is part of a bigger query
If I can crack and understand this I should be able to resolve further slow running issues
Thanks
Damian.
- Damian
May 6, 2016 at 12:50 pm
Can you post the execution plans of the queries (the one that's fast, and the slower plans with the added clauses)?
Actual plans please.
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
May 7, 2016 at 9:35 am
Hi
I've attached a before and after plan
Before uses:
select count(*)
FROM F_POLICY_PREMIUM POLICY_DATA
INNER JOIN D_PRODUCT
ON POLICY_DATA.PRODUCT_SKEY = D_PRODUCT.PRODUCT_SKEY
INNER JOIN D_BUSINESS_AREA
ON POLICY_DATA.BUSINESS_AREA_SKEY = D_BUSINESS_AREA.BUSINESS_AREA_SKEY
INNER JOIN D_POLICY
ON POLICY_DATA.POLICY_SKEY = D_POLICY.POLICY_SKEY
INNER JOIN D_CALENDAR AS COVER_START_CALENDAR
ON POLICY_DATA.COVER_START_DATE_SKEY = COVER_START_CALENDAR.CALENDAR_SKEY
INNER JOIN D_CALENDAR AS COVER_EXPIRY_CALENDAR
ON POLICY_DATA.COVER_EXPIRY_DATE_SKEY = COVER_EXPIRY_CALENDAR.CALENDAR_SKEY
INNER JOIN D_CALENDAR AS LAPSED_CALENDAR
ON POLICY_DATA.LAPSED_DATE_SKEY = LAPSED_CALENDAR.CALENDAR_SKEY
INNER JOIN D_POLICY_POSTING AS POSTING
ON POLICY_DATA.POLICY_POSTING_SKEY = POSTING.POLICY_POSTING_SKEY
WHERE D_PRODUCT.PRODUCT_DESC <> N'Claims Dummy Product'
After uses:
select count(*)
FROM F_POLICY_PREMIUM POLICY_DATA
INNER JOIN D_PRODUCT
ON POLICY_DATA.PRODUCT_SKEY = D_PRODUCT.PRODUCT_SKEY
INNER JOIN D_BUSINESS_AREA
ON POLICY_DATA.BUSINESS_AREA_SKEY = D_BUSINESS_AREA.BUSINESS_AREA_SKEY
INNER JOIN D_POLICY
ON POLICY_DATA.POLICY_SKEY = D_POLICY.POLICY_SKEY
INNER JOIN D_CALENDAR AS COVER_START_CALENDAR
ON POLICY_DATA.COVER_START_DATE_SKEY = COVER_START_CALENDAR.CALENDAR_SKEY
INNER JOIN D_CALENDAR AS COVER_EXPIRY_CALENDAR
ON POLICY_DATA.COVER_EXPIRY_DATE_SKEY = COVER_EXPIRY_CALENDAR.CALENDAR_SKEY
INNER JOIN D_CALENDAR AS LAPSED_CALENDAR
ON POLICY_DATA.LAPSED_DATE_SKEY = LAPSED_CALENDAR.CALENDAR_SKEY
INNER JOIN D_POLICY_POSTING AS POSTING
ON POLICY_DATA.POLICY_POSTING_SKEY = POSTING.POLICY_POSTING_SKEY
WHERE D_PRODUCT.PRODUCT_DESC <> N'Claims Dummy Product'
and POSTING.POLICY_POSTING_LEVEL_1 = 'Posted to Accounts'
Thoughts are appreciated
Thanks
- Damian
May 7, 2016 at 10:43 am
Actual plans please. The estimated (which is what you posted) are lacking run-time information.
Could you also please post the definitions of the indexes on POLICY_DATA?
Is this a star-schema?
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
May 7, 2016 at 1:25 pm
Hi
Yes, it's a star schema
I've attached actual plan along with code for the indexes (there's quite a few)
Thanks
Damian.
- Damian
May 11, 2016 at 2:18 am
Last night, I fixed this with the creation of this index:
CREATE NONCLUSTERED INDEX [IX_F_POLICY_PREMIUM_TRANSACTION_DATE_SKEY] ON [dbo].[F_POLICY_PREMIUM]
(
[TRANSACTION_DATE_SKEY] ASC
)
INCLUDE ( [COVER_START_DATE_SKEY],
[POLICY_SKEY],
[POLICY_POSTING_SKEY],
[BUSINESS_AREA_SKEY],
[PRODUCT_SKEY],
[COVER_EXPIRY_DATE_SKEY],
[LAPSED_DATE_SKEY]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
I had a similar index that had the columns as key columns rather than including (called IX_F_POLICY_PREMIUM_FULL)
I dropped IX_F_POLICY_PREMIUM_FULL and created the new one
The strange this is, when I first I built this new index I still had the issue
I then built it bit by bit i.e. kept adding more included columns and expanding my select (also including the corresponding filter)
Once everything was in place it worked, ran efficiently and created a sound execution plan
Just checked again this morning and it continues to look good
Not sure why I had to build the index in this manner though
Thanks
Damian.
- Damian
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply