Query Parallelism (maxdop)

I have 4 processor SQL server. When I run this query in query analyzer
(two table joins with wly aggregation approx 500,000 rows each) ...
it takes only 7 seconds... when I run it in a stored procedure... it
takes about 60 minutes. I dont see parallelism in the execution plan of
SP... I have already tried hint maxdop... it did'nt work... Im not
sure what I might be running into'
Any help is greatly appreciated...Google "parameter sniffing".
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"zomer" <noneee@.gmail.com> wrote in message
news:1145384015.190289.310680@.e56g2000cwe.googlegroups.com...
I have 4 processor SQL server. When I run this query in query analyzer
(two table joins with wly aggregation approx 500,000 rows each) ...
it takes only 7 seconds... when I run it in a stored procedure... it
takes about 60 minutes. I dont see parallelism in the execution plan of
SP... I have already tried hint maxdop... it did'nt work... Im not
sure what I might be running into'
Any help is greatly appreciated...|||Try using WITH RECOMPILE on your stored procedure just as an initial test.
It may be getting a less than optimal execution plan. See
http://www.dbtalk.net/microsoft-pub...ter-203396.html
for some thoughts.
I'm running into some similar issues with ADO taking ludicrously long while
Query Analyzer goes quickly. Haven't tracked down the exact cause yet,
though it appears many other people have had the same issue.
Mike
"zomer" <noneee@.gmail.com> wrote in message
news:1145384015.190289.310680@.e56g2000cwe.googlegroups.com...
>I have 4 processor SQL server. When I run this query in query analyzer
> (two table joins with wly aggregation approx 500,000 rows each) ...
> it takes only 7 seconds... when I run it in a stored procedure... it
> takes about 60 minutes. I dont see parallelism in the execution plan of
> SP... I have already tried hint maxdop... it did'nt work... Im not
> sure what I might be running into'
> Any help is greatly appreciated...
>|||Tom -
Are there other things besides the parameter sniffing that could cause the
performance difference?
I'm running into a similar problem, except here's what's happening:
1. I have SQL Profiler on in production.
2. I catch the "slow" procedure
3. Immediately I copy and paste the text into Query Analyzer and execute the
query on the same production server -- and it executes quickly.
Same parameters. Same execution plan I would think (I'm not seeing any
recompiles). The parameter sniffing discussion made sense but it doesn't
seem to fit this scenario since the procedure is already compiled and I'm
executing it with the same paramters that made it run slow from the app.
Thanks for any help,
Mike
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ucLVCUxYGHA.3880@.TK2MSFTNGP04.phx.gbl...
> Google "parameter sniffing".
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "zomer" <noneee@.gmail.com> wrote in message
> news:1145384015.190289.310680@.e56g2000cwe.googlegroups.com...
> I have 4 processor SQL server. When I run this query in query analyzer
> (two table joins with wly aggregation approx 500,000 rows each) ...
> it takes only 7 seconds... when I run it in a stored procedure... it
> takes about 60 minutes. I dont see parallelism in the execution plan of
> SP... I have already tried hint maxdop... it did'nt work... Im not
> sure what I might be running into'
> Any help is greatly appreciated...
>|||I'd take a close look at the Execution Plan for each (sproc and QA runs) -
sounds like it's not using an index somewhere along the line.
HTH
"zomer" wrote:

