Query plan question

Is there a way we can figure out if the query optimiser is using a
particular stat/s ( Non indexed specifically ) to determine a query plan ?
I understand when missing stats are noticed, its obviously looking for it
but if we dont see any, can we tell if its looking at some non indexed stats
to come to a conclusion .
Also how does update stats affect performance on a highly OLTP system ? Does
it include blocking ,etc. ?sql server will use any stats available,
make a table, populate one column with unique values, but
not indexed, then query against it, notice that the
execution plan will have a fairly accurate estimate of the
expected row count, even though it is scanning the entire
table
stats will matter if it influences the plan, notice that
loop joins may exhibit a spike in plan cost at ~130 rows,
so there is a tendency to shift to scan
>--Original Message--
>Is there a way we can figure out if the query optimiser
is using a
>particular stat/s ( Non indexed specifically ) to
determine a query plan ?
>I understand when missing stats are noticed, its
obviously looking for it
>but if we dont see any, can we tell if its looking at
some non indexed stats
>to come to a conclusion .
>Also how does update stats affect performance on a highly
OLTP system ? Does
>it include blocking ,etc. ?
>
>.
>|||> sql server will use any stats available,
> make a table, populate one column with unique values, but
> not indexed, then query against it, notice that the
> execution plan will have a fairly accurate estimate of the
> expected row count, even though it is scanning the entire
> table
When does this happen ? When does it make this table you referred to ? Is
this at the time of update stats or create stats or every time a query runs
.
You also mentioned that it will use any stats available. Does it even use
those that may not be needed ?
> loop joins may exhibit a spike in plan cost at ~130 rows,
> so there is a tendency to shift to scan
What scan are you talking about ?
"joe chang" <jchang6@.yahoo.com> wrote in message
news:0ea901c37281$fd521af0$a001280a@.phx.gbl...
> sql server will use any stats available,
> make a table, populate one column with unique values, but
> not indexed, then query against it, notice that the
> execution plan will have a fairly accurate estimate of the
> expected row count, even though it is scanning the entire
> table
> stats will matter if it influences the plan, notice that
> loop joins may exhibit a spike in plan cost at ~130 rows,
> so there is a tendency to shift to scan
> >--Original Message--
> >Is there a way we can figure out if the query optimiser
> is using a
> >particular stat/s ( Non indexed specifically ) to
> determine a query plan ?
> >I understand when missing stats are noticed, its
> obviously looking for it
> >but if we dont see any, can we tell if its looking at
> some non indexed stats
> >to come to a conclusion .
> >
> >Also how does update stats affect performance on a highly
> OLTP system ? Does
> >it include blocking ,etc. ?
> >
> >
> >.
> >|||the following white paper contains a good description of the statistics
framework in SQL Server 2000 and probably answers all your questions.
http://msdn.microsoft.com/library/default.asp?URL=/library/techart/statquery.htm
Peter Zabback
SQL Server Development
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OFA7oOocDHA.1696@.TK2MSFTNGP10.phx.gbl...
> > sql server will use any stats available,
> > make a table, populate one column with unique values, but
> > not indexed, then query against it, notice that the
> > execution plan will have a fairly accurate estimate of the
> > expected row count, even though it is scanning the entire
> > table
> When does this happen ? When does it make this table you referred to ? Is
> this at the time of update stats or create stats or every time a query
runs
> .
> You also mentioned that it will use any stats available. Does it even use
> those that may not be needed ?
> > loop joins may exhibit a spike in plan cost at ~130 rows,
> > so there is a tendency to shift to scan
> What scan are you talking about ?
> "joe chang" <jchang6@.yahoo.com> wrote in message
> news:0ea901c37281$fd521af0$a001280a@.phx.gbl...
> > sql server will use any stats available,
> > make a table, populate one column with unique values, but
> > not indexed, then query against it, notice that the
> > execution plan will have a fairly accurate estimate of the
> > expected row count, even though it is scanning the entire
> > table
> > stats will matter if it influences the plan, notice that
> > loop joins may exhibit a spike in plan cost at ~130 rows,
> > so there is a tendency to shift to scan
> >
> > >--Original Message--
> > >Is there a way we can figure out if the query optimiser
> > is using a
> > >particular stat/s ( Non indexed specifically ) to
> > determine a query plan ?
> > >I understand when missing stats are noticed, its
> > obviously looking for it
> > >but if we dont see any, can we tell if its looking at
> > some non indexed stats
> > >to come to a conclusion .
> > >
> > >Also how does update stats affect performance on a highly
> > OLTP system ? Does
> > >it include blocking ,etc. ?
> > >
> > >
> > >.
> > >
>

