473,883 Members | 1,617 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

HELP - SQL Server Crash ? Memory leak ?

Hi everybody !

I´m maintaining a large intranet (approx 10000 concurrent users) running on
one IIS box and one DB box with sqlserver 2000.

Currently there is 2,5 GB Ram, 1 1400 mhz cpu and 2 scsi disks installed on
the db box.
Sqlserver is set to use max 1,4 GB RAM, and the sqlserver does not seem to
be using it all.

Currently SQLSERVER 2000 crashes at least once a day.
Its very weird, I run performance monitor with counters on, memory, disk
usage, num users, locks and such.

There is no indications in the counters before the crashes, they just happen
very sudden.
Only indication is that sqlserver makes some huge jumps in memory usage and
mostly the sqlserver then crashes an hour or 2 later.

The only thing that peaks a lot are the locks/sec counter.

My analysis of disk usage, queues etc. tells me i got no kind of i/o
bottlenecks.

Can anybody give me a clue as to what i should do ?

Best regards, Thue
Jul 20 '05
19 14855
Thank you Greg and Erland

I ran an Event trace using SQL profiler when CPU was at 100%, generating
approaching 3200 rows within a few minutes, and interestingly, ordering by
"Duration" revealed the following entries:-

(The first six are all event type 15 - which is "Disconnect " i believe. They
have massive duration times, and massive values for Reads.)

1002 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 90
616436 2004-01-12 18:04:35.687 187793 2 3326
1474 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 58
614733 2004-01-12 18:04:44.687 212743 1 20373
3118 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 108
612796 2004-01-12 18:05:42.107 215728 0 19657
2522 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 66
600640 2004-01-12 18:05:41.810 281198 5 12674
1881 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 72
256296 2004-01-12 18:10:57.093 69592 0 375
353 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 86
126046 2004-01-12 18:12:17.403 331 0 15
974 10 declare @P1 int set @P1=180150025 declare @P2 int set @P2=8 declare
@P3 int set @P3=1 declare @P4 int set @P4=0 exec sp_cursoropen @P1 output,
N' select (select IsNull(min(boar dnumber),0) from boardsplayers a WITH
(NOLOCK), games b WITH (NOLOCK) where a NULL 1320 Microsoft(R) Windows (R)
2000 Operating System sa 62 36763 2004-01-12 18:14:14.797 19868 0 0
I am not sure how to interpret these events. What does a massive duration on
Event Type 15 mean?! Also there are a massive amount of "Reads" associated
with these.

Any help greatly appreciated!

If I order by the CPU column descending, the top 10 rows are:-

