Check Performance for queries

Hi,
I have two queries which will yield me the same result.
Is there a way in SQL Server(Query analyzer or Enterprise Manager) to
check the performance of both these queries and select one.
I have heard of performance plans... all those things. I am not
familiar to it. But I would like to learn.
pls guide me
Thanks
kiranAs a starter you might use the SET STATISTICS option (see BOL). Further
on you can view execution plans by selecting the option in the query
menu of Query Analyzer.
M|||Hi
You may want to look at using SQL profiler to do this see duration, reads,
writes and CPU used. You may want to see Tony's blog casts on
http://www.sqlserverfaq.com/ for an introduction to SQL Profiler. Also check
out the topic "Query Tuning", "Analyzing Queries" and "Analyzing a Query" in
books online on who to display query plans and statistics.
John
"kiran" wrote:

> Hi,
> I have two queries which will yield me the same result.
> Is there a way in SQL Server(Query analyzer or Enterprise Manager) to
> check the performance of both these queries and select one.
> I have heard of performance plans... all those things. I am not
> familiar to it. But I would like to learn.
> pls guide me
>
> Thanks
> kiran
>|||Mark he query you wnat to examine and hit CTRL-L in QA.
HTH, jens Suessmeyer.|||Jens wrote:
> Mark he query you wnat to examine and hit CTRL-L in QA.
> HTH, jens Suessmeyer.
>
Thanks a lot Guys, I will give it a try.|||Hi
Also look at running DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE to clear
out the buffer and procedure cache.
http://www.transactsql.com/html/DBC...EANBUFFERS.html
http://www.transactsql.com/html/DBC...EPROCCACHE.html
John
"kiran" wrote:

> Jens wrote:
> Thanks a lot Guys, I will give it a try.
>

Check Performance for queries

Hi,
I have two queries which will yield me the same result.
Is there a way in SQL Server(Query analyzer or Enterprise Manager) to
check the performance of both these queries and select one.
I have heard of performance plans... all those things. I am not
familiar to it. But I would like to learn.
pls guide me
Thanks
kiran
As a starter you might use the SET STATISTICS option (see BOL). Further
on you can view execution plans by selecting the option in the query
menu of Query Analyzer.
M
|||Hi
You may want to look at using SQL profiler to do this see duration, reads,
writes and CPU used. You may want to see Tony's blog casts on
http://www.sqlserverfaq.com/ for an introduction to SQL Profiler. Also check
out the topic "Query Tuning", "Analyzing Queries" and "Analyzing a Query" in
books online on who to display query plans and statistics.
John
"kiran" wrote:

> Hi,
> I have two queries which will yield me the same result.
> Is there a way in SQL Server(Query analyzer or Enterprise Manager) to
> check the performance of both these queries and select one.
> I have heard of performance plans... all those things. I am not
> familiar to it. But I would like to learn.
> pls guide me
>
> Thanks
> kiran
>
|||Mark he query you wnat to examine and hit CTRL-L in QA.
HTH, jens Suessmeyer.
|||Jens wrote:
> Mark he query you wnat to examine and hit CTRL-L in QA.
> HTH, jens Suessmeyer.
>
Thanks a lot Guys, I will give it a try.
|||Hi
Also look at running DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE to clear
out the buffer and procedure cache.
http://www.transactsql.com/html/DBCC...ANBUFFERS.html
http://www.transactsql.com/html/DBCC...PROCCACHE.html
John
"kiran" wrote:

> Jens wrote:
> Thanks a lot Guys, I will give it a try.
>

Check Performance for queries

Hi,
I have two queries which will yield me the same result.
Is there a way in SQL Server(Query analyzer or Enterprise Manager) to
check the performance of both these queries and select one.
I have heard of performance plans... all those things. I am not
familiar to it. But I would like to learn.
pls guide me
Thanks
kiranAs a starter you might use the SET STATISTICS option (see BOL). Further
on you can view execution plans by selecting the option in the query
menu of Query Analyzer.
M|||Hi
You may want to look at using SQL profiler to do this see duration, reads,
writes and CPU used. You may want to see Tony's blog casts on
http://www.sqlserverfaq.com/ for an introduction to SQL Profiler. Also check
out the topic "Query Tuning", "Analyzing Queries" and "Analyzing a Query" in
books online on who to display query plans and statistics.
John
"kiran" wrote:
> Hi,
> I have two queries which will yield me the same result.
> Is there a way in SQL Server(Query analyzer or Enterprise Manager) to
> check the performance of both these queries and select one.
> I have heard of performance plans... all those things. I am not
> familiar to it. But I would like to learn.
> pls guide me
>
> Thanks
> kiran
>|||Mark he query you wnat to examine and hit CTRL-L in QA.
HTH, jens Suessmeyer.|||Jens wrote:
> Mark he query you wnat to examine and hit CTRL-L in QA.
> HTH, jens Suessmeyer.
>
Thanks a lot Guys, I will give it a try.|||Hi
Also look at running DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE to clear
out the buffer and procedure cache.
http://www.transactsql.com/html/DBCC%20DROPCLEANBUFFERS.html
http://www.transactsql.com/html/DBCC%20FREEPROCCACHE.html
John
"kiran" wrote:
> Jens wrote:
> > Mark he query you wnat to examine and hit CTRL-L in QA.
> >
> > HTH, jens Suessmeyer.
> >
> Thanks a lot Guys, I will give it a try.
>

Cheapest way to see if a value is in a table

Hi,
Can anyone tell me what the most performance friendly way to see if an
id exists in a table is?
I'm currently doing a count on the ID, but I dont really need the server
to count them all - I just need a bool telling me whether its in there!
:-)
Thanks all
Simon
Simon,
I can't guarantee it, but I believe one of these is the the most efficient
way:
if exists (select MyID from MyTable where MyID = <something>)
if exists (select * from MyTable where MyID = <something>)
Turn on statistics I/O and statistics time in QA and compare the resources
used: logical reads, cpu, etc. More than likely, they will be very close.
-- Bill
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:O0z4ZRKQHHA.4156@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Can anyone tell me what the most performance friendly way to see if an id
> exists in a table is?
> I'm currently doing a count on the ID, but I dont really need the server
> to count them all - I just need a bool telling me whether its in there!
> :-)
> Thanks all
> Simon
|||Looks good to me - I'll give it a shot
:-)

Cheapest way to see if a value is in a table

Hi,
Can anyone tell me what the most performance friendly way to see if an
id exists in a table is?
I'm currently doing a count on the ID, but I dont really need the server
to count them all - I just need a bool telling me whether its in there!
:-)
Thanks all
SimonSimon,
I can't guarantee it, but I believe one of these is the the most efficient
way:
if exists (select MyID from MyTable where MyID = <something>)
if exists (select * from MyTable where MyID = <something>)
Turn on statistics I/O and statistics time in QA and compare the resources
used: logical reads, cpu, etc. More than likely, they will be very close.
-- Bill
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:O0z4ZRKQHHA.4156@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Can anyone tell me what the most performance friendly way to see if an id
> exists in a table is?
> I'm currently doing a count on the ID, but I dont really need the server
> to count them all - I just need a bool telling me whether its in there!
> :-)
> Thanks all
> Simon|||Looks good to me - I'll give it a shot
:-)

Cheapest way to see if a value is in a table

Hi,
Can anyone tell me what the most performance friendly way to see if an
id exists in a table is?
I'm currently doing a count on the ID, but I dont really need the server
to count them all - I just need a bool telling me whether its in there!
:-)
Thanks all
SimonSimon,
I can't guarantee it, but I believe one of these is the the most efficient
way:
if exists (select MyID from MyTable where MyID = <something> )
if exists (select * from MyTable where MyID = <something> )
Turn on statistics I/O and statistics time in QA and compare the resources
used: logical reads, cpu, etc. More than likely, they will be very close.
-- Bill
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:O0z4ZRKQHHA.4156@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Can anyone tell me what the most performance friendly way to see if an id
> exists in a table is?
> I'm currently doing a count on the ID, but I dont really need the server
> to count them all - I just need a bool telling me whether its in there!
> :-)
> Thanks all
> Simon|||Looks good to me - I'll give it a shot
:-)

Cheap solution for .Net/sql giving high availability?

