Showing posts with label analyzer. Show all posts
Showing posts with label analyzer. Show all posts

Tuesday, March 20, 2012

Query Performance

Hi,
I have a query which takes approx 45 sec to execute in Query Analyzer but it
takes more than 60 min to execute in MSRS. What is the reason for it and how
to improve it's performance ?
ThanksHi push:
Could you show us the query?
--
Scott
http://www.OdeToCode.com
On Fri, 20 Aug 2004 02:37:01 -0700, "push"
<push@.discussions.microsoft.com> wrote:
>Hi,
>I have a query which takes approx 45 sec to execute in Query Analyzer but it
>takes more than 60 min to execute in MSRS. What is the reason for it and how
>to improve it's performance ?
>Thanks|||push wrote:
> I have a query which takes approx 45 sec to execute in Query Analyzer but it
> takes more than 60 min to execute in MSRS. What is the reason for it and how
> to improve it's performance ?
I would first try and pinpoint where the time is being taken. Looking
at the ExecutionLog in the Report Server catalog database would tell you
the total row count and how the total report rendering time divides up
between data retrieval, internal processing, and format-specific
rendering. Could you post these numbers as well as a brief description
of the hardware you're running this on? An example query would be nice
as well.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

Monday, March 12, 2012

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.

Wednesday, March 7, 2012

Query OK in QA, never finishes in report

I have a pretty involved query that takes about 5 minutes to run in query analyzer. I'm not too upset with that performance based on what it does. The problem I'm having is that it doesn't finish when run in reporting services as a report. I've had issues when a lot of rows and columns are returned, but the query returns 400 rows.

Is there a timeout setting somewhere in reporting services that causes it to hang? Is it a known problem for long-running queries? I can log into QA as the user I use for the report connection and run it fine. No apparent permission issues. It just never finishes.

Thanks for any insight.

Is this the property you are looking for?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_ir_9gds.asp
And a whole article on it:
http://www.databasejournal.com/features/mssql/article.php/3488466