[RESOLVED] Setting Parallelism for an SP in SQL Server

[GaryMazzone]

Hi

Does anyone know if it is possible to set the degree of parallelism for a specific Stored proc in SQL Server?

We are running SQL Server 2005 Enterprise Edition.

I'm having an issue all of a sudden with one SP coming up with a bad execution plan and want to see to I can force the SP to run on only once processor.

As another option how would I force a new plan to be generated for the SP?

Thanks

Gary

[szlamany]

Wow - nice question

Go here and post it

http://www.microsoft.com/communities...&lang=en&cr=US

And then come back and tell us what they said!

[GaryMazzone]

OK I got part of the answer reading into Books on Line.

For forcing the degree of Parallelism on an SP I can use the Option MaxDOP 1.

This would be listed at the end of the statment (either after the Where clause or Grouping or Order By).

Where yada = yada Option (MAXDOP 1).

As for the recompile I found the With recomplie of course but still got a junk plan.

The problem is that if I take the statements out of the proc and run them as a simple query the results return in 4 sec. If I run the proc 13 hrs later still not complete. The Execution plans look extremly different for the Ad Hock query and the SP.

[szlamany]

Nothing easy here...

Can you look at the "good execution" plan and start forcing "hints" all over the sproc to force that execution plan? Hints are generally to be avoided - but seems like a possibility here.

Obviously - 13 hours means something ugly is happening. Are table locks occuring? I've had nightmares like that myself - nothing like 13 hours - but in the couple of hour range.

Can you give us a summary of what the sproc is doing?

[GaryMazzone]

Haven't seen any locks (the selects are all with NOLOCK).

The problem appears to be 2 new conditions that where added the the Where clause. (Both of them are Or conditions)

Where is the query

 SELECT   
 [Profile].[ProfileId]  
    ,[Profile].[ProfileTypeId]  
    ,[Profile].[SicCodeId]  
    ,[Profile].[CompanyName]  
    ,[Profile].[AddressLine1]  
    ,[Profile].[AddressLine2]  
    ,[Profile].[AddressLine3]  
    ,[Profile].[Url]  
    ,[Profile].[PhoneNumber]  
    ,[Profile].[FaxNumber]  
    ,[Profile].[Email]  
    ,[Profile].[City]  
    ,[Profile].[StateProvince]  
    ,[Profile].[PostalCode]  
    ,[Profile].[Commission]  
    ,[Profile].[ExternalSicCode]  
    ,[Profile].[CountryId]  
    ,[Profile].[ExternalCountry]  
    ,[Profile].[CreateDate]  
    ,[Profile].[LastModifiedDate]  
    ,[Profile].[IsCommissionable]  
    ,[Profile].[ExternalProfileId]
    ,[SetupValue].[Name] AS SicCode 
    ,[Profile].[IATANumber] 
  FROM [Profile] WITH (NOLOCK)  
 LEFT JOIN [SetupValue] WITH (NOLOCK) --Added as per CR0050710  
   ON [Profile].SicCodeId = [SetupValue].SetupValueId  
   
  WHERE ([Profile].LastModifiedDate BETWEEN @startDate AND @endDate AND [Profile].[SubscriptionId] = @subscriptionId)
OR
[Profile].[ProfileId] IN (
SELECT MasterAgencyProfileId FROM  meetinginfo WITH (NOLOCK) WHERE  EntityTypeId=1 AND EntityId IN(
SELECT RfpId FROM RfpAuditLog WITH (NOLOCK) WHERE SubscriptionId = @subscriptionId 
AND EffectiveDate BETWEEN @startDate AND @endDate))
OR
[Profile].[ProfileId] IN (
SELECT MasterCompanyProfileId FROM  meetinginfo WITH (NOLOCK) WHERE EntityTypeId=1 AND EntityId IN(
SELECT RfpId FROM RfpAuditLog WITH (NOLOCK) WHERE SubscriptionId = @subscriptionId 
AND EffectiveDate BETWEEN @startDate AND @endDate))
OR
[Profile].[ProfileId] IN (
SELECT TransactionalAgencyProfileId FROM  meetinginfo WITH (NOLOCK) WHERE  EntityTypeId=1 AND EntityId IN(
SELECT RfpId FROM RfpAuditLog WITH (NOLOCK) WHERE SubscriptionId = @subscriptionId 
AND EffectiveDate BETWEEN @startDate AND @endDate))
OR
[Profile].[ProfileId] IN (
SELECT TransactionalCompanyProfileId FROM  meetinginfo WITH (NOLOCK) WHERE  EntityTypeId=1 AND EntityId IN(
SELECT RfpId FROM RfpAuditLog WITH (NOLOCK) WHERE SubscriptionId = @subscriptionId 
AND EffectiveDate BETWEEN @startDate AND @endDate))

