Asger Blond
ab-mi at post3.tele.dk
Mon Jun 16 17:31:44 CDT 2008
Arthur, sorry I didn't read your scenario close enough: "I have a table into which a process inserts 1300 rows every 10 minutes. Currently there are 34 million rows in the table." When rebuilding an index SQL Server normally locks the key, which will raise concurrency issues for your insert-process. To circumvent this issue you should use the option WITH ONLINE = ON, which will place the index rebuild-values into the tempdb database and apply them as allowed by concurrency. So this would be the statement: ALTER INDEX [PK_BESTSecurityLog] ON [dbo].[BESTSecurityLog] REBUILD WITH ONLINE = ON Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Asger Blond Sendt: 16. juni 2008 21:32 Til: 'Discussion concerning MS SQL Server' Emne: Re: [dba-SQLServer] Can't drop the PK from a table If you really want to drop an index associated with a constraint (a PK or Unique Constraint) you have to drop the *constraint* - you can't just drop the index, but dropping the constraint will automatically drop the associated index as well. SQL Server has made this restriction as a precaution against dropping a unique index not knowing that the index is there for a constraint-reason: that's why you have to explicitly drop the constraint, telling SQL Server that you are aware of what you are doing... But any way, I don't think you have to drop the index. The REBUILD option is made just for your case: a heavy fragmented index and an index bound to a constraint. Using the example from your previous posting I would recommend this statement: ALTER INDEX [PK_BESTSecurityLog] ON [dbo].[BESTSecurityLog] REBUILD Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Arthur Fuller Sendt: 16. juni 2008 19:07 Til: Discussion concerning MS SQL Server Emne: [dba-SQLServer] Can't drop the PK from a table I have a table into which a process inserts 1300 rows every 10 minutes. Currently there are 34 million rows in the table. There are 7 indexes on the table, two of which are seriously fragmented (98% and 87%). I ran dbcc reindex on the table and it changed the fragmentation not at all. Why would that be the case? Another thing that puzzles me is that one of the fragmented indexes is the PK clustered index and the PK as is int identity. Also, I find that I cannot drop the PK index, which I thought I'd do as a way of rebuilding it from scratch. When I try to drop the index and save the table, the system times out and won't let me do it. Any idea why this might be so? Arthur _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com