Post reply

Create Nonclustered Index hangs and won't complete

  • April 11, 2012 at 1:11 pm

    #1472174

    Stefan Krzywicki (4/11/2012)

    Last time I tried to create the index, it told me I couldn't because it already existed, so I figured I'd start the query to create it and sure enough, this time it tried to run. I guess the statistics were removed by some automatic cleanup.

    I now have a simpler version of the index on the table (no included columns) and the queries that run against that table are now as fast as or faster than when the old index was on there, so I'm not going to mess with it for now and leave it as is.

    I appreciate the assistance everyone gave. If I figure anything further out, I'll post it here.

    It's an interesting problem; you might also want to check for excessive VLF's on either that DB or tempdb (DBCC LOGINFO), and check for filesystem level fragmentation of your database and tempdb files with (http://www.piriform.com/defraggler or a similar defrag tool. Ignore the percentages, look at how many fragments your data and log files are in, and how scattered across the disk they are.

    Also check to see if anyone turned NTFS level compression on any of the SQL Server files or directories (that tends to cause extreme fragmentation).

  • March 17, 2014 at 2:15 pm

    #1697773

    Did you ever figure out what caused this? I am having the same issue.

Viewing 2 posts - 61 through 61 (of 61 total)

You must be logged in to reply to this topic. Login to reply