Difference Between Physical IOs and Read-Ahead

Hello ...

I was running a table scan query on a 650MB table w/ 1 million rows. With statistics IO and time I noticed that I was able to get the following numbers on SQL Server 2005 (note this was the Sept CTP):

Logical IOs: 76,931

Physical IOs: 0

Read-Ahead: 58,321

CPU Time: 1212 ms

Clock Time: 42946 ms

So, in looking at the above I'm not seeing any physical IOs, but a lot of "read-ahead" IOs. If I understand it from the docs, a read-ahead essentially moves a data page into the cache. I understand this may mean getting a larger IO block size. Does this mean that I'm in fact doing physical IOs? This is a little confusing on the difference.

I noticed that when I ran a similar table scan query on a smaller table (say 266MB with 1 million rows) I had zero physical IOs and zero read-ahead hits. The query was significantly faster. The clock time was about 500 ms.

There were no indexes on either table.

Any advise here? Seems a little odd

Thanks!

DB

Read-ahead just means that the query processor asks for pages from a table to be pre-fetched into the cache. In this case, QP doesn't wait for the requests to complete - it is done asynchronously. The pages in the cache might be used later during the processing of the query at which point it may or may not be in the cache due to other activities on the server. So when the request for the page happens again it might incur in a physical IO (page was evicted from cache) or login IO (page is already in cache). It is possible that you see non-zero values for all the 3 counters. Physical IO is just that - fetching page from disk to memory. Logical IO is a page that is already in cache or memory (this can exceed the actual number of pages for a table if the same page is requested multiple times during query processing). You should think of read-ahead as an optimization mechanism that allows QP to request for pre-fetching pages that will be potentially used in the query.|||

Hi Umachandar ...

Sorry for the delay in responding to this...

From your message above and my real-life example above, since I have no physical IOs on the query using read aheads ... it sounds like the number of read ahead IOs is included in the total number of logical IOs. I think what you're saying is that in this situation w/ zero physical IOs, these are re-reads from cache of pages that had been previously cached ... is this the right way to look at it?

The example with the larger table using read aheads and the example with the smaller table and no read aheads were both just table scans. There were no indexes on either table, no where clause, and no physical IOs. I can see where scanning the larger table would take a bit longer than scanning the smaller table, but the clock time difference between the two samples seems really extreme. The larger table did require more CPU time (perhaps processing the read aheads ... ummm maybe re-reading previously cached pages?) but the overall picture doesn't make sense.

Are there cases where read aheads can perform poorly, and if so would one shut this feature off in terms of performance and tuning?

Thanks so much!

Doug

|||Yes, this is correct. The read-aheads are just requests to fetch pages from disk to cache and if they are already in memory then there is no additional work required. You should watch for cases where there was lot of read-ahead requests but the actual number of pages that were processed for the query is less in number. The difference in the CPU time might be due to the size of the larger table (i.e., more pages to read and process).

Difference Between Physical IOs and Read-Ahead

Hello ...

I was running a table scan query on a 650MB table w/ 1 million rows. With statistics IO and time I noticed that I was able to get the following numbers on SQL Server 2005 (note this was the Sept CTP):

Logical IOs: 76,931

Physical IOs: 0

Read-Ahead: 58,321

CPU Time: 1212 ms

Clock Time: 42946 ms

So, in looking at the above I'm not seeing any physical IOs, but a lot of "read-ahead" IOs. If I understand it from the docs, a read-ahead essentially moves a data page into the cache. I understand this may mean getting a larger IO block size. Does this mean that I'm in fact doing physical IOs? This is a little confusing on the difference.

I noticed that when I ran a similar table scan query on a smaller table (say 266MB with 1 million rows) I had zero physical IOs and zero read-ahead hits. The query was significantly faster. The clock time was about 500 ms.

There were no indexes on either table.

Any advise here? Seems a little odd

Thanks!

DB

Read-ahead just means that the query processor asks for pages from a table to be pre-fetched into the cache. In this case, QP doesn't wait for the requests to complete - it is done asynchronously. The pages in the cache might be used later during the processing of the query at which point it may or may not be in the cache due to other activities on the server. So when the request for the page happens again it might incur in a physical IO (page was evicted from cache) or login IO (page is already in cache). It is possible that you see non-zero values for all the 3 counters. Physical IO is just that - fetching page from disk to memory. Logical IO is a page that is already in cache or memory (this can exceed the actual number of pages for a table if the same page is requested multiple times during query processing). You should think of read-ahead as an optimization mechanism that allows QP to request for pre-fetching pages that will be potentially used in the query.|||

