Problem with SQL Query Analyzer

Hi,
I have the following problem: if I call a stored procedure from a program (via COM+/OLE DB) I measure the response time and I get something around 400 ms (including reading the data from the result set, a couple of MoveNext operations).
Now if I call the very same stored procedure within the SQL Query Analyzer, it takes almost 8 seconds until I can see the results! Does anybody know why?
Is the Query Analyzer so badly implemented when it reads the results and displays them?
Even if I eliminate the results (by adding select top 0 in my final query in the stored procedure) it still takes about 7 seconds within the Query Analyzer.
My bigger problem is that I use the query analyzer to fine-tune my application and it seems I can't rely on the results, because the same queries run differently once they are called from the application.
It's weird because I also get these confusing response times displayed in the Profiler! At the beginning I thought that the Query Analyzer adds some overhead, but why do I get different times in the Profiler?
Any hints/tips? Did anyone experience that before?
Thanks in advance,
Florin Micle
Execution plans can differ for several reasons. Different SET option is one potential reason. The first thing
to do is to compare the execution plans and see if they are the same or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"fmicle" <fmicle@.hotmail.com> wrote in message news:824E53F3-AC7C-4C1C-AFD5-06D4609F8714@.microsoft.com...
> Hi,
> I have the following problem: if I call a stored procedure from a program (via COM+/OLE DB) I measure the
response time and I get something around 400 ms (including reading the data from the result set, a couple of
MoveNext operations).
> Now if I call the very same stored procedure within the SQL Query Analyzer, it takes almost 8 seconds until
I can see the results! Does anybody know why?
> Is the Query Analyzer so badly implemented when it reads the results and displays them?
> Even if I eliminate the results (by adding select top 0 in my final query in the stored procedure) it still
takes about 7 seconds within the Query Analyzer.
> My bigger problem is that I use the query analyzer to fine-tune my application and it seems I can't rely on
the results, because the same queries run differently once they are called from the application.
> It's weird because I also get these confusing response times displayed in the Profiler! At the beginning I
thought that the Query Analyzer adds some overhead, but why do I get different times in the Profiler?
> Any hints/tips? Did anyone experience that before?
> Thanks in advance,
> Florin Micle
|||Perhaps your data happened to be cached in some instances. Try clearing the
cache using DBCC DROPCLEANBUFFERS before testing the queries using.
As Tibor mentioned, different execution plans may be another cause, which
you can also capture using Profiler.
Regards
Ray Mond
"fmicle" <fmicle@.hotmail.com> wrote in message
news:824E53F3-AC7C-4C1C-AFD5-06D4609F8714@.microsoft.com...
> Hi,
> I have the following problem: if I call a stored procedure from a program
(via COM+/OLE DB) I measure the response time and I get something around 400
ms (including reading the data from the result set, a couple of MoveNext
operations).
> Now if I call the very same stored procedure within the SQL Query
Analyzer, it takes almost 8 seconds until I can see the results! Does
anybody know why?
> Is the Query Analyzer so badly implemented when it reads the results and
displays them?
> Even if I eliminate the results (by adding select top 0 in my final query
in the stored procedure) it still takes about 7 seconds within the Query
Analyzer.
> My bigger problem is that I use the query analyzer to fine-tune my
application and it seems I can't rely on the results, because the same
queries run differently once they are called from the application.
> It's weird because I also get these confusing response times displayed in
the Profiler! At the beginning I thought that the Query Analyzer adds some
overhead, but why do I get different times in the Profiler?
> Any hints/tips? Did anyone experience that before?
> Thanks in advance,
> Florin Micle
|||Hi, when you look at profiler do you to capture exactly what it being run when the program calls the sp? I ask because unless the inputs are exactly the same you could get differing results. I've seen this with datetime inputs where query plans have been
different depending on the format of the date input. As Tibor says, did the query plan show anything different? I assume they must have done? You've tried the TOP 0 which should eliminate the displaying of the results and as Ray says you can drop the data
from cache (and run CHECKPOINT) to give the two approaches a level playing field. I feel the answer will be in your execution plan and I'd expect either as Tibor says, your SET options will be the root, or that it's perhaps something to do with one or mo
re of your sp inputs.
Alicia
Http://www.sqlporn.co.uk
|||Well, how am I suppose to know how exactly the query is executed and which query plan is taken?
I can see in the Profiler, that the exact same query is launched when I call it from my application and when I call it from the Query Analyzer. The query plan I can only see in the Query Analyzer, if I run my query in it, so I'll never know what plan is a
ctually taken when my query runs from the application.
That's exactly my problem, I can't tune my application properly, since the queries work differently in the Query Analyzer and from my app.
By the way, to answer your questions, the plan is always the same for my query.
I also think there are some bugs in the Profiler.
My stored procedure, let's name it SP1 calls 2 other SP's, which are very fast, let's call them SP2 and SP3.
So SP1 looks something like:
...
EXEC SP2
EXEC SP3
Do some complex queries
...
In the profiler, every time I call SP1, I get 3 SP Completed events, like this (two consecutive calls, you can see the effects of the cache, which is ok):
EventClass TextData Duration
SP:Completedexec SP1 0
SP:Completedexec SP1 0
SP:Completedexec SP1 453
SP:Completedexec SP1 0
SP:Completedexec SP1 0
SP:Completedexec SP1 313
Now, I call the same SP two times from the Query Analyzer, this is what I get in the profiler:
EventClass TextData Duration
SP:Completedexec SP2 0
SP:Completedexec SP3 0
SP:Completedexec SP1 1926
SP:Completedexec SP2 0
SP:Completedexec SP3 0
SP:Completedexec SP1 2005
The profiling mechanisms seem to be different, if the SP1 is called from the Query Analyzer it looks OK, but from my app, there seems to be a bug somwhere in the event handling in the Profiler.
Any ideas?
|||I can't believe this, if I delete the proccache (dbcc freeproccache) I get almost the same response times within QA like from my application!
About 600 ms, which is much better than 2 seconds!
What can make QA generate such a bad query plan?
Another thing: my application (C++) runs as a COM object in COM+. I wrote a small C++ program which does the exact same thing (calls the same stored procedure) and this is slower than when it runs in COM+. I use ATL OLE DB to connect to the DB in both cas
es.
I can only imagine, that there must be some magic in COM+, which sets some parameters differently, thus making the SQL Server run faster/better when called from COM+.
Confusing...
|||> What can make QA generate such a bad query plan?
The cached plan may have been generated for a query that uses parameter
values that do not fall into the same distribution pattern that the later
query has. However, this is unlikely in your case since you mentioned that
they are both using the same query.
I suggest checking the execution plan from both sides. Use Profiler to
capture the execution plan, using the Show Plan Statistics event under the
Performance heading.
Regards
Ray Mond
"fmicle" <fmicle@.hotmail.com> wrote in message
news:D42860F2-6C23-48C0-AA90-9301F2697191@.microsoft.com...
> I can't believe this, if I delete the proccache (dbcc freeproccache) I get
almost the same response times within QA like from my application!
> About 600 ms, which is much better than 2 seconds!
> What can make QA generate such a bad query plan?
> Another thing: my application (C++) runs as a COM object in COM+. I wrote
a small C++ program which does the exact same thing (calls the same stored
procedure) and this is slower than when it runs in COM+. I use ATL OLE DB to
connect to the DB in both cases.
> I can only imagine, that there must be some magic in COM+, which sets some
parameters differently, thus making the SQL Server run faster/better when
called from COM+.
> Confusing...
|||No, you're right, it makes sense. When I call the SP from COM+, I use placeholders and Accessors to bind my parameters.
But I get the exact same query text in the profiler.
On the other hand, if I do the same thing outside COM+, even using the parameters, I still get the bad plan (WITH PREFETCH).
I can only reach almost the same speed outside COM+ if I delete the PROCCACHE.

