all queries are taking forever

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 2005 Forums

SQL Server Administration (2005)

all queries are taking forever

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-23 : 09:51:33

what can I do?

all queries that used to work are taking forever now???

what can I do?

is there a max size for the db that I may have reached

please advise asap

ratheeshknair
Posting Yak Master

129 Posts

Posted - 2007-08-23 : 10:05:20

If u r using sql 2k5 express the max size limit of a database is 4Gb.

RKNAIR

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-23 : 10:07:12

and how can I see how much I have used?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 10:40:19

sp_spaceused

show information per database.

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-23 : 12:57:39

master has .91 mb unallocated -- could this be the problem?

would this make queries slow?
when i restarted the computer the queries went back to normal but this keeps happening. these queries used to work fine and not much has changed. (except more info added but to different tables and not even the tables being queried)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 12:58:54

What kind of queries?

INSERTS? DELETES? UPDATES?

Check you logfile size too.

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-23 : 12:59:03

besides for master i see

reserved 3160 KB data 1416 KB index size1080 KB unused 664 KB

how can I compact this? can i delete logs like i used to in sql 7?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-23 : 13:05:02

how can i check the log files

problem is in queries such as

select count(id) from users where registration in(select distinct reg from reglist where reg is not null) and registration not in(select distinct registration from permits)

used to work but now can take 20 minutes to execute

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 13:10:58

[code]-- Alternative 1
SELECT COUNT(u.ID)
FROM Users AS u
WHERE EXISTS (SELECT * FROM RegList AS rl WHERE rl.Reg = u.Registration reg)
AND NOT EXISTS (SELECT * FROM Permits AS p WHERE p.Registration = u.Registration)

-- Alternative 2
SELECT COUNT(DISTINCT u.ID)
FROM Users AS u
INNER JOIN RegList AS rl ON rl.Reg = u.Registration reg
LEFT JOIN Permits AS p ON p.Registration = u.Registration
WHERE p.Registration IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-23 : 13:18:47

esthera, ignore the master database for now. Also, there aren't any log files to delete that would help with performance.

Have you checked Performance Monitor to determine if there is a hardware bottleneck? Have you check sp_who/sp_who2 to see if there is any blocking? When was the last time you ran ALTER INDEX (if SQL 2k5) to defragment indexes? When was the last time you ran UPDATE STATISTICS?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-23 : 13:22:41

i will try this but this is happening with every query.

if i restart the machine it seems to work but then slows down

what else could it be?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-23 : 13:30:40

thanks for your help... can you help me with how to do what you suggest below

Have you checked Performance Monitor to determine if there is a hardware bottleneck?
how?

Have you check sp_who/sp_who2 to see if there is any blocking?
yes and each time i run one of these queries i end up killing it as it hangs the computer.

When was the last time you ran ALTER INDEX (if SQL 2k5) to defragment indexes?
please guide me -- how do I do this?

When was the last time you ran UPDATE STATISTICS?
again how??

thnaks again for your help and I would appreciate if you can guide me how to do these as I think they will help

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-23 : 13:50:15

Let me guess

You have other services running on the box besodes sql server

Is that right?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-23 : 13:55:34

Restarting the machine is not a solution since it isn't helping in the long run.

Since sp_who/sp_who2 is taking forever, you've got an issue with locks.

Have you run SQL Profiler to determine what is running when the slow down occurs?

For the things that I mentioned in my last post, you'll need to do some reading. Check out the two commands in SQL Server Books Online. Also check out my blog that contains code for these: http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

For Performance Monitor, check out CPU and memory to start with.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-23 : 14:02:15

sql server is on the web server so i guess yes to other services

ince sp_who/sp_who2 works - it's just that i keep stopping the processes and doesn't help.

Have you run SQL Profiler to determine what is running when the slow down occurs? - How do I run this on express?

now everything is working fine (since i restarted a few hours ago) but something must be causing all this and I'm trying to figure out what.
at the time when the server is slow the cpu goes to 99%

any other suggestions? i'll read your links.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-23 : 14:05:33

I just don't have the time to explain all of the things that I asked about. They are complex tools and I've learned to use them over several years. I'd suggest reading all of the performance articles over at www.sql-server-performance.com that has to do with SQL Profiler and Performance Monitor. That's what I did, plus I bought several books on the subject matters.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-23 : 14:10:37

tara -- your alter index looks like it may help

can you explain me the difference between

@statsMode types
@defragType types

which would you suggest in this case?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-23 : 14:18:09

I usually use SAMPLED for @statsMode. You can also use DETAILED, but that'll cause it to run for a while.

I'd suggest REBUILD for @defragType.

REBUILD equates to DBCC DBREINDEX in SQL Server 2000. REORGANIZE equates to DBCC INDEXDEFRAGMENT in SQL Server 2000. If you are interested in the differences, then you've got a lot of reading to do: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-23 : 14:30:23

my easiest guess is that you have no more room on disk.

How many redundant backups are eating up your hard drive?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-23 : 14:48:29

no plenty of room free on hard drive.

no reduntant backups.

i reindexed and i'm going to see what happens and look into performance more tomorrow but any more suggestions will be helpful

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 15:39:05

Using IN in queries increases the possiblities of locking.
Try to run the two alternatives to your query, as posted above.

E 12°55'05.25"
N 56°04'39.16"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources