jwcolby
jwcolby at colbyconsulting.com
Mon Mar 31 06:41:03 CDT 2008
Yea, I figured I would likely just have to time it. The updates are not critical either, so I can gather perhaps 4 of them and do them at once. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Asger Blond Sent: Monday, March 31, 2008 6:24 AM To: 'Discussion concerning MS SQL Server' Subject: Re: [dba-SQLServer] To Drop or not to Drop, that is the question 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 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com