I've got a MS SQL table about 8 million records. There's a principal key (with clustered index with only .8% fragmentation) around the column "ID". After I run apparently any query referencing the ID column, the query takes very lengthy (and actually ultimately crashes my application). Including simple queries like "Choose * FROM table WHERE ID=2020". By comparison, queries that don't reference ID (for example "Choose Best Players * from table") are simply fine.
I'd check statistics if you have checked fragmentation
They have been disabled or otherwise up-to-date?
They magic formula to check on is by using STATS_DATE
When the totally taking ten minutes(!?!) you have something seriously wrong. A table scan of 8 million records should take merely a second or two. I'd look into the event log to have an signs of imminent hardware failure, or try moving the database to another server to ascertain if there's another hardware fault.
I'd suspect the totally carrying out a table scan (or, a minimum of, a catalog scan on the really large or statistics-out-of-date index). Generate an believed execution plan (Control-L in SSMS) or have SQL Server return the execution plan it really used since it is sometimes different (Control-M make it possible for it, then run your query normally - it'll produce a new tab alongside your results).
After you have the execution plan, look for a table scan or perhaps an index scan, which is the supply of your slowness. The "believed execution plan" might even recommend and index to assist the query return more rapidly - more recent versions of SQL Server/SSMS include this feature.
Though I suspect you will not find anything interesting - your query is simply a single step - here is a quick intro on reading through execution plans: http://sqlserverpedia.com/wiki/Examining_Query_Execution_Plans
Is ID a GUID by any chance? SQL Server comes with an problem using the hash generation on GUID posts causing them to be perform badly in indexes.
Take your exact query and grab the "believed execution plan" from Sql Server Management Studio. Should you totally triggering a table scan (so it should not be) than the would explain the timing. Possibly the program might demonstrate what else is going on.
I suppose you have already reconstructed the index (according to your .8% fragmentation comment).
I suppose if you work with the default read level and you've got a lengthy running update you might be striking a deadlock too? That may certainly cause that as well.