Problem with sp_grantdbaccess

When the I execute the following back to back in the SQL Query Analyzer,
I get an error:
Line 2: Incorrect syntax near 'sp_grantdbaccess'.
sp_revokedbaccess auser;
sp_grantdbaccess auser;
However, when I execute them individually, they work fine. Where's the
syntax error?
Or is there a better way remove a user as db_owner but still grant them
access to the database?
The two commands need to be executed in separate batches. So the syntax
should be:
sp_revokedbaccess auser
go
sp_grantdbaccess auser
However, it appears that you just want to remove a user from the 'db_owner'
role. So following command should do your work:
EXEC sp_droprolemember 'db_owner', 'YourUserName'
"O.B." wrote:

> When the I execute the following back to back in the SQL Query Analyzer,
> I get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?
|||If a call to a stored procedure is not the first thing in a batch, you must
use the word EXECUTE (or EXEC).
So you can either separate the two calls into separate batches:
sp_revokedbaccess auser;
go
sp_grantdbaccess auser;
go
OR
you can use EXEC:
sp_revokedbaccess auser;
EXEC sp_grantdbaccess auser;
You might want to consider always using EXEC to call a procedure, then you
never need to worry about whether it's the first thing in the batch or not.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"O.B." <funkjunk@.bellsouth.net> wrote in message
news:11om94vm6grltd3@.corp.supernews.com...
> When the I execute the following back to back in the SQL Query Analyzer, I
> get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?
>
sql

Problem with sp_grantdbaccess

When the I execute the following back to back in the SQL Query Analyzer,
I get an error:
Line 2: Incorrect syntax near 'sp_grantdbaccess'.
sp_revokedbaccess auser;
sp_grantdbaccess auser;
However, when I execute them individually, they work fine. Where's the
syntax error?
Or is there a better way remove a user as db_owner but still grant them
access to the database?The two commands need to be executed in separate batches. So the syntax
should be:
sp_revokedbaccess auser
go
sp_grantdbaccess auser
However, it appears that you just want to remove a user from the 'db_owner'
role. So following command should do your work:
EXEC sp_droprolemember 'db_owner', 'YourUserName'
"O.B." wrote:
> When the I execute the following back to back in the SQL Query Analyzer,
> I get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?|||If a call to a stored procedure is not the first thing in a batch, you must
use the word EXECUTE (or EXEC).
So you can either separate the two calls into separate batches:
sp_revokedbaccess auser;
go
sp_grantdbaccess auser;
go
OR
you can use EXEC:
sp_revokedbaccess auser;
EXEC sp_grantdbaccess auser;
You might want to consider always using EXEC to call a procedure, then you
never need to worry about whether it's the first thing in the batch or not.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"O.B." <funkjunk@.bellsouth.net> wrote in message
news:11om94vm6grltd3@.corp.supernews.com...
> When the I execute the following back to back in the SQL Query Analyzer, I
> get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?
>

Problem with sp_grantdbaccess

When the I execute the following back to back in the SQL Query Analyzer,
I get an error:
Line 2: Incorrect syntax near 'sp_grantdbaccess'.
sp_revokedbaccess auser;
sp_grantdbaccess auser;
However, when I execute them individually, they work fine. Where's the
syntax error?
Or is there a better way remove a user as db_owner but still grant them
access to the database?The two commands need to be executed in separate batches. So the syntax
should be:
sp_revokedbaccess auser
go
sp_grantdbaccess auser
However, it appears that you just want to remove a user from the 'db_owner'
role. So following command should do your work:
EXEC sp_droprolemember 'db_owner', 'YourUserName'
"O.B." wrote:

