x

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?

more ▼

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

Katie 1 gravatar image

Katie 1
1.4k 132 163 202

(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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(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)][1]

[1]: http://msdn.microsoft.com/en-us/library/ms188917.aspx
more ▼

answered Aug 23, 2011 at 11:14 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1850
x737
x252
x87
x14

asked: Aug 23, 2011 at 07:31 PM

Seen: 1539 times

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