Hi Umachandar ...

Sorry for the delay in responding to this...

From your message above and my real-life example above, since I have no physical IOs on the query using read aheads ... it sounds like the number of read ahead IOs is included in the total number of logical IOs. I think what you're saying is that in this situation w/ zero physical IOs, these are re-reads from cache of pages that had been previously cached ... is this the right way to look at it?

The example with the larger table using read aheads and the example with the smaller table and no read aheads were both just table scans. There were no indexes on either table, no where clause, and no physical IOs. I can see where scanning the larger table would take a bit longer than scanning the smaller table, but the clock time difference between the two samples seems really extreme. The larger table did require more CPU time (perhaps processing the read aheads ... ummm maybe re-reading previously cached pages?) but the overall picture doesn't make sense.

Are there cases where read aheads can perform poorly, and if so would one shut this feature off in terms of performance and tuning?

Thanks so much!

Doug

|||Yes, this is correct. The read-aheads are just requests to fetch pages from disk to cache and if they are already in memory then there is no additional work required. You should watch for cases where there was lot of read-ahead requests but the actual number of pages that were processed for the query is less in number. The difference in the CPU time might be due to the size of the larger table (i.e., more pages to read and process).

Difference between Index & Statistics

2000 & 2005 (The DDL was pulled from the 2005 box, but should be the same, or
very close, on the 2000 box)
I know what statistics are: distribution of values used by the Query
Optimizer.
I know what indexes are.
I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
table, nor do I understand why Visio seem to be marking a column with
Statistics (ObjectID) as having a Unique index.
The column in question is "ObjectID" and is used in a JOIN to other tables.
REATE TABLE [dbo].[Documents](
[DocumentID] [int] IDENTITY(1,1) NOT NULL,
[ObjectID] [int] NULL,
[ObjectTypeID] [int] NULL,
[StatusID] [int] NOT NULL CONSTRAINT [DF_Documents_StatusID] DEFAULT (1),
[StatusComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Searchable] [bit] NOT NULL CONSTRAINT [DF_Documents_Searchable] DEFAULT
(1),
[DateEntered] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateEntered]
DEFAULT (getdate()),
[DateModified] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateModified]
DEFAULT (getdate()),
[ModifiedBy] [int] NULL,
[ReleaseDate] [smalldatetime] NULL,
[ExpireDate] [smalldatetime] NULL,
[ViewCount] [int] NOT NULL CONSTRAINT [DF_Documents_ViewCount] DEFAULT
((0)),
[AddedBy] [int] NULL,
[DateAuthorCreated] [datetime] NULL,
[DateAuthorRevised] [datetime] NULL,
[Title] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShortTitle] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED (
[DocumentID] ASC
)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY])
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE STATISTICS [_dta_stat_29243159_1_2] ON
[dbo].[Documents]([DocumentID], [ObjectID])
GO
ALTER TABLE [dbo].[Documents] WITH CHECK ADD CONSTRAINT
[FK_Documents_ltblObjectType] FOREIGN KEY([ObjectTypeID])
REFERENCES [dbo].[ltblObjectType] ([ObjectTypeID])
GO
ALTER TABLE [dbo].[Documents] CHECK CONSTRAINT [FK_Documents_ltblObjectType]> I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
> table
That you should ask the one show created the statistics. In fact, the name implies it was done by
Database Engine Tuning Advisor. Can that be correct? Anyhow, the statistics is on the column
*combination* (DocumentID, ObjectID). The only index I see is the one created for the PK which is on
only the column DocumentID. Even though distributiution information is for only the first column,
SQL Server *does* maintain density for the two columns (see output from DBCC SHOW_STATISTICS). So my
guess is that someone did a DTA for a workload and DTA suggested to create this statistics.
> nor do I understand why Visio seem to be marking a column with
> Statistics (ObjectID) as having a Unique index.
A bug in Visio? I suggest you ask in a Visio group, since you are more likely to find Visio experts
there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:1BE2F35F-7A55-4B69-8FF7-B48CECD48E91@.microsoft.com...
> 2000 & 2005 (The DDL was pulled from the 2005 box, but should be the same, or
> very close, on the 2000 box)
> I know what statistics are: distribution of values used by the Query
> Optimizer.
> I know what indexes are.
> I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
> table, nor do I understand why Visio seem to be marking a column with
> Statistics (ObjectID) as having a Unique index.
> The column in question is "ObjectID" and is used in a JOIN to other tables.
> REATE TABLE [dbo].[Documents](
> [DocumentID] [int] IDENTITY(1,1) NOT NULL,
> [ObjectID] [int] NULL,
> [ObjectTypeID] [int] NULL,
> [StatusID] [int] NOT NULL CONSTRAINT [DF_Documents_StatusID] DEFAULT (1),
> [StatusComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Searchable] [bit] NOT NULL CONSTRAINT [DF_Documents_Searchable] DEFAULT
> (1),
> [DateEntered] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateEntered]
> DEFAULT (getdate()),
> [DateModified] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateModified]
> DEFAULT (getdate()),
> [ModifiedBy] [int] NULL,
> [ReleaseDate] [smalldatetime] NULL,
> [ExpireDate] [smalldatetime] NULL,
> [ViewCount] [int] NOT NULL CONSTRAINT [DF_Documents_ViewCount] DEFAULT
> ((0)),
> [AddedBy] [int] NULL,
> [DateAuthorCreated] [datetime] NULL,
> [DateAuthorRevised] [datetime] NULL,
> [Title] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ShortTitle] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED (
> [DocumentID] ASC
> )
> WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY])
> ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> GO
> CREATE STATISTICS [_dta_stat_29243159_1_2] ON
> [dbo].[Documents]([DocumentID], [ObjectID])
> GO
> ALTER TABLE [dbo].[Documents] WITH CHECK ADD CONSTRAINT
> [FK_Documents_ltblObjectType] FOREIGN KEY([ObjectTypeID])
> REFERENCES [dbo].[ltblObjectType] ([ObjectTypeID])
> GO
> ALTER TABLE [dbo].[Documents] CHECK CONSTRAINT [FK_Documents_ltblObjectType]
>|||Thanks Tibor,
After reading your reply, I decided to look at the table again. For some
reason I was expecting MSSMS to give me all the DDL to the table in a single
option. My bad.
There is a non-unique index on ObjectID of the Documents table, so Visio's
"U" means non-unique and "I" means unique :( Oh well, Visio vs. ERwin.
As to the DTA, it kinda of sounds like you don't think much of it. Yes? No?
As to the Statistics, I'm still unclear why I would want them and not an
index. I am, of course, only refering to the statistics that show up in the
DDL, not the engine stats. Am I correct in the distinction I just made, or
should it be phrased differently?
"Tibor Karaszi" wrote:
> > I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
> > table
> That you should ask the one show created the statistics. In fact, the name implies it was done by
> Database Engine Tuning Advisor. Can that be correct? Anyhow, the statistics is on the column
> *combination* (DocumentID, ObjectID). The only index I see is the one created for the PK which is on
> only the column DocumentID. Even though distributiution information is for only the first column,
> SQL Server *does* maintain density for the two columns (see output from DBCC SHOW_STATISTICS). So my
> guess is that someone did a DTA for a workload and DTA suggested to create this statistics.
>
> > nor do I understand why Visio seem to be marking a column with
> > Statistics (ObjectID) as having a Unique index.
> A bug in Visio? I suggest you ask in a Visio group, since you are more likely to find Visio experts
> there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:1BE2F35F-7A55-4B69-8FF7-B48CECD48E91@.microsoft.com...
> > 2000 & 2005 (The DDL was pulled from the 2005 box, but should be the same, or
> > very close, on the 2000 box)
> >
> > I know what statistics are: distribution of values used by the Query
> > Optimizer.
> > I know what indexes are.
> >
> > I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
> > table, nor do I understand why Visio seem to be marking a column with
> > Statistics (ObjectID) as having a Unique index.
> >
> > The column in question is "ObjectID" and is used in a JOIN to other tables.
> >
> > REATE TABLE [dbo].[Documents](
> > [DocumentID] [int] IDENTITY(1,1) NOT NULL,
> > [ObjectID] [int] NULL,
> > [ObjectTypeID] [int] NULL,
> > [StatusID] [int] NOT NULL CONSTRAINT [DF_Documents_StatusID] DEFAULT (1),
> > [StatusComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [Searchable] [bit] NOT NULL CONSTRAINT [DF_Documents_Searchable] DEFAULT
> > (1),
> > [DateEntered] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateEntered]
> > DEFAULT (getdate()),
> > [DateModified] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateModified]
> > DEFAULT (getdate()),
> > [ModifiedBy] [int] NULL,
> > [ReleaseDate] [smalldatetime] NULL,
> > [ExpireDate] [smalldatetime] NULL,
> > [ViewCount] [int] NOT NULL CONSTRAINT [DF_Documents_ViewCount] DEFAULT
> > ((0)),
> > [AddedBy] [int] NULL,
> > [DateAuthorCreated] [datetime] NULL,
> > [DateAuthorRevised] [datetime] NULL,
> > [Title] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [ShortTitle] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED (
> > [DocumentID] ASC
> > )
> > WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY])
> > ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> >
> >
> > GO
> > CREATE STATISTICS [_dta_stat_29243159_1_2] ON
> > [dbo].[Documents]([DocumentID], [ObjectID])
> >
> > GO
> > ALTER TABLE [dbo].[Documents] WITH CHECK ADD CONSTRAINT
> > [FK_Documents_ltblObjectType] FOREIGN KEY([ObjectTypeID])
> > REFERENCES [dbo].[ltblObjectType] ([ObjectTypeID])
> > GO
> >
> > ALTER TABLE [dbo].[Documents] CHECK CONSTRAINT [FK_Documents_ltblObjectType]
> >
>|||> There is a non-unique index on ObjectID of the Documents table, so Visio's
> "U" means non-unique and "I" means unique :( Oh well, Visio vs. ERwin.
Now, that's weird. I guess different tool makers has different preferences...
> As to the DTA, it kinda of sounds like you don't think much of it.
No, that was not what I was trying to say. DTA is been much improved since Index Tuning Wizard (2000
and 7.0). IMO, a tool like this will never replace the human brain, but it is a good complement to
the work we do.
> As to the Statistics, I'm still unclear why I would want them and not an
> index. I am, of course, only refering to the statistics that show up in the
> DDL, not the engine stats. Am I correct in the distinction I just made, or
> should it be phrased differently?
Sometimes, statistics can help the optimizer pick a better plan, even in cases where an index
wouldn't be used. So, in these cases, why carry a b-tree if it won't be used?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:9CAF481A-3FF4-4557-9D7E-8053544E480D@.microsoft.com...
> Thanks Tibor,
> After reading your reply, I decided to look at the table again. For some
> reason I was expecting MSSMS to give me all the DDL to the table in a single
> option. My bad.
> There is a non-unique index on ObjectID of the Documents table, so Visio's
> "U" means non-unique and "I" means unique :( Oh well, Visio vs. ERwin.
> As to the DTA, it kinda of sounds like you don't think much of it. Yes? No?
> As to the Statistics, I'm still unclear why I would want them and not an
> index. I am, of course, only refering to the statistics that show up in the
> DDL, not the engine stats. Am I correct in the distinction I just made, or
> should it be phrased differently?
> "Tibor Karaszi" wrote:
>> > I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
>> > table
>> That you should ask the one show created the statistics. In fact, the name implies it was done by
>> Database Engine Tuning Advisor. Can that be correct? Anyhow, the statistics is on the column
>> *combination* (DocumentID, ObjectID). The only index I see is the one created for the PK which is
>> on
>> only the column DocumentID. Even though distributiution information is for only the first column,
>> SQL Server *does* maintain density for the two columns (see output from DBCC SHOW_STATISTICS). So
>> my
>> guess is that someone did a DTA for a workload and DTA suggested to create this statistics.
>>
>> > nor do I understand why Visio seem to be marking a column with
>> > Statistics (ObjectID) as having a Unique index.
>> A bug in Visio? I suggest you ask in a Visio group, since you are more likely to find Visio
>> experts
>> there.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
>> news:1BE2F35F-7A55-4B69-8FF7-B48CECD48E91@.microsoft.com...
>> > 2000 & 2005 (The DDL was pulled from the 2005 box, but should be the same, or
>> > very close, on the 2000 box)
>> >
>> > I know what statistics are: distribution of values used by the Query
>> > Optimizer.
>> > I know what indexes are.
>> >
>> > I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
>> > table, nor do I understand why Visio seem to be marking a column with
>> > Statistics (ObjectID) as having a Unique index.
>> >
>> > The column in question is "ObjectID" and is used in a JOIN to other tables.
>> >
>> > REATE TABLE [dbo].[Documents](
>> > [DocumentID] [int] IDENTITY(1,1) NOT NULL,
>> > [ObjectID] [int] NULL,
>> > [ObjectTypeID] [int] NULL,
>> > [StatusID] [int] NOT NULL CONSTRAINT [DF_Documents_StatusID] DEFAULT (1),
>> > [StatusComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
>> > [Searchable] [bit] NOT NULL CONSTRAINT [DF_Documents_Searchable] DEFAULT
>> > (1),
>> > [DateEntered] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateEntered]
>> > DEFAULT (getdate()),
>> > [DateModified] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateModified]
>> > DEFAULT (getdate()),
>> > [ModifiedBy] [int] NULL,
>> > [ReleaseDate] [smalldatetime] NULL,
>> > [ExpireDate] [smalldatetime] NULL,
>> > [ViewCount] [int] NOT NULL CONSTRAINT [DF_Documents_ViewCount] DEFAULT
>> > ((0)),
>> > [AddedBy] [int] NULL,
>> > [DateAuthorCreated] [datetime] NULL,
>> > [DateAuthorRevised] [datetime] NULL,
>> > [Title] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
>> > [ShortTitle] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
>> > CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED (
>> > [DocumentID] ASC
>> > )
>> > WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY])
>> > ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>> >
>> >
>> > GO
>> > CREATE STATISTICS [_dta_stat_29243159_1_2] ON
>> > [dbo].[Documents]([DocumentID], [ObjectID])
>> >
>> > GO
>> > ALTER TABLE [dbo].[Documents] WITH CHECK ADD CONSTRAINT
>> > [FK_Documents_ltblObjectType] FOREIGN KEY([ObjectTypeID])
>> > REFERENCES [dbo].[ltblObjectType] ([ObjectTypeID])
>> > GO
>> >
>> > ALTER TABLE [dbo].[Documents] CHECK CONSTRAINT [FK_Documents_ltblObjectType]
>> >
>>sql

Difference between Index & Statistics

2000 & 2005 (The DDL was pulled from the 2005 box, but should be the same, o
r
very close, on the 2000 box)
I know what statistics are: distribution of values used by the Query
Optimizer.
I know what indexes are.
I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
table, nor do I understand why visio seem to be marking a column with
Statistics (ObjectID) as having a Unique index.
The column in question is "ObjectID" and is used in a JOIN to other tables.
REATE TABLE [dbo].[Documents](
[DocumentID] [int] IDENTITY(1,1) NOT NULL,
[ObjectID] [int] NULL,
[ObjectTypeID] [int] NULL,
[StatusID] [int] NOT NULL CONSTRAINT [DF_Documents_StatusID] DE
FAULT (1),
[StatusComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Searchable] [bit] NOT NULL CONSTRAINT [DF_Documents_Searchable]
DEFAULT
(1),
[DateEntered] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateE
ntered]
DEFAULT (getdate()),
[DateModified] [datetime] NOT NULL CONSTRAINT [DF_Documents_Date
Modified]
DEFAULT (getdate()),
[ModifiedBy] [int] NULL,
[ReleaseDate] [smalldatetime] NULL,
[ExpireDate] [smalldatetime] NULL,
[ViewCount] [int] NOT NULL CONSTRAINT [DF_Documents_ViewCount]
DEFAULT
((0)),
[AddedBy] [int] NULL,
[DateAuthorCreated] [datetime] NULL,
[DateAuthorRevised] [datetime] NULL,
[Title] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShortTitle] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED (
[DocumentID] ASC
)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY])
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE STATISTICS [_dta_stat_29243159_1_2] ON
[dbo].[Documents]([DocumentID], [ObjectID])
GO
ALTER TABLE [dbo].[Documents] WITH CHECK ADD CONSTRAINT
[FK_Documents_ltblObjectType] FOREIGN KEY([ObjectTypeID])
REFERENCES [dbo].[ltblObjectType] ([ObjectTypeID])
GO
ALTER TABLE [dbo].[Documents] CHECK CONSTRAINT [FK_Documents_ltb
lObjectType]> I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
> table
That you should ask the one show created the statistics. In fact, the name i
mplies it was done by
Database Engine Tuning Advisor. Can that be correct? Anyhow, the statistics
is on the column
*combination* (DocumentID, ObjectID). The only index I see is the one create
d for the PK which is on
only the column DocumentID. Even though distributiution information is for o
nly the first column,
SQL Server *does* maintain density for the two columns (see output from DBCC
SHOW_STATISTICS). So my
guess is that someone did a DTA for a workload and DTA suggested to create t
his statistics.

> nor do I understand why visio seem to be marking a column with
> Statistics (ObjectID) as having a Unique index.
A bug in Visio? I suggest you ask in a visio group, since you are more likel
y to find visio experts
there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:1BE2F35F-7A55-4B69-8FF7-B48CECD48E91@.microsoft.com...
> 2000 & 2005 (The DDL was pulled from the 2005 box, but should be the same,
or
> very close, on the 2000 box)
> I know what statistics are: distribution of values used by the Query
> Optimizer.
> I know what indexes are.
> I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
> table, nor do I understand why visio seem to be marking a column with
> Statistics (ObjectID) as having a Unique index.
> The column in question is "ObjectID" and is used in a JOIN to other tables
.
> REATE TABLE [dbo].[Documents](
> [DocumentID] [int] IDENTITY(1,1) NOT NULL,
> [ObjectID] [int] NULL,
> [ObjectTypeID] [int] NULL,
> [StatusID] [int] NOT NULL CONSTRAINT [DF_Documents_StatusID]
DEFAULT (1),
> [StatusComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Searchable] [bit] NOT NULL CONSTRAINT [DF_Documents_Searchabl
e] DEFAULT
> (1),
> [DateEntered] [datetime] NOT NULL CONSTRAINT [DF_Documents_Dat
eEntered]
> DEFAULT (getdate()),
> [DateModified] [datetime] NOT NULL CONSTRAINT [DF_Documents_Da
teModified]
> DEFAULT (getdate()),
> [ModifiedBy] [int] NULL,
> [ReleaseDate] [smalldatetime] NULL,
> [ExpireDate] [smalldatetime] NULL,
> [ViewCount] [int] NOT NULL CONSTRAINT [DF_Documents_ViewCount]
DEFAULT
> ((0)),
> [AddedBy] [int] NULL,
> [DateAuthorCreated] [datetime] NULL,
> [DateAuthorRevised] [datetime] NULL,
> [Title] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ShortTitle] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED (
> [DocumentID] ASC
> )
> WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY])
> ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> GO
> CREATE STATISTICS [_dta_stat_29243159_1_2] ON
> [dbo].[Documents]([DocumentID], [ObjectID])
> GO
> ALTER TABLE [dbo].[Documents] WITH CHECK ADD CONSTRAINT
> [FK_Documents_ltblObjectType] FOREIGN KEY([ObjectTypeID])
> REFERENCES [dbo].[ltblObjectType] ([ObjectTypeID])
> GO
> ALTER TABLE [dbo].[Documents] CHECK CONSTRAINT [FK_Documents_l
tblObjectType]
>|||Thanks Tibor,
After reading your reply, I decided to look at the table again. For some
reason I was expecting MSSMS to give me all the DDL to the table in a single
option. My bad.
There is a non-unique index on ObjectID of the Documents table, so Visio's
"U" means non-unique and "I" means unique Oh well, visio vs. ERwin.
As to the DTA, it kinda of sounds like you don't think much of it. Yes? No?
As to the Statistics, I'm still unclear why I would want them and not an
index. I am, of course, only refering to the statistics that show up in the
DDL, not the engine stats. Am I correct in the distinction I just made, or
should it be phrased differently?
"Tibor Karaszi" wrote:

> That you should ask the one show created the statistics. In fact, the name
implies it was done by
> Database Engine Tuning Advisor. Can that be correct? Anyhow, the statistic
s is on the column
> *combination* (DocumentID, ObjectID). The only index I see is the one crea
ted for the PK which is on
> only the column DocumentID. Even though distributiution information is for
only the first column,
> SQL Server *does* maintain density for the two columns (see output from DB
CC SHOW_STATISTICS). So my
> guess is that someone did a DTA for a workload and DTA suggested to create
this statistics.
>
> A bug in Visio? I suggest you ask in a visio group, since you are more lik
ely to find visio experts
> there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:1BE2F35F-7A55-4B69-8FF7-B48CECD48E91@.microsoft.com...
>|||> There is a non-unique index on ObjectID of the Documents table, so Visio's
> "U" means non-unique and "I" means unique Oh well, visio vs. ERwin.
Now, that's weird. I guess different tool makers has different preferences..
.

> As to the DTA, it kinda of sounds like you don't think much of it.
No, that was not what I was trying to say. DTA is been much improved since I
ndex Tuning Wizard (2000
and 7.0). IMO, a tool like this will never replace the human brain, but it i
s a good complement to
the work we do.

> As to the Statistics, I'm still unclear why I would want them and not an
> index. I am, of course, only refering to the statistics that show up in th
e
> DDL, not the engine stats. Am I correct in the distinction I just made, or
> should it be phrased differently?
Sometimes, statistics can help the optimizer pick a better plan, even in cas
es where an index
wouldn't be used. So, in these cases, why carry a b-tree if it won't be used
?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:9CAF481A-3FF4-4557-9D7E-8053544E480D@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor,
> After reading your reply, I decided to look at the table again. For some
> reason I was expecting MSSMS to give me all the DDL to the table in a sing
le
> option. My bad.
> There is a non-unique index on ObjectID of the Documents table, so Visio's
> "U" means non-unique and "I" means unique Oh well, visio vs. ERwin.
> As to the DTA, it kinda of sounds like you don't think much of it. Yes? No
?
> As to the Statistics, I'm still unclear why I would want them and not an
> index. I am, of course, only refering to the statistics that show up in th
e
> DDL, not the engine stats. Am I correct in the distinction I just made, or
> should it be phrased differently?
> "Tibor Karaszi" wrote:
>

Difference between Index & Statistics

2000 & 2005 (The DDL was pulled from the 2005 box, but should be the same, or
very close, on the 2000 box)
I know what statistics are: distribution of values used by the Query
Optimizer.
I know what indexes are.
I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
table, nor do I understand why Visio seem to be marking a column with
Statistics (ObjectID) as having a Unique index.
The column in question is "ObjectID" and is used in a JOIN to other tables.
REATE TABLE [dbo].[Documents](
[DocumentID] [int] IDENTITY(1,1) NOT NULL,
[ObjectID] [int] NULL,
[ObjectTypeID] [int] NULL,
[StatusID] [int] NOT NULL CONSTRAINT [DF_Documents_StatusID] DEFAULT (1),
[StatusComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Searchable] [bit] NOT NULL CONSTRAINT [DF_Documents_Searchable] DEFAULT
(1),
[DateEntered] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateEntered]
DEFAULT (getdate()),
[DateModified] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateModified]
DEFAULT (getdate()),
[ModifiedBy] [int] NULL,
[ReleaseDate] [smalldatetime] NULL,
[ExpireDate] [smalldatetime] NULL,
[ViewCount] [int] NOT NULL CONSTRAINT [DF_Documents_ViewCount] DEFAULT
((0)),
[AddedBy] [int] NULL,
[DateAuthorCreated] [datetime] NULL,
[DateAuthorRevised] [datetime] NULL,
[Title] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShortTitle] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED (
[DocumentID] ASC
)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY])
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE STATISTICS [_dta_stat_29243159_1_2] ON
[dbo].[Documents]([DocumentID], [ObjectID])
GO
ALTER TABLE [dbo].[Documents] WITH CHECK ADD CONSTRAINT
[FK_Documents_ltblObjectType] FOREIGN KEY([ObjectTypeID])
REFERENCES [dbo].[ltblObjectType] ([ObjectTypeID])
GO
ALTER TABLE [dbo].[Documents] CHECK CONSTRAINT [FK_Documents_ltblObjectType]
> I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
> table
That you should ask the one show created the statistics. In fact, the name implies it was done by
Database Engine Tuning Advisor. Can that be correct? Anyhow, the statistics is on the column
*combination* (DocumentID, ObjectID). The only index I see is the one created for the PK which is on
only the column DocumentID. Even though distributiution information is for only the first column,
SQL Server *does* maintain density for the two columns (see output from DBCC SHOW_STATISTICS). So my
guess is that someone did a DTA for a workload and DTA suggested to create this statistics.

> nor do I understand why Visio seem to be marking a column with
> Statistics (ObjectID) as having a Unique index.
A bug in Visio? I suggest you ask in a Visio group, since you are more likely to find Visio experts
there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:1BE2F35F-7A55-4B69-8FF7-B48CECD48E91@.microsoft.com...
> 2000 & 2005 (The DDL was pulled from the 2005 box, but should be the same, or
> very close, on the 2000 box)
> I know what statistics are: distribution of values used by the Query
> Optimizer.
> I know what indexes are.
> I do not understand why I see both CREATE INDEX and CREATE STATISTICS on a
> table, nor do I understand why Visio seem to be marking a column with
> Statistics (ObjectID) as having a Unique index.
> The column in question is "ObjectID" and is used in a JOIN to other tables.
> REATE TABLE [dbo].[Documents](
> [DocumentID] [int] IDENTITY(1,1) NOT NULL,
> [ObjectID] [int] NULL,
> [ObjectTypeID] [int] NULL,
> [StatusID] [int] NOT NULL CONSTRAINT [DF_Documents_StatusID] DEFAULT (1),
> [StatusComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Searchable] [bit] NOT NULL CONSTRAINT [DF_Documents_Searchable] DEFAULT
> (1),
> [DateEntered] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateEntered]
> DEFAULT (getdate()),
> [DateModified] [datetime] NOT NULL CONSTRAINT [DF_Documents_DateModified]
> DEFAULT (getdate()),
> [ModifiedBy] [int] NULL,
> [ReleaseDate] [smalldatetime] NULL,
> [ExpireDate] [smalldatetime] NULL,
> [ViewCount] [int] NOT NULL CONSTRAINT [DF_Documents_ViewCount] DEFAULT
> ((0)),
> [AddedBy] [int] NULL,
> [DateAuthorCreated] [datetime] NULL,
> [DateAuthorRevised] [datetime] NULL,
> [Title] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ShortTitle] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED (
> [DocumentID] ASC
> )
> WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY])
> ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> GO
> CREATE STATISTICS [_dta_stat_29243159_1_2] ON
> [dbo].[Documents]([DocumentID], [ObjectID])
> GO
> ALTER TABLE [dbo].[Documents] WITH CHECK ADD CONSTRAINT
> [FK_Documents_ltblObjectType] FOREIGN KEY([ObjectTypeID])
> REFERENCES [dbo].[ltblObjectType] ([ObjectTypeID])
> GO
> ALTER TABLE [dbo].[Documents] CHECK CONSTRAINT [FK_Documents_ltblObjectType]
>
|||Thanks Tibor,
After reading your reply, I decided to look at the table again. For some
reason I was expecting MSSMS to give me all the DDL to the table in a single
option. My bad.
There is a non-unique index on ObjectID of the Documents table, so Visio's
"U" means non-unique and "I" means unique Oh well, Visio vs. ERwin.
As to the DTA, it kinda of sounds like you don't think much of it. Yes? No?
As to the Statistics, I'm still unclear why I would want them and not an
index. I am, of course, only refering to the statistics that show up in the
DDL, not the engine stats. Am I correct in the distinction I just made, or
should it be phrased differently?
"Tibor Karaszi" wrote:

> That you should ask the one show created the statistics. In fact, the name implies it was done by
> Database Engine Tuning Advisor. Can that be correct? Anyhow, the statistics is on the column
> *combination* (DocumentID, ObjectID). The only index I see is the one created for the PK which is on
> only the column DocumentID. Even though distributiution information is for only the first column,
> SQL Server *does* maintain density for the two columns (see output from DBCC SHOW_STATISTICS). So my
> guess is that someone did a DTA for a workload and DTA suggested to create this statistics.
>
> A bug in Visio? I suggest you ask in a Visio group, since you are more likely to find Visio experts
> there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:1BE2F35F-7A55-4B69-8FF7-B48CECD48E91@.microsoft.com...
>
|||> There is a non-unique index on ObjectID of the Documents table, so Visio's
> "U" means non-unique and "I" means unique Oh well, Visio vs. ERwin.
Now, that's weird. I guess different tool makers has different preferences...

> As to the DTA, it kinda of sounds like you don't think much of it.
No, that was not what I was trying to say. DTA is been much improved since Index Tuning Wizard (2000
and 7.0). IMO, a tool like this will never replace the human brain, but it is a good complement to
the work we do.

> As to the Statistics, I'm still unclear why I would want them and not an
> index. I am, of course, only refering to the statistics that show up in the
> DDL, not the engine stats. Am I correct in the distinction I just made, or
> should it be phrased differently?
Sometimes, statistics can help the optimizer pick a better plan, even in cases where an index
wouldn't be used. So, in these cases, why carry a b-tree if it won't be used?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:9CAF481A-3FF4-4557-9D7E-8053544E480D@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor,
> After reading your reply, I decided to look at the table again. For some
> reason I was expecting MSSMS to give me all the DDL to the table in a single
> option. My bad.
> There is a non-unique index on ObjectID of the Documents table, so Visio's
> "U" means non-unique and "I" means unique Oh well, Visio vs. ERwin.
> As to the DTA, it kinda of sounds like you don't think much of it. Yes? No?
> As to the Statistics, I'm still unclear why I would want them and not an
> index. I am, of course, only refering to the statistics that show up in the
> DDL, not the engine stats. Am I correct in the distinction I just made, or
> should it be phrased differently?
> "Tibor Karaszi" wrote: