query Execution time-out settings

In order to stop some occasional blocking issues could I set the "query
execution time-out settings" to 30 seconds for example. I understand it will
kill the queries that exceed 30 seconds, but I would rather have that then t
o
have everyone "lockup". I also understand that blocking is caused by some
ineffecient queries, however untill we identify the problematic queries I
would like to kill the blocking automatically.
Thanks for any help.
JamesOf course you can make such a change.
Please recognize that several activities, including reporting, usually
require longer running queries, and your proposed change may cause those
other activities and reports to mal-function.
Using Profiler, you should be able to identify the blocking queries
relatively easily.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"James" <James@.discussions.microsoft.com> wrote in message
news:11D15332-D7BC-4694-A3BB-15EB8F428406@.microsoft.com...
> In order to stop some occasional blocking issues could I set the "query
> execution time-out settings" to 30 seconds for example. I understand it
> will
> kill the queries that exceed 30 seconds, but I would rather have that then
> to
> have everyone "lockup". I also understand that blocking is caused by some
> ineffecient queries, however untill we identify the problematic queries I
> would like to kill the blocking automatically.
> Thanks for any help.
> James|||Thanks Arnie for your reply
Do you know if this action will definitely kill blocking?
James
"Arnie Rowland" wrote:

> Of course you can make such a change.
> Please recognize that several activities, including reporting, usually
> require longer running queries, and your proposed change may cause those
> other activities and reports to mal-function.
> Using Profiler, you should be able to identify the blocking queries
> relatively easily.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "James" <James@.discussions.microsoft.com> wrote in message
> news:11D15332-D7BC-4694-A3BB-15EB8F428406@.microsoft.com...
>
>

Query execution time using an ORMapper

Hello there!
I have a strange problem regarding the query execution time of a
parameterised query executed over sp_executesql. We use an ORMapper
(NHibernate) which generates the query, so it is not necessary to discuss the
use of sp_executesql. If I run the query in SQL Server Management Studio it
is much faster than executing it over the application. Looking at Profiler
the same query needs in MMS 6000 reads and 500 ms and over the application
nearly 200.000 reads and 30.000 ms.
I have no idea why this happens because the database doesnâ't change at all
and I profile on database level. So Iâ'm confused that it depends on the
execution source. The count of database sites in the data cache is constant
and the query plan cache is hit also in both executions.
Any ideas? Thanks
Rob"RobRoma" <RobRoma@.discussions.microsoft.com> wrote in message
news:EB7FF480-3C92-48AA-8867-FF2C30E55767@.microsoft.com...
> Hello there!
> I have a strange problem regarding the query execution time of a
> parameterised query executed over sp_executesql. We use an ORMapper
> (NHibernate) which generates the query, so it is not necessary to discuss
> the
> use of sp_executesql. If I run the query in SQL Server Management Studio
> it
> is much faster than executing it over the application. Looking at Profiler
> the same query needs in MMS 6000 reads and 500 ms and over the application
> nearly 200.000 reads and 30.000 ms.
> I have no idea why this happens because the database doesn't change at all
> and I profile on database level. So I'm confused that it depends on the
> execution source. The count of database sites in the data cache is
> constant
> and the query plan cache is hit also in both executions.
>
sp_executesql is fine, the issue is that a parameterized query is just
different than a non-parameterized one. You should expect a different plan
for expensive queries.
Test, analyze and optimize the query in Management Studio using
sp_executesql.
David

Query execution time using an ORMapper

Hello there!
I have a strange problem regarding the query execution time of a
parameterised query executed over sp_executesql. We use an ORMapper
(NHibernate) which generates the query, so it is not necessary to discuss th
e
use of sp_executesql. If I run the query in SQL Server Management Studio it
is much faster than executing it over the application. Looking at Profiler
the same query needs in MMS 6000 reads and 500 ms and over the application
nearly 200.000 reads and 30.000 ms.
I have no idea why this happens because the database doesn’t change at all
and I profile on database level. So I’m confused that it depends on the
execution source. The count of database sites in the data cache is constant
and the query plan cache is hit also in both executions.
Any ideas? Thanks
Rob"RobRoma" <RobRoma@.discussions.microsoft.com> wrote in message
news:EB7FF480-3C92-48AA-8867-FF2C30E55767@.microsoft.com...
> Hello there!
> I have a strange problem regarding the query execution time of a
> parameterised query executed over sp_executesql. We use an ORMapper
> (NHibernate) which generates the query, so it is not necessary to discuss
> the
> use of sp_executesql. If I run the query in SQL Server Management Studio
> it
> is much faster than executing it over the application. Looking at Profiler
> the same query needs in MMS 6000 reads and 500 ms and over the application
> nearly 200.000 reads and 30.000 ms.
> I have no idea why this happens because the database doesn't change at all
> and I profile on database level. So I'm confused that it depends on the
> execution source. The count of database sites in the data cache is
> constant
> and the query plan cache is hit also in both executions.
>
sp_executesql is fine, the issue is that a parameterized query is just
different than a non-parameterized one. You should expect a different plan
for expensive queries.
Test, analyze and optimize the query in Management Studio using
sp_executesql.
David

Query execution time problem

We are executing following query from query analyzer,
SELECT PD_ID, PD_DATA FROM TBL_PROJECT_DETAILS
[Table Strcuture => id int, data varchar(500)]
This table contains 45000 records.
When this query is executed from Query Analyzer locally on the database
server, output is returned in 30 seconds.
However, when the query is executed from Query Analyzer installed on
application server it takes 110 seconds.
Execution plan is same both times. CPU, reads are identical for both
executions. However there is big difference between the execution times.
Also, both database server and application server in same VLAN and direct
network connectivity is available without any interim hops.
Any kind of help or suggestion giving insight into this aspect is welcomed.
Thanks in advance for all the inputs.pvv30 (pvv30@.discussions.microsoft.com) writes:
> We are executing following query from query analyzer,
> SELECT PD_ID, PD_DATA FROM TBL_PROJECT_DETAILS
> [Table Strcuture => id int, data varchar(500)]
> This table contains 45000 records.
> When this query is executed from Query Analyzer locally on the database
> server, output is returned in 30 seconds.
> However, when the query is executed from Query Analyzer installed on
> application server it takes 110 seconds.
> Execution plan is same both times. CPU, reads are identical for both
> executions. However there is big difference between the execution times.
> Also, both database server and application server in same VLAN and direct
> network connectivity is available without any interim hops.
The most obvious guess is that network latency matters. It may be a good
VLAN, but on the database server the connection is over shared memory
so it's not unreasonable that it takes more time to get it over the
wire.
Another thing to consider is the CPU power on the two machines.
Particularly if you run the output in text mode in QA, this could
matter.
Then again, I don't really see the point in measuring how long time
takes to receive 45000 rows in Query Analyzer. The tool is not really
intended for that.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Query Analyzer was used just to indicate a client application.
We are also monitoring the SQL calls using Profiler which is also giving us
the same output.
Also, the two boxes are of same configuration in terms of RAM & CPU.
You mentioned about connection being created in shared memory. Can anyone
please explain it in more detail as to how will it impact the query
performance? It might help us in understanding the problem better and work
towards a solution.
This time we enabled client statistics option in Query Analyzer. The
difference between two queries is for following attributes,
1. "Cumulative wait time on server replies" 38045 [for 110 sec execution
(app server)] v/s 81 [for 30 sec execution (locally from database server)]
2. "Number of bytes received" recieved is slightly more for query executed
from app server compared to database server
What does "Cumulative wait time on server replies" stats represents and how
to interpret it?
Thanks in advance for the responses.
"Erland Sommarskog" wrote:

