December 15, 2011 at 7:46 pm
Fraggle-805517 (12/15/2011)
An update on this. The MAXDOP statement worked the best. It took the entire process down from 8-15hours to under 5 minutes.Thanks,
Nathan
Heh... call it what you want, the important part is that you fixed it. Must be Christmas instead of Halloween. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
December 15, 2011 at 8:46 pm
as designed.
December 16, 2011 at 4:22 am
Fraggle-805517 (12/15/2011)
An update on this. The MAXDOP statement worked the best. It took the entire process down from 8-15hours to under 5 minutes.
Not fixed. What you've done ist tell SQL 'My way!'. Can you post the non-parallel plan? I want to see if I can find why it's paralleling inappropriately.
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
December 16, 2011 at 8:14 am
Gila, more than happy to help. I would be interested to know your thoughts on it. I am usually pretty good on this stuff (at least I think I am 😛 ) and I wasn't seeing why it would go parallel and why it made such a big difference.
Edit:// you are correct, it isn't "fixed" as I put it. However, management is happy, which, at least for the time being, is a good thing. However, I still want a better way to do it.
Fraggle
December 16, 2011 at 10:43 am
Long weekend here, will take a look on Mon.
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
December 16, 2011 at 2:15 pm
AWESOME Gila. Thanks. Fraggle
December 21, 2011 at 1:00 pm
Gila,
Just wanted to follow up and see if you saw anything on those plans.
Thanks,
Fraggle
December 22, 2011 at 7:03 am
Add one Dummy Flag Filed to ContactGroupMember
Update This Flag as 1
by Following Command
UPDATE ContactGroupMember
SET
FROM ContactGroupMember AS CGM
INNER JOIN #FinalResults AS FR ON CGM.ContactGroupId = FR.ContactGroupID
WHERE cgm.ContactId <> fr.OwnerID
December 22, 2011 at 7:07 am
Add one Dummy Flag Filed to ContactGroupMember
Update This Flag as 1
by Following Command
UPDATE ContactGroupMember
SET Flag = 1
FROM ContactGroupMember
INNER JOIN #FinalResults AS FR ON ContactGroupMember.ContactGroupId = FR.ContactGroupID and
ContactGroupMember.ContactId = fr.OwnerID
Then use following Command for deleting.
DELETE ContactGroupMember
Where Flag is NULL
Mahesh Patel
Let me know if it helps.
December 22, 2011 at 11:35 am
Apologies, got tied up with a misbehaving SAN.
The row count's off by a couple of orders of magnitude, so SQL thinks far more rows than there are, this will lead to it paralleling where it shouldn't.
What's the definition of the index Test on ContactGroupMember? What's the unique columns in that table?
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
December 28, 2011 at 10:36 am
Gila,
Sorry for not getting back with you sooner. For some reason I never saw an email. Still not sure what happened.
In answer to your question about the 'test' index, it was one that I thought might be beneficial.
CREATE NONCLUSTERED INDEX [TEST] ON [dbo].[ContactGroupMember]
(
[ContactId] 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, FILLFACTOR = 80) ON [PRIMARY]
GO
The Clustered index is also the primary key.
ALTER TABLE [dbo].[ContactGroupMember] ADD CONSTRAINT [PK_ContactGroupMember] PRIMARY KEY CLUSTERED
(
[ContactGroupId] ASC,
[ContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
I added the index as Contact was getting referenced without ContactGroupID, and as such, was doing a table scan on some other queries due to the "left to right" reading of the clustered index.
Thanks,
Fraggle
December 28, 2011 at 10:41 am
Could you try changing the index Test to a unique index on ContactId, ContactGroupId and see what the execution time (with and without parallelism) looks like?
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
December 28, 2011 at 12:27 pm
GilaMonster (12/28/2011)
Could you try changing the index Test to a unique index on ContactId, ContactGroupId and see what the execution time (with and without parallelism) looks like?
I can and will to test it, but why? Wouldn't that essentially be the same as the Clustered Index except the columns are reversed? Just curious as to the reason while the test are going on.
Thanks,
Fraggle
December 28, 2011 at 12:41 pm
Fraggle-805517 (12/28/2011)
I can and will to test it, but why? Wouldn't that essentially be the same as the Clustered Index except the columns are reversed?
Yes, except much smaller than the cluster (because it doesn't have the rest of the columns at the leaf level). Same reason you created the index initially, with the second column and unique to try and fix the cardinality error.
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
December 30, 2011 at 8:39 am
Gila,
There is absolutely no difference in duration after adding the new index. The one using parallism doesn't even use the new index. The execution plans are attached for your review.
Thanks,
Fraggle
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply