SQL Server Performance Forum – Threads Archive

STP not using parallelism

Hi, Im having a complicated which is using parallelism when i run it in qyery analyzer, but when i put the statements into a stored procedure it runs without. I tried to remove the parameters for the stored procedure and hardcode them inside the procedure (not in the actual query) but with the same results. Excution time with parellelism is around 10 seconds, and without 80 secs. Anyone having a qlue why the server will not choose to use parallelism inside a procedure?

Did you take a look of execution plan, with sp?
Also how many processor do you have and how many are for paralellims?
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

i would use profiler -> performance -> degree of parallelism & integer data
to determine if a parallel plan is being used
8X is really too much to be explained by parallelism unless you have a machine with 16 physical procs. trying executing the proc from Query Analyzer
if this runs in 10sec, then from the client appl, declare the command objecy as text even though its a proc, and append the parameters as string. does executing this proc or query generate multiple compiles or recompiles? also profiler or SET STATISTICS TIME ON to compare duration and cpu

Thanks for taking the time guys!!! >>Did you take a look of execution plan, with sp?
Yes, the actual execution plan of the sp does not have parallism, this is when i call the procedure from query analyzer.
When i execute the same statements in query analyzer, it gives the same actual execution plan but WITH parallelism. >>Also how many processor do you have and how many are for paralellims?
4 cpu’s in total, 2 for paralelism. I tried to but the inside of the procedure into a sp_execute statement, same result’s, it ran without parallelism. >>i would use profiler -> performance -> degree of parallelism & integer data
>>to determine if a parallel plan is being used
Im sure it is not used, as I can see the actual execution plan of the stored procedure.
I cannot use profiler as were not sa’s on the machine, but i guess the actual execution plan will do. >>does executing this proc or query generate multiple compiles or recompiles?
No, the sp is not recompiled at all, tried "with recompile" to see if it had an out dated plan though.
The query is naturally compiled once, taking 750 ms.
As the script uses a temp. (declare) table (about 20 rows) i tried to add option keep plan just to be sure, no success though. >>also profiler or SET STATISTICS TIME ON to compare duration and cpu
This is taken this morning:
sp result : SQL Server Execution Times: CPU time = 29438 ms, elapsed time = 163281 ms.
query result : SQL Server Execution Times: CPU time = 5125 ms, elapsed time = 13257 ms. FYI:
sql server version : 8.00.2039 (Intel X86)
we’re running in READ UNCOMMITTED isolation

]]>