> pvv30 (pvv30@.discussions.microsoft.com) writes:
> The most obvious guess is that network latency matters. It may be a good
> VLAN, but on the database server the connection is over shared memory
> so it's not unreasonable that it takes more time to get it over the
> wire.
> Another thing to consider is the CPU power on the two machines.
> Particularly if you run the output in text mode in QA, this could
> matter.
> Then again, I don't really see the point in measuring how long time
> takes to receive 45000 rows in Query Analyzer. The tool is not really
> intended for that.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||pvv30 (pvv30@.discussions.microsoft.com) writes:
> Query Analyzer was used just to indicate a client application.
I don't think it's a good benchmark for that purpose, since QA does a lot
of things a client app wouldn't do. You are better of writing a client
application that just receives the dataset. Particularly, you should use
the client API you plan to use in your client application.

> You mentioned about connection being created in shared memory. Can anyone
> please explain it in more detail as to how will it impact the query
> performance? It might help us in understanding the problem better and work
> towards a solution.
Normally clients communicate with SQL Server over network connection, most
commonly these days is TCP/IP, but there is also support for named pipes
and a few more protocols.
But when the client and server are on the same physical machine, using
the network is bit of overkill and therefore there is the possibiliy to
use shared memory, and this is also the default when connecting locally.
You can use the Client Network Utility to turn off shared memory, but
even with TCP/IP locally, you would see much better execution times
locally than over the network.

> This time we enabled client statistics option in Query Analyzer. The
> difference between two queries is for following attributes,
> 1. "Cumulative wait time on server replies" 38045 [for 110 sec execution
> (app server)] v/s 81 [for 30 sec execution (locally from database server)]
> 2. "Number of bytes received" recieved is slightly more for query executed
> from app server compared to database server
> What does "Cumulative wait time on server replies" stats represents and
> how to interpret it?
Books Online says "Cumulative amount of time the driver spent waiting for
replies from the server."
I would account the difference in numbers to the network connection. I
did a quick test on our site. I ran the same SELECT * against a 35000
row table on two servers. One which is hear at our main office in
Stockholm, and one which is at our branch office in Gvle. For the
server here in Stockholm the number was 60, and the server in Gvle
it was 12075. The difference in execution time was 2 seconds in
Stockholm and 28 seconds for the server in Gvle.
I ran both tests from my own machine, which hosts none of the servers in
question.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

query execution time

i want to know is there is a command to know how many time takes a query to run

You can achive this using SQL Profiler. You need not to do any programming/coding for this.

See at Books Online..

You can do it programatically also...

Here the sample code..

Code Snippet

DECLARE @.StartDateTime DATETIME

DECLARE @.EndDateTime DATETIME

DECLARE @.Msg VARCHAR(200)

DECLARE @.RC as Int

SELECT @.StartDateTime = GETDATE()

EXEC YOURSP / QUERY

SELECT @.RC = @.@.ROWCOUNT, @.EndDateTime = GETDATE()

SELECT @.Msg = 'Your SP Name' + CONVERT(VARCHAR(10),@.RC) + ' ' + CONVERT(VARCHAR(25), DATEDIFF(MS, @.StartDateTime, @.EndDateTime)) + 'ms'

PRINT @.Msg

|||

instead of above code use sp_who.

|||there have to be something like just one command
|||

Luis:

Maybe SET STATISTICS TIME ON and SET STATISTICS TIME OFF?

sql

Query execution time

Hi,
Is there any command that i can use, that tells me with
precision the execution time of a query?
I'm using:
select getdate()
execute Query
select getdate()
Thanks,
Best regards
Have you tried: SET STATISTICS TIME ON ?
This will return a message with CPU time and elapsed time.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards
|||Look at the SET STATISTICS TIME statement. You might want to look ate SET
STATISTICS IO and SET STATISTICS PROFILE as additional performance tuning
tools.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards

Query execution time

Hi,
Is there any command that i can use, that tells me with
precision the execution time of a query?
I'm using:
select getdate()
execute Query
select getdate()
Thanks,
Best regardsHave you tried: SET STATISTICS TIME ON ?
This will return a message with CPU time and elapsed time.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx
.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards|||Look at the SET STATISTICS TIME statement. You might want to look ate SET
STATISTICS IO and SET STATISTICS PROFILE as additional performance tuning
tools.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx
.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards

Query execution time

Hi,
Is there any command that i can use, that tells me with
precision the execution time of a query?
I'm using:
select getdate()
execute Query
select getdate()
Thanks,
Best regardsHave you tried: SET STATISTICS TIME ON ?
This will return a message with CPU time and elapsed time.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards|||Look at the SET STATISTICS TIME statement. You might want to look ate SET
STATISTICS IO and SET STATISTICS PROFILE as additional performance tuning
tools.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards

Query execution takes different times from different clients

Hi everyone,
first of all, i hope this is the correct newsgroup for this question
I've hit a scenario where I'm execuing a view from inside some .NET code.
I've set a timeout of 10 minutes, although this should be more than enough.
However, the query times out. When i connect to the database using SQL
Server Management Studio (full version) and execute the same view it takes 4
seconds to return the 84 rows to me that i'm expecting... Now, i've never
experienced this kind of issue before so i've no idea what could be causing
it. Some other queries are run before it and they finish fine (although a
little slower than i would expect perhaps).I've also used MS Access to try
and run the same view and I get the same problem as from code.
Could there be any client settings that could cause this kind of difference?
I'd appreciate anyones thoughts on this,
Thanks,
Andrew
Try turning on SQL Server Profiler and see if SQL Server is really receiving
the same query from both sources. You can also check the query execution
command to double check the performance.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:Ou7KiNcNIHA.4832@.TK2MSFTNGP04.phx.gbl...
> Hi everyone,
> first of all, i hope this is the correct newsgroup for this question
> I've hit a scenario where I'm execuing a view from inside some .NET code.
> I've set a timeout of 10 minutes, although this should be more than
> enough. However, the query times out. When i connect to the database using
> SQL Server Management Studio (full version) and execute the same view it
> takes 4 seconds to return the 84 rows to me that i'm expecting... Now,
> i've never experienced this kind of issue before so i've no idea what
> could be causing it. Some other queries are run before it and they finish
> fine (although a little slower than i would expect perhaps).I've also used
> MS Access to try and run the same view and I get the same problem as from
> code.
> Could there be any client settings that could cause this kind of
> difference?
> I'd appreciate anyones thoughts on this,
> Thanks,
> Andrew
>
|||Thanks Rick,
I've tried the profiler to make sure that my call was making it was far as
the SQL Server and I can see the query being started. However, after my
timeout period has elapsed, it just shows up in the profiler with the number
of reads etc and also showing the query time as being just more than the
timeout period. Initially I was wondering if some kind of lock was
preventing the query from finishing, however, I'm afraid I don't have access
to see that kind of information on my SQL Server, and it seemed unlikely if
other types of client were able to run the query (SQL Server management
studio)
Andrew
"Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
news:C47DC58D-8643-44AE-9397-8A7DD092F510@.microsoft.com...
> Try turning on SQL Server Profiler and see if SQL Server is really
> receiving the same query from both sources. You can also check the query
> execution command to double check the performance.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Andrew Brook" <ykoorb@.hotmail.com> wrote in message
> news:Ou7KiNcNIHA.4832@.TK2MSFTNGP04.phx.gbl...
>
|||In a freak revelation, it turns out I wasn't executing the same query I
thought I was executing (even after confirming with SQL Profiler...).
Unfortunately it turned out that my development DB and production DB were
very different, and I didn't have access to determine this earlier.
Apologies for any time spent thinking about this. I now have a much easier
task of optimizing a query that takes too long.
thanks,
Andrew
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:%23adOLIlNIHA.4196@.TK2MSFTNGP04.phx.gbl...
> Thanks Rick,
> I've tried the profiler to make sure that my call was making it was far as
> the SQL Server and I can see the query being started. However, after my
> timeout period has elapsed, it just shows up in the profiler with the
> number of reads etc and also showing the query time as being just more
> than the timeout period. Initially I was wondering if some kind of lock
> was preventing the query from finishing, however, I'm afraid I don't have
> access to see that kind of information on my SQL Server, and it seemed
> unlikely if other types of client were able to run the query (SQL Server
> management studio)
> Andrew
> "Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
> news:C47DC58D-8643-44AE-9397-8A7DD092F510@.microsoft.com...
>
|||No problems. It's the oldest issue in the book--debugging the wrong program.
Been there, done that, have the scars... ;)
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:u2XBYemNIHA.3400@.TK2MSFTNGP03.phx.gbl...
> In a freak revelation, it turns out I wasn't executing the same query I
> thought I was executing (even after confirming with SQL Profiler...).
> Unfortunately it turned out that my development DB and production DB were
> very different, and I didn't have access to determine this earlier.
> Apologies for any time spent thinking about this. I now have a much easier
> task of optimizing a query that takes too long.
> thanks,
> Andrew
>
> "Andrew Brook" <ykoorb@.hotmail.com> wrote in message
> news:%23adOLIlNIHA.4196@.TK2MSFTNGP04.phx.gbl...
>

Query execution takes different times from different clients

Hi everyone,
first of all, i hope this is the correct newsgroup for this question
I've hit a scenario where I'm execuing a view from inside some .NET code.
I've set a timeout of 10 minutes, although this should be more than enough.
However, the query times out. When i connect to the database using SQL
Server Management Studio (full version) and execute the same view it takes 4
seconds to return the 84 rows to me that i'm expecting... Now, i've never
experienced this kind of issue before so i've no idea what could be causing
it. Some other queries are run before it and they finish fine (although a
little slower than i would expect perhaps).I've also used MS Access to try
and run the same view and I get the same problem as from code.
Could there be any client settings that could cause this kind of difference?
I'd appreciate anyones thoughts on this,
Thanks,
AndrewTry turning on SQL Server Profiler and see if SQL Server is really receiving
the same query from both sources. You can also check the query execution
command to double check the performance.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:Ou7KiNcNIHA.4832@.TK2MSFTNGP04.phx.gbl...
> Hi everyone,
> first of all, i hope this is the correct newsgroup for this question
> I've hit a scenario where I'm execuing a view from inside some .NET code.
> I've set a timeout of 10 minutes, although this should be more than
> enough. However, the query times out. When i connect to the database using
> SQL Server Management Studio (full version) and execute the same view it
> takes 4 seconds to return the 84 rows to me that i'm expecting... Now,
> i've never experienced this kind of issue before so i've no idea what
> could be causing it. Some other queries are run before it and they finish
> fine (although a little slower than i would expect perhaps).I've also used
> MS Access to try and run the same view and I get the same problem as from
> code.
> Could there be any client settings that could cause this kind of
> difference?
> I'd appreciate anyones thoughts on this,
> Thanks,
> Andrew
>|||Thanks Rick,
I've tried the profiler to make sure that my call was making it was far as
the SQL Server and I can see the query being started. However, after my
timeout period has elapsed, it just shows up in the profiler with the number
of reads etc and also showing the query time as being just more than the
timeout period. Initially I was wondering if some kind of lock was
preventing the query from finishing, however, I'm afraid I don't have access
to see that kind of information on my SQL Server, and it seemed unlikely if
other types of client were able to run the query (SQL Server management
studio)
Andrew
"Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
news:C47DC58D-8643-44AE-9397-8A7DD092F510@.microsoft.com...
> Try turning on SQL Server Profiler and see if SQL Server is really
> receiving the same query from both sources. You can also check the query
> execution command to double check the performance.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Andrew Brook" <ykoorb@.hotmail.com> wrote in message
> news:Ou7KiNcNIHA.4832@.TK2MSFTNGP04.phx.gbl...
>|||In a freak revelation, it turns out I wasn't executing the same query I
thought I was executing (even after confirming with SQL Profiler...).
Unfortunately it turned out that my development DB and production DB were
very different, and I didn't have access to determine this earlier.
Apologies for any time spent thinking about this. I now have a much easier
task of optimizing a query that takes too long.
thanks,
Andrew
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:%23adOLIlNIHA.4196@.TK2MSFTNGP04.phx.gbl...
> Thanks Rick,
> I've tried the profiler to make sure that my call was making it was far as
> the SQL Server and I can see the query being started. However, after my
> timeout period has elapsed, it just shows up in the profiler with the
> number of reads etc and also showing the query time as being just more
> than the timeout period. Initially I was wondering if some kind of lock
> was preventing the query from finishing, however, I'm afraid I don't have
> access to see that kind of information on my SQL Server, and it seemed
> unlikely if other types of client were able to run the query (SQL Server
> management studio)
> Andrew
> "Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
> news:C47DC58D-8643-44AE-9397-8A7DD092F510@.microsoft.com...
>|||No problems. It's the oldest issue in the book--debugging the wrong program.
Been there, done that, have the scars... ;)
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Andrew Brook" <ykoorb@.hotmail.com> wrote in message
news:u2XBYemNIHA.3400@.TK2MSFTNGP03.phx.gbl...
> In a freak revelation, it turns out I wasn't executing the same query I
> thought I was executing (even after confirming with SQL Profiler...).
> Unfortunately it turned out that my development DB and production DB were
> very different, and I didn't have access to determine this earlier.
> Apologies for any time spent thinking about this. I now have a much easier
> task of optimizing a query that takes too long.
> thanks,
> Andrew
>
> "Andrew Brook" <ykoorb@.hotmail.com> wrote in message
> news:%23adOLIlNIHA.4196@.TK2MSFTNGP04.phx.gbl...
>sql