1474 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 58
614733 2004-01-12 18:04:44.687 212743 1 20373
3118 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 108
612796 2004-01-12 18:05:42.107 215728 0 19657
2522 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 66
600640 2004-01-12 18:05:41.810 281198 5 12674
1002 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 90
616436 2004-01-12 18:04:35.687 187793 2 3326
56 12 SELECT
notepadvisible, sandbagger,tour namentsummaryvi ew,admin,ShowRa tingsofplayers, j
avascriptboardc reator,htmlemai ls,listcurrentg amesview,loginc ount,JavaScript S
upportLevel,Rat ingPredictorSty le,MessageBoxTy pe,LanguageID,T eamCreator,bann e
rs,IsNull(Count ryID,1 NULL 1320 Microsoft(R) Windows (R) 2000 Operating
System sa 102 12843 2004-01-12 18:14:00.733 11599 0 2875
954 12 SELECT
notepadvisible, sandbagger,tour namentsummaryvi ew,admin,ShowRa tingsofplayers, j
avascriptboardc reator,htmlemai ls,listcurrentg amesview,loginc ount,JavaScript S
upportLevel,Rat ingPredictorSty le,MessageBoxTy pe,LanguageID,T eamCreator,bann e
rs,IsNull(Count ryID,1 NULL 1320 Microsoft(R) Windows (R) 2000 Operating
System sa 96 25050 2004-01-12 18:14:25.340 11599 0 2797
1817 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 85
6810 2004-01-12 18:15:02.640 3484 0 1516
1533 12 select GameNumber,Tour namentID from TournamentGames WITH (NOLOCK)
where gamenumber = 349913 NULL 1320 Microsoft(R) Windows (R) 2000 Operating
System sa 58 1563 2004-01-12 18:14:59.437 755 0 1015
1553 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 58
1966 2004-01-12 18:14:59.437 765 0 1015
1881 15 NULL NULL 1320 Microsoft(R) Windows (R) 2000 Operating System sa 72
256296 2004-01-12 18:10:57.093 69592 0 375

Again any help in diagnosing the cause of this, which puts the CPU to 100%
would be greatly appreciated.

Best wishes
Tryfon

"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** *************@1 27.0.0.1...
Tryfon Gavriel (tr****@gtryfon .demon.co.uk) writes:
I am currently having to reboot the machine every few days now since we
put in a new database server with the latest service packs (SQL Server
2000, SP3). Removing the named-pipes protcol does not seem to have
resolved this nasty problem this time round. I have seen on some
newsgroup postings, that it is no longer possible to actually remove
Named Pipes fully since SP3.


100% CPU may not be cause for alarm. When SQL Server becomes completely
unresponsive, it certainly is.

I know of two way this can happen. Or rather, I know of one, and one
"seemingly unresponsive". The one case where it becomes unresponsive,
is error 17883. If this happens, you should see this in the error log,
where you get a load of these messages. The message only appears with
SP3 or later hotfixes.

The other case I've seen was with some poor SQL. In this particular
case I was testing performance of this poor SQL for an article on my
web site. I was surprised to see that this particular query took so
much CPU, that issuing an sp_who could have a response time on over
30 seconds.

But there are probably more possibilities than these two. But then again,
it certainly not somehing which happens all over town, so if your SQL
Server becomes unresponsive, there is something fishy on your machine,
be that hardware or poor SQL statements.

One way to track down the latter is to have a profiler trace running,
and see what you get just before the machine goes into nirvana.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #11
Tryfon Gavriel (tr****@gtryfon .demon.co.uk) writes:
(The first six are all event type 15 - which is "Disconnect " i believe.
They have massive duration times, and massive values for Reads.)
...

I am not sure how to interpret these events. What does a massive
duration on Event Type 15 mean?! Also there are a massive amount of
"Reads" associated with these.


As you said, event 15 is disconnection. Duration is just how long the
connection was open. And Reads are just the accumulated number of
reads during that session.

In itself, not that exciting. Then again, maybe it is a clue that four
long-running processes owned by sa quits just before the machines
reaches nirvana. No, please don't ask me what that clue would mean!

It is possible that the SQL statements you see when you sort on Duration
has anything to do with the CPU hog. However, I wouid not really expect
that process to show up. I would include the Starting events in the trace,
and then investigate the uncompleted events at the end of the trace
when the CPU goes 100%.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #12
Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************** @127.0.0.1>...
Tryfon Gavriel (tr****@gtryfon .demon.co.uk) writes:
(The first six are all event type 15 - which is "Disconnect " i believe.
They have massive duration times, and massive values for Reads.)
...

I am not sure how to interpret these events. What does a massive
duration on Event Type 15 mean?! Also there are a massive amount of
"Reads" associated with these.


As you said, event 15 is disconnection. Duration is just how long the
connection was open. And Reads are just the accumulated number of
reads during that session.

In itself, not that exciting. Then again, maybe it is a clue that four
long-running processes owned by sa quits just before the machines
reaches nirvana. No, please don't ask me what that clue would mean!

It is possible that the SQL statements you see when you sort on Duration
has anything to do with the CPU hog. However, I wouid not really expect
that process to show up. I would include the Starting events in the trace,
and then investigate the uncompleted events at the end of the trace
when the CPU goes 100%.

TJI:

I have seen a similar problem (loss of connectivity, performance
grinds to a halt, etc.) when one of our servers has 3 (or more)
exchange waits active for extended periods of time. We start seeing
performance degradation at 2 exchange waits, which raises the flag for
us.

What is happening in our case is that users for some reason disconnect
their side (reboot their workstation, etc) with bad timing, just in
the middle of a network handshake before responding to the server. The
server just waits for the packet from the client that never comes.
These transactions stay alive, tying up datapages and locks, which
escalate as users log back in and retry.....

(We saw this by waiting, sometimes as long as 1/2 hour, for EM to
connect, then waiting again for the Process list to present itself.)

With your large number of concurrent users, I would not be surprised
if this is not your problem.
Jul 20 '05 #13
Hi Erland and all

The server has been standing for 2 weeks without a reboot. This has been a
great relief to me. If my solution may help others, the two things I did
were:-

a) Simplify some of the SQL - taking out some luxury sub-queries off many
pages
b) Taking off auto-grow from three of the databases - tempdb, the main
Chessworld db, and master.

I was not exactly sure if it was a) or b) but I have more evidence now it
was in fact b) that was causing massive slow-downs requiring a reboot
because CPU seems to go unrecoverably to 100%.

The reason for more evidence, is that today, I finally had a "cannot
allocate space error" being logged. I increased the size of the chessworld
db, and the tempdb, and put back the auto-grow on the chessworld db. Within
about an hour or two, the symptoms of a big slow-down came back with CPU
100%.

I rebooted the database server but have again taken off auto-grow options. I
believe for my site with many concurrent users, the auto-grow is causing
issues. I will keep you posted.

Best wishes
Tryfon

"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...
Tryfon Gavriel (tr****@gtryfon .demon.co.uk) writes:
(The first six are all event type 15 - which is "Disconnect " i believe.
They have massive duration times, and massive values for Reads.)
...

I am not sure how to interpret these events. What does a massive
duration on Event Type 15 mean?! Also there are a massive amount of
"Reads" associated with these.


As you said, event 15 is disconnection. Duration is just how long the
connection was open. And Reads are just the accumulated number of
reads during that session.

In itself, not that exciting. Then again, maybe it is a clue that four
long-running processes owned by sa quits just before the machines
reaches nirvana. No, please don't ask me what that clue would mean!

It is possible that the SQL statements you see when you sort on Duration
has anything to do with the CPU hog. However, I wouid not really expect
that process to show up. I would include the Starting events in the trace,
and then investigate the uncompleted events at the end of the trace
when the CPU goes 100%.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #14

"Tryfon Gavriel" <tr****@gtryfon .demon.co.uk> wrote in message
news:bv******** ***********@new s.demon.co.uk.. .
Hi Erland and all

The server has been standing for 2 weeks without a reboot. This has been a
great relief to me. If my solution may help others, the two things I did
were:-

a) Simplify some of the SQL - taking out some luxury sub-queries off many
pages
b) Taking off auto-grow from three of the databases - tempdb, the main
Chessworld db, and master.
That'll do it right there.

Here's a typical scenario:

DB growth is set to 10%

DB is 100MB...

An insert is performed.... limit gets reached. So, now the DB wants to
expand.

It starts to allocate 10MB.

During this time, deletes and updates can generally be performed, but
basically any additional inserts will be blocked while the space is
allocated. (and any updates or deletes that need to occur on those blocked
inserts obviously get blocked.)

Now, SQL Server can generally allocate 10MB pretty quick.

But now you've got 110MB. Next expansion will be 11MB. Putting you at
121MB. Next one will be 12.1 MB. And this continues.

Before you know it, you've got a 10 gig DB trying to allocate 1GB. (and the
kicker is, it probably only needs 10MB at that point. :-)

And of course during this allocation, the DB appears hung.

So, I generally try NOT to allow auto-growth, or set it to a fixed amount
(like 10MB or 100MB, etc. depending on the size and type of DB).

Also, this can occur a lot with transaction logs. Which generally means
that no transcation backups are being done. Which on a production DB is
almost always a "bad thing".

Hmm, given what yu say, I'm guessing that your tempb may be growing a lot.
(Since upon restart I believe it'll get resized back to it's original size.)

This could be a result of a bad design, or simply the result of a necessary
design.

What I'd do is check which DB is growing the most and resize it.

The master DB normally should not grow much at all.

So it's most likely the tempdb or the chessworld one. (as he states the
obvious.)

I was not exactly sure if it was a) or b) but I have more evidence now it
was in fact b) that was causing massive slow-downs requiring a reboot
because CPU seems to go unrecoverably to 100%.

The reason for more evidence, is that today, I finally had a "cannot
allocate space error" being logged. I increased the size of the chessworld
db, and the tempdb, and put back the auto-grow on the chessworld db. Within about an hour or two, the symptoms of a big slow-down came back with CPU
100%.

I rebooted the database server but have again taken off auto-grow options. I believe for my site with many concurrent users, the auto-grow is causing
issues. I will keep you posted.

Please do.

Best wishes
Tryfon

"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...
Tryfon Gavriel (tr****@gtryfon .demon.co.uk) writes:
(The first six are all event type 15 - which is "Disconnect " i believe. They have massive duration times, and massive values for Reads.)
...

I am not sure how to interpret these events. What does a massive
duration on Event Type 15 mean?! Also there are a massive amount of
"Reads" associated with these.


As you said, event 15 is disconnection. Duration is just how long the
connection was open. And Reads are just the accumulated number of
reads during that session.

In itself, not that exciting. Then again, maybe it is a clue that four
long-running processes owned by sa quits just before the machines
reaches nirvana. No, please don't ask me what that clue would mean!

It is possible that the SQL statements you see when you sort on Duration
has anything to do with the CPU hog. However, I wouid not really expect
that process to show up. I would include the Starting events in the trace, and then investigate the uncompleted events at the end of the trace
when the CPU goes 100%.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp


Jul 20 '05 #15
Hi there

Thank you for that feedback.

I should have also mentioned the following: There was an unexpected database
server shutdown last night recorded in the w2k event viewer. I had to
request the database server to be restarted. Once it did it was working fine
for a while, but then I noticed on my ASP error log, the "cannot allocate
space" errors. It was then that I increased the size, but also put auto-grow
on. It just died within 2 hours. What I then did was take off autogrow, and
restart the database server again.

So basically:-

* I have doubled the size of the tempdb database to 2 gig for data file and
1 gig for transaction log
* I have also doubled the size of the Chessworld database (One concern here
is the time it takes the backup the database, but it still seems to be able
to back it up within a few minutes.. a relief :) )
* Auto-grow taken off both databases

If the server shuts down in 2 months time, then fine. I will request a
database server reboot and increase their sizes again. I cannot have a
background process re-allocating space, when I have tonnes of players online
playing chess moves (or trying to!), resulting in me having to reboot the
server. The "cannot allocate" space errors that occured last night have now
stopped.

The following may be useful for other ASP/SQL Server developers for general
problem diagnosis: About two weeks ago, I knocked up an ASP admin page to
monitor the sysprocesses table. This is useful to me in trying to understand
the processes with greatest CPU usage. I ordered it by CPU, but also made it
highlight in red processes which had a last batch time of more than 10
minutes ago. The idea was to highlight potential processes that could be
killed. I found the following three particularly useful web references :-

Kill documentation:
http://msdn.microsoft.com/library/de...kf-kz_1zos.asp
Tips for handling blocking:
http://www.sql-server-performance.com/blocking.asp
Understanding and resolving blocking problems:
http://support.microsoft.com/default...NoWebContent=1

Which I have put links at the top of my admin page for viewing processes :-)
It also made me paranoid about the background processes going on- hence my
intuition to turn off the auto-grow tick boxes.

Some insights include - simplification of pages, do seem to lead to
processes consuming less CPU, and generally a faster site. But also the
Background processes are highlighted. I think viewing the sysprocesses table
is very useful point of reference, and the reason I started investigating
it, is because it is mentioned in SQL Serrver 2000 programming book page
1081, where it also highlights using the following tools for analysing
problems:-

a) SHOWPLAN TEXT | ALL
b) STATISTICS IO
c) DBCC
d) Query governer
e) sp_lock
f) sysprocesses table
g) SQL Server Profiler
(should be listed because detailed): h) Perfmon

Before posting to this excellent group, I had not actually used the SQL
profiler much at all. I did have admin pages already for sp_lock and sp_who.
But I usually use sp_lock for analysing locks, and ignored the sp_who most
of the time. The view on sysprocesses is more useful to me because you can
order by cpu, etc. I now regularly look at the sp_lock page and the
"processes" page.

I also make use of the ASP error object to generate errors in a log file,
and my most frequently logged error is now SQL Server related. This means I
can immediately see any bottleneck ASP pages where there is potentially bad
SQL or other issues.

Best wishes
Tryfon
"Greg D. Moore (Strider)" <mo************ ****@greenms.co m> wrote in message
news:Ug******** **********@twis ter.nyroc.rr.co m...

"Tryfon Gavriel" <tr****@gtryfon .demon.co.uk> wrote in message
news:bv******** ***********@new s.demon.co.uk.. .
Hi Erland and all

The server has been standing for 2 weeks without a reboot. This has been a
great relief to me. If my solution may help others, the two things I did
were:-

a) Simplify some of the SQL - taking out some luxury sub-queries off many pages
b) Taking off auto-grow from three of the databases - tempdb, the main
Chessworld db, and master.
That'll do it right there.

Here's a typical scenario:

DB growth is set to 10%

DB is 100MB...

An insert is performed.... limit gets reached. So, now the DB wants to
expand.

It starts to allocate 10MB.

During this time, deletes and updates can generally be performed, but
basically any additional inserts will be blocked while the space is
allocated. (and any updates or deletes that need to occur on those blocked
inserts obviously get blocked.)

Now, SQL Server can generally allocate 10MB pretty quick.

But now you've got 110MB. Next expansion will be 11MB. Putting you at
121MB. Next one will be 12.1 MB. And this continues.

Before you know it, you've got a 10 gig DB trying to allocate 1GB. (and

the kicker is, it probably only needs 10MB at that point. :-)

And of course during this allocation, the DB appears hung.

So, I generally try NOT to allow auto-growth, or set it to a fixed amount
(like 10MB or 100MB, etc. depending on the size and type of DB).

Also, this can occur a lot with transaction logs. Which generally means
that no transcation backups are being done. Which on a production DB is
almost always a "bad thing".

Hmm, given what yu say, I'm guessing that your tempb may be growing a lot.
(Since upon restart I believe it'll get resized back to it's original size.)
This could be a result of a bad design, or simply the result of a necessary design.

What I'd do is check which DB is growing the most and resize it.

The master DB normally should not grow much at all.

So it's most likely the tempdb or the chessworld one. (as he states the
obvious.)

I was not exactly sure if it was a) or b) but I have more evidence now i t was in fact b) that was causing massive slow-downs requiring a reboot
because CPU seems to go unrecoverably to 100%.

The reason for more evidence, is that today, I finally had a "cannot
allocate space error" being logged. I increased the size of the chessworld db, and the tempdb, and put back the auto-grow on the chessworld db. Within
about an hour or two, the symptoms of a big slow-down came back with CPU
100%.

I rebooted the database server but have again taken off auto-grow

options. I
believe for my site with many concurrent users, the auto-grow is causing
issues. I will keep you posted.


Please do.

Best wishes
Tryfon

"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...
Tryfon Gavriel (tr****@gtryfon .demon.co.uk) writes:
> (The first six are all event type 15 - which is "Disconnect " i

believe. > They have massive duration times, and massive values for Reads.)
>...
>
> I am not sure how to interpret these events. What does a massive
> duration on Event Type 15 mean?! Also there are a massive amount of
> "Reads" associated with these.

As you said, event 15 is disconnection. Duration is just how long the
connection was open. And Reads are just the accumulated number of
reads during that session.

In itself, not that exciting. Then again, maybe it is a clue that four
long-running processes owned by sa quits just before the machines
reaches nirvana. No, please don't ask me what that clue would mean!

