[RESOLVED] Setting Parallelism for an SP in SQL Server

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

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!

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.

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?

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

Code:

---

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

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

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

Code:

---

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

Code:

---

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)

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