I have a MS SQL table with about 8 million records. There is a primary key (with clustered index with only 0.8% fragmentation) on the column "ID". When I run seemingly any query referencing the ID column, the query takes very long (and in fact ultimately crashes my application). This includes simple queries like "SELECT * FROM table WHERE ID=2020". By contrast, queries that do not reference ID (such as "SELECT TOP 100 * from table") are just fine.
Any ideas?
Is ID a GUID by any chance? SQL Server has an issue with the hash generation on GUID columns which makes them perform badly in indexes.
Take your exact query and grab the "estimated execution plan" from Sql Server Management Studio. If you query is triggering a table scan (which it shouldn't be) then this would explain the timing. Perhaps the plan might show you what else is happening.
I assume that you have already rebuilt the index (based on your 0.8% fragmentation comment).
I'd suspect that the query is doing a table scan (or, at least, an index scan on a really large or statistics-out-of-date index). Generate an estimated execution plan (Control-L in SSMS) or have SQL Server return the execution plan it actually used because it's sometimes different (Control-M to enable it, then run your query normally - it will create a new tab next to your results).
Once you have the execution plan, search for a table scan or an index scan, and that is most likely the source of your slowness. The "estimated execution plan" may even recommend and index to help the query return more quickly - newer versions of SQL Server/SSMS include this feature.
Though I suspect you won't find anything interesting - your query is just a single step - here's a quick intro on reading execution plans: http://sqlserverpedia.com/wiki/Examining_Query_Execution_Plans
I'd check statistics if you've already checked fragmentation
Have they been disabled or not updated?
They quick way to check is to use STATS_DATE
If the query is taking 10 minutes(!?!) you've got something seriously wrong. Even a table scan of 8 million records should take only a second or two. I would check the event log for an indications of imminent hardware failure, or try moving the database to a different server to see if there's some other hardware fault.
I guess if you are using the default read level and you have a long running update you may be hitting a deadlock as well? That could definitely cause this too.