Do While in SQL: indexes

Showing posts with label indexes. Show all posts

Thursday, March 29, 2012

Do other RDMS have clustered indexes?

i realize the term "clustered index" is a MS thing. i also know that the
clustered index is the physical sort order of data in a table. And once i
have this technique available to me, i can take advantage of it to group
low-cardinality rows together, where an index would not be selective enough.
So, with clustered indexes in SQL Server, i have another performance tuning
option. Does Oracle, MySQL, DB2 let the user change the physical sort order
of a table - and be able to take advantage of that for query optimization?I recall a long long time ago working with the OS/2 version of DB2 (DB2/2):
You did a rebuild of the table to cluster it. This was a once operation. At the end of the table
(imagine pages and extents), you had overflow pages. New rows were not inserted in place, but were
added to these overflow pages. So, you didn't get the fragmentation aspects of SQL Server, but there
was some extra cost for looking up these overflow pages.
All above is from memory, from around 1991. This might have been special to the OS/2 version of DB2,
and/or it might have been changed since.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the clustered index is the
>physical sort order of data in a table. And once i have this technique available to me, i can take
>advantage of it to group low-cardinality rows together, where an index would not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance tuning option. Does Oracle,
> MySQL, DB2 let the user change the physical sort order of a table - and be able to take advantage
> of that for query optimization?
>|||With DB2 you can create a single clustered index on a table and specify
whether or not you want it ASC or DESC. You can also specify to either
allow or disallow Reverse Scans.
In addition DB2 v. 8.2 has added functionality to eliminate the limit
of one clustered index per table. They have what is call an MDC (Multi
Dimensional Cluster) which allows you to effectively create more than
one clustered index on a single table. They do this by arranging the
data into blocks rather than index pages. The are extremely effective
in performance tuning where you have a large data set.|||Oracle's Index Organized tables aka IOT are quite similar to MS SQL
Servers clustered index.
DB2's clustered index and its table are separate objects. The docs used
to say that DB2 will try to maintain physical order, but there was no
guarantee - you needed to reorganize the table periodically once the
clustering factor dropped too low. ("clustering factor" is statistics
present in DB2 and Oracle, but not relevant to MS SQL Server, because
MS SQL Server uses bookmarks, not Row identifiers to locate a row from
a non-clustered index)|||"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the
>clustered index is the physical sort order of data in a table. And once i
>have this technique available to me, i can take advantage of it to group
>low-cardinality rows together, where an index would not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance
> tuning option. Does Oracle, MySQL, DB2 let the user change the physical
> sort order of a table - and be able to take advantage of that for query
> optimization?
In Oracle an "Index-Organized Table" is the equivalent as a SQL Server
clustered index. There are some minor differences in implementation, for
instance, the requirement that the "clustered index" must be the primary
key. But they are pretty much the same thing.
David|||Alexander Kuznetsov wrote:
> Oracle's Index Organized tables aka IOT are quite similar to MS SQL
> Servers clustered index.
> DB2's clustered index and its table are separate objects. The docs
> used to say that DB2 will try to maintain physical order, but there
> was no guarantee - you needed to reorganize the table periodically
> once the clustering factor dropped too low. ("clustering factor" is
> statistics present in DB2 and Oracle, but not relevant to MS SQL
> Server, because MS SQL Server uses bookmarks, not Row identifiers to
> locate a row from a non-clustered index)
MaxDB has a similar feature: *all* tables are stored the way MS SQL Server
tables with a clustered index are stored. The index used is the PK of the
table and if there is no PK then a hidden column with a synthetic value is
added and used for the PK.
Kind regards
robert

Do other RDMS have clustered indexes?

i realize the term "clustered index" is a MS thing. i also know that the
clustered index is the physical sort order of data in a table. And once i
have this technique available to me, i can take advantage of it to group
low-cardinality rows together, where an index would not be selective enough.
So, with clustered indexes in SQL Server, i have another performance tuning
option. Does Oracle, MySQL, DB2 let the user change the physical sort order
of a table - and be able to take advantage of that for query optimization?I recall a long long time ago working with the OS/2 version of DB2 (DB2/2):
You did a rebuild of the table to cluster it. This was a once operation. At
the end of the table
(imagine pages and extents), you had overflow pages. New rows were not inser
ted in place, but were
added to these overflow pages. So, you didn't get the fragmentation aspects
of SQL Server, but there
was some extra cost for looking up these overflow pages.
All above is from memory, from around 1991. This might have been special to
the OS/2 version of DB2,
and/or it might have been changed since.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the cl
ustered index is the
>physical sort order of data in a table. And once i have this technique avai
lable to me, i can take
>advantage of it to group low-cardinality rows together, where an index woul
d not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance tunin
g option. Does Oracle,
> MySQL, DB2 let the user change the physical sort order of a table - and be
able to take advantage
> of that for query optimization?
>|||With DB2 you can create a single clustered index on a table and specify
whether or not you want it ASC or DESC. You can also specify to either
allow or disallow Reverse Scans.
In addition DB2 v. 8.2 has added functionality to eliminate the limit
of one clustered index per table. They have what is call an MDC (Multi
Dimensional Cluster) which allows you to effectively create more than
one clustered index on a single table. They do this by arranging the
data into blocks rather than index pages. The are extremely effective
in performance tuning where you have a large data set.|||Oracle's Index Organized tables aka IOT are quite similar to MS SQL
Servers clustered index.
DB2's clustered index and its table are separate objects. The docs used
to say that DB2 will try to maintain physical order, but there was no
guarantee - you needed to reorganize the table periodically once the
clustering factor dropped too low. ("clustering factor" is statistics
present in DB2 and Oracle, but not relevant to MS SQL Server, because
MS SQL Server uses bookmarks, not Row identifiers to locate a row from
a non-clustered index)|||"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the
>clustered index is the physical sort order of data in a table. And once i
>have this technique available to me, i can take advantage of it to group
>low-cardinality rows together, where an index would not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance
> tuning option. Does Oracle, MySQL, DB2 let the user change the physical
> sort order of a table - and be able to take advantage of that for query
> optimization?
In Oracle an "Index-Organized Table" is the equivalent as a SQL Server
clustered index. There are some minor differences in implementation, for
instance, the requirement that the "clustered index" must be the primary
key. But they are pretty much the same thing.
David|||Alexander Kuznetsov wrote:
> Oracle's Index Organized tables aka IOT are quite similar to MS SQL
> Servers clustered index.
> DB2's clustered index and its table are separate objects. The docs
> used to say that DB2 will try to maintain physical order, but there
> was no guarantee - you needed to reorganize the table periodically
> once the clustering factor dropped too low. ("clustering factor" is
> statistics present in DB2 and Oracle, but not relevant to MS SQL
> Server, because MS SQL Server uses bookmarks, not Row identifiers to
> locate a row from a non-clustered index)
MaxDB has a similar feature: *all* tables are stored the way MS SQL Server
tables with a clustered index are stored. The index used is the PK of the
table and if there is no PK then a hidden column with a synthetic value is
added and used for the PK.
Kind regards
robert

Do other RDMS have clustered indexes?

i realize the term "clustered index" is a MS thing. i also know that the
clustered index is the physical sort order of data in a table. And once i
have this technique available to me, i can take advantage of it to group
low-cardinality rows together, where an index would not be selective enough.
So, with clustered indexes in SQL Server, i have another performance tuning
option. Does Oracle, MySQL, DB2 let the user change the physical sort order
of a table - and be able to take advantage of that for query optimization?
I recall a long long time ago working with the OS/2 version of DB2 (DB2/2):
You did a rebuild of the table to cluster it. This was a once operation. At the end of the table
(imagine pages and extents), you had overflow pages. New rows were not inserted in place, but were
added to these overflow pages. So, you didn't get the fragmentation aspects of SQL Server, but there
was some extra cost for looking up these overflow pages.
All above is from memory, from around 1991. This might have been special to the OS/2 version of DB2,
and/or it might have been changed since.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the clustered index is the
>physical sort order of data in a table. And once i have this technique available to me, i can take
>advantage of it to group low-cardinality rows together, where an index would not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance tuning option. Does Oracle,
> MySQL, DB2 let the user change the physical sort order of a table - and be able to take advantage
> of that for query optimization?
>
|||With DB2 you can create a single clustered index on a table and specify
whether or not you want it ASC or DESC. You can also specify to either
allow or disallow Reverse Scans.
In addition DB2 v. 8.2 has added functionality to eliminate the limit
of one clustered index per table. They have what is call an MDC (Multi
Dimensional Cluster) which allows you to effectively create more than
one clustered index on a single table. They do this by arranging the
data into blocks rather than index pages. The are extremely effective
in performance tuning where you have a large data set.
|||Oracle's Index Organized tables aka IOT are quite similar to MS SQL
Servers clustered index.
DB2's clustered index and its table are separate objects. The docs used
to say that DB2 will try to maintain physical order, but there was no
guarantee - you needed to reorganize the table periodically once the
clustering factor dropped too low. ("clustering factor" is statistics
present in DB2 and Oracle, but not relevant to MS SQL Server, because
MS SQL Server uses bookmarks, not Row identifiers to locate a row from
a non-clustered index)
|||"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the
>clustered index is the physical sort order of data in a table. And once i
>have this technique available to me, i can take advantage of it to group
>low-cardinality rows together, where an index would not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance
> tuning option. Does Oracle, MySQL, DB2 let the user change the physical
> sort order of a table - and be able to take advantage of that for query
> optimization?
In Oracle an "Index-Organized Table" is the equivalent as a SQL Server
clustered index. There are some minor differences in implementation, for
instance, the requirement that the "clustered index" must be the primary
key. But they are pretty much the same thing.
David
|||Alexander Kuznetsov wrote:
> Oracle's Index Organized tables aka IOT are quite similar to MS SQL
> Servers clustered index.
> DB2's clustered index and its table are separate objects. The docs
> used to say that DB2 will try to maintain physical order, but there
> was no guarantee - you needed to reorganize the table periodically
> once the clustering factor dropped too low. ("clustering factor" is
> statistics present in DB2 and Oracle, but not relevant to MS SQL
> Server, because MS SQL Server uses bookmarks, not Row identifiers to
> locate a row from a non-clustered index)
MaxDB has a similar feature: *all* tables are stored the way MS SQL Server
tables with a clustered index are stored. The index used is the PK of the
table and if there is no PK then a hidden column with a synthetic value is
added and used for the PK.
Kind regards
robert

Thursday, March 22, 2012

Do foreign keys generate implicit indexes?

Heres an example of how the foreign key constraint is being added:

ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])

My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!

Bobbobdurie@.gmail.com (bobdurie@.gmail.com) writes:

Quote:

Originally Posted by

If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID?

In SQL Server, no.

Quote:

Originally Posted by

I've been told this is done in some databases, but i need to know for
sure if SQL Server does it. Has anyone heard of this before, on any
other databses perhaps?

I seem to recall having heard this about Sybase Anywhere.

Quote:

Originally Posted by

Heres an example of how the foreign key constraint is being added:
>
ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
>
My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!

Indeed, it is often a good idea to add indexes on foreign keys, as it
can speed up deletions considerably. And it is not uncommon to search
for data in a table on a foreign key. However, as always, you should
think twice, and not add indexes blindly. For instance, if you have a
country-code column in a address table, there is little reason to add
an index on that column, since you don't delete countries very often.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>I've been told this is done in some databases, .. <<

Yes, but better. Sybase SQL Anywhere (nee Watcom SQL) builds links
from all the FK references to the single PRIMARY KET/UNIQUE occurence
in the referenced table. Saves space, pre-joins tables for speed and
makes DRI actions both easy and fast.

SQL Server is still thinking in terms of "table = file" instead of
"table is part of a whole schema" and that "record =row" instead of
"row is made up of columns". Stonebreaker had a recent blog on column-
oriented design over contigous storage model.|||On Fri, 21 Sep 2007 19:51:14 -0000, "bobdurie@.gmail.com"
<bobdurie@.gmail.comwrote:

Microsoft Access does this, when you create a relationship between two
tables.
Check with sysindexes to see if SQL Server does this too.

-Tom.

Quote:

Originally Posted by

>If i create a simple table with a foreign key constraint, does it
>create an implicit index on that given ID? I've been told this is
>done in some databases, but i need to know for sure if SQL Server does
>it. Has anyone heard of this before, on any other databses perhaps?
>
>Heres an example of how the foreign key constraint is being added:
>
>ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
>[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
>REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
>
>My initial testing seems to indicate adding an index on the foreign
>key column helps, but i need to know for sure. Any insight would be
>greatly appreciated!
>
>Bob

|||Check with sysindexes to see if SQL Server does this too.

As Erland mentioned, SQL Server does not automatically index foreign key
columns. That task is left to the discretion of the DBA, who might choose
not to index the foreign column(s) due to low cardinality and static data.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tom van Stiphout" <no.spam.tom7744@.cox.netwrote in message
news:8qqbf3190hk4cis52502s9th2ebsjjfpd5@.4ax.com...

Quote:

Originally Posted by

On Fri, 21 Sep 2007 19:51:14 -0000, "bobdurie@.gmail.com"
<bobdurie@.gmail.comwrote:
>
Microsoft Access does this, when you create a relationship between two
tables.
Check with sysindexes to see if SQL Server does this too.
>
-Tom.
>
>

Quote:

Originally Posted by

>>If i create a simple table with a foreign key constraint, does it
>>create an implicit index on that given ID? I've been told this is
>>done in some databases, but i need to know for sure if SQL Server does
>>it. Has anyone heard of this before, on any other databses perhaps?
>>
>>Heres an example of how the foreign key constraint is being added:
>>
>>ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
>>[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
>>REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
>>
>>My initial testing seems to indicate adding an index on the foreign
>>key column helps, but i need to know for sure. Any insight would be
>>greatly appreciated!
>>
>>Bob

|||Also, analyse your query requirements then apply an Indexing Strategy

Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
<bobdurie@.gmail.comwrote in message
news:1190404274.471197.197240@.n39g2000hsh.googlegr oups.com...

Quote:

Originally Posted by

If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID? I've been told this is
done in some databases, but i need to know for sure if SQL Server does
it. Has anyone heard of this before, on any other databses perhaps?
>
Heres an example of how the foreign key constraint is being added:
>
ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
>
My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!
>
Bob
>

|||On Sep 24, 3:25 am, "Jack Vamvas" <DEL_TO_RE...@.del.comwrote:

Quote:

Originally Posted by

Also, analyse your query requirements then apply an Indexing Strategy
>
--
>
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
>
<bobdu...@.gmail.comwrote in message
>
news:1190404274.471197.197240@.n39g2000hsh.googlegr oups.com...
>

Quote:

Originally Posted by

Quote:

Originally Posted by

Heres an example of how the foreign key constraint is being added:

Quote:

Originally Posted by

ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])

Quote:

Originally Posted by

My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!

Quote:

Originally Posted by

Bob

Thanks for all the responses on this, its much appreciated!!! I also
found this article which makes me realize other people have had the
same misconceptions as me :)
http://www.sqlskills.com/blogs/kimb...yColu mns.aspx

Monday, March 19, 2012

dm_db_index_physical_stats not getting updated

I have a sql script that I use to reorganize indexes with more than 5%
fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
fragmentation. On running this, I found that, the script was working fine but
every iteration updated about 550 indexes. After a run, if I queried the
dynamic management view, it still gave back indexes which were fragmented.
My understanding is that after I do a reorg/rebuild, the entry should
disappear from sys.dm_db_index_physical_stats, if the alter table succeeds.
Question -- what can I do as a DBA, after the script runs to make sure that
the management view gives back updated information and not state information?
My script --
BEGIN
SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
INTO #INDEX_STATS_TEMP
FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
NULL, NULL)
WHERE avg_fragmentation_in_percent >= 5
AND index_type_desc != 'HEAP'
-- DECLARE LOCAL VARIABLES FOR THE CURSOR
DECLARE @.dbID int,
@.tableID int,
@.indexID int,
@.frag_percent float,
@.index_name varchar(100),
@.table_name varchar(100),
@.sql varchar(1000)
--DEFINE THE CURSOR
DECLARE FRAG_CURSOR CURSOR
FOR SELECT
TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
OPEN FRAG_CURSOR
FETCH NEXT FROM FRAG_CURSOR INTO
@.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
print @.tableID
SELECT @.table_name = name from DB_NAME.sys.objects where
object_id = @.tableID and type = 'U'
IF (@.frag_percent <=30)
BEGIN
USE DB_NAME
SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
REORGANIZE'
print @.SQL
exec (@.SQL)
END
ELSE IF (@.frag_percent > 30)
BEGIN
USE DB_NAME
SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
REBUILD WITH (ONLINE = OFF)'
print @.SQL
exec (@.SQL)
END
SET @.SQL = NULL
FETCH NEXT FROM FRAG_CURSOR INTO
@.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
END
CLOSE FRAG_CURSOR
DEALLOCATE FRAG_CURSOR
DROP TABLE #INDEX_STATS_TEMP
END
Regards
JaideepCheck the size of the index. I exclude indexes below a particular cutoff
(don't exactly recall what it is) because fragmentation information was not
meaningful.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine
> but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table
> succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure
> that
> the management view gives back updated information and not state
> information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>|||Try doing update statistics. Statistics will automatically get updated when
an index is rebuilt, but I don't think they do when an index is reorganized
or defragged.
--
MG
"Geoff N. Hiten" wrote:
> Check the size of the index. I exclude indexes below a particular cutoff
> (don't exactly recall what it is) because fragmentation information was not
> meaningful.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "bubai" <bubai@.discussions.microsoft.com> wrote in message
> news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
> >I have a sql script that I use to reorganize indexes with more than 5%
> > fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> > fragmentation. On running this, I found that, the script was working fine
> > but
> > every iteration updated about 550 indexes. After a run, if I queried the
> > dynamic management view, it still gave back indexes which were fragmented.
> >
> > My understanding is that after I do a reorg/rebuild, the entry should
> > disappear from sys.dm_db_index_physical_stats, if the alter table
> > succeeds.
> >
> > Question -- what can I do as a DBA, after the script runs to make sure
> > that
> > the management view gives back updated information and not state
> > information?
> >
> > My script --
> >
> > BEGIN
> >
> > SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
> >
> > INTO #INDEX_STATS_TEMP
> >
> > FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
> > NULL, NULL)
> >
> > WHERE avg_fragmentation_in_percent >= 5
> >
> > AND index_type_desc != 'HEAP'
> >
> >
> >
> >
> >
> > -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> >
> > DECLARE @.dbID int,
> >
> > @.tableID int,
> >
> > @.indexID int,
> >
> > @.frag_percent float,
> >
> > @.index_name varchar(100),
> >
> > @.table_name varchar(100),
> >
> > @.sql varchar(1000)
> >
> >
> >
> > --DEFINE THE CURSOR
> >
> > DECLARE FRAG_CURSOR CURSOR
> >
> > FOR SELECT
> > TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> >
> > FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> >
> > WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
> >
> >
> >
> > OPEN FRAG_CURSOR
> >
> > FETCH NEXT FROM FRAG_CURSOR INTO
> > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> >
> >
> >
> > WHILE (@.@.FETCH_STATUS = 0)
> >
> > BEGIN
> >
> > print @.tableID
> >
> > SELECT @.table_name = name from DB_NAME.sys.objects where
> > object_id = @.tableID and type = 'U'
> >
> > IF (@.frag_percent <=30)
> >
> > BEGIN
> >
> > USE DB_NAME
> >
> > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> > REORGANIZE'
> >
> > print @.SQL
> >
> > exec (@.SQL)
> >
> > END
> >
> > ELSE IF (@.frag_percent > 30)
> >
> > BEGIN
> >
> > USE DB_NAME
> >
> > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> > REBUILD WITH (ONLINE = OFF)'
> >
> > print @.SQL
> >
> > exec (@.SQL)
> >
> > END
> >
> > SET @.SQL = NULL
> >
> > FETCH NEXT FROM FRAG_CURSOR INTO
> > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> >
> > END
> >
> > CLOSE FRAG_CURSOR
> >
> > DEALLOCATE FRAG_CURSOR
> >
> > DROP TABLE #INDEX_STATS_TEMP
> >
> > END
> >
> > Regards
> >
> > Jaideep
> >
> >
> >
> >
>|||Thanks. I will update the statistics. What does the index size have to
defragmentation? Even if the size is less, but is spanning multiple pages,
the fragmentation can happen. I did not understand why did the management
view not get updated after the alter table? Is it solely because update
statistics was not run? Does anyone know how does the management view get
it's data?
Regards
Jaideep
"Hurme" wrote:
> Try doing update statistics. Statistics will automatically get updated when
> an index is rebuilt, but I don't think they do when an index is reorganized
> or defragged.
> --
> MG
>
> "Geoff N. Hiten" wrote:
> > Check the size of the index. I exclude indexes below a particular cutoff
> > (don't exactly recall what it is) because fragmentation information was not
> > meaningful.
> >
> > --
> > Geoff N. Hiten
> > Senior SQL Infrastructure Consultant
> > Microsoft SQL Server MVP
> >
> >
> > "bubai" <bubai@.discussions.microsoft.com> wrote in message
> > news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
> > >I have a sql script that I use to reorganize indexes with more than 5%
> > > fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> > > fragmentation. On running this, I found that, the script was working fine
> > > but
> > > every iteration updated about 550 indexes. After a run, if I queried the
> > > dynamic management view, it still gave back indexes which were fragmented.
> > >
> > > My understanding is that after I do a reorg/rebuild, the entry should
> > > disappear from sys.dm_db_index_physical_stats, if the alter table
> > > succeeds.
> > >
> > > Question -- what can I do as a DBA, after the script runs to make sure
> > > that
> > > the management view gives back updated information and not state
> > > information?
> > >
> > > My script --
> > >
> > > BEGIN
> > >
> > > SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
> > >
> > > INTO #INDEX_STATS_TEMP
> > >
> > > FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
> > > NULL, NULL)
> > >
> > > WHERE avg_fragmentation_in_percent >= 5
> > >
> > > AND index_type_desc != 'HEAP'
> > >
> > >
> > >
> > >
> > >
> > > -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> > >
> > > DECLARE @.dbID int,
> > >
> > > @.tableID int,
> > >
> > > @.indexID int,
> > >
> > > @.frag_percent float,
> > >
> > > @.index_name varchar(100),
> > >
> > > @.table_name varchar(100),
> > >
> > > @.sql varchar(1000)
> > >
> > >
> > >
> > > --DEFINE THE CURSOR
> > >
> > > DECLARE FRAG_CURSOR CURSOR
> > >
> > > FOR SELECT
> > > TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> > >
> > > FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> > >
> > > WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
> > >
> > >
> > >
> > > OPEN FRAG_CURSOR
> > >
> > > FETCH NEXT FROM FRAG_CURSOR INTO
> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> > >
> > >
> > >
> > > WHILE (@.@.FETCH_STATUS = 0)
> > >
> > > BEGIN
> > >
> > > print @.tableID
> > >
> > > SELECT @.table_name = name from DB_NAME.sys.objects where
> > > object_id = @.tableID and type = 'U'
> > >
> > > IF (@.frag_percent <=30)
> > >
> > > BEGIN
> > >
> > > USE DB_NAME
> > >
> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> > > REORGANIZE'
> > >
> > > print @.SQL
> > >
> > > exec (@.SQL)
> > >
> > > END
> > >
> > > ELSE IF (@.frag_percent > 30)
> > >
> > > BEGIN
> > >
> > > USE DB_NAME
> > >
> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> > > REBUILD WITH (ONLINE = OFF)'
> > >
> > > print @.SQL
> > >
> > > exec (@.SQL)
> > >
> > > END
> > >
> > > SET @.SQL = NULL
> > >
> > > FETCH NEXT FROM FRAG_CURSOR INTO
> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> > >
> > > END
> > >
> > > CLOSE FRAG_CURSOR
> > >
> > > DEALLOCATE FRAG_CURSOR
> > >
> > > DROP TABLE #INDEX_STATS_TEMP
> > >
> > > END
> > >
> > > Regards
> > >
> > > Jaideep
> > >
> > >
> > >
> > >
> >
> >|||In a trivially sized index (< 8 pages) the fragmentation information is
irrelevant. They will never be perfectly defragmented.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:AEBC9084-F137-42C4-B6BA-816AB32A4B55@.microsoft.com...
> Thanks. I will update the statistics. What does the index size have to
> defragmentation? Even if the size is less, but is spanning multiple pages,
> the fragmentation can happen. I did not understand why did the management
> view not get updated after the alter table? Is it solely because update
> statistics was not run? Does anyone know how does the management view get
> it's data?
> Regards
> Jaideep
> "Hurme" wrote:
>> Try doing update statistics. Statistics will automatically get updated
>> when
>> an index is rebuilt, but I don't think they do when an index is
>> reorganized
>> or defragged.
>> --
>> MG
>>
>> "Geoff N. Hiten" wrote:
>> > Check the size of the index. I exclude indexes below a particular
>> > cutoff
>> > (don't exactly recall what it is) because fragmentation information was
>> > not
>> > meaningful.
>> >
>> > --
>> > Geoff N. Hiten
>> > Senior SQL Infrastructure Consultant
>> > Microsoft SQL Server MVP
>> >
>> >
>> > "bubai" <bubai@.discussions.microsoft.com> wrote in message
>> > news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>> > >I have a sql script that I use to reorganize indexes with more than 5%
>> > > fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
>> > > fragmentation. On running this, I found that, the script was working
>> > > fine
>> > > but
>> > > every iteration updated about 550 indexes. After a run, if I queried
>> > > the
>> > > dynamic management view, it still gave back indexes which were
>> > > fragmented.
>> > >
>> > > My understanding is that after I do a reorg/rebuild, the entry should
>> > > disappear from sys.dm_db_index_physical_stats, if the alter table
>> > > succeeds.
>> > >
>> > > Question -- what can I do as a DBA, after the script runs to make
>> > > sure
>> > > that
>> > > the management view gives back updated information and not state
>> > > information?
>> > >
>> > > My script --
>> > >
>> > > BEGIN
>> > >
>> > > SELECT
>> > > database_id,object_id,index_id,avg_fragmentation_in_percent
>> > >
>> > > INTO #INDEX_STATS_TEMP
>> > >
>> > > FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL,
>> > > NULL,
>> > > NULL, NULL)
>> > >
>> > > WHERE avg_fragmentation_in_percent >= 5
>> > >
>> > > AND index_type_desc != 'HEAP'
>> > >
>> > >
>> > >
>> > >
>> > >
>> > > -- DECLARE LOCAL VARIABLES FOR THE CURSOR
>> > >
>> > > DECLARE @.dbID int,
>> > >
>> > > @.tableID int,
>> > >
>> > > @.indexID int,
>> > >
>> > > @.frag_percent float,
>> > >
>> > > @.index_name varchar(100),
>> > >
>> > > @.table_name varchar(100),
>> > >
>> > > @.sql varchar(1000)
>> > >
>> > >
>> > >
>> > > --DEFINE THE CURSOR
>> > >
>> > > DECLARE FRAG_CURSOR CURSOR
>> > >
>> > > FOR SELECT
>> > > TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
>> > >
>> > > FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
>> > >
>> > > WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id =>> > > S.INDEX_ID
>> > >
>> > >
>> > >
>> > > OPEN FRAG_CURSOR
>> > >
>> > > FETCH NEXT FROM FRAG_CURSOR INTO
>> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>> > >
>> > >
>> > >
>> > > WHILE (@.@.FETCH_STATUS = 0)
>> > >
>> > > BEGIN
>> > >
>> > > print @.tableID
>> > >
>> > > SELECT @.table_name = name from DB_NAME.sys.objects where
>> > > object_id = @.tableID and type = 'U'
>> > >
>> > > IF (@.frag_percent <=30)
>> > >
>> > > BEGIN
>> > >
>> > > USE DB_NAME
>> > >
>> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON
>> > > '+@.table_name+'
>> > > REORGANIZE'
>> > >
>> > > print @.SQL
>> > >
>> > > exec (@.SQL)
>> > >
>> > > END
>> > >
>> > > ELSE IF (@.frag_percent > 30)
>> > >
>> > > BEGIN
>> > >
>> > > USE DB_NAME
>> > >
>> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON
>> > > '+@.table_name+'
>> > > REBUILD WITH (ONLINE = OFF)'
>> > >
>> > > print @.SQL
>> > >
>> > > exec (@.SQL)
>> > >
>> > > END
>> > >
>> > > SET @.SQL = NULL
>> > >
>> > > FETCH NEXT FROM FRAG_CURSOR INTO
>> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>> > >
>> > > END
>> > >
>> > > CLOSE FRAG_CURSOR
>> > >
>> > > DEALLOCATE FRAG_CURSOR
>> > >
>> > > DROP TABLE #INDEX_STATS_TEMP
>> > >
>> > > END
>> > >
>> > > Regards
>> > >
>> > > Jaideep
>> > >
>> > >
>> > >
>> > >
>> >
>> >|||Are you perhaps using autogrowth to manage the size of your database? How
much free space do you have in the db? Without a LOT of free space,
reorg/rebuild cannot actually accomplish their tasks effectively because
there is no contiguous block of empty space to lay down the pages in order.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"bubai" <bubai@.discussions.microsoft.com> wrote in message
news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine
> but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table
> succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure
> that
> the management view gives back updated information and not state
> information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>|||... and for an elaboration of this, read about extents, shared extents etc in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OHaB1DSbIHA.5208@.TK2MSFTNGP04.phx.gbl...
> In a trivially sized index (< 8 pages) the fragmentation information is irrelevant. They will
> never be perfectly defragmented.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "bubai" <bubai@.discussions.microsoft.com> wrote in message
> news:AEBC9084-F137-42C4-B6BA-816AB32A4B55@.microsoft.com...
>> Thanks. I will update the statistics. What does the index size have to
>> defragmentation? Even if the size is less, but is spanning multiple pages,
>> the fragmentation can happen. I did not understand why did the management
>> view not get updated after the alter table? Is it solely because update
>> statistics was not run? Does anyone know how does the management view get
>> it's data?
>> Regards
>> Jaideep
>> "Hurme" wrote:
>> Try doing update statistics. Statistics will automatically get updated when
>> an index is rebuilt, but I don't think they do when an index is reorganized
>> or defragged.
>> --
>> MG
>>
>> "Geoff N. Hiten" wrote:
>> > Check the size of the index. I exclude indexes below a particular cutoff
>> > (don't exactly recall what it is) because fragmentation information was not
>> > meaningful.
>> >
>> > --
>> > Geoff N. Hiten
>> > Senior SQL Infrastructure Consultant
>> > Microsoft SQL Server MVP
>> >
>> >
>> > "bubai" <bubai@.discussions.microsoft.com> wrote in message
>> > news:D5E61278-B73D-4B56-B459-1D7961C1A976@.microsoft.com...
>> > >I have a sql script that I use to reorganize indexes with more than 5%
>> > > fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
>> > > fragmentation. On running this, I found that, the script was working fine
>> > > but
>> > > every iteration updated about 550 indexes. After a run, if I queried the
>> > > dynamic management view, it still gave back indexes which were fragmented.
>> > >
>> > > My understanding is that after I do a reorg/rebuild, the entry should
>> > > disappear from sys.dm_db_index_physical_stats, if the alter table
>> > > succeeds.
>> > >
>> > > Question -- what can I do as a DBA, after the script runs to make sure
>> > > that
>> > > the management view gives back updated information and not state
>> > > information?
>> > >
>> > > My script --
>> > >
>> > > BEGIN
>> > >
>> > > SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
>> > >
>> > > INTO #INDEX_STATS_TEMP
>> > >
>> > > FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
>> > > NULL, NULL)
>> > >
>> > > WHERE avg_fragmentation_in_percent >= 5
>> > >
>> > > AND index_type_desc != 'HEAP'
>> > >
>> > >
>> > >
>> > >
>> > >
>> > > -- DECLARE LOCAL VARIABLES FOR THE CURSOR
>> > >
>> > > DECLARE @.dbID int,
>> > >
>> > > @.tableID int,
>> > >
>> > > @.indexID int,
>> > >
>> > > @.frag_percent float,
>> > >
>> > > @.index_name varchar(100),
>> > >
>> > > @.table_name varchar(100),
>> > >
>> > > @.sql varchar(1000)
>> > >
>> > >
>> > >
>> > > --DEFINE THE CURSOR
>> > >
>> > > DECLARE FRAG_CURSOR CURSOR
>> > >
>> > > FOR SELECT
>> > > TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
>> > >
>> > > FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
>> > >
>> > > WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>> > >
>> > >
>> > >
>> > > OPEN FRAG_CURSOR
>> > >
>> > > FETCH NEXT FROM FRAG_CURSOR INTO
>> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>> > >
>> > >
>> > >
>> > > WHILE (@.@.FETCH_STATUS = 0)
>> > >
>> > > BEGIN
>> > >
>> > > print @.tableID
>> > >
>> > > SELECT @.table_name = name from DB_NAME.sys.objects where
>> > > object_id = @.tableID and type = 'U'
>> > >
>> > > IF (@.frag_percent <=30)
>> > >
>> > > BEGIN
>> > >
>> > > USE DB_NAME
>> > >
>> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
>> > > REORGANIZE'
>> > >
>> > > print @.SQL
>> > >
>> > > exec (@.SQL)
>> > >
>> > > END
>> > >
>> > > ELSE IF (@.frag_percent > 30)
>> > >
>> > > BEGIN
>> > >
>> > > USE DB_NAME
>> > >
>> > > SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
>> > > REBUILD WITH (ONLINE = OFF)'
>> > >
>> > > print @.SQL
>> > >
>> > > exec (@.SQL)
>> > >
>> > > END
>> > >
>> > > SET @.SQL = NULL
>> > >
>> > > FETCH NEXT FROM FRAG_CURSOR INTO
>> > > @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>> > >
>> > > END
>> > >
>> > > CLOSE FRAG_CURSOR
>> > >
>> > > DEALLOCATE FRAG_CURSOR
>> > >
>> > > DROP TABLE #INDEX_STATS_TEMP
>> > >
>> > > END
>> > >
>> > > Regards
>> > >
>> > > Jaideep
>> > >
>> > >
>> > >
>> > >
>> >
>> >
>|||"bubai" wrote:
> I have a sql script that I use to reorganize indexes with more than 5%
> fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
> fragmentation. On running this, I found that, the script was working fine but
> every iteration updated about 550 indexes. After a run, if I queried the
> dynamic management view, it still gave back indexes which were fragmented.
> My understanding is that after I do a reorg/rebuild, the entry should
> disappear from sys.dm_db_index_physical_stats, if the alter table succeeds.
> Question -- what can I do as a DBA, after the script runs to make sure that
> the management view gives back updated information and not state information?
> My script --
> BEGIN
> SELECT database_id,object_id,index_id,avg_fragmentation_in_percent
> INTO #INDEX_STATS_TEMP
> FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
> NULL, NULL)
> WHERE avg_fragmentation_in_percent >= 5
> AND index_type_desc != 'HEAP'
>
>
> -- DECLARE LOCAL VARIABLES FOR THE CURSOR
> DECLARE @.dbID int,
> @.tableID int,
> @.indexID int,
> @.frag_percent float,
> @.index_name varchar(100),
> @.table_name varchar(100),
> @.sql varchar(1000)
>
> --DEFINE THE CURSOR
> DECLARE FRAG_CURSOR CURSOR
> FOR SELECT
> TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME
> FROM #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S
> WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID
>
> OPEN FRAG_CURSOR
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> print @.tableID
> SELECT @.table_name = name from DB_NAME.sys.objects where
> object_id = @.tableID and type = 'U'
> IF (@.frag_percent <=30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REORGANIZE'
> print @.SQL
> exec (@.SQL)
> END
> ELSE IF (@.frag_percent > 30)
> BEGIN
> USE DB_NAME
> SET @.SQL= 'ALTER INDEX '+@.index_name+' ON '+@.table_name+'
> REBUILD WITH (ONLINE = OFF)'
> print @.SQL
> exec (@.SQL)
> END
> SET @.SQL = NULL
> FETCH NEXT FROM FRAG_CURSOR INTO
> @.dbID,@.tableID,@.indexID,@.frag_percent,@.index_name
> END
> CLOSE FRAG_CURSOR
> DEALLOCATE FRAG_CURSOR
> DROP TABLE #INDEX_STATS_TEMP
> END
> Regards
> Jaideep
>
>|||I am having the same problem, but I have a more specific example of the issue.
Here is some cut down output from dm_db_index_physical_stats
index_id alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent page_count
1 IN_ROW_DATA 3 0 50.16835017 297
1 IN_ROW_DATA 3 1 0 2
1 IN_ROW_DATA 3 2 0 1
2 IN_ROW_DATA 3 0 99.46236559 372
2 IN_ROW_DATA 3 1 0 3
2 IN_ROW_DATA 3 2 0 1
In case this doesn't display too well the key point is that at index level
zero there are 297 pages allocated to the clustered index and 372 pages to
the non clustered index.
Except the problem is that this table has no rows in it (although it used to
have)
I have tried rebuilding the indexes using ALTER INDEX, and a DBCC REINDEX
all to no avail,
However DBCC SHOWCONTIG returns the correct results.
Sorry I can't offer a solution but I hope this clarifies the issue I think
we both have.
Let me know if you find one.|||Sorry, just noticed a typo in the way I was doing this which explains the
results. Please ignore my previous post. Apologies again.
"Tim Walker" wrote:
> I am having the same problem, but I have a more specific example of the issue.
> Here is some cut down output from dm_db_index_physical_stats
> index_id alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent page_count
> 1 IN_ROW_DATA 3 0 50.16835017 297
> 1 IN_ROW_DATA 3 1 0 2
> 1 IN_ROW_DATA 3 2 0 1
> 2 IN_ROW_DATA 3 0 99.46236559 372
> 2 IN_ROW_DATA 3 1 0 3
> 2 IN_ROW_DATA 3 2 0 1
> In case this doesn't display too well the key point is that at index level
> zero there are 297 pages allocated to the clustered index and 372 pages to
> the non clustered index.
> Except the problem is that this table has no rows in it (although it used to
> have)
> I have tried rebuilding the indexes using ALTER INDEX, and a DBCC REINDEX
> all to no avail,
> However DBCC SHOWCONTIG returns the correct results.
> Sorry I can't offer a solution but I hope this clarifies the issue I think
> we both have.
> Let me know if you find one.

Sunday, March 11, 2012

dm_db_index_physical_stats not getting updated

Saturday, February 25, 2012

Distribution database indexes

All,
I can seem to find much info on this. I'm trying to determine when and if I
need to rebuild the indexes on the Distribution database. We're running
transactional replicaiton 24/7 so I'm concerned it will interupt service.
Thanks in advance.
For the most part the transactions/queries which hit the distribution
database are seeks you will probably find that the impact of fragmentation
is minimal.
While you can defrag your indexes online in SQL 2000 you can't rebuild them
online in SQL 2000 (you can in SQL 2005). I suspect your problems are not
related to indexes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:AD2E42BC-DD48-4964-9EE8-5134FED52DD4@.microsoft.com...
> All,
> I can seem to find much info on this. I'm trying to determine when and if
> I
> need to rebuild the indexes on the Distribution database. We're running
> transactional replicaiton 24/7 so I'm concerned it will interupt service.
> Thanks in advance.
|||We're not having problems, I was looking for general info about distribution
db indexes.
Thanks for the info!
"Hilary Cotter" wrote:

> For the most part the transactions/queries which hit the distribution
> database are seeks you will probably find that the impact of fragmentation
> is minimal.
> While you can defrag your indexes online in SQL 2000 you can't rebuild them
> online in SQL 2000 (you can in SQL 2005). I suspect your problems are not
> related to indexes.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:AD2E42BC-DD48-4964-9EE8-5134FED52DD4@.microsoft.com...
>
>

Friday, February 24, 2012

Distribution Agent - Replication Monitor warning

Hi,

I am replicating a large table with a number of indexes. During the initialisation phase (creating the indexes at the subscriber), I am seing the following error/warning in Replication monitor. When the initialisation is complete the error warnings dissapear.

The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

Is there a way to increase the 10 minute time-limit?

Thanks,
Priyanga

Hi Priyanga,

You can use the -KeepAliveMessage parameter of the distribution agent to specify a larger time interval for logging the "agent suspect" messages. However, I am a bit hesitant to recommend that you simply increase the time interval in general since you would probably want to be notified that the distribution agent may be stalled in a more timely manner when it is delivering incremental changes. On the other hand, delivery of snapshot is arguably a rare enough occurrence that the "agent suspect" messages should not become a significant source of annoyance.

In truth, the following factors had conspired to make the "agent suspect" messages almost unavoidable when delivering a large snapshot:

1) BCP API does not allow the distribution agent to log a progress message until every "BcpBatchSize" number of rows have been bulk-loaded into a subscriber table
2) We use a large "BcpBatchSize" (2^31 - delta) as the default "BcpBatchSize" in SQL2005 so the distribution agent can more readily meet the minimally-logged bulk-load requirements.
3) The time required to create an index on a large table probably exceeds the default -KeepAliveMessageInterval

Based on 1) and 2) above, distribution agent will mostly not be able to log a progress message in-between a bulk-load operation;and if the amount of data that needs to be bulk-loaded is large, the distribution agent will likely not be able to finish the bulk-load operation within the default -KeepAliveMessageInterval.

HTH

-Raymond

Thursday, March 29, 2012

Do other RDMS have clustered indexes?

Do other RDMS have clustered indexes?

Do other RDMS have clustered indexes?

Thursday, March 22, 2012

Do foreign keys generate implicit indexes?

Monday, March 19, 2012

dm_db_index_physical_stats not getting updated

Sunday, March 11, 2012

dm_db_index_physical_stats not getting updated

Saturday, February 25, 2012

Distribution database indexes

Friday, February 24, 2012

Distribution Agent - Replication Monitor warning

Do While in SQL

Blog Archive

About Me