Compare Queries

Chris,
Put both queries in the same query window and run them. Then the
percentiles will reflect both queries. Therefore, if query1 and its
functions use 25% and query2 and its functions use 75%, that suggests that
query1 is more efficient. However, UDFs (and some other functions) do
degrade the reliability of these numbers. Still, it is a good first take.
However, running both queries repeatly and getting a set of actual execution
times for each will, in the final analysis, be a more accurate measure of
the queries.
RLF
"Chris" <nospam@.nospam.com> wrote in message
news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I am creating an SP. I want to compare alternative ways of writing the
>query to see which one is the most efficient. I am trying to use the
>execution plan but it doesn't give an overall cost of the query so I can
>compare it to the alternative query. Rather I get a break down of the
>various steps (this query uses functions which produce aggregates and these
>steps are included in the execution plan) . I want to explore the steps
>later, at this point I just want to compare the two queries as a whole.
>What is the best way of going about this? Regards, Chris.
>
On Jun 29, 1:02 am, "Chris" <nos...@.nospam.com> wrote:
> The wording is a bit unclear. My SP could potentially contain a variety of
> variables obtained from sub queries etc. This means there will be more than
> just two queries to compare. How would you deal with a more complex scenario
> with SP's that use temp tables or subqueries etc.
> Regards, Chris.
> "Russell Fields" <russellfie...@.nomail.com> wrote in message
> news:emDSzebuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>
>
>
>
> - Show quoted text -
Make each version of your SP. Then capture in profiler
SP:StatementCompleted
with read,writes,duration,CPU
You need to clear database and procedure cache before each
execution of SP to have accurate values
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

command window open when SQL agent start

When i start mssqlserver service, it do not start SQLAgent
It is listed in the dependancies of both
when i start the sqlAgent manually, there is a command window that opens up with nothing in it, just the cursor blinking, BUT everything runs fine, the msssqlServer and the SQLAgent are both running
if i close that window, SQLAgent stop, and naturally, the connection to the DB is lost as well.
This never use to do that before
?
Thanx
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
You need to check the configuration for the service. Go to
Services(from Control Panel or Administrative Tools -
depending on your OS).
Go to the sqlserveragent service and view the properties for
the service. Select the LogOn tab and make sure to uncheck
the "Allow service to interact with desktop" checkbox.
-Sue
On Wed, 23 Jun 2004 07:51:56 -0700, korivo
(mathieu_corriveau@.hotmail.com) wrote:

>When i start mssqlserver service, it do not start SQLAgent
>It is listed in the dependancies of both
>when i start the sqlAgent manually, there is a command window that opens up with nothing in it, just the cursor blinking, BUT everything runs fine, the msssqlServer and the SQLAgent are both running
>if i close that window, SQLAgent stop, and naturally, the connection to the DB is lost as well.
>This never use to do that before
>?
>Thanx
>************************************************* *********************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

command window open when SQL agent start

When i start mssqlserver service, it do not start SQLAgent
It is listed in the dependancies of both
when i start the sqlAgent manually, there is a command window that opens up
with nothing in it, just the cursor blinking, BUT everything runs fine, the
msssqlServer and the SQLAgent are both running
if i close that window, SQLAgent stop, and naturally, the connection to the
DB is lost as well.
This never use to do that before
?
Thanx
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...You need to check the configuration for the service. Go to
Services(from Control Panel or Administrative Tools -
depending on your OS).
Go to the sqlserveragent service and view the properties for
the service. Select the LogOn tab and make sure to uncheck
the "Allow service to interact with desktop" checkbox.
-Sue
On Wed, 23 Jun 2004 07:51:56 -0700, korivo
(mathieu_corriveau@.hotmail.com) wrote:

>When i start mssqlserver service, it do not start SQLAgent
>It is listed in the dependancies of both
>when i start the sqlAgent manually, there is a command window that opens up
with nothing in it, just the cursor blinking, BUT everything runs fine, the
msssqlServer and the SQLAgent are both running
>if i close that window, SQLAgent stop, and naturally, the connection to the
DB is lost as well.
>This never use to do that before
>?
>Thanx
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resourc
es...

command line

Using Query Analyzer, I can right click on an object and select "script
object to new window as create" and I get the text of the object's
definition (schema). Can I get same result from command line, i.e., from
osql, I can get text output for the definition of the object (something like
defncopy under Sybase)? If so, what is the command or store procedure name?

Thanks in advance.Unfortunately not. You can look at scptxfr.exe (it's installed with
MSSQL server - run it with /? to see the options), which is a
command-line tool used during an upgrade process. But it's not
documented, and it doesn't give you the same flexibility as the QA/EM
scripting fucntionality.

The best general solution is probably to write your own scripts using
the SQLDMO COM interface, which exposes .Script methods for all MSSQL
objects - the details are in Books Online. Or there are plenty of
third-party tools to do this as well.

Simon|||Hi Dav,

Take a look at this sample. I guess this helps you. It is really good.

http://www.databasejournal.com/feat...10894_3401081_1

Thank you
Raju