Hi
Where can I find good resources re: peformance tuning of queries / indexes ,
using EXPLAIN etc ?
Thanks
BruceBecause you have EXPLAIN in capitals I am going to presume you either
1. Want the SQL Server equivalent to Oracle's EXPLAIN PLAN
2. Have the wrong Newsgroup
Presuming #1 you can look at.
SET SHOWPLAN_ALL ON
once you have that then you can start to look to articles like this
http://www.sql-server-performance.com/query_execution_plan_analysis.asp
or anything on the subject by Kalen Delaney.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Bruce Baker" <bruceb@.ardex.com.au> wrote in message
news:eWvSyqJuDHA.2448@.TK2MSFTNGP12.phx.gbl...
> Hi
> Where can I find good resources re: peformance tuning of queries / indexes
,
> using EXPLAIN etc ?
> Thanks
> Bruce
>|||www.sql-server-performance.com will be a good resource.
Suresh
>--Original Message--
>Because you have EXPLAIN in capitals I am going to
presume you either
>1. Want the SQL Server equivalent to Oracle's EXPLAIN
PLAN
>2. Have the wrong Newsgroup
>
>Presuming #1 you can look at.
>SET SHOWPLAN_ALL ON
>once you have that then you can start to look to articles
like this
>http://www.sql-server-
performance.com/query_execution_plan_analysis.asp
>or anything on the subject by Kalen Delaney.
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>"Bruce Baker" <bruceb@.ardex.com.au> wrote in message
>news:eWvSyqJuDHA.2448@.TK2MSFTNGP12.phx.gbl...
>> Hi
>> Where can I find good resources re: peformance tuning
of queries / indexes
>,
>> using EXPLAIN etc ?
>> Thanks
>> Bruce
>>
>
>.
>
Showing posts with label explain. Show all posts
Showing posts with label explain. Show all posts
Friday, March 23, 2012
Monday, March 12, 2012
Query Optimizer selection...
Hi Team,
Let me explain my doubts.
There are two types of query optimizer
1. Syntax-based
2. Cost-based.
Whatever I know, SQL Server internally maintain this optimization
issue. Now I have two doubts.
1. Can user select the option before running any proc/sql statement?
2. If question no. 1 is wrong then how SQL server decides which
optimizer to select?
Please guide me.
Thanks in advance.
Regards
Arijit ChatterjeeSQL Server uses cost-based optimization. It is possible to use
"hints" to influence the execution plan generated by the optimizer,
but this is generally only required in extreme circumstances.
The details of how the optimizer works would fill a book rather than a
newsgroup message. The good news is that it works well.
Roy Harvey
Beacon Falls, ct
On 7 Aug 2006 00:10:00 -0700, arijitchatterjee123@.yahoo.co.in wrote:
>Hi Team,
>Let me explain my doubts.
>There are two types of query optimizer
>1. Syntax-based
>2. Cost-based.
>Whatever I know, SQL Server internally maintain this optimization
>issue. Now I have two doubts.
>1. Can user select the option before running any proc/sql statement?
>2. If question no. 1 is wrong then how SQL server decides which
>optimizer to select?
>Please guide me.
>Thanks in advance.
>Regards
>Arijit Chatterjee|||"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:ksfed2p3uu43n0lu70omnq60ngtupphohg@.
4ax.com...
> SQL Server uses cost-based optimization. It is possible to use
> "hints" to influence the execution plan generated by the optimizer,
> but this is generally only required in extreme circumstances.
> The details of how the optimizer works would fill a book rather than a
> newsgroup message. The good news is that it works well.
Also note, that any such book (though I'll still recommend Kalen's excellent
Inside SQL 2000) will be out of date soon after it's published since it's
not at all unusual (in fact very likely) that Service Packs will make
changes to the cost optimizer.
(We in fact hit a degenerate case with SP3 that w/o a hint would pick the
wrong path and do a table scan. SP4 solved the problem and we subsequently
removed the hint.)
[vbcol=seagreen]
> Roy Harvey
> Beacon Falls, ct
> On 7 Aug 2006 00:10:00 -0700, arijitchatterjee123@.yahoo.co.in wrote:
>|||Thanks Group,
Appreciate your comments.
Regards
Arijit
Let me explain my doubts.
There are two types of query optimizer
1. Syntax-based
2. Cost-based.
Whatever I know, SQL Server internally maintain this optimization
issue. Now I have two doubts.
1. Can user select the option before running any proc/sql statement?
2. If question no. 1 is wrong then how SQL server decides which
optimizer to select?
Please guide me.
Thanks in advance.
Regards
Arijit ChatterjeeSQL Server uses cost-based optimization. It is possible to use
"hints" to influence the execution plan generated by the optimizer,
but this is generally only required in extreme circumstances.
The details of how the optimizer works would fill a book rather than a
newsgroup message. The good news is that it works well.
Roy Harvey
Beacon Falls, ct
On 7 Aug 2006 00:10:00 -0700, arijitchatterjee123@.yahoo.co.in wrote:
>Hi Team,
>Let me explain my doubts.
>There are two types of query optimizer
>1. Syntax-based
>2. Cost-based.
>Whatever I know, SQL Server internally maintain this optimization
>issue. Now I have two doubts.
>1. Can user select the option before running any proc/sql statement?
>2. If question no. 1 is wrong then how SQL server decides which
>optimizer to select?
>Please guide me.
>Thanks in advance.
>Regards
>Arijit Chatterjee|||"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:ksfed2p3uu43n0lu70omnq60ngtupphohg@.
4ax.com...
> SQL Server uses cost-based optimization. It is possible to use
> "hints" to influence the execution plan generated by the optimizer,
> but this is generally only required in extreme circumstances.
> The details of how the optimizer works would fill a book rather than a
> newsgroup message. The good news is that it works well.
Also note, that any such book (though I'll still recommend Kalen's excellent
Inside SQL 2000) will be out of date soon after it's published since it's
not at all unusual (in fact very likely) that Service Packs will make
changes to the cost optimizer.
(We in fact hit a degenerate case with SP3 that w/o a hint would pick the
wrong path and do a table scan. SP4 solved the problem and we subsequently
removed the hint.)
[vbcol=seagreen]
> Roy Harvey
> Beacon Falls, ct
> On 7 Aug 2006 00:10:00 -0700, arijitchatterjee123@.yahoo.co.in wrote:
>|||Thanks Group,
Appreciate your comments.
Regards
Arijit
Query Optimizer selection...
Hi Team,
Let me explain my doubts.
There are two types of query optimizer
1. Syntax-based
2. Cost-based.
Whatever I know, SQL Server internally maintain this optimization
issue. Now I have two doubts.
1. Can user select the option before running any proc/sql statement?
2. If question no. 1 is wrong then how SQL server decides which
optimizer to select?
Please guide me.
Thanks in advance.
Regards
Arijit ChatterjeeSQL Server uses cost-based optimization. It is possible to use
"hints" to influence the execution plan generated by the optimizer,
but this is generally only required in extreme circumstances.
The details of how the optimizer works would fill a book rather than a
newsgroup message. The good news is that it works well.
Roy Harvey
Beacon Falls, ct
On 7 Aug 2006 00:10:00 -0700, arijitchatterjee123@.yahoo.co.in wrote:
>Hi Team,
>Let me explain my doubts.
>There are two types of query optimizer
>1. Syntax-based
>2. Cost-based.
>Whatever I know, SQL Server internally maintain this optimization
>issue. Now I have two doubts.
>1. Can user select the option before running any proc/sql statement?
>2. If question no. 1 is wrong then how SQL server decides which
>optimizer to select?
>Please guide me.
>Thanks in advance.
>Regards
>Arijit Chatterjee|||"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:ksfed2p3uu43n0lu70omnq60ngtupphohg@.4ax.com...
> SQL Server uses cost-based optimization. It is possible to use
> "hints" to influence the execution plan generated by the optimizer,
> but this is generally only required in extreme circumstances.
> The details of how the optimizer works would fill a book rather than a
> newsgroup message. The good news is that it works well.
Also note, that any such book (though I'll still recommend Kalen's excellent
Inside SQL 2000) will be out of date soon after it's published since it's
not at all unusual (in fact very likely) that Service Packs will make
changes to the cost optimizer.
(We in fact hit a degenerate case with SP3 that w/o a hint would pick the
wrong path and do a table scan. SP4 solved the problem and we subsequently
removed the hint.)
> Roy Harvey
> Beacon Falls, ct
> On 7 Aug 2006 00:10:00 -0700, arijitchatterjee123@.yahoo.co.in wrote:
> >Hi Team,
> >Let me explain my doubts.
> >There are two types of query optimizer
> >1. Syntax-based
> >2. Cost-based.
> >Whatever I know, SQL Server internally maintain this optimization
> >issue. Now I have two doubts.
> >1. Can user select the option before running any proc/sql statement?
> >2. If question no. 1 is wrong then how SQL server decides which
> >optimizer to select?
> >
> >Please guide me.
> >Thanks in advance.
> >Regards
> >Arijit Chatterjee|||Thanks Group,
Appreciate your comments.
Regards
Arijit
Let me explain my doubts.
There are two types of query optimizer
1. Syntax-based
2. Cost-based.
Whatever I know, SQL Server internally maintain this optimization
issue. Now I have two doubts.
1. Can user select the option before running any proc/sql statement?
2. If question no. 1 is wrong then how SQL server decides which
optimizer to select?
Please guide me.
Thanks in advance.
Regards
Arijit ChatterjeeSQL Server uses cost-based optimization. It is possible to use
"hints" to influence the execution plan generated by the optimizer,
but this is generally only required in extreme circumstances.
The details of how the optimizer works would fill a book rather than a
newsgroup message. The good news is that it works well.
Roy Harvey
Beacon Falls, ct
On 7 Aug 2006 00:10:00 -0700, arijitchatterjee123@.yahoo.co.in wrote:
>Hi Team,
>Let me explain my doubts.
>There are two types of query optimizer
>1. Syntax-based
>2. Cost-based.
>Whatever I know, SQL Server internally maintain this optimization
>issue. Now I have two doubts.
>1. Can user select the option before running any proc/sql statement?
>2. If question no. 1 is wrong then how SQL server decides which
>optimizer to select?
>Please guide me.
>Thanks in advance.
>Regards
>Arijit Chatterjee|||"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:ksfed2p3uu43n0lu70omnq60ngtupphohg@.4ax.com...
> SQL Server uses cost-based optimization. It is possible to use
> "hints" to influence the execution plan generated by the optimizer,
> but this is generally only required in extreme circumstances.
> The details of how the optimizer works would fill a book rather than a
> newsgroup message. The good news is that it works well.
Also note, that any such book (though I'll still recommend Kalen's excellent
Inside SQL 2000) will be out of date soon after it's published since it's
not at all unusual (in fact very likely) that Service Packs will make
changes to the cost optimizer.
(We in fact hit a degenerate case with SP3 that w/o a hint would pick the
wrong path and do a table scan. SP4 solved the problem and we subsequently
removed the hint.)
> Roy Harvey
> Beacon Falls, ct
> On 7 Aug 2006 00:10:00 -0700, arijitchatterjee123@.yahoo.co.in wrote:
> >Hi Team,
> >Let me explain my doubts.
> >There are two types of query optimizer
> >1. Syntax-based
> >2. Cost-based.
> >Whatever I know, SQL Server internally maintain this optimization
> >issue. Now I have two doubts.
> >1. Can user select the option before running any proc/sql statement?
> >2. If question no. 1 is wrong then how SQL server decides which
> >optimizer to select?
> >
> >Please guide me.
> >Thanks in advance.
> >Regards
> >Arijit Chatterjee|||Thanks Group,
Appreciate your comments.
Regards
Arijit
Subscribe to:
Posts (Atom)