--Edited to change from SQL tag to Code tags

[szlamany]

Gary - could you edit that post and change it to [code] tags - maybe that will help with the display - I'm seeing way too much "escape character" garbage

[szlamany]

I would suggest a change to the way that WHERE clause is written.

Since it's a sproc why not put all the profile id's into a temp table (or a table variable if the number is low - like around a couple of 100).

Let's say you go the temp table route

Create #ProfileList (ProfileId asdf)
Insert into #ProfileList
   Select ProfileId From Profile
	WHERE ([Profile].LastModifiedDate BETWEEN @startDate AND @endDate AND [Profile].[SubscriptionId] = @subscriptionId)

Union -- do not use UNION ALL as we want to get rid of duplicate profile id's

   SELECT MasterAgencyProfileId FROM  meetinginfo WITH (NOLOCK) WHERE  EntityTypeId=1 AND EntityId IN(
    SELECT RfpId FROM RfpAuditLog WITH (NOLOCK) WHERE SubscriptionId = @subscriptionId 
    AND EffectiveDate BETWEEN @startDate AND @endDate)

Union

   SELECT MasterCompanyProfileId FROM  meetinginfo WITH (NOLOCK) WHERE EntityTypeId=1 AND EntityId IN(
    SELECT RfpId FROM RfpAuditLog WITH (NOLOCK) WHERE SubscriptionId = @subscriptionId 
    AND EffectiveDate BETWEEN @startDate AND @endDate)

Union

   SELECT TransactionalAgencyProfileId FROM  meetinginfo WITH (NOLOCK) WHERE  EntityTypeId=1 AND EntityId IN(
    SELECT RfpId FROM RfpAuditLog WITH (NOLOCK) WHERE SubscriptionId = @subscriptionId 
    AND EffectiveDate BETWEEN @startDate AND @endDate)


Union

   SELECT TransactionalCompanyProfileId FROM  meetinginfo WITH (NOLOCK) WHERE  EntityTypeId=1 AND EntityId IN(
    SELECT RfpId FROM RfpAuditLog WITH (NOLOCK) WHERE SubscriptionId = @subscriptionId 
    AND EffectiveDate BETWEEN @startDate AND @endDate)

Then your actual query would come from this table

SELECT   
 [Profile].[ProfileId]  
    ,[Profile].[ProfileTypeId]  
    ,[Profile].[SicCodeId]  
    ,[Profile].[CompanyName]  
    ,[Profile].[AddressLine1]  
    ,[Profile].[AddressLine2]  
    ,[Profile].[AddressLine3]  
    ,[Profile].[url]  
    ,[Profile].[PhoneNumber]  
    ,[Profile].[FaxNumber]  
    ,[Profile].[email]  
    ,[Profile].[City]  
    ,[Profile].[StateProvince]  
    ,[Profile].[PostalCode]  
    ,[Profile].[Commission]  
    ,[Profile].[ExternalSicCode]  
    ,[Profile].[CountryId]  
    ,[Profile].[ExternalCountry]  
    ,[Profile].[CreateDate]  
    ,[Profile].[LastModifiedDate]  
    ,[Profile].[IsCommissionable]  
    ,[Profile].[ExternalProfileId]
    ,[SetupValue].[Name] AS SicCode 
    ,[Profile].[IATANumber] 
  FROM #ProfileList PL
  Left Join [Profile] PR on PR.ProfileID=PL.ProfileId WITH (NOLOCK)  
 LEFT JOIN [SetupValue] WITH (NOLOCK) --Added as per CR0050710  
   ON [Profile].SicCodeId = [SetupValue].SetupValueId

If you have a problem with temp tables that whole select/union query could go into a DERIVED QUERY and come FROM that. Would basically do the exact same thing.

This would definately get rid of that heavy WHERE clause - and you can also tell if the SELECT/UNION is still slow and further fix it up (it does have it's own IN () sub-query issue that could be put first into another temp table for a staged build)

[GaryMazzone]

I was actually thinking along the same line. I used the method without the temp table and did a Union in the Where clause holding the 4 selects. I need to test that method out yet also


Thanks Steve