Asger Blond
ab-mi at post3.tele.dk
Mon Mar 31 05:23:41 CDT 2008
A small test inserting just 100000 rows in a table with one non-clustered index gave me this Client Statistics for Total Execution Time: Insert with Disable and Rebuild index: 2994 Insert with Drop and Create index: 3044 Insert with existing index active: 5547 No big difference between Disable/Rebuild and Drop/Create, but a huge difference between having and not having an active index (even with a small number of inserts and just a single index). Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Asger Blond Sendt: 31. marts 2008 10:52 Til: 'Discussion concerning MS SQL Server' Emne: Re: [dba-SQLServer] To Drop or not to Drop, that is the question Don't think you can *predict* it. But one way to *test* it is to run your updates from a query window in SSMS using Query | Include Client Statistics, which will give you an extra result-tab with informations such as "Total execution time". Another way is to use SQL Server Profiler. Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jwcolby Sendt: 31. marts 2008 04:36 Til: 'Discussion concerning MS SQL Server' Emne: [dba-SQLServer] To Drop or not to Drop, that is the question I have a largish database, currently 92 million records. I get an update with ~1 million records every week. At what point does dropping / rebuilding the indexes become more expensive than adding the records with the index in place? Is there a tool to predict such a thing? John W. Colby Colby Consulting www.ColbyConsulting.com _______________________________________________ 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