Queries running very slowly


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?

more ▼

asked Aug 23, 2011 at 07:31 PM in Default

avatar image

Katie 1
1.4k 132 164 205

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Aug 23, 2011 at 07:52 PM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left

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)

more ▼

answered Aug 23, 2011 at 11:14 PM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 23, 2011 at 07:31 PM

Seen: 2037 times

Last Updated: Aug 23, 2011 at 07:31 PM

Copyright 2018 Redgate Software. Privacy Policy