mah_j (11/18/2012)
Thanks JeffThe time out is on inserts.But i can not test any thing on main server.I even could not run DBCC SHOWCONTIG ('dbo.Table_Name' ) to see if it is necessary to rebuild the indexes.
(it takes a lot of time and causes many time out)
Is there any other way to find the hot index?
Yes... I haven't tried the code therein because I've always just been able to look at the first column of the indexes and test the cardinality of the column using a distinct on the column but I'vee not had to do that with a table quite as large. See the following link for alternate methods that actually detect page splits as recorded in the log file.
http://www.sqlservercentral.com/blogs/sqlballs/2012/08/06/how-to-find-bad-page-splits/
What do you mean by partitioning?Do you mean using partitioning with an appropriate range of data in that table to separate the files?
Yes although they can remain on the same file. I don't know about the claims of performance cited in the article (because I've not worked anywhere that has the luxury of split disk space) that Bhuvnesh posted but I do know partitions that are setup in a temporal fashion (by age of mostly static rows) can really take a load off of index maintenance. If you have the Enterprise Edition, lookup "Table Partitioning" in Books Online (press the {f1} key to get there. If you have the Standard Edition, lookup "Partitioned Views".
--Jeff Moden
Change is inevitable... Change for the better is not.