Hi All,
How to estimate the number of pages there should be in the index? Thanks.well a page is 8 kb. each page has a 96 byte header. it would roughly depend on the size of datatypes involved in your index times the number of rows divided by the size of a page minus the size of the header.|||well a page is 8 kb. each page has a 96 byte header. Have you just taken some MS exams? I did not know the size of the page header.
Sean - do you need to account for fill factor?|||Have you just taken some MS exams? I did not know the size of the page header.
Sean - do you need to account for fill factor?
yes.
and it depends. I believe the fillfactor is only valid when it comes to space when the index is first created.|||yes. I know - I was being factitious. Should have winked. ;) I was impressed you had that figure to hand
and it depends. I believe the fillfactor is only valid when it comes to space when the index is first created.and when there is a page split. So I guess it is a factor but would depend from page to page. Perhaps checking page density would be a consideration.
EDIT - I am probably back to your "divide by size of page" there I think. Ho hum.|||I know - I was being factitious.
Sorry. I am humorless today. I am on a long string of 11 to 12 hour days and there was not a weekend last week. 2 weeks until phase one of the company's top priority project goes to QA. 2 months until vacation after said project ships. If I live that long.sql
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Friday, March 23, 2012
Friday, March 9, 2012
Nullable Columns and Query Index Relief
I seem to recall reading an article that suggested Nullable columns may
prevent that column from receiving index relief in Query Plans.
Am I remembering incorrectly, or is this actually the case...?
If you have a link to an article that proves\disproves this I would be
greatful.
I am attempting to put together a case for removing nulls from our DB over
time and need as much ammo as I can muster.
I am the only "Anti-Null" guy here.
Thanks in advance,
Greg Jackson
PDX, OregonGreg,
SQL Server treats a NULL just like any value in an index.
Null pros and cons in general has been discussed in many places (just read
Date's books vs. Codd and Celko, for instance). If you search this newsgroup
archive, you will find such discussions as well.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:OP$$jv4BEHA.3568@.tk2msftngp13.phx.gbl...
> I seem to recall reading an article that suggested Nullable columns may
> prevent that column from receiving index relief in Query Plans.
> Am I remembering incorrectly, or is this actually the case...?
>
> If you have a link to an article that proves\disproves this I would be
> greatful.
> I am attempting to put together a case for removing nulls from our DB over
> time and need as much ammo as I can muster.
> I am the only "Anti-Null" guy here.
>
> Thanks in advance,
>
> Greg Jackson
> PDX, Oregon
>|||yeah, I have lots of data on Null pros and cons, I was just looking for the
Index Relief thing.
Guess, I was just reaching.
Thanks again Tibor, Jaxon is keeping you busy this week....
:-)
GAJ
prevent that column from receiving index relief in Query Plans.
Am I remembering incorrectly, or is this actually the case...?
If you have a link to an article that proves\disproves this I would be
greatful.
I am attempting to put together a case for removing nulls from our DB over
time and need as much ammo as I can muster.
I am the only "Anti-Null" guy here.
Thanks in advance,
Greg Jackson
PDX, OregonGreg,
SQL Server treats a NULL just like any value in an index.
Null pros and cons in general has been discussed in many places (just read
Date's books vs. Codd and Celko, for instance). If you search this newsgroup
archive, you will find such discussions as well.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:OP$$jv4BEHA.3568@.tk2msftngp13.phx.gbl...
> I seem to recall reading an article that suggested Nullable columns may
> prevent that column from receiving index relief in Query Plans.
> Am I remembering incorrectly, or is this actually the case...?
>
> If you have a link to an article that proves\disproves this I would be
> greatful.
> I am attempting to put together a case for removing nulls from our DB over
> time and need as much ammo as I can muster.
> I am the only "Anti-Null" guy here.
>
> Thanks in advance,
>
> Greg Jackson
> PDX, Oregon
>|||yeah, I have lots of data on Null pros and cons, I was just looking for the
Index Relief thing.
Guess, I was just reaching.
Thanks again Tibor, Jaxon is keeping you busy this week....
:-)
GAJ
Saturday, February 25, 2012
NULL Returned for Populated Column
Here is a strange one:
The following partitioned table (partitioned for 31 days) with a single nonclustered, nonunique index is behaving improperly on a select. A standard select using the index returns all NULL values for altitude yet if I do a select for the TOP x rows ordered by reporttime desc for not null altitudes, I see the values stored in the column. There are over 5,000,000 rows in the table with a relatively even distribution over each partition (except partition 1 which is kept empty).
CREATE TABLE [dbo].[APRSTrack](
[CallsignSSID] [varchar](9) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[ReportTime] [datetime] NOT NULL,
[Latitude] [float] NOT NULL,
[Longitude] [float] NOT NULL,
[Icon] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Course] [smallint] NULL,
[Speed] [int] NULL,
[Altitude] [int] NULL
) ON [onemonth]([ReportTime])
CREATE NONCLUSTERED INDEX [IX_APRSTrack] ON [dbo].[APRSTrack]
(
[CallsignSSID] ASC,
[ReportTime] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90, ONLINE = OFF) ON [onemonth]([ReportTime])
The failing select statement is:
SELECT *
FROM [Ham Radio].[dbo].[APRSTrack]
where callsignssid = 'EA3ABN-9' and reporttime >= '2006-06-06'
order by reporttime
and the working select statement is:
select top 100 *
from APRSTrack
where (not (altitude is null)) and reporttime >= '2006-06-06'
order by reporttime desc
The execution plan for the first lookup is:
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.2047.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="2.67004" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00992778" StatementText="SELECT *
 FROM [Ham Radio].[dbo].[APRSTrack]
where callsignssid = 'EA3ABN-9' and reporttime >= '2006-06-06'
order by reporttime" StatementType="SELECT">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan CachedPlanSize="60">
<RelOp AvgRowSize="54" EstimateCPU="1.11608E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.67004" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00992778">
<OutputList>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Latitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Longitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Icon" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Course" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Speed" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Altitude" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Column="PtnIds1007" />
<ColumnReference Column="Bmk1000" />
</OuterReferences>
<PartitionId>
<ColumnReference Column="PtnIds1007" />
</PartitionId>
<RelOp AvgRowSize="38" EstimateCPU="2.67004E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.67004" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0032852">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
<ColumnReference Column="PtnIds1007" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="PtnIds1007" />
<ScalarOperator ScalarString="RangePartitionNew([Ham Radio].[dbo].[APRSTrack].[ReportTime],(1),'2006-05-07 00:00:00.000','2006-05-08 00:00:00.000','2006-05-09 00:00:00.000','2006-05-10 00:00:00.000','2006-05-11 00:00:00.000','2006-05-12 00:00:00.000','2006-05-13 00:00:00.000','2006-05-14 00:00:00.000','2006-05-15 00:00:00.000','2006-05-16 00:00:00.000','2006-05-17 00:00:00.000','2006-05-18 00:00:00.000','2006-05-19 00:00:00.000','2006-05-20 00:00:00.000','2006-05-21 00:00:00.000','2006-05-22 00:00:00.000','2006-05-23 00:00:00.000','2006-05-24 00:00:00.000','2006-05-25 00:00:00.000','2006-05-26 00:00:00.000','2006-05-27 00:00:00.000','2006-05-28 00:00:00.000','2006-05-29 00:00:00.000','2006-05-30 00:00:00.000','2006-05-31 00:00:00.000','2006-06-01 00:00:00.000','2006-06-02 00:00:00.000','2006-06-03 00:00:00.000','2006-06-04 00:00:00.000','2006-06-05 00:00:00.000','2006-06-06 00:00:00.000')">
<Intrinsic FunctionName="RangePartitionNew">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-07 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-08 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-09 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-10 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-11 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-12 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-13 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-14 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-15 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-16 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-17 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-18 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-19 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-20 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-21 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-22 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-23 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-24 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-25 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-26 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-27 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-28 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-29 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-30 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-31 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-01 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-02 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-03 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-04 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-05 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-06 00:00:00.000'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="34" EstimateCPU="0.000159937" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.67004" LogicalOp="Index Seek" NodeId="2" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00328494">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Bmk1000" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</DefinedValue>
</DefinedValues>
<Object Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Index="[IX_APRSTrack]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="'EA3ABN-9'">
<Const ConstValue="'EA3ABN-9'" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
<StartRange ScanType="GE">
<RangeColumns>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="'2006-06-06 00:00:00.000'">
<Const ConstValue="'2006-06-06 00:00:00.000'" />
</ScalarOperator>
</RangeExpressions>
</StartRange>
</SeekPredicate>
</SeekPredicates>
<PartitionId>
<ColumnReference Column="ConstExpr1012">
<ScalarOperator ScalarString="(32)">
<Const ConstValue="(32)" />
</ScalarOperator>
</ColumnReference>
</PartitionId>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="35" EstimateCPU="0.000251393" EstimateIO="0.003125" EstimateRebinds="1.67004" EstimateRewinds="0" EstimateRows="1" LogicalOp="RID Lookup" NodeId="7" Parallel="false" PhysicalOp="RID Lookup" EstimatedTotalSubtreeCost="0.00663141">
<OutputList>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Latitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Longitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Icon" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Course" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Speed" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Altitude" />
</OutputList>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Latitude" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Longitude" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Icon" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Course" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Speed" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Altitude" />
</DefinedValue>
</DefinedValues>
<Object Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" TableReferenceId="-1" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Column="Bmk1000" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Bmk1000]">
<Identifier>
<ColumnReference Column="Bmk1000" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<PartitionId>
<ColumnReference Column="PtnIds1007" />
</PartitionId>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<ParameterList>
<ColumnReference Column="@.2" ParameterCompiledValue="'2006-06-06'" />
<ColumnReference Column="@.1" ParameterCompiledValue="'EA3ABN-9'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Any ideas?
Pete Loveall
AME Corp.
This is definitely an issue with the nonclustered index. I changed the index to a clustered index and everything works fine. In fact, the table originally had a clustered index just on reporttime and IX_APRSTrack was a nonclustered index. When I dropped the reporttime clustered index, all entries added after that point were improperly displayed in any query using the nonclustered index. Once I changed the nonclustered index to a clustered index, every row displays properly now including those that did not display properly previously.
SQL 2005 query has some definite issues with nonclustered indexes. Some of those issues may be related to partioned indexes on partitioned tables, but the nonclustered seeks are definitely wrong. The indexes and databases check just fine using CHECKDB. I also notice that doing a search using a nonclustered index which should be confined to one or two partitions is actually done across all partitions (see the execution plan above). The same SELECT done against a clustered index restricts the lookup to the relavent partitions.
This table has a high rate of insertions which make the clustered index undesireable. However, if that is what must be done until Microsoft fixes the query generator, we'll limp along.
Microsoft, are you listening?
Pete Loveall
AME Corp.
Subscribe to:
Posts (Atom)