Bookmark lookup cost factors (SQL Server 7.0)

Good day to you.
I am working with some large databases with several tables hosting
millions or tens of millions of records. The actual data is being
stored on a SAN, while the database host is a 4 CPU SMP machine. In
this case the SAN is often a bottleneck while the CPUs are idled.
A situation that I am consistently observing is extremely poor choices
for the uses of indexes -- or rather the lack of index use. What I am
constantly finding for these large tables is that highly unique
non-clustered indexes are being ignored, and instead a table scan is
being performed. The execution engine is (wrongly) assuming that the
bookmark lookups will be more costly than simply doing a tablescan.
The end result is that many queries run 10s or 100s of times longer
than they should without explicitly adding index hints to force it to
use the index.
In one example a table with 5,000,000 or so records has a
non-clustered index on a field, and the statistics believe that there
are around 141 records that match the requested specific criteria
(there are really less -- as a sidenote can one increase the number of
steps stored by statistics?). Instead of quickly pulling the presumed
141 index matches and then doing the corresponding bookmark lookups,
it's instead table scanning all 5,000,000 records. Indeed if I look at
the estimated execution plans, it's claiming that the forced use of
the non-clutered index, and hence forced bookmark lookups, will yield
5x the subtree cost. The end results tell the real story, though -
with the index hint it completes instantly, while without the hint it
takes hundreds of times longer.
Is there a server or database setting that governs the estimation of
the cost of bookmark lookups? I do not wish to resort to unnecessary
hardcoded index hints when it's such a basically incorrect assumption
by the query engine that affects the entire system wherever large
tables are accessed. As a sidenote I had previously heard the
statistic that a non-clustered index will only be used if the
estimated set yields less than 2% of the total set, due to the cost of
bookmark lookups, but in this case it isn't using it even when the
estimated set is ~ 0.003%... how is it choosing?
Thanks
Dennis ForbesHi Dennis,
I think that 285996 PRB: Cost of Using Non-Clustered Indexes May Be Incorrect if Data (
http://support.microsoft.com/?id=285996) is something that may be applicable
to your scenario. Unfortunately there are no 'knobs' that tune the cost es
timation for any operators inside SQL Server.
Thanks,
--R
"Dennis Forbes" <dennis_forbes@.hotmail.com> wrote in message news:32811a52.0
403190902.35fd2ef5@.posting.google.com...
Good day to you.
I am working with some large databases with several tables hosting
millions or tens of millions of records. The actual data is being
stored on a SAN, while the database host is a 4 CPU SMP machine. In
this case the SAN is often a bottleneck while the CPUs are idled.
A situation that I am consistently observing is extremely poor choices
for the uses of indexes -- or rather the lack of index use. What I am
constantly finding for these large tables is that highly unique
non-clustered indexes are being ignored, and instead a table scan is
being performed. The execution engine is (wrongly) assuming that the
bookmark lookups will be more costly than simply doing a tablescan.
The end result is that many queries run 10s or 100s of times longer
than they should without explicitly adding index hints to force it to
use the index.
In one example a table with 5,000,000 or so records has a
non-clustered index on a field, and the statistics believe that there
are around 141 records that match the requested specific criteria
(there are really less -- as a sidenote can one increase the number of
steps stored by statistics?). Instead of quickly pulling the presumed
141 index matches and then doing the corresponding bookmark lookups,
it's instead table scanning all 5,000,000 records. Indeed if I look at
the estimated execution plans, it's claiming that the forced use of
the non-clutered index, and hence forced bookmark lookups, will yield
5x the subtree cost. The end results tell the real story, though -
with the index hint it completes instantly, while without the hint it
takes hundreds of times longer.
Is there a server or database setting that governs the estimation of
the cost of bookmark lookups? I do not wish to resort to unnecessary
hardcoded index hints when it's such a basically incorrect assumption
by the query engine that affects the entire system wherever large
tables are accessed. As a sidenote I had previously heard the
statistic that a non-clustered index will only be used if the
estimated set yields less than 2% of the total set, due to the cost of
bookmark lookups, but in this case it isn't using it even when the
estimated set is ~ 0.003%... how is it choosing?
Thanks
Dennis Forbessql

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptest
If you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest
|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:

> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
>
>
|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10), 'NULL') + ''''

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptestIf you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:
> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> >
> > Hi,
> >
> > I'm doing dynamic queries. When I try to insert blank or null date data
> > into a table, I'm getting the default value of '01-01-2000' or
> > '01-01'1900'
> > respectively. What I really want it to do is insert a null into the
> > table.
> >
> > I created some sample code below. Can anyone help?
> >
> > create table #mytemptest
> > (testdate smalldatetime null
> > )
> >
> > SET CONCAT_NULL_YIELDS_NULL OFF
> >
> > declare @.query as varchar(1000)
> > declare @.inputdate as smalldatetime
> >
> > select @.inputdate=''
> > select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> > select @.query
> > insert into #mytemptest(testdate)
> > exec(@.query)
> >
> > select @.inputdate=null
> > select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> > select @.query
> > insert into #mytemptest(testdate)
> > exec(@.query)
> >
> > select * from #mytemptest
> > drop table #mytemptest
>
>|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10), 'NULL') + ''''

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptestIf you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:

> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
>
>|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10
), 'NULL') + ''''

Bitmask fields in sysjobschedules

Does anybody have any code to show the the bitmask fields
in sysjobschedules as actual days of the week.
The end result i require is
name x
Freq_type 8
Freq_interval 129
to be converted into 6 table entries
NAME Day
x Mon
x Tues
x Wed
x Thur
x Fri
x Sat
etc
if the scheduled time could be included it would be most
helpful
Thanks
MatHere is an article I wrote about using an INT column to store different
statuses. This article should help you:
http://www.databasejournal.com/feat...cle.php/3359321
Here is some that might also be closer to your needs:
SELECT NAME,FREQ_TYPE, FREQ_INTERVAL,
CASE WHEN (FREQ_INTERVAL & 1) = 1 THEN 'SUN' end SUN,
CASE WHEN (FREQ_INTERVAL & 2) = 2 THEN 'MON' end MON,
CASE WHEN (FREQ_INTERVAL & 4) = 4 THEN 'TUE' end TUE,
CASE WHEN (FREQ_INTERVAL & 8) = 8 THEN 'WED' end WED,
CASE WHEN (FREQ_INTERVAL & 16) = 16 THEN 'THU' end THU,
CASE WHEN (FREQ_INTERVAL & 32) = 32 THEN 'FRI' end FRI,
CASE WHEN (FREQ_INTERVAL & 64) = 64 THEN 'SAT' end SAT FROM SYSJOBSCHEDULES
WHERE FREQ_TYPE=8
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:c4cf01c489d3$01dc8330$a601280a@.phx.gbl...
> Does anybody have any code to show the the bitmask fields
> in sysjobschedules as actual days of the week.
> The end result i require is
> name x
> Freq_type 8
> Freq_interval 129
> to be converted into 6 table entries
> NAME Day
> x Mon
> x Tues
> x Wed
> x Thur
> x Fri
> x Sat
> etc
> if the scheduled time could be included it would be most
> helpful
> Thanks
> Mat

Bitmask fields in sysjobschedules

Does anybody have any code to show the the bitmask fields
in sysjobschedules as actual days of the week.
The end result i require is
name x
Freq_type 8
Freq_interval 129
to be converted into 6 table entries
NAME Day
x Mon
x Tues
x Wed
x Thur
x Fri
x Sat
etc
if the scheduled time could be included it would be most
helpful
Thanks
MatHere is an article I wrote about using an INT column to store different
statuses. This article should help you:
http://www.databasejournal.com/features/mssql/article.php/3359321
Here is some that might also be closer to your needs:
SELECT NAME,FREQ_TYPE, FREQ_INTERVAL,
CASE WHEN (FREQ_INTERVAL & 1) = 1 THEN 'SUN' end SUN,
CASE WHEN (FREQ_INTERVAL & 2) = 2 THEN 'MON' end MON,
CASE WHEN (FREQ_INTERVAL & 4) = 4 THEN 'TUE' end TUE,
CASE WHEN (FREQ_INTERVAL & 8) = 8 THEN 'WED' end WED,
CASE WHEN (FREQ_INTERVAL & 16) = 16 THEN 'THU' end THU,
CASE WHEN (FREQ_INTERVAL & 32) = 32 THEN 'FRI' end FRI,
CASE WHEN (FREQ_INTERVAL & 64) = 64 THEN 'SAT' end SAT FROM SYSJOBSCHEDULES
WHERE FREQ_TYPE=8
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:c4cf01c489d3$01dc8330$a601280a@.phx.gbl...
> Does anybody have any code to show the the bitmask fields
> in sysjobschedules as actual days of the week.
> The end result i require is
> name x
> Freq_type 8
> Freq_interval 129
> to be converted into 6 table entries
> NAME Day
> x Mon
> x Tues
> x Wed
> x Thur
> x Fri
> x Sat
> etc
> if the scheduled time could be included it would be most
> helpful
> Thanks
> Mat

Bitmask fields in sysjobschedules

Does anybody have any code to show the the bitmask fields
in sysjobschedules as actual days of the week.
The end result i require is
name x
Freq_type 8
Freq_interval 129
to be converted into 6 table entries
NAME Day
x Mon
x Tues
x Wed
x Thur
x Fri
x Sat
etc
if the scheduled time could be included it would be most
helpful
Thanks
Mat
Here is an article I wrote about using an INT column to store different
statuses. This article should help you:
http://www.databasejournal.com/featu...le.php/3359321
Here is some that might also be closer to your needs:
SELECT NAME,FREQ_TYPE, FREQ_INTERVAL,
CASE WHEN (FREQ_INTERVAL & 1) = 1 THEN 'SUN' end SUN,
CASE WHEN (FREQ_INTERVAL & 2) = 2 THEN 'MON' end MON,
CASE WHEN (FREQ_INTERVAL & 4) = 4 THEN 'TUE' end TUE,
CASE WHEN (FREQ_INTERVAL & 8) = 8 THEN 'WED' end WED,
CASE WHEN (FREQ_INTERVAL & 16) = 16 THEN 'THU' end THU,
CASE WHEN (FREQ_INTERVAL & 32) = 32 THEN 'FRI' end FRI,
CASE WHEN (FREQ_INTERVAL & 64) = 64 THEN 'SAT' end SAT FROM SYSJOBSCHEDULES
WHERE FREQ_TYPE=8
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:c4cf01c489d3$01dc8330$a601280a@.phx.gbl...
> Does anybody have any code to show the the bitmask fields
> in sysjobschedules as actual days of the week.
> The end result i require is
> name x
> Freq_type 8
> Freq_interval 129
> to be converted into 6 table entries
> NAME Day
> x Mon
> x Tues
> x Wed
> x Thur
> x Fri
> x Sat
> etc
> if the scheduled time could be included it would be most
> helpful
> Thanks
> Mat