> When the I execute the following back to back in the SQL Query Analyzer,
> I get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?|||If a call to a stored procedure is not the first thing in a batch, you must
use the word EXECUTE (or EXEC).
So you can either separate the two calls into separate batches:
sp_revokedbaccess auser;
go
sp_grantdbaccess auser;
go
OR
you can use EXEC:
sp_revokedbaccess auser;
EXEC sp_grantdbaccess auser;
You might want to consider always using EXEC to call a procedure, then you
never need to worry about whether it's the first thing in the batch or not.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"O.B." <funkjunk@.bellsouth.net> wrote in message
news:11om94vm6grltd3@.corp.supernews.com...
> When the I execute the following back to back in the SQL Query Analyzer, I
> get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?
>

problem with select * from [remote server] in QA

In Query Analyzer connected to my local server using Windows authenticatio I
tried to run this select statement against my remote server:
select * from [11.22.333.444].remoteDB.dbo.tbl1
I got this error:
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection."
I can open a new connection in QA using an ID and password to this remote
server (which is not the same userID and password for my local server), and
then I can successfully run the select statement above, but I also need to
run statements against my local tables. So If I log into the remote server
and then try to reach my local server like this:
select * from myLocalServer.myLocalDB.dbo.myTable
--now I get the error message that :
"Server 'myLocalServer' is not configured for DATA ACCESS."
I believe the remote server IS configured for data access. So my question
is if it is possible to append the userID and password to the select
statement from the local connection?
select * from [11.22.333.444].remoteDB.dbo.tbl1, 'joey', 'password123'
or is there some way to supply the credentials along with the select
statement? Any suggestions appreciated.
Thanks,
RichRich,
I don't know exactly what you meant when you said 'remote server', but
you can only exec stored procedures but not query on a remote server,
use linked server instead.

>From BOL:
========================================
==========
If you want to set up a server configuration in order to execute stored
procedures on another server and do not have existing remote server
configurations, use linked servers instead of remote servers. Both
stored procedures and distributed queries are allowed against linked
servers; however, only stored procedures are allowed against remote
servers.
Note Support for remote servers is provided for backward compatibility
only. New applications that must execute stored procedures against
remote instances of SQL Server should use linked servers instead.
========================================
===================
Example,
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
You can define your remote password/login using sp_addlinkedsrvlogin on
the linked server.
Mel|||Rich
Have you created linked server?
exec sp_serveroption 'servername','data access','true'
go
select * from servername.database.dbo.tablename where ...
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D3D8127B-DE34-4598-9A11-88AE2FF24F3A@.microsoft.com...
> In Query Analyzer connected to my local server using Windows authenticatio
> I
> tried to run this select statement against my remote server:
> select * from [11.22.333.444].remoteDB.dbo.tbl1
> I got this error:
> "Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection."
> I can open a new connection in QA using an ID and password to this remote
> server (which is not the same userID and password for my local server),
> and
> then I can successfully run the select statement above, but I also need to
> run statements against my local tables. So If I log into the remote
> server
> and then try to reach my local server like this:
> select * from myLocalServer.myLocalDB.dbo.myTable
> --now I get the error message that :
> "Server 'myLocalServer' is not configured for DATA ACCESS."
> I believe the remote server IS configured for data access. So my question
> is if it is possible to append the userID and password to the select
> statement from the local connection?
> select * from [11.22.333.444].remoteDB.dbo.tbl1, 'joey', 'password123'
> or is there some way to supply the credentials along with the select
> statement? Any suggestions appreciated.
> Thanks,
> Rich
>

problem with output SP that takes Input

