waittype=WRITELOG

[Marek Wierzbicki]

Hi

In my server (MSSQL 2000 sp4) very often I see long time of executing query
with waittype=WRITELOG

Recovery is set to SIMPLE and there is no autoShrink.

Auto increment file size (log) is set to 10%

What can be the reason of this problem?

Marek

[Jack Vamvas]

If there are aren't anny errors and the query completes, there usually isn't
a problem.
Without knowing anything about your system, it usually points to the sql i/o
being greater then the disk system can maintain. In other words a request is
made and then retries until it gets a response.
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Marek Wierzbicki" <ma******************@azymuttttt.pl> wrote in message
news:dv***********@news2.ipartners.pl...

Hi

In my server (MSSQL 2000 sp4) very often I see long time of executing query with waittype=WRITELOG

Recovery is set to SIMPLE and there is no autoShrink.

Auto increment file size (log) is set to 10%

What can be the reason of this problem?

Marek

[Erland Sommarskog]

Marek Wierzbicki (ma******************@azymuttttt.pl) writes:

In my server (MSSQL 2000 sp4) very often I see long time of executing
query with waittype=WRITELOG

Recovery is set to SIMPLE and there is no autoShrink.

Auto increment file size (log) is set to 10%

What can be the reason of this problem?

On my machine at home I see six processes with *last*waittype = WRITELOG.
All are system spids.

As far as I know you can see the current wait type in clear text anyway,
but there is a binary column waittype in sysprocesses.

This long-running query, is it performing any updates?

And most importantly, do you perceive any real performance problems?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Marek Wierzbicki]

>> What can be the reason of this problem?

On my machine at home I see six processes with *last*waittype = WRITELOG.
All are system spids.

As far as I know you can see the current wait type in clear text anyway,
but there is a binary column waittype in sysprocesses.

This long-running query, is it performing any updates?

And most importantly, do you perceive any real performance problems?

I am looking for reason slowly works of my SQL

Marek

[Erland Sommarskog]

Marek Wierzbicki (ma******************@azymuttttt.pl) writes:

What can be the reason of this problem?

On my machine at home I see six processes with *last*waittype = WRITELOG.
All are system spids.

As far as I know you can see the current wait type in clear text anyway,
but there is a binary column waittype in sysprocesses.

This long-running query, is it performing any updates?

And most importantly, do you perceive any real performance problems?

I am looking for reason slowly works of my SQL

WRITELOG may have something to do with it, but it may also be a false
clue.

Since I don't know your system, I cannot give much direct advice. But
I would suggest to start to scrutinize queries and indexes,.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Marek Wierzbicki]

> I would suggest to start to scrutinize queries and indexes,.

I start do it from 2 weeks. After create indexes and join on indexed fields
is a little bit better. I will be
looking for next havy queries and optimize it.

Do you hear sth about
"periscope" - tolls for searching worst queries (or others such tools)? I
can't do it with profiler (all queries have zero execution time)

Marek

[Erland Sommarskog]

Marek Wierzbicki (ma******************@azymuttttt.pl) writes:

I start do it from 2 weeks. After create indexes and join on indexed
fields is a little bit better. I will be looking for next havy queries
and optimize it.

Do you hear sth about
"periscope" - tolls for searching worst queries (or others such tools)? I
can't do it with profiler (all queries have zero execution time)

If all queries execute in zero time, then you must have a good system. :-)
What value are looking at Duration or CPU? Does StartTime and EndTime
agree with the zero-time?

You can look at http://www.sqldev.net/xp/xphrtimer.htm. This download
permits you install a DLL, so you can get millisecond resolution in
Profiler. With the standard time, you cannot get durations below 16 ms.

Then again, if your queries are that quick, it is not likely that you
can get that much information, even with a better timer.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx