Query taking more time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query taking more time

Author

Topic

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2007-04-25 : 09:20:59

Gurus
i have a select query,It's a simple query that used to run in less than a minute; it is know taking about 7 minutes.
What can be the possible reasons.Please guide
Regards
Nitin

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-25 : 09:29:37

1. Table statistics may be stale
2. Indexes used are fragmented due to heavy DML operations
3. Table has grown quite large over period of time
4. Table lacks necessary indexes
5. Table is getting locked or process is getting blocked by another process
6. Query is not written properly to take advantage of indexes.

What the heck!! There can be any number of reasons...We need more information.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-25 : 09:37:06

Basic question : What is changed ?

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2007-04-25 : 09:44:24

Nothing has been changed,It used to run ok till yesterday.
I killed one process
itz working fine now

Regards
Nitin

mattyblah
Starting Member

49 Posts

Posted - 2007-04-25 : 11:09:47

Try this. Run your select query in query analyzer. If it is taking a long time,
in another window run:


exec sp_who 'active'

look for any rows that have a value in blk. Run:


dbcc inputbuffer(spid)

for the spid that is in blk. That should give you the sql statements that are holding up your select. More than likely it will be a transaction. Hope that helps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources