Query performance -- sql server clients

sql server clients : Query performance

Phil Lee
4/14/2006 3:18:28 PM

Is anyone able to explain why I'm getting very different performance as
reported by SQL Profiler for the same query when executed from 1) ADO.NET
and 2) SQL Management Studio.

I'm accessing SQL 2000 from a web service using .NET2 and Enterprise
Library.
Two particular queries have the following figures reported in Profiler
Application Name = .Net SqlClient Data Provider
1) Reads = 452, Duration = 203
2) Reads = 611931, Duration = 1636

If I copy the sql for these queries from the profiler and execute it in SQL
Management Studio, then profiler reports
Application Name = SQL Server Management Studio
1) Reads = 1238, Duration = 16
2) Reads = 704, Duration = 186

Which is a major improvement even though SQL profiler is reporting exactly
the same query.

Any idea what's causing this?

Regards
Phil Lee

weilu NO[at]SPAM online.microsoft.com
4/17/2006 12:00:00 AM

Hi Phil,

Thank you for using MSDN Managed Newsgroup Support.

I understand that you want to know why in SQL Profiler, your .Net
Application is reads more Data Page than using the same query in Management
Studio. If I misunderstood your concern, please feel free to let me know.

Performance Issue can be related to a lot of things.

Do you use index on the tables you are querying?

Also, would you please try to view the Execution Plan in the Management
Studio to see the detail Execution steps.

Basicly, .Net SQL provider will use the ADO.NET to connect to the SQL
Server 2005. I have tested on my side for some .Net Applications and using
the same query in Management Studio. They accessed the same data pages.

Performance issues can be difficult to troubleshoot and resolve in a
newsgroup setting due to the number of variables and the amount of time
required to narrow down possible causes and observe the effects. We will
assist as best as we can, but you may wish to consider contacting CSS for a
more timely resolution for these type issues.

To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS

If you are outside the US please see http://support.microsoft.com
for regional support phone numbers.

Thank you for your patience and understanding.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Phil Lee
4/17/2006 12:00:00 AM

Wei,

I'm not sure I made myself clear. I'm running the same queries, either via
ADO.NET or SQL Management Studio.
SQL profiler sees the same queries being executed but the ADO.NET executed
query takes a lot longer to execute.

So for example when the server is SQL 2000 a simple select from a view with
ADO.NET has a reported duration of 420(ms?) in SQL Profiler. The same query
executed from SSMS has a reported duration of 93ms.

Strangely if I switch to SQL 2005 (on a different machine) the same query
takes the same time from both ADO.NET and SSMS.

I can only examine the execution plan in SQL Management Studio, so how could
I tell if different execution plans are being generated from different
connections? And why would there be since it's the same query?

Regards
Phil

[quoted text, click to view]

weilu NO[at]SPAM online.microsoft.com
4/17/2006 12:00:00 AM

Hi Phil,

Thanks for the update.

The duration of a query operation is related to several things like the CPU
usage, network connection, etc.
Also, if there is other queries executed on the same table, you may recieve
a different duration time.

As you mentioned, your SQL 2005 is on a different machine, so I think the
duration of a query will include the network connection time. The same
query will take the same time.

Performance issues can be difficult to troubleshoot and resolve in a
newsgroup setting due to the number of variables and the amount of time
required to narrow down possible causes and observe the effects. We will
assist as best as we can, but you may wish to consider contacting CSS for a
more timely resolution for these type issues.

To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS

If you are outside the US please see http://support.microsoft.com
for regional support phone numbers.

Thank you for your patience and understanding.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server clients

View Other Months
March 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007

home · blog · groups

Questions? Comments? Contact the dn