query on datetime column with

Hi,
I want to query my table using datetime column, which is indexed. One query
with covert function, one without. Funny thing is the one with convert
function is much faster than without. Should not be?
-- query 1, cost 11.48%
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time,
pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt, pos_seq_no
as tx_seq_no, pos_msg_type as tx_msg_type,
pos_trans_code as tx_tran_type
from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000' and
CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
order by pos_trandate_time
--query 2, cost 88.52%
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time,
pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt, pos_seq_no
as tx_seq_no, pos_msg_type as tx_msg_type,
pos_trans_code as tx_tran_type
from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000' and
pos_settlementdate = '2004-07-05 00:00:00'
order by pos_trandate_time
Andrew
What kind of index is defined on this column?
I have my doubts that you have seen an INDEX SEEK on this column ( moreover
,you have two columns taking part in WHERE clause)
What amount of data that you are queried?
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:C118DD02-7B93-4835-8FC6-12D0AB41C434@.microsoft.com...
> Hi,
> I want to query my table using datetime column, which is indexed. One
query
> with covert function, one without. Funny thing is the one with convert
> function is much faster than without. Should not be?
> -- query 1, cost 11.48%
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,
> pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
pos_seq_no
> as tx_seq_no, pos_msg_type as tx_msg_type,
> pos_trans_code as tx_tran_type
> from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
and
> CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> order by pos_trandate_time
> --query 2, cost 88.52%
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,
> pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
pos_seq_no
> as tx_seq_no, pos_msg_type as tx_msg_type,
> pos_trans_code as tx_tran_type
> from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
and
> pos_settlementdate = '2004-07-05 00:00:00'
> order by pos_trandate_time
>
|||Thanks for your reply, Uri,
this how I create the index:
CREATE INDEX idx_pos_txn_log1
ON pos_txn_log(pos_tid, pos_rid, pos_settlementdate)
GO
These two queries give me same 66 records, out of 7 million records in one
table.
I am really new to SQL server, can you tell me what's index seek?
regards
andrew
"Uri Dimant" wrote:

> Andrew
> What kind of index is defined on this column?
> I have my doubts that you have seen an INDEX SEEK on this column ( moreover
> ,you have two columns taking part in WHERE clause)
> What amount of data that you are queried?
>
>
>
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:C118DD02-7B93-4835-8FC6-12D0AB41C434@.microsoft.com...
> query
> pos_seq_no
> and
> pos_seq_no
> and
>
>
|||Andrew
Index seek means that there is non clustered index on the table and Query
Optimizer is able to get the data directly from the index page by using
pointers in the leaf level of B-Tree and not scaning entire table.
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:0D2CF5FE-0D34-4BAF-A9BA-A188AC2A55DF@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply, Uri,
> this how I create the index:
> CREATE INDEX idx_pos_txn_log1
> ON pos_txn_log(pos_tid, pos_rid, pos_settlementdate)
> GO
> These two queries give me same 66 records, out of 7 million records in one
> table.
> I am really new to SQL server, can you tell me what's index seek?
> regards
> andrew
>
> "Uri Dimant" wrote:
moreover[vbcol=seagreen]
CONVERT(char(8),[vbcol=seagreen]
CONVERT(char(8),[vbcol=seagreen]

query on datetime column with

Hi,
I want to query my table using datetime column, which is indexed. One query
with covert function, one without. Funny thing is the one with convert
function is much faster than without. Should not be?
-- query 1, cost 11.48%
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time,
pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt, pos_seq_no
as tx_seq_no, pos_msg_type as tx_msg_type,
pos_trans_code as tx_tran_type
from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000' and
CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
order by pos_trandate_time
--query 2, cost 88.52%
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time,
pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt, pos_seq_no
as tx_seq_no, pos_msg_type as tx_msg_type,
pos_trans_code as tx_tran_type
from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000' and
pos_settlementdate = '2004-07-05 00:00:00'
order by pos_trandate_timeAndrew
What kind of index is defined on this column?
I have my doubts that you have seen an INDEX SEEK on this column ( moreover
,you have two columns taking part in WHERE clause)
What amount of data that you are queried?
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:C118DD02-7B93-4835-8FC6-12D0AB41C434@.microsoft.com...
> Hi,
> I want to query my table using datetime column, which is indexed. One
query
> with covert function, one without. Funny thing is the one with convert
> function is much faster than without. Should not be?
> -- query 1, cost 11.48%
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,
> pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
pos_seq_no
> as tx_seq_no, pos_msg_type as tx_msg_type,
> pos_trans_code as tx_tran_type
> from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
and
> CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> order by pos_trandate_time
> --query 2, cost 88.52%
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,
> pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
pos_seq_no
> as tx_seq_no, pos_msg_type as tx_msg_type,
> pos_trans_code as tx_tran_type
> from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
and
> pos_settlementdate = '2004-07-05 00:00:00'
> order by pos_trandate_time
>|||Thanks for your reply, Uri,
this how I create the index:
CREATE INDEX idx_pos_txn_log1
ON pos_txn_log(pos_tid, pos_rid, pos_settlementdate)
GO
These two queries give me same 66 records, out of 7 million records in one
table.
I am really new to SQL server, can you tell me what's index seek?
regards
andrew
"Uri Dimant" wrote:
> Andrew
> What kind of index is defined on this column?
> I have my doubts that you have seen an INDEX SEEK on this column ( moreover
> ,you have two columns taking part in WHERE clause)
> What amount of data that you are queried?
>
>
>
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:C118DD02-7B93-4835-8FC6-12D0AB41C434@.microsoft.com...
> > Hi,
> >
> > I want to query my table using datetime column, which is indexed. One
> query
> > with covert function, one without. Funny thing is the one with convert
> > function is much faster than without. Should not be?
> >
> > -- query 1, cost 11.48%
> > select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> > pos_trandate_time, 8) as tx_time,
> > pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
> pos_seq_no
> > as tx_seq_no, pos_msg_type as tx_msg_type,
> > pos_trans_code as tx_tran_type
> > from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
> and
> > CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> > order by pos_trandate_time
> >
> > --query 2, cost 88.52%
> > select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> > pos_trandate_time, 8) as tx_time,
> > pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
> pos_seq_no
> > as tx_seq_no, pos_msg_type as tx_msg_type,
> > pos_trans_code as tx_tran_type
> > from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
> and
> > pos_settlementdate = '2004-07-05 00:00:00'
> > order by pos_trandate_time
> >
> >
>
>|||Andrew
Index seek means that there is non clustered index on the table and Query
Optimizer is able to get the data directly from the index page by using
pointers in the leaf level of B-Tree and not scaning entire table.
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:0D2CF5FE-0D34-4BAF-A9BA-A188AC2A55DF@.microsoft.com...
> Thanks for your reply, Uri,
> this how I create the index:
> CREATE INDEX idx_pos_txn_log1
> ON pos_txn_log(pos_tid, pos_rid, pos_settlementdate)
> GO
> These two queries give me same 66 records, out of 7 million records in one
> table.
> I am really new to SQL server, can you tell me what's index seek?
> regards
> andrew
>
> "Uri Dimant" wrote:
> > Andrew
> > What kind of index is defined on this column?
> > I have my doubts that you have seen an INDEX SEEK on this column (
moreover
> > ,you have two columns taking part in WHERE clause)
> >
> > What amount of data that you are queried?
> >
> >
> >
> >
> >
> >
> > "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> > news:C118DD02-7B93-4835-8FC6-12D0AB41C434@.microsoft.com...
> > > Hi,
> > >
> > > I want to query my table using datetime column, which is indexed. One
> > query
> > > with covert function, one without. Funny thing is the one with convert
> > > function is much faster than without. Should not be?
> > >
> > > -- query 1, cost 11.48%
> > > select CONVERT(char(8), pos_trandate_time, 3) as tx_date,
CONVERT(char(8),
> > > pos_trandate_time, 8) as tx_time,
> > > pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
> > pos_seq_no
> > > as tx_seq_no, pos_msg_type as tx_msg_type,
> > > pos_trans_code as tx_tran_type
> > > from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
> > and
> > > CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> > > order by pos_trandate_time
> > >
> > > --query 2, cost 88.52%
> > > select CONVERT(char(8), pos_trandate_time, 3) as tx_date,
CONVERT(char(8),
> > > pos_trandate_time, 8) as tx_time,
> > > pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
> > pos_seq_no
> > > as tx_seq_no, pos_msg_type as tx_msg_type,
> > > pos_trans_code as tx_tran_type
> > > from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
> > and
> > > pos_settlementdate = '2004-07-05 00:00:00'
> > > order by pos_trandate_time
> > >
> > >
> >
> >
> >

