|
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 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?
(comments are locked)
|
|
A query like:
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.
(comments are locked)
|
|
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)
(comments are locked)
|