It is possible that the SQL statements you see when you sort on Duration has anything to do with the CPU hog. However, I wouid not really expect that process to show up. I would include the Starting events in the trace, and then investigate the uncompleted events at the end of the trace
when the CPU goes 100%.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp



Jul 20 '05 #16
Tryfon Gavriel (tr****@gtryfon .demon.co.uk) writes:
* I have also doubled the size of the Chessworld database (One concern
here is the time it takes the backup the database, but it still seems to
be able to back it up within a few minutes.. a relief :) )


My experience is that the time to do a backup is related to the actual
amount of data in the database. That is, if you allocate 60 GB for a
1 GB database, then those 59 GB are cheap. (The one occassion they
cost, is when you want to restore a backup into a clone database;
then the allocation of those 59 GB will take 10-20 minutes extra.)
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #17

"Tryfon Gavriel" <tr****@gtryfon .demon.co.uk> wrote in message
news:bv******** ***********@new s.demon.co.uk.. .
Hi there

Thank you for that feedback.

I should have also mentioned the following: There was an unexpected database server shutdown last night recorded in the w2k event viewer. I had to
request the database server to be restarted. Once it did it was working fine for a while, but then I noticed on my ASP error log, the "cannot allocate
space" errors. It was then that I increased the size, but also put auto-grow on. It just died within 2 hours. What I then did was take off autogrow, and restart the database server again.

So basically:-

* I have doubled the size of the tempdb database to 2 gig for data file and 1 gig for transaction log
That's a fairly large tempdb. I'd question your design if you really need
that much. But I don't know how many users you're supporting.

But it does indicate that perhaps your transactions are lasting for two
long.

Consider this, let's say (and this is a wild guess here) you're supporting 1
million users. If each one needs 1KB of temp data stored, that would be
about 1 gig of data. However, in theory it should be unusual for all 1
million users to need that data stored at once.

But like I say, I don't know much about the design, but I'd be suspicious
about how long your transactions are being held open.

* I have also doubled the size of the Chessworld database (One concern here is the time it takes the backup the database, but it still seems to be able to back it up within a few minutes.. a relief :) )
Note that a backup will not affect database performance. (or at least the
affect is extremely small because of an increased disk I/O. Note that a
backup does NOT stop processing.)
* Auto-grow taken off both databases

If the server shuts down in 2 months time, then fine. I will request a
database server reboot and increase their sizes again.
Even if the SQL server locks up, simply stopping or worse killing it should
be enough. Rebooting the box should be an absolutel last resort.
I cannot have a
background process re-allocating space, when I have tonnes of players online playing chess moves (or trying to!), resulting in me having to reboot the
server. The "cannot allocate" space errors that occured last night have now stopped.

Understandable.
The following may be useful for other ASP/SQL Server developers for general problem diagnosis: About two weeks ago, I knocked up an ASP admin page to
monitor the sysprocesses table. This is useful to me in trying to understand the processes with greatest CPU usage. I ordered it by CPU, but also made it highlight in red processes which had a last batch time of more than 10
minutes ago. The idea was to highlight potential processes that could be
killed. I found the following three particularly useful web references :-

Hmm, can you post thse someplace? Might be interesting to use.

Just as a reference we have databases that had uptimes over over a year.
(only reason we needed to change that was due to finally being able to apply
SP3a.)
Kill documentation:
http://msdn.microsoft.com/library/de...kf-kz_1zos.asp Tips for handling blocking:
http://www.sql-server-performance.com/blocking.asp
Understanding and resolving blocking problems:
http://support.microsoft.com/default...NoWebContent=1
Which I have put links at the top of my admin page for viewing processes :-) It also made me paranoid about the background processes going on- hence my
intuition to turn off the auto-grow tick boxes.

Some insights include - simplification of pages, do seem to lead to
processes consuming less CPU, and generally a faster site. But also the
Background processes are highlighted. I think viewing the sysprocesses table is very useful point of reference, and the reason I started investigating
it, is because it is mentioned in SQL Serrver 2000 programming book page
1081, where it also highlights using the following tools for analysing
problems:-