query on datetime column with

Hi,
I want to query my table using datetime column, which is indexed. One query
with covert function, one without. Funny thing is the one with convert
function is much faster than without. Should not be?
-- query 1, cost 11.48%
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time,
pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt, pos_seq_no
as tx_seq_no, pos_msg_type as tx_msg_type,
pos_trans_code as tx_tran_type
from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000' and
CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
order by pos_trandate_time
--query 2, cost 88.52%
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time,
pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt, pos_seq_no
as tx_seq_no, pos_msg_type as tx_msg_type,
pos_trans_code as tx_tran_type
from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000' and
pos_settlementdate = '2004-07-05 00:00:00'
order by pos_trandate_timeAndrew
What kind of index is defined on this column?
I have my doubts that you have seen an INDEX SEEK on this column ( moreover
,you have two columns taking part in WHERE clause)
What amount of data that you are queried?
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:C118DD02-7B93-4835-8FC6-12D0AB41C434@.microsoft.com...
> Hi,
> I want to query my table using datetime column, which is indexed. One
query
> with covert function, one without. Funny thing is the one with convert
> function is much faster than without. Should not be?
> -- query 1, cost 11.48%
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,
> pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
pos_seq_no
> as tx_seq_no, pos_msg_type as tx_msg_type,
> pos_trans_code as tx_tran_type
> from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
and
> CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> order by pos_trandate_time
> --query 2, cost 88.52%
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,
> pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
pos_seq_no
> as tx_seq_no, pos_msg_type as tx_msg_type,
> pos_trans_code as tx_tran_type
> from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
and
> pos_settlementdate = '2004-07-05 00:00:00'
> order by pos_trandate_time
>|||Thanks for your reply, Uri,
this how I create the index:
CREATE INDEX idx_pos_txn_log1
ON pos_txn_log(pos_tid, pos_rid, pos_settlementdate)
GO
These two queries give me same 66 records, out of 7 million records in one
table.
I am really new to SQL server, can you tell me what's index seek?
regards
andrew
"Uri Dimant" wrote:

> Andrew
> What kind of index is defined on this column?
> I have my doubts that you have seen an INDEX SEEK on this column ( moreove
r
> ,you have two columns taking part in WHERE clause)
> What amount of data that you are queried?
>
>
>
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:C118DD02-7B93-4835-8FC6-12D0AB41C434@.microsoft.com...
> query
> pos_seq_no
> and
> pos_seq_no
> and
>
>|||Andrew
Index seek means that there is non clustered index on the table and Query
Optimizer is able to get the data directly from the index page by using
pointers in the leaf level of B-Tree and not scaning entire table.
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:0D2CF5FE-0D34-4BAF-A9BA-A188AC2A55DF@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply, Uri,
> this how I create the index:
> CREATE INDEX idx_pos_txn_log1
> ON pos_txn_log(pos_tid, pos_rid, pos_settlementdate)
> GO
> These two queries give me same 66 records, out of 7 million records in one
> table.
> I am really new to SQL server, can you tell me what's index seek?
> regards
> andrew
>
> "Uri Dimant" wrote:
>
moreover[vbcol=seagreen]
CONVERT(char(8),[vbcol=seagreen]
CONVERT(char(8),[vbcol=seagreen]