Some users complained about slow db performance so I ran some queries against the dmvs to look at queries that generate the most IO and queries that have the longest elapsed time. In both results I found quite a few instances where a version of
select top 1 * from table
is near the top of the list.. where the only change is the table they are looking at.
Now, without wondering why they repeatedly need to see the first row in the table (I'll ask that question too), I have a question as to why this would generate so much I/O. When I display the estimated execution plan for these queries, I see that SQL Server wants to do a table scan.
Should this be relatively quick since we are just looking at the top row?