asked
Queries running very slowly
hi, the database queries have been running very very slowly. As a part of getting the database with updated statistics I have run this execute EXEC sp_updatestats. Later expecting to see some change for good, executed one select statement on a single table of the database select * from dbo.a This table has only 85k records and and the query ran for 1 minute 7 seconds. does not seem normal to me. and when i run this command select * from sys.sysprocesses order by cpu desc it takes around 5795, what could be the reason. that a simple select query is running so long and slow? what should be done to increase the performance?
A query like: `select * from dbo.a` is going to always perform a scan of the table (either through the clustered index, or the table itself if it's a heap). As the number of rows in that table increase, so is the execution time. There's no way to speed that up, at all. The query you ran against sysprocesses suggests you might have a system that is suffering from some sort of resource constraint, meaning, something is using up all the memory, cpu, or disk i/o. Simply running queries isn't going to help. You need to identify where the processes are slowing down. I'd suggest trying this: SELECT * FROM sys.dm_os_wait_stats AS dows ORDER BY wait_time_ms DESC That query can tell you what things are waiting on for your system. You'll probably need to lookup the lock that it provides, but it'll give you a quick reference to why things are running slowly. Otherwise, you need to setup monitoring to capture statistics on cpu, memory & disk. You can do this yourself through performance monitor or you can look into picking up a third party tool.
I add to @Grant Fritchey, that in case of the table is clustered, then the slow processing and higher resources utilization can be caused by heave fragmentation of the clustered index. In this case it will be necessary to rebuild the index. You can determine the index fragmentation using [sys.dm_db_index_physical_stats (Transact-SQL)][1] [1]:
http://msdn.microsoft.com/en-us/library/ms188917.aspx