Query Execution Speed

Hi there - i'm hoping someone can help me!
I'm having a problem with a live database that i'm running on MSDE - It
seems to have slowed down quite considerably from the test environment
(even when all the data is the same). The is notably different on one
particular query that takes 1 sec on the test machine and almost 1 min
on the live machine
The total number of user connections on the live machine is normally 4
or so (found out through the Performance monitor). So I can't see that
it's MSDE's performance throttler...
Has anybody got any ideas on things i can check for?
Many thanks
James
I've done some more investigation ... I create a snapshot of the
database every evening so this is effectively the same data as at
midnight the previous day. If I run the stored procedure on the same
instance of SQL server but the snapshot database it executes in a
couple of seconds. Is there anything that could be slowing down this
one database? I've turned off the autoclose and autoshrink on it - but
the other copies have this set anyway!!
|||I've done some more investigation ... I create a snapshot of the
database every evening so this is effectively the same data as at
midnight the previous day. If I run the stored procedure on the same
instance of SQL server but the snapshot database it executes in a
couple of seconds. Is there anything that could be slowing down this
one database? I've turned off the autoclose and autoshrink on it - but
the other copies have this set anyway!!
Help gratefully accepted!!
|||Do the execution plans look different on the two servers? We had
similar problems and it turned out that the one plan used parallelism
and the other didn't.
|||It seems that the execution plans are different!!! I'm a bit new at
this kind of configuration with SQL Server... what do I need to change
to make the execution plans the same on both machines?
|||This is not unlike how two people perform the same task and get the
same result, but the process of performing it differs. In this case, I
assume the servers are not exactly the same, the physical distribution
of data on disk could be different, the load on the server could be
different, etc.
In general terms, I would try the following:
1. Update statistics on both servers, then compare execution plans
again.
2. Add query hints on the slower plan to get the desired result if the
above does not change things.
How are the plans different?
|||Sorry, I didnt' explain myself properly and I guess used the wrong
terminology somewhere along the line!!
Every evening, a copy of the database is made to another database on
the same instance of SQL Server. When I execute the SP on this
'snapshot' database it only takes a few seconds, but when I execute the
original it takes up to a minute or so.
So - the database is on the same SQL Server and has exactly (albeit to
a few hours) the same data as the live database, yet the execution
plans are different and the speed is dramatically different!!
|||Here are the two execution plans (I hope this is the right format that
you can understand...)
Query running on live data (slow):
|--Sort(ORDER BY[Expr1016] ASC, [Expr1017] ASC))
|--Compute
Scalar(DEFINE[Expr1014]=[StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity],
[Expr1015]=If ([Expr1013]=0) then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr
|--Filter(WHEREIf ([Expr1013]=0) then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr1013])<=If
([@.MinDaysCover]<>NULL) then Convert([@.MinDaysCover]) else If
([Expr1013]=0) then 0 else (
|--Nested Loops(Left Outer Join, OUTER
REFERENCES[StockLevel].[StockLine_ID]))
|--Bookmark Lookup(BOOKMARK[Bmk1011]),
OBJECT[foodcontrolSQL].[dbo].[Supplier]))
| |--Nested Loops(Left Outer Join, OUTER
REFERENCES[Product].[Supplier_ID]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES[StockLine].[StockLine_ID]) WITH PREFETCH)
| |
|--Filter(WHERE[StockLine].[StockLineStatus_ID]=1 AND
Convert([StockLine].[IsFutureDelist])=If (If
(Convert([@.ExcludeFutureDelist])=1) then 0 else NULL<>NULL) then If
(Convert([@.ExcludeFutureDelist])=1) then 0 el
| | | |--Bookmark
Lookup(BOOKMARK[Bmk1007]),
OBJECT[foodcontrolSQL].[dbo].[StockLine]))
| | | |--Nested
Loops(Inner Join, OUTER REFERENCES[Product].[Product_ID]) WITH
PREFETCH)
| | | |--Table
Scan(OBJECT[foodcontrolSQL].[dbo].[Product]),
WHERE[Product].[ProductStatus_ID]=2 AND [Product].[Supplier_ID]=If
(If (Convert([@.SupplierFilter])=1) then [@.Supplier_ID] else NULL<>NULL)
t
| | | |--Index
Seek(OBJECT[foodcontrolSQL].[dbo].[StockLine].[Product_ID]),
SEEK[StockLine].[Product_ID]=[Product].[Product_ID]) ORDERED FORWARD)
| | |--Clustered Index
Seek(OBJECT[foodcontrolSQL].[dbo].[StockLevel].[PK__StockLevelBackup__5E74FADA]),
SEEK[StockLevel].[StockLocation_ID]=1 AND
[StockLevel].[StockLine_ID]=[StockLine].[StockLine_ID]), WHERE[S
| |--Index
Seek(OBJECT[foodcontrolSQL].[dbo].[Supplier].[Supplier_ID]),
SEEK[Supplier].[Supplier_ID]=[Product].[Supplier_ID]) ORDERED
FORWARD)
|--Compute
Scalar(DEFINE[Expr1013]=Convert([Expr1004])/Convert([@.NumDays])))
|--Compute Scalar(DEFINE[Expr1004]=If
([Expr1032]=0) then NULL else [Expr1033]))
|--Stream
Aggregate(DEFINE[Expr1032]=COUNT_BIG([StockTransaction].[StockAdjustmentQuantity]),
[Expr1033]=SUM([StockTransaction].[StockAdjustmentQuantity])))
|--Filter(WHERE(([StockTransaction].[StockLine_ID]=[StockLevel].[StockLine_ID]
AND [StockTransaction].[ExcludeROS]=If ([@.ExcludeROS]<>NULL) then
[@.ExcludeROS] else [StockTransaction].[ExcludeROS]) AND [StockTrans
|--Bookmark
Lookup(BOOKMARK[Bmk1000]),
OBJECT[foodcontrolSQL].[dbo].[StockTransaction]))
|--Index
Seek(OBJECT[foodcontrolSQL].[dbo].[StockTransaction].[StockTransaction11]),
SEEK[StockTransaction].[TransactionDate] >= [@.StartDate] AND
[StockTransaction].[TransactionDate] <= [@.EndDate]) OR
Query running on snapshot data (fast):
|--Sort(ORDER BY[Expr1016] ASC, [Expr1017] ASC))
|--Compute
Scalar(DEFINE[Expr1014]=[StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity],
[Expr1015]=If ([Expr1013]=0) then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr
|--Bookmark Lookup(BOOKMARK[Bmk1011]),
OBJECT[foodcontrolSnapshot].[dbo].[Supplier]))
|--Nested Loops(Left Outer Join, OUTER
REFERENCES[Product].[Supplier_ID]))
|--Hash Match(Inner Join,
HASH[StockLine].[Product_ID])=([Product].[Product_ID]),
RESIDUAL[Product].[Product_ID]=[StockLine].[Product_ID]))
| |--Merge Join(Inner Join,
MERGE[StockLine].[StockLine_ID])=([StockLevel].[StockLine_ID]),
RESIDUAL[StockLevel].[StockLine_ID]=[StockLine].[StockLine_ID]))
| | |--Sort(ORDER
BY[StockLine].[StockLine_ID] ASC))
| | | |--Table
Scan(OBJECT[foodcontrolSnapshot].[dbo].[StockLine]),
WHERE[StockLine].[StockLineStatus_ID]=1 AND
Convert([StockLine].[IsFutureDelist])=If (If
(Convert([@.ExcludeFutureDelist])=1) then 0 else NULL<>NULL)
| | |--Filter(WHEREIf ([Expr1013]=0)
then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr1013])<=If
([@.MinDaysCover]<>NULL) then Convert([@.MinDaysCover]) else If ([Expr10
| | |--Merge Join(Left Outer
Join,
MERGE[StockLevel].[StockLine_ID])=([StockTransaction].[StockLine_ID]),
RESIDUAL[StockTransaction].[StockLine_ID]=[StockLevel].[StockLine_ID]))
| | |--Clustered Index
Seek(OBJECT[foodcontrolSnapshot].[dbo].[StockLevel].[PK__StockLevel__6DA725A5]),
SEEK[StockLevel].[StockLocation_ID]=1),
WHERE[StockLevel].[CurrentStockQuantity]-[StockLevel].[Committ
| | |--Compute
Scalar(DEFINE[Expr1013]=Convert([Expr1004])/Convert([@.NumDays])))
| | |--Compute
Scalar(DEFINE[Expr1004]=If ([Expr1035]=0) then NULL else [Expr1036]))
| | |--Stream
Aggregate(GROUP BY[StockTransaction].[StockLine_ID])
DEFINE[Expr1035]=COUNT_BIG([StockTransaction].[StockAdjustmentQuantity]),
[Expr1036]=SUM([StockTransaction].[StockAdjustmentQuantity
| |
|--Sort(ORDER BY[StockTransaction].[StockLine_ID] ASC))
| |
|--Clustered Index
Scan(OBJECT[foodcontrolSnapshot].[dbo].[StockTransaction].[PK_StockTransaction]),
WHERE((([StockTransaction].[StockLocation_ID]=1 AND
[StockTransaction].[StockTransac
| |--Table
Scan(OBJECT[foodcontrolSnapshot].[dbo].[Product]),
WHERE[Product].[ProductStatus_ID]=2 AND [Product].[Supplier_ID]=If
(If (Convert([@.SupplierFilter])=1) then [@.Supplier_ID] else NULL<>NULL)
then If (Convert([@.Su
|--Index
Seek(OBJECT[foodcontrolSnapshot].[dbo].[Supplier].[aaaaaSupplier_PK]),
SEEK[Supplier].[Supplier_ID]=[Product].[Supplier_ID]) ORDERED
FORWARD)
|||Just in case anybody is reading this topic and wants to know how i
solved the problem, I've run the command
UPDATE STATISTICS tablename
for each table that was dependent on the query and it's made the
queries run nice and fast again!!