> I have 4 processor SQL server. When I run this query in query analyzer
> (two table joins with wly aggregation approx 500,000 rows each) ...
> it takes only 7 seconds... when I run it in a stored procedure... it
> takes about 60 minutes. I dont see parallelism in the execution plan of
> SP... I have already tried hint maxdop... it did'nt work... Im not
> sure what I might be running into'
> Any help is greatly appreciated...
>|||More info for my previous post: When I profiled SP:Stmt Starting and
SP:StmtEnding, the application executed query showed the length of the
SELECT statements as being lengthened (there are multiple result sets
returned). ALL of the indivdual SELECTs were propotionally slower.
To me this seems to be some type of interaction problem between ADO and SQL
Server, not a SQL Server performance issue. The ADO client uses client side
cursors. This brings up the question: Can slow response on the
SQLOLEDB/ADO driver side affect duration times in SQL Profiler ?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ucLVCUxYGHA.3880@.TK2MSFTNGP04.phx.gbl...
> Google "parameter sniffing".
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "zomer" <noneee@.gmail.com> wrote in message
> news:1145384015.190289.310680@.e56g2000cwe.googlegroups.com...
> I have 4 processor SQL server. When I run this query in query analyzer
> (two table joins with wly aggregation approx 500,000 rows each) ...
> it takes only 7 seconds... when I run it in a stored procedure... it
> takes about 60 minutes. I dont see parallelism in the execution plan of
> SP... I have already tried hint maxdop... it did'nt work... Im not
> sure what I might be running into'
> Any help is greatly appreciated...
>|||I think what you're seeing is data and/or plan caching - and that's by
design. When a query first enters SQL Server, a plan is created and cached.
This takes time. Upon subsequent execution, the original plan is (likely)
used, and this goes faster. However, any data that were read by the
just-executed query are likely now in data cache. Thus, the next time the
query is run, it's getting the data from cache - not from disk. The more
RAM you have, the bigger the cache you have - and the faster (in most cases)
that SQL Server will run.
If you want a pure apples to apples comparison, run the following before
each test:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
This flushes the proc and data caches, respectively. Now, you get a fresh
plan - and you get your data from disk. A subsequent run without running
these DBCC's should go significantly faster.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:uTK$ihxYGHA.1192@.TK2MSFTNGP03.phx.gbl...
Tom -
Are there other things besides the parameter sniffing that could cause the
performance difference?
I'm running into a similar problem, except here's what's happening:
1. I have SQL Profiler on in production.
2. I catch the "slow" procedure
3. Immediately I copy and paste the text into Query Analyzer and execute the
query on the same production server -- and it executes quickly.
Same parameters. Same execution plan I would think (I'm not seeing any
recompiles). The parameter sniffing discussion made sense but it doesn't
seem to fit this scenario since the procedure is already compiled and I'm
executing it with the same paramters that made it run slow from the app.
Thanks for any help,
Mike
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ucLVCUxYGHA.3880@.TK2MSFTNGP04.phx.gbl...
> Google "parameter sniffing".
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "zomer" <noneee@.gmail.com> wrote in message
> news:1145384015.190289.310680@.e56g2000cwe.googlegroups.com...
> I have 4 processor SQL server. When I run this query in query analyzer
> (two table joins with wly aggregation approx 500,000 rows each) ...
> it takes only 7 seconds... when I run it in a stored procedure... it
> takes about 60 minutes. I dont see parallelism in the execution plan of
> SP... I have already tried hint maxdop... it did'nt work... Im not
> sure what I might be running into'
> Any help is greatly appreciated...
>|||Im glad you mentioned that... the only difference of execution plan is
that the if I use query analyzer... it uses parallelism while stored
procedure does not. Why is it so' The server load is pretty
consistent.|||You may want to search on "firehose cursor".
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:e%236DQxxYGHA.3832@.TK2MSFTNGP04.phx.gbl...
> Duration on the Profiler is the time between receiving the statement and
> the
> last row being retrieved.
Cool. That's what I was suspecting.

> My guess is that your ADO may not be optimal.
It's a single call to Recordset.Open with a client side cursor so there's
not much other than tuning some settings I can do. I think its time to take
this issue to the ADO forum. Thanks for all your input.
Mike|||I'm thinking it's ADO-related.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"zomer" <noneee@.gmail.com> wrote in message
news:1145387868.100688.258810@.e56g2000cwe.googlegroups.com...
yes... datatypes are the same.