Hi
I am looking into finding a cheap solution for a .Net/sql server web
app that gives high availability and hardware redundancy.
Given that performance isn't a great issue, I was wondering if the
following would work:
Two servers running Windows 2003 Standard and clustered using Network
Load Balancing (NLB). Each server running SQL 2005 Standard and
configured so that one server mirrors the other.
Would this configuration mean if either server failed the application
would keep working on the remaining server without any intervention? If
not what other options do I have? Having one active server with another
as a backup would also be fine but I assume this would require some
maintenance to switch the servers over when a failure occurs.
In terms of the actual servers I was looking at the Dell PowerEdge 860
Quad Core Intel Xeon X3210 2.13GHz as these would only need one sql
server licence per server. Storage using RAID 1 for more redundancy and
a UPS for short power outages. I know I could get cheaper servers but
the largest cost would be SQL server 2005 (approx 4100) so getting a
2 server solution would be Ideal.
Any more suggestions on setup or hardware would be appreciated.
Thanks
Henry
If you're using SQL Server 2005, why not investigate Failover Clustering or
Database Mirroring? Both are available on the standard edition (with
limitations compared to enterprise ed).
In what you described below, you have 2 physically independent databases
running (NLB doesn't mean anything to SQL Server). How would you keep them
both in sync? You mentioned configured so that they mirror each other, did
you mean using DB Mirroring or something else? This is not a trivial issue.
With homegrown solution you need to ensure you don't get into situations
where data is out of sync but your application/users are not aware of it.
HP has some nifty cluster-in-a-box solutions with the DL38x and DL58x
(beefier) platforms. They're relatively inexpensive. Haven't worked with
Dell hardware in some time but they probably have similar offerings also.
If you don't want to deal with Failover Clustering, you can get just the 2
servers and implement DB Mirroring.
In your case, I'd recommend DB Mirroring.
joe.
"Henry" <henry.nelson@.gmail.com> wrote in message
news:1169572502.397351.275050@.m58g2000cwm.googlegr oups.com...
Hi
I am looking into finding a cheap solution for a .Net/sql server web
app that gives high availability and hardware redundancy.
Given that performance isn't a great issue, I was wondering if the
following would work:
Two servers running Windows 2003 Standard and clustered using Network
Load Balancing (NLB). Each server running SQL 2005 Standard and
configured so that one server mirrors the other.
Would this configuration mean if either server failed the application
would keep working on the remaining server without any intervention? If
not what other options do I have? Having one active server with another
as a backup would also be fine but I assume this would require some
maintenance to switch the servers over when a failure occurs.
In terms of the actual servers I was looking at the Dell PowerEdge 860
Quad Core Intel Xeon X3210 2.13GHz as these would only need one sql
server licence per server. Storage using RAID 1 for more redundancy and
a UPS for short power outages. I know I could get cheaper servers but
the largest cost would be SQL server 2005 (approx 4100) so getting a
2 server solution would be Ideal.
Any more suggestions on setup or hardware would be appreciated.
Thanks
Henry

Character set translation / tp performance

Hi,

I have a problem regarding the perfomance of a stp in combination with
character translation.

The following happens.
We have an automated installation script (nt command file) for
creating all stored procedures in a database.

At first we used osql in this script, but with osql the international
characters (, etc) were not entered correctly into the database.
The tip given in some newsgroups was to use isql in stead of osql, and
turn off automatic ansi to oem translation.
This worked perfectly. However now some stored procedures are executed
much slower than before (and they take up much cpu). When I drop and
recreate the stored procedure using Query Analyser, the performance
goes up again.

When I look through the messages in this group, the advise is to use
osql instead of isql. But I just changed to isql because of the
character translation!

Does anyone have a solution for this?You might try including SET QUOTED_IDENTIFIER ON and SET ANSI_NULLS ON
at the beginning of your script file. QA (which uses ODBC) sets these
options on by default and this may result in a different execution plan.

Regarding ISQL, you might consider saving your file in Unicode format
and using OSQL instead. You can then use the OSQL -I command line
parameter to turn on the QUOTED_IDENTIFIER option.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Dick Zeeman" <minicontainer@.hotmail.com> wrote in message
news:fad166e1.0309010100.3464bf9e@.posting.google.c om...
> Hi,
> I have a problem regarding the perfomance of a stp in combination with
> character translation.
> The following happens.
> We have an automated installation script (nt command file) for
> creating all stored procedures in a database.
> At first we used osql in this script, but with osql the international
> characters (, etc) were not entered correctly into the database.
> The tip given in some newsgroups was to use isql in stead of osql, and
> turn off automatic ansi to oem translation.
> This worked perfectly. However now some stored procedures are executed
> much slower than before (and they take up much cpu). When I drop and
> recreate the stored procedure using Query Analyser, the performance
> goes up again.
> When I look through the messages in this group, the advise is to use
> osql instead of isql. But I just changed to isql because of the
> character translation!
> Does anyone have a solution for this?|||[posted and mailed, please reply in news]

Dick Zeeman (minicontainer@.hotmail.com) writes:
> I have a problem regarding the perfomance of a stp in combination with
> character translation.
> The following happens.
> We have an automated installation script (nt command file) for
> creating all stored procedures in a database.
> At first we used osql in this script, but with osql the international
> characters (, etc) were not entered correctly into the database.
> The tip given in some newsgroups was to use isql in stead of osql, and
> turn off automatic ansi to oem translation.
> This worked perfectly. However now some stored procedures are executed
> much slower than before (and they take up much cpu). When I drop and
> recreate the stored procedure using Query Analyser, the performance
> goes up again.
> When I look through the messages in this group, the advise is to use
> osql instead of isql. But I just changed to isql because of the
> character translation!

Dan's reply gave the answer you needed to get things working. I'll only
add some explanation to this.

With ISQL, all SET options are off. With Query Analyzer, a couple are on
by default. The ones that Dan mentioned, ANSI_NULLS and QUOTED_IDENTIFIER
are particularly important, because the setting at compile time is
saved with the procedures.

I don't think that QUOTED_IDENTIFIER can affect the query plan for
queries in general. ANSI_NULLS could in theory, although I don't know
how common this is. However, there are two cases where these settings
are essential, and that is when you have queries that involves indexed
computed columns and indexed views. For these indexes to be used, these
two settings must be ON. This is the most likely reason why you saw
such a drastic difference in execution. It is also therefore Dan tipped
you to use -I with OSQL.

I should also add that queries that involves linked queries requires
ANSI_NULLS to be on.

Finally, I should add to Dan's reply that you can use QA to save files
in Unicode format. You can actually even save in OEM format from QA,
if you like.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Dan and Erland thanks for you reaction.

We will try saving all scripts in oem format.
Unicode is not an option, since we use SourceSafe and SS does not like
unicode files.

Regards

Dick Zeeman