a) SHOWPLAN TEXT | ALL
b) STATISTICS IO
c) DBCC
d) Query governer
e) sp_lock
f) sysprocesses table
g) SQL Server Profiler
(should be listed because detailed): h) Perfmon

Before posting to this excellent group, I had not actually used the SQL
profiler much at all. I did have admin pages already for sp_lock and sp_who. But I usually use sp_lock for analysing locks, and ignored the sp_who most
of the time. The view on sysprocesses is more useful to me because you can
order by cpu, etc. I now regularly look at the sp_lock page and the
"processes" page.

I also make use of the ASP error object to generate errors in a log file,
and my most frequently logged error is now SQL Server related. This means I can immediately see any bottleneck ASP pages where there is potentially bad SQL or other issues.

That's some good stuff.


Best wishes
Tryfon
"Greg D. Moore (Strider)" <mo************ ****@greenms.co m> wrote in message news:Ug******** **********@twis ter.nyroc.rr.co m...

"Tryfon Gavriel" <tr****@gtryfon .demon.co.uk> wrote in message
news:bv******** ***********@new s.demon.co.uk.. .
Hi Erland and all

The server has been standing for 2 weeks without a reboot. This has been
a
great relief to me. If my solution may help others, the two things I
did were:-

a) Simplify some of the SQL - taking out some luxury sub-queries off many pages
b) Taking off auto-grow from three of the databases - tempdb, the main
Chessworld db, and master.
That'll do it right there.

Here's a typical scenario:

DB growth is set to 10%

DB is 100MB...

An insert is performed.... limit gets reached. So, now the DB wants to
expand.

It starts to allocate 10MB.

During this time, deletes and updates can generally be performed, but
basically any additional inserts will be blocked while the space is
allocated. (and any updates or deletes that need to occur on those blocked inserts obviously get blocked.)

Now, SQL Server can generally allocate 10MB pretty quick.

But now you've got 110MB. Next expansion will be 11MB. Putting you at
121MB. Next one will be 12.1 MB. And this continues.

Before you know it, you've got a 10 gig DB trying to allocate 1GB. (and

the
kicker is, it probably only needs 10MB at that point. :-)

And of course during this allocation, the DB appears hung.

So, I generally try NOT to allow auto-growth, or set it to a fixed amount (like 10MB or 100MB, etc. depending on the size and type of DB).

Also, this can occur a lot with transaction logs. Which generally means that no transcation backups are being done. Which on a production DB is
almost always a "bad thing".

Hmm, given what yu say, I'm guessing that your tempb may be growing a lot. (Since upon restart I believe it'll get resized back to it's original

size.)

This could be a result of a bad design, or simply the result of a

necessary
design.

What I'd do is check which DB is growing the most and resize it.

The master DB normally should not grow much at all.

So it's most likely the tempdb or the chessworld one. (as he states the
obvious.)

I was not exactly sure if it was a) or b) but I have more evidence now i t was in fact b) that was causing massive slow-downs requiring a reboot
because CPU seems to go unrecoverably to 100%.

The reason for more evidence, is that today, I finally had a "cannot
allocate space error" being logged. I increased the size of the chessworld db, and the tempdb, and put back the auto-grow on the chessworld db.

Within
about an hour or two, the symptoms of a big slow-down came back with
CPU 100%.

I rebooted the database server but have again taken off auto-grow

options.
I
believe for my site with many concurrent users, the auto-grow is causing issues. I will keep you posted.


Please do.

Best wishes
Tryfon

"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...
> Tryfon Gavriel (tr****@gtryfon .demon.co.uk) writes:
> > (The first six are all event type 15 - which is "Disconnect " i

believe.
> > They have massive duration times, and massive values for Reads.)
> >...
> >
> > I am not sure how to interpret these events. What does a massive
> > duration on Event Type 15 mean?! Also there are a massive amount of > > "Reads" associated with these.
>
> As you said, event 15 is disconnection. Duration is just how long the > connection was open. And Reads are just the accumulated number of
> reads during that session.
>
> In itself, not that exciting. Then again, maybe it is a clue that four > long-running processes owned by sa quits just before the machines
> reaches nirvana. No, please don't ask me what that clue would mean!
>
> It is possible that the SQL statements you see when you sort on

Duration > has anything to do with the CPU hog. However, I wouid not really expect > that process to show up. I would include the Starting events in the

trace,
> and then investigate the uncompleted events at the end of the trace
> when the CPU goes 100%.
>
>
> --
> Erland Sommarskog, SQL Server MVP, so****@algonet. se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp



Jul 20 '05 #18
It seems time to change db :-))
Jul 20 '05 #19
> * I have doubled the size of the tempdb database to 2 gig for data file and
1 gig for transaction log

Hi,

You might want to check the amount of free space, for the drives that
the TempDB and TLog are on.
Jul 20 '05 #20

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
5562
by: Mark D. Anderson | last post by:
About a month ago Richard Cornford did an interesting analysis of a memory leak in jscript (internet explorer) when there are "circular" references between DOM objects and (real) jscript objects: http://groups.google.com/groups?selm=bcq6fn%24g53%241%248300dec7%40news.demon.co.uk This message summarizes some testing I've done and their results. These results somewhat contradict Cornford's conclusions; I haven't
32
3879
by: John | last post by:
Hi all: When I run my code, I find that the memory that the code uses keeps increasing. I have a PC with 2G RAM running Debian linux. The code consumes 1.5G memory by the time it finishes execution. But I do not think it needs so much memory. About 500M memory should be enough. I have following questions about memory leak. (1).If in my code I only define constructor for my class, and do not define destructor, will it cause memory leak?
2
2319
by: Prashant | last post by:
Hello All, I am new to win32 programming,I was trying to get all the Subkeys under any Key. I have written a C program for achieving this, I am using WinNT Server . In this program *networkCardKeyList is a Global array The memory allocated to the pointers in this array are freed in the main function.
4
6100
by: Don Nell | last post by:
Hello Why is there a memory leak when this code is executed. for(;;) { ManagementScope scope = new ManagementScope(); scope.Options.Username="username"; scope.Options.Password="password"; scope.Path.Path=@"\\pc\root\cimv2";
13
1558
by: Boni | last post by:
I use 3-d party component. In this component I must pass a reference to my object. The problem is that this component has an ugly bug.When this component is disposed, it incorrectly don't delete the reference to my object from one of its shared lists.And since the operation repeats many times the leak is huge. Is there a way to kill my object anyway? Thanks a lot, Boni
1
2322
by: Charlotte | last post by:
Hello, We are doing a stability test on our application to prepare for Microsoft SQL Server 2000 certification, and we found a memory leak, but we cannot find its source and fix it. We use an ASP page written in JScript that connects to Analysis Services 2000 using ADOMD and retrieves data from it (this is a very simplified version of our real application that we created just to test the memory leak), see the code below.
0
1213
by: hairobinson | last post by:
Can any one help me explaing about Kernel Memory leak? what is kernel memory leak? how do we debug Kernel level Memory Leak? Do we have standard tool for finding it? How do we differentiate Kernel level memory leak with Application level Memory leak? Thanks in Advance,
1
13999
by: cherman | last post by:
I don't know if anybody here worked with gSOAP software, but I do hope so. I've made some simple server app using gSOAP example. Everythig worked fine until I switched it into ssl mode. Using it results in huge memory leak. My question is if anybody has any idea what am I doing wrong or what am I missing? Here is some of my code: ----------------------------------------------------------------------------------------------------- ...
22
9378
by: Peter | last post by:
I am using VS2008. I have a Windows Service application which creates Crystal Reports. This is a multi theaded application which can run several reports at one time. My problem - there is a memory leak someplace. I can not detect the memory leak by running several reports by hand, but when I run tha app as a servrice and process few hundred reports there is significant memory leak. The application can consume over 1GB of memory where it...
0
9781
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10836
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9564
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7960
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7114
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5793
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5982
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4606
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4210
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.