Hello,
I am trying to run/test an SP in Query Analyzer. The SP takes an input
param and outputs a value. How do I set this up in QA? Here is the SP
CREATE proc sp_Company_Workshop_Exists
@.RecordID int,
@.WorkshopExists bit output
as
if exists (
select *
from Workshop a
inner join Subscriber b
on ( a.CoID = b.CoID and a.SubscrID = b.SubscrID )
where b.RecordID = @.RecordID
)
set @.WorkshopExists = 1
else
set @.WorkshopExists = 0
return
I tried the following but getting error -- 15367 is my input param:
declare @.c int
declare @.WorkshopExists int
exec @.c = sp_Company_Workshop_Exists 15367 = @.WorkshopExists output
print @.WorkshopExists
Any suggestions appreciated
Thanks,
RichYou had a missing comma in the execution of the proc. The working code (conv
erted to pubs database)
below. A couple of comments:
Having sp_ in beginning of procedure name is considered bad practice.
I suggest you match the datatype of the out parameter to the one in the call
ing batch.
CREATE proc #sp_Company_Workshop_Exists
@.RecordID int,
@.WorkshopExists bit output
as
if exists (
select *
from authors)
set @.WorkshopExists = 1
else
set @.WorkshopExists = 0
return
GO
declare @.c int
declare @.WorkshopExists int
exec @.c = #sp_Company_Workshop_Exists 15367, @.WorkshopExists output
print @.WorkshopExists
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:ECECE8A6-31A8-49C0-AF2F-54F45D1918A3@.microsoft.com...
> Hello,
> I am trying to run/test an SP in Query Analyzer. The SP takes an input
> param and outputs a value. How do I set this up in QA? Here is the SP
> CREATE proc sp_Company_Workshop_Exists
> @.RecordID int,
> @.WorkshopExists bit output
> as
> if exists (
> select *
> from Workshop a
> inner join Subscriber b
> on ( a.CoID = b.CoID and a.SubscrID = b.SubscrID )
> where b.RecordID = @.RecordID
> )
> set @.WorkshopExists = 1
> else
> set @.WorkshopExists = 0
> return
> I tried the following but getting error -- 15367 is my input param:
> declare @.c int
> declare @.WorkshopExists int
> exec @.c = sp_Company_Workshop_Exists 15367 = @.WorkshopExists output
> print @.WorkshopExists
> Any suggestions appreciated
> Thanks,
> Rich|||OK. I changed the setup and now seems to work:
declare @.WorkshopExistsB bit
exec sp_Company_Workshop_Exists 15367, @.WorkshopExists = @.WorkshopExistsB
output
print @.WorkshopExistsB
"Rich" wrote:

> Hello,
> I am trying to run/test an SP in Query Analyzer. The SP takes an input
> param and outputs a value. How do I set this up in QA? Here is the SP
> CREATE proc sp_Company_Workshop_Exists
> @.RecordID int,
> @.WorkshopExists bit output
> as
> if exists (
> select *
> from Workshop a
> inner join Subscriber b
> on ( a.CoID = b.CoID and a.SubscrID = b.SubscrID )
> where b.RecordID = @.RecordID
> )
> set @.WorkshopExists = 1
> else
> set @.WorkshopExists = 0
> return
> I tried the following but getting error -- 15367 is my input param:
> declare @.c int
> declare @.WorkshopExists int
> exec @.c = sp_Company_Workshop_Exists 15367 = @.WorkshopExists output
> print @.WorkshopExists
> Any suggestions appreciated
> Thanks,
> Rich|||Yes, I am aware of the "Bad Practice". Not to pass the buck, but I am takin
g
over for a young man who has moved on to bigger and better things. So I wil
l
have to deal with his youthful exuberance, this being one of them. The kid
is very smart, just out of college. He just needs to refine a few things,
just like me :).
Anyway, thank you for your reply and example.
Rich
"Tibor Karaszi" wrote:

> You had a missing comma in the execution of the proc. The working code (co
nverted to pubs database)
> below. A couple of comments:
> Having sp_ in beginning of procedure name is considered bad practice.
> I suggest you match the datatype of the out parameter to the one in the ca
lling batch.
> CREATE proc #sp_Company_Workshop_Exists
> @.RecordID int,
> @.WorkshopExists bit output
> as
> if exists (
> select *
> from authors)
> set @.WorkshopExists = 1
> else
> set @.WorkshopExists = 0
> return
> GO
> declare @.c int
> declare @.WorkshopExists int
> exec @.c = #sp_Company_Workshop_Exists 15367, @.WorkshopExists output
> print @.WorkshopExists
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:ECECE8A6-31A8-49C0-AF2F-54F45D1918A3@.microsoft.com...
>