SQL Server Performance Forum – Threads Archive
long-running sproc only using 1 thread?
Hi all,I’ve got a question about a stored procedure. We have a stored procedure used for reporting that is taking quite a long time to run (10-15 minutes on avg) for some larger datasets. The server is a dual Xeon 2.8ghz with 6gb ram. Sql server is setup to use all the ram it wants, and all the processors it wants (4 logical, 2 physical). The minimum query plan threshold for considering parallel execution is set at 5. When this stored procedure runs, cpu usage is only 25%. If a second query is run at the same time, it goes to 50. How can I go about deciphering why this procedure isn’t being split into parallel plans? Is it an index thing, or something? Thanks for any and all advice
Peter L.
Hi ya, is this proc a single big query or lots of small ones? lots of small ones may not qualify to run using parallel plans… the thread through a proc is always single threaded but a single statement could use multiple CPUs if it is complex enough to warrant it and no one has fiddled with max degrees of parallelism Cheers
Twan
Hi, thanks for the reply.
It’s actually one big SELECT statement. The performance issues come, I believe, because of heavy use of UDF’s (used to calculate certain qtys needed for this and other reports) — could that impact the suitability of this query to have parallel plans? Also, you mentioned max degress of parallelism. Is that seperate from the Processor Control tab of the sql server configuration screen?
Thanks! EDIT: I checked the config value for max degree of parallelism, and it’s set at 0 (use all available processors), so it doesn’t look like that’s the issue
if you have a dual Xeon with HT, ie, 4 logical proc.
then a nonparallel query could peg 1 cpu, ie, 100% of 1 of the 4 cpus,
lets see: 1/4 = 0.25 = 25%
if you have a second query that also pegs a cpu, that would make 25+25 = 50% so the question should be: do you have a parallel execution plan, if not, why not.
a query that aggregates a large number of rows usually does generate a parallel execution plan
a query that actually returns a lot of rows may not because the cost of merging the multiple streams is rated as too high.
show actually query, any function calls, and provide the execution plan details with estimate rows and estimate executions,
]]>