Alter to specify a fillfactor?

For SQL2000 - I would like to be able to alter indexes on a table to specify
a fillfactor where previously a fillfactor was not defined.
I first tried this on one table in Enterprise Manager where I specified a
fillfactor of 80 for the clustered index. I ran profiler to see how it was
done. It looks like it dropped the Clustered Index and then rebuilt it.
I was hoping that there was an ALTER statement that I could run that would
effectively update the fillfactor definition so that the next time I ran
DBREINDEX it would take effect. Is this possible.
Thanks in advance!
Fillfactor is not maintained during regular DML operation; it only matters
when an index is built. Therefore, there is little or no need to introduce
ALTER INDEX statement to specify a fillfactor that will not be used. When
you are ready to build/re-build index, you can specify fillfactor in DBCC
DBREINDEX/CREATE INDEX statement; after the index is built, the fillfactor
number is stored in system table for future index build to use.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJT" <TJT@.nospam.com> wrote in message
news:%23k0I981cFHA.3040@.TK2MSFTNGP14.phx.gbl...
> For SQL2000 - I would like to be able to alter indexes on a table to
> specify
> a fillfactor where previously a fillfactor was not defined.
> I first tried this on one table in Enterprise Manager where I specified a
> fillfactor of 80 for the clustered index. I ran profiler to see how it
> was
> done. It looks like it dropped the Clustered Index and then rebuilt it.
> I was hoping that there was an ALTER statement that I could run that would
> effectively update the fillfactor definition so that the next time I ran
> DBREINDEX it would take effect. Is this possible.
> Thanks in advance!
>

Alter to specify a fillfactor?

For SQL2000 - I would like to be able to alter indexes on a table to specify
a fillfactor where previously a fillfactor was not defined.
I first tried this on one table in Enterprise Manager where I specified a
fillfactor of 80 for the clustered index. I ran profiler to see how it was
done. It looks like it dropped the Clustered Index and then rebuilt it.
I was hoping that there was an ALTER statement that I could run that would
effectively update the fillfactor definition so that the next time I ran
DBREINDEX it would take effect. Is this possible.
Thanks in advance!Fillfactor is not maintained during regular DML operation; it only matters
when an index is built. Therefore, there is little or no need to introduce
ALTER INDEX statement to specify a fillfactor that will not be used. When
you are ready to build/re-build index, you can specify fillfactor in DBCC
DBREINDEX/CREATE INDEX statement; after the index is built, the fillfactor
number is stored in system table for future index build to use.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJT" <TJT@.nospam.com> wrote in message
news:%23k0I981cFHA.3040@.TK2MSFTNGP14.phx.gbl...
> For SQL2000 - I would like to be able to alter indexes on a table to
> specify
> a fillfactor where previously a fillfactor was not defined.
> I first tried this on one table in Enterprise Manager where I specified a
> fillfactor of 80 for the clustered index. I ran profiler to see how it
> was
> done. It looks like it dropped the Clustered Index and then rebuilt it.
> I was hoping that there was an ALTER statement that I could run that would
> effectively update the fillfactor definition so that the next time I ran
> DBREINDEX it would take effect. Is this possible.
> Thanks in advance!
>

Alter to specify a fillfactor?

For SQL2000 - I would like to be able to alter indexes on a table to specify
a fillfactor where previously a fillfactor was not defined.
I first tried this on one table in Enterprise Manager where I specified a
fillfactor of 80 for the clustered index. I ran profiler to see how it was
done. It looks like it dropped the Clustered Index and then rebuilt it.
I was hoping that there was an ALTER statement that I could run that would
effectively update the fillfactor definition so that the next time I ran
DBREINDEX it would take effect. Is this possible.
Thanks in advance!Fillfactor is not maintained during regular DML operation; it only matters
when an index is built. Therefore, there is little or no need to introduce
ALTER INDEX statement to specify a fillfactor that will not be used. When
you are ready to build/re-build index, you can specify fillfactor in DBCC
DBREINDEX/CREATE INDEX statement; after the index is built, the fillfactor
number is stored in system table for future index build to use.
--
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJT" <TJT@.nospam.com> wrote in message
news:%23k0I981cFHA.3040@.TK2MSFTNGP14.phx.gbl...
> For SQL2000 - I would like to be able to alter indexes on a table to
> specify
> a fillfactor where previously a fillfactor was not defined.
> I first tried this on one table in Enterprise Manager where I specified a
> fillfactor of 80 for the clustered index. I ran profiler to see how it
> was
> done. It looks like it dropped the Clustered Index and then rebuilt it.
> I was hoping that there was an ALTER statement that I could run that would
> effectively update the fillfactor definition so that the next time I ran
> DBREINDEX it would take effect. Is this possible.
> Thanks in advance!
>

alter table lock?

Hi!

I use proc handling special business logic (I also use constraints, indexes for that ;-)

Now I have a situation where I should check multiple rows with an proc.

Preventing multi-user issues I want to lock the table (yes, yes potential performance issue, but in this case there are few simultaneous jobs) - in Oracle I could lock the table, but what to do in SQL Server?

Maybe you have an better alternative, then let me hear ;-)

Or should I use "begin transaction"...

Thanks for help

Hmm, maybe

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

-- DO YOUR stuff

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

will be the solution...

Some comments?

sql

Alter Index with REBUILD on master database?

SQL Server 2005:
We plan to use the alter index with rebuild syntax to rebuild our indexes
weekly in a job. Should master and msdb tables be included? I have no
interest in doing it manually a couple times per year if it needs it.
Thanks,
MarkI remember asking the same thing in a SQL 2000 forum a long time ago and the
consensus was you never need to include any of the system databases in
reindexing / update stats. I would gather the same is applicable to SQL
2005.
HTH,
Rubens
"Mark" <mark@.idonotlikespam.com> wrote in message
news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2005:
> We plan to use the alter index with rebuild syntax to rebuild our indexes
> weekly in a job. Should master and msdb tables be included? I have no
> interest in doing it manually a couple times per year if it needs it.
> Thanks,
> Mark
>|||Why not?
"Rubens" <rubensrose@.hotmail.com> wrote in message
news:uUmDCKBpIHA.4912@.TK2MSFTNGP03.phx.gbl...
>I remember asking the same thing in a SQL 2000 forum a long time ago and
>the consensus was you never need to include any of the system databases in
>reindexing / update stats. I would gather the same is applicable to SQL
>2005.
> HTH,
> Rubens
> "Mark" <mark@.idonotlikespam.com> wrote in message
> news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2005:
>> We plan to use the alter index with rebuild syntax to rebuild our indexes
>> weekly in a job. Should master and msdb tables be included? I have no
>> interest in doing it manually a couple times per year if it needs it.
>> Thanks,
>> Mark|||Because in SQL2005 there shouldn't be any tables (of consequence) in the
master database that you can actually run UPDATE STATISTICS or rebuild
indexes.
Linchi
"Mark" wrote:
> Why not?
> "Rubens" <rubensrose@.hotmail.com> wrote in message
> news:uUmDCKBpIHA.4912@.TK2MSFTNGP03.phx.gbl...
> >I remember asking the same thing in a SQL 2000 forum a long time ago and
> >the consensus was you never need to include any of the system databases in
> >reindexing / update stats. I would gather the same is applicable to SQL
> >2005.
> >
> > HTH,
> > Rubens
> >
> > "Mark" <mark@.idonotlikespam.com> wrote in message
> > news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
> >> SQL Server 2005:
> >>
> >> We plan to use the alter index with rebuild syntax to rebuild our indexes
> >> weekly in a job. Should master and msdb tables be included? I have no
> >> interest in doing it manually a couple times per year if it needs it.
> >>
> >> Thanks,
> >> Mark
> >>
>
>

Alter Index issue & Try/Catch question

All,
I'm currently modifying a BOL procedure to rebuild/reorganize my
indexes. I've changed the ALTER INDEX command so that it performs the
rebuild online. The first time I ran the script, it errored with the
following error below:
Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index
'Company$Attachment$0' because the index contains column 'Entry Pointer
ID' of data type text, ntext, image, varchar(max), nvarchar(max),
varbinary(max) or xml. For non-clustered index the column could be an
include column of the index, for clustered index it could be any column
of the table. In case of drop_existing the column could be part of new
or old index. The operation must be performed offline.
At this point, I have tried to integrate the TRY/CATCH routine so that
when this error appears, the script executes the ALTER INDEXES offline
instead (script below). I was wondering if there is a way to possible
write out the error to a log file perhaps?
Thanks,
Ian
SET NOCOUNT ON;
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.partitioncount bigint;
DECLARE @.schemaname sysname;
DECLARE @.objectname sysname;
DECLARE @.indexname sysname;
DECLARE @.partitionnum bigint;
DECLARE @.partitions bigint;
DECLARE @.frag float;
DECLARE @.command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index
IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number as partitionnum,
avg_fragmentation_in_percent as frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @.objectid, @.indexid, @.partitionnum, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
SELECT @.objectname = o.name, @.schemaname = s.name
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @.objectid;
SELECT @.indexname = name
FROM sys.indexes
WHERE object_id = @.objectid AND index_id = @.indexid;
SELECT @.partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @.objectid AND index_id = @.indexid;
-- 30 is an arbitrary decision point at which to switch between
reorganizing and rebuilding
IF @.frag < 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '[' +
@.objectname + '] REORGANIZE';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
PRINT (@.command);
EXEC (@.command);
END;
IF @.frag >= 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
BEGIN TRY
EXEC (@.command);
END TRY
BEGIN CATCH
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
EXEC (@.command);
END CATCH
PRINT (@.command);
END;
PRINT 'Executed ' + @.command;
PRINT
'----';
FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
@.frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;To write a event log entry use: RAISERROR
RAISERROR ('Something happened and needs to be logged', 10, 1 ) WITH LOG
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<theredmiata@.hotmail.com> wrote in message
news:1165272932.638685.169330@.n67g2000cwd.googlegroups.com...
> All,
> I'm currently modifying a BOL procedure to rebuild/reorganize my
> indexes. I've changed the ALTER INDEX command so that it performs the
> rebuild online. The first time I ran the script, it errored with the
> following error below:
> Msg 2725, Level 16, State 2, Line 1
> Online index operation cannot be performed for index
> 'Company$Attachment$0' because the index contains column 'Entry Pointer
> ID' of data type text, ntext, image, varchar(max), nvarchar(max),
> varbinary(max) or xml. For non-clustered index the column could be an
> include column of the index, for clustered index it could be any column
> of the table. In case of drop_existing the column could be part of new
> or old index. The operation must be performed offline.
> At this point, I have tried to integrate the TRY/CATCH routine so that
> when this error appears, the script executes the ALTER INDEXES offline
> instead (script below). I was wondering if there is a way to possible
> write out the error to a log file perhaps?
> Thanks,
> Ian
>
> SET NOCOUNT ON;
> DECLARE @.objectid int;
> DECLARE @.indexid int;
> DECLARE @.partitioncount bigint;
> DECLARE @.schemaname sysname;
> DECLARE @.objectname sysname;
> DECLARE @.indexname sysname;
> DECLARE @.partitionnum bigint;
> DECLARE @.partitions bigint;
> DECLARE @.frag float;
> DECLARE @.command varchar(8000);
> -- ensure the temporary table does not exist
> IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
> DROP TABLE work_to_do;
> -- conditionally select from the function, converting object and index
> IDs to names.
> SELECT
> object_id AS objectid,
> index_id AS indexid,
> partition_number as partitionnum,
> avg_fragmentation_in_percent as frag
> INTO work_to_do
> FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
> WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
> -- Declare the cursor for the list of partitions to be processed.
> DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
> -- Open the cursor.
> OPEN partitions;
> -- Loop through the partitions.
> FETCH NEXT
> FROM partitions
> INTO @.objectid, @.indexid, @.partitionnum, @.frag;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> SELECT @.objectname = o.name, @.schemaname = s.name
> FROM sys.objects AS o
> JOIN sys.schemas as s ON s.schema_id = o.schema_id
> WHERE o.object_id = @.objectid;
> SELECT @.indexname = name
> FROM sys.indexes
> WHERE object_id = @.objectid AND index_id = @.indexid;
> SELECT @.partitioncount = count (*)
> FROM sys.partitions
> WHERE object_id = @.objectid AND index_id = @.indexid;
> -- 30 is an arbitrary decision point at which to switch between
> reorganizing and rebuilding
> IF @.frag < 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '[' +
> @.objectname + '] REORGANIZE';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> PRINT (@.command);
> EXEC (@.command);
> END;
> IF @.frag >= 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> BEGIN TRY
> EXEC (@.command);
> END TRY
> BEGIN CATCH
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> EXEC (@.command);
> END CATCH
> PRINT (@.command);
> END;
> PRINT 'Executed ' + @.command;
> PRINT
> '----';
> FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
> @.frag;
> END;
> -- Close and deallocate the cursor.
> CLOSE partitions;
> DEALLOCATE partitions;
>

all indexes lost

I have recently had a a database where all indexes (over 300) have been lost
for no apparent reason. - in addition to this some primary keys were also
lost.
a review of the t-log archives shows no sign of DDL statements being
executed and the only conclusion i can come to is that rows from sysindexes
have been lost.
the indexes were lost during normal working hours, no reindexes or defrags
were taking place and the objects were lost in a matter of seconds.
we also suspected a user may have downloaded some sort of optimisation tool
and tried to "optimise" the database without knowledge, however no-one with
those permissions was logged in at the time.
has anyone else experienced this or can they point me at a KB articleshadowswiss wrote:
> I have recently had a a database where all indexes (over 300) have
> been lost for no apparent reason. - in addition to this some primary
> keys were also lost.
> a review of the t-log archives shows no sign of DDL statements being
> executed and the only conclusion i can come to is that rows from
> sysindexes have been lost.
> the indexes were lost during normal working hours, no reindexes or
> defrags were taking place and the objects were lost in a matter of
> seconds.
> we also suspected a user may have downloaded some sort of
> optimisation tool and tried to "optimise" the database without
> knowledge, however no-one with those permissions was logged in at the
> time.
> has anyone else experienced this or can they point me at a KB article
If someone ran the index tuning wizard, they could have instructed it to
kill old indexes infavor of the recommended list of new indexes. I doubt
this option would have killed any constraints, unless they were just
being enforces using indexes as opposed to the declarative approach.
I've personally never seen this reported before as a bug in SQL Server.
This type of problem is normally someone executing some T-SQL to to
this. I would immediately secure the SQL Server by removing all users
from the admin group that really don't require admin rights and changing
existing passwords for those admins that require those rights.
Although, I would expect to see these drop statements in the t-log. I
assume you are using a 3rd-party log reader to review the t-logs.
Have you checked database integrity using DBCC CHECKDB to see if any
errors are reported?
David Gugick
Quest Software
www.imceda.com
www.quest.com

all indexes lost

I have recently had a a database where all indexes (over 300) have been lost
for no apparent reason. - in addition to this some primary keys were also
lost.
a review of the t-log archives shows no sign of DDL statements being
executed and the only conclusion i can come to is that rows from sysindexes
have been lost.
the indexes were lost during normal working hours, no reindexes or defrags
were taking place and the objects were lost in a matter of seconds.
we also suspected a user may have downloaded some sort of optimisation tool
and tried to "optimise" the database without knowledge, however no-one with
those permissions was logged in at the time.
has anyone else experienced this or can they point me at a KB article
shadowswiss wrote:
> I have recently had a a database where all indexes (over 300) have
> been lost for no apparent reason. - in addition to this some primary
> keys were also lost.
> a review of the t-log archives shows no sign of DDL statements being
> executed and the only conclusion i can come to is that rows from
> sysindexes have been lost.
> the indexes were lost during normal working hours, no reindexes or
> defrags were taking place and the objects were lost in a matter of
> seconds.
> we also suspected a user may have downloaded some sort of
> optimisation tool and tried to "optimise" the database without
> knowledge, however no-one with those permissions was logged in at the
> time.
> has anyone else experienced this or can they point me at a KB article
If someone ran the index tuning wizard, they could have instructed it to
kill old indexes infavor of the recommended list of new indexes. I doubt
this option would have killed any constraints, unless they were just
being enforces using indexes as opposed to the declarative approach.
I've personally never seen this reported before as a bug in SQL Server.
This type of problem is normally someone executing some T-SQL to to
this. I would immediately secure the SQL Server by removing all users
from the admin group that really don't require admin rights and changing
existing passwords for those admins that require those rights.
Although, I would expect to see these drop statements in the t-log. I
assume you are using a 3rd-party log reader to review the t-logs.
Have you checked database integrity using DBCC CHECKDB to see if any
errors are reported?
David Gugick
Quest Software
www.imceda.com
www.quest.com

all indexes lost

I have recently had a a database where all indexes (over 300) have been lost
for no apparent reason. - in addition to this some primary keys were also
lost.
a review of the t-log archives shows no sign of DDL statements being
executed and the only conclusion i can come to is that rows from sysindexes
have been lost.
the indexes were lost during normal working hours, no reindexes or defrags
were taking place and the objects were lost in a matter of seconds.
we also suspected a user may have downloaded some sort of optimisation tool
and tried to "optimise" the database without knowledge, however no-one with
those permissions was logged in at the time.
has anyone else experienced this or can they point me at a KB articleshadowswiss wrote:
> I have recently had a a database where all indexes (over 300) have
> been lost for no apparent reason. - in addition to this some primary
> keys were also lost.
> a review of the t-log archives shows no sign of DDL statements being
> executed and the only conclusion i can come to is that rows from
> sysindexes have been lost.
> the indexes were lost during normal working hours, no reindexes or
> defrags were taking place and the objects were lost in a matter of
> seconds.
> we also suspected a user may have downloaded some sort of
> optimisation tool and tried to "optimise" the database without
> knowledge, however no-one with those permissions was logged in at the
> time.
> has anyone else experienced this or can they point me at a KB article
If someone ran the index tuning wizard, they could have instructed it to
kill old indexes infavor of the recommended list of new indexes. I doubt
this option would have killed any constraints, unless they were just
being enforces using indexes as opposed to the declarative approach.
I've personally never seen this reported before as a bug in SQL Server.
This type of problem is normally someone executing some T-SQL to to
this. I would immediately secure the SQL Server by removing all users
from the admin group that really don't require admin rights and changing
existing passwords for those admins that require those rights.
Although, I would expect to see these drop statements in the t-log. I
assume you are using a 3rd-party log reader to review the t-logs.
Have you checked database integrity using DBCC CHECKDB to see if any
errors are reported?
David Gugick
Quest Software
www.imceda.com
www.quest.com

all indexes in DB

Hi all,
I want to know all indexes in database.
What do I do to get them?
Thanks in advanced,
Thi Nguyenthe sysindexes-table might get your started. From BOL: "Contains one row for each index and table in the database. This table is stored in each database".|||No, I want to know all names of indexes in database.|||No, I want to know all names of indexes in database.
select name from sysindexes|||that is good.
I just want to get user defined index names, not all names.

Thanks
Thi Nguyen