farax_x (1/12/2011)
HI,I had the same problem and I changed some queries with [with (nolock)] and fastfirstrow ,... to reduce locking and rebuild with ALLOW_PAGE_LOCKS = OFF, ONLINE = on !
ALTER INDEX [index_Name] ON [table_name]REBUILD PARTITION = ALL WITH
( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, ONLINE = on, SORT_IN_TEMPDB = OFF )
Everyone PLEASE be aware that NOLOCK hint can get you BAD data - not just uncommitted data!!
Point two is that fastfirstrow can be DEVASTATING to performance for queries that hit lots of rows of data (whether or not it returns them) because it will basically lead to index seeks and bookmark lookup query plans just to quickly get some rows back. In large plan cases, scans and hashes/merges for joins can be WAY faster and less resource intensive in total!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service