x

same query showing different execution plans on the same server

I have a select query that is executed by a hosted database application (ASP).

It is the same select query used by both groups (identical in every way).

They are both using the same application and same servers (SQL and Application).

Everything is identical except the end-users login details and the machine they are connecting from.

Regardless of the end-users login details, all queries are executed using the same SQL login. Yet, for some reason, the query consistently runs slower for some users than others.
more ▼

asked Nov 15, 2012 at 09:56 AM in Default

IamStevieJohn gravatar image

IamStevieJohn
20 1 1 1

Any linked servers in the query?
Nov 15, 2012 at 10:05 AM Cyborg
There are no linked servers. One physical application server, one physical SQL server.
Nov 15, 2012 at 10:06 AM IamStevieJohn
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Since it's coming from two different machines, you should check the connection settings on those machines to see if there are differences. The main differences that might lead to different plans are from the ANSI settings. What you can do is look at the execution plans themselves, in the first operator, SELECT/DELETE/UPDATE/INSERT. There you will find the settings that the plan is based on. You can compare the two to look for differences.

You'll also find a query hash and a query plan hash. With the hash values, you can validate differences between the plans, because maybe the queries aren't really identical (it's a possibility).

In the event you're dealing with bad parameter sniffing, that same location will show you compile time parameter values that could be leading to different execution plans.

That's where I'd start. You should get most of the information you need from there to determine exactly what's up with the queries.
more ▼

answered Nov 15, 2012 at 10:05 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

As I explained though, the application is a hosted ASP web site, so the connection properties are shared by all users. The queries are identical, because both users are using the same SP, which contains a single SELECT query using the same parameters.

How can I see the execution plans in this context ?
Nov 15, 2012 at 10:33 AM IamStevieJohn

Sorry for any confusion. This part "verything is identical except the end-users login details" made me believe there could be differences with connection settings.

You said you're already seeing the execution plans, so I'm a little confused why you need to know how to see the execution plans, but the best way is to use the Dynamic Management Objects. sys.dm_exec_query_stats will have aggregate information about all queries in cache. You can combine this with sys.dm_exec_query_plan through the plan_handle to get the query plan. You can combine it with sys.dm_exec_sql_text in order to get the statements associated with each plan as well as the entire batch statement. This join is through the sql_handle.
Nov 15, 2012 at 10:39 AM Grant Fritchey ♦♦

Unfortunately the SQL server is running SQL 2000, so I don't have DMVs/DMFs.

My assumption is that the execution plans are different because of the total time to return the results, I haven't proved it.

Is there a way to see if the SP cache has been cleared ? Potentially if that was the case then the server would need to re-optimise the query plan on the next execution. This wouldn't really explain why it was consistently faster for some users over others though.
Nov 15, 2012 at 10:48 AM IamStevieJohn

I haven't worked with SQL Server 2000 for quite a while. The one thing I'm pretty sure you can do is capture the execution plans using trace events. But, this is an expensive operation from trace, so if you do use it, be sure you filter the events in order to capture only the query you're interested in, not all queries.

SQL 2000 just doesn't have the same instrumentation that we have in 2005+. It's going to be much more difficult to discover for sure what's happening.
Nov 15, 2012 at 02:19 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

Where are you measuring the timings from?

If at the Client end then you could be suffering router issues or user desktop resource contention to name a couple of factors. Are the Clients on different subnets? Are there hosts files on the systems? Is your ASP server multi-NICed?

SQL Server has to be examined holistically.
more ▼

answered Nov 15, 2012 at 02:05 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x474
x41

asked: Nov 15, 2012 at 09:56 AM

Seen: 2224 times

Last Updated: Nov 15, 2012 at 02:19 PM