High Log flush Wait Time

Hello,
We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
to an HP EVA 3000.
4 CPU with HyperTh enabled. 8 GB of RAM
Sqlserver 2000 (SP3a) with 180 Gb of DB.
Since 2 weeks, we have slow performance but CPU are not stressed.
We have High Log flush Wait time (>2000 ms) and high latch wait time (800
ms)
Anyone as an idea ?
THX
Message posted via http://www.sqlmonster.com
You probably also have a high disk queue length on the disk(s) where yout
LOG device lives. Make sure yout LOG files are on a RAID 1 or 1+0 device
with no other data files on those disks. This must be a physical disk set,
not just a logical partition of an underlying shared RAID set.
What is happening is that your SQL server cannot write log files to the disk
in a timely manner. SQL will not process a transaction unless the
write-ahead transaction log has the start transaction marker committed to
disk. Thus, your CPUs are stuck waiting on this particular disk function.
Therefore, you make log writes function as fast as possible. Since logs are
written sequentially in relatively small chunks, RAID 1 or1+ 0 is ideal.
RAID 5 will cause performance bottlenecks on log devices.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"John via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:61f18ba8c87443539797e121a8dd7f14@.SQLMonster.c om...
> Hello,
> We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
> to an HP EVA 3000.
> 4 CPU with HyperTh enabled. 8 GB of RAM
> Sqlserver 2000 (SP3a) with 180 Gb of DB.
> Since 2 weeks, we have slow performance but CPU are not stressed.
> We have High Log flush Wait time (>2000 ms) and high latch wait time (800
> ms)
> Anyone as an idea ?
> THX
> --
> Message posted via http://www.sqlmonster.com

High Log flush Wait Time

Hello,
We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
to an HP EVA 3000.
4 CPU with HyperTh enabled. 8 GB of RAM
Sqlserver 2000 (SP3a) with 180 Gb of DB.
Since 2 weeks, we have slow performance but CPU are not stressed.
We have High Log flush Wait time (>2000 ms) and high latch wait time (800
ms)
Anyone as an idea ?
THX
--
Message posted via http://www.sqlmonster.comYou probably also have a high disk queue length on the disk(s) where yout
LOG device lives. Make sure yout LOG files are on a RAID 1 or 1+0 device
with no other data files on those disks. This must be a physical disk set,
not just a logical partition of an underlying shared RAID set.
What is happening is that your SQL server cannot write log files to the disk
in a timely manner. SQL will not process a transaction unless the
write-ahead transaction log has the start transaction marker committed to
disk. Thus, your CPUs are stuck waiting on this particular disk function.
Therefore, you make log writes function as fast as possible. Since logs are
written sequentially in relatively small chunks, RAID 1 or1+ 0 is ideal.
RAID 5 will cause performance bottlenecks on log devices.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"John via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:61f18ba8c87443539797e121a8dd7f14@.SQLMonster.com...
> Hello,
> We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
> to an HP EVA 3000.
> 4 CPU with HyperTh enabled. 8 GB of RAM
> Sqlserver 2000 (SP3a) with 180 Gb of DB.
> Since 2 weeks, we have slow performance but CPU are not stressed.
> We have High Log flush Wait time (>2000 ms) and high latch wait time (800
> ms)
> Anyone as an idea ?
> THX
> --
> Message posted via http://www.sqlmonster.com

High Log flush Wait Time

Hello,
We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
to an HP EVA 3000.
4 CPU with HyperTh enabled. 8 GB of RAM
Sqlserver 2000 (SP3a) with 180 Gb of DB.
Since 2 weeks, we have slow performance but CPU are not stressed.
We have High Log flush Wait time (>2000 ms) and high latch wait time (800
ms)
Anyone as an idea ?
THX
Message posted via http://www.droptable.comYou probably also have a high disk queue length on the disk(s) where yout
LOG device lives. Make sure yout LOG files are on a RAID 1 or 1+0 device
with no other data files on those disks. This must be a physical disk set,
not just a logical partition of an underlying shared RAID set.
What is happening is that your SQL server cannot write log files to the disk
in a timely manner. SQL will not process a transaction unless the
write-ahead transaction log has the start transaction marker committed to
disk. Thus, your CPUs are stuck waiting on this particular disk function.
Therefore, you make log writes function as fast as possible. Since logs are
written sequentially in relatively small chunks, RAID 1 or1+ 0 is ideal.
RAID 5 will cause performance bottlenecks on log devices.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"John via droptable.com" <forum@.droptable.com> wrote in message
news:61f18ba8c87443539797e121a8dd7f14@.SQ
droptable.com...
> Hello,
> We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
> to an HP EVA 3000.
> 4 CPU with HyperTh enabled. 8 GB of RAM
> Sqlserver 2000 (SP3a) with 180 Gb of DB.
> Since 2 weeks, we have slow performance but CPU are not stressed.
> We have High Log flush Wait time (>2000 ms) and high latch wait time (800
> ms)
> Anyone as an idea ?
> THX
> --
> Message posted via http://www.droptable.comsql

Hiding instances on the n/w (Port 1434)

Hi,
I tried to hide an instance of MSSQL2000 SP3 on the n/w by first removing
all the enabled protocols using the server network utility which worked fine,
i then renabled the TCP/IP protocol and selected hide server which seemed to
switch the port number to 2433, which i understand has an implication for
clients regarding connection strings including port numbers, aliasing etc.
I then switched the port number back to 1433 and de-selected the hide server
option but now i can connect to the machine using query analyzer when i know
the machine name but if i try to hit the button which lists all servers on
the n/w the server does not appear. I believe this list is populated based on
a n/w port scan which builds the list based on machines that respond to a
request on UDP port 1434.
If anyone knows why the server will not appear i would appreciate a pointer,
i have also attempted to find help about blocking on port 1434 but cannot
find help on how to do so, keep getting instructions on blocking at the
firewall, i just want to understand how this works.
Thanks in advance
Pat
Pmcg,
You cannot prevent SQL Server listening on port 1434. If you want to prevent
clients accessing this port, you have no choice but to use a firewall.
SQL Server's enumeration mechanism can only list servers that are registered
in Enterprise Manager, and ones that are on your subnet - the broadcast does
not work across routers or firewalls. Perhaps this is the problem.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Pmcg" wrote:

> Hi,
> I tried to hide an instance of MSSQL2000 SP3 on the n/w by first removing
> all the enabled protocols using the server network utility which worked fine,
> i then renabled the TCP/IP protocol and selected hide server which seemed to
> switch the port number to 2433, which i understand has an implication for
> clients regarding connection strings including port numbers, aliasing etc.
> I then switched the port number back to 1433 and de-selected the hide server
> option but now i can connect to the machine using query analyzer when i know
> the machine name but if i try to hit the button which lists all servers on
> the n/w the server does not appear. I believe this list is populated based on
> a n/w port scan which builds the list based on machines that respond to a
> request on UDP port 1434.
> If anyone knows why the server will not appear i would appreciate a pointer,
> i have also attempted to find help about blocking on port 1434 but cannot
> find help on how to do so, keep getting instructions on blocking at the
> firewall, i just want to understand how this works.
>
> Thanks in advance
> Pat

Hiding instances on the n/w (Port 1434)

Hi,
I tried to hide an instance of MSSQL2000 SP3 on the n/w by first removing
all the enabled protocols using the server network utility which worked fine
,
i then renabled the TCP/IP protocol and selected hide server which seemed to
switch the port number to 2433, which i understand has an implication for
clients regarding connection strings including port numbers, aliasing etc.
I then switched the port number back to 1433 and de-selected the hide server
option but now i can connect to the machine using query analyzer when i know
the machine name but if i try to hit the button which lists all servers on
the n/w the server does not appear. I believe this list is populated based o
n
a n/w port scan which builds the list based on machines that respond to a
request on UDP port 1434.
If anyone knows why the server will not appear i would appreciate a pointer,
i have also attempted to find help about blocking on port 1434 but cannot
find help on how to do so, keep getting instructions on blocking at the
firewall, i just want to understand how this works.
Thanks in advance
PatPmcg,
You cannot prevent SQL Server listening on port 1434. If you want to prevent
clients accessing this port, you have no choice but to use a firewall.
SQL Server's enumeration mechanism can only list servers that are registered
in Enterprise Manager, and ones that are on your subnet - the broadcast does
not work across routers or firewalls. Perhaps this is the problem.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Pmcg" wrote:

> Hi,
> I tried to hide an instance of MSSQL2000 SP3 on the n/w by first removing
> all the enabled protocols using the server network utility which worked fi
ne,
> i then renabled the TCP/IP protocol and selected hide server which seemed
to
> switch the port number to 2433, which i understand has an implication for
> clients regarding connection strings including port numbers, aliasing etc.
> I then switched the port number back to 1433 and de-selected the hide serv
er
> option but now i can connect to the machine using query analyzer when i kn
ow
> the machine name but if i try to hit the button which lists all servers on
> the n/w the server does not appear. I believe this list is populated based
on
> a n/w port scan which builds the list based on machines that respond to a
> request on UDP port 1434.
> If anyone knows why the server will not appear i would appreciate a pointe
r,
> i have also attempted to find help about blocking on port 1434 but cannot
> find help on how to do so, keep getting instructions on blocking at the
> firewall, i just want to understand how this works.
>
> Thanks in advance
> Pat

Hiding instances on the n/w (Port 1434)

Hi,
I tried to hide an instance of MSSQL2000 SP3 on the n/w by first removing
all the enabled protocols using the server network utility which worked fine,
i then renabled the TCP/IP protocol and selected hide server which seemed to
switch the port number to 2433, which i understand has an implication for
clients regarding connection strings including port numbers, aliasing etc.
I then switched the port number back to 1433 and de-selected the hide server
option but now i can connect to the machine using query analyzer when i know
the machine name but if i try to hit the button which lists all servers on
the n/w the server does not appear. I believe this list is populated based on
a n/w port scan which builds the list based on machines that respond to a
request on UDP port 1434.
If anyone knows why the server will not appear i would appreciate a pointer,
i have also attempted to find help about blocking on port 1434 but cannot
find help on how to do so, keep getting instructions on blocking at the
firewall, i just want to understand how this works.
Thanks in advance
PatPmcg,
You cannot prevent SQL Server listening on port 1434. If you want to prevent
clients accessing this port, you have no choice but to use a firewall.
SQL Server's enumeration mechanism can only list servers that are registered
in Enterprise Manager, and ones that are on your subnet - the broadcast does
not work across routers or firewalls. Perhaps this is the problem.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Pmcg" wrote:
> Hi,
> I tried to hide an instance of MSSQL2000 SP3 on the n/w by first removing
> all the enabled protocols using the server network utility which worked fine,
> i then renabled the TCP/IP protocol and selected hide server which seemed to
> switch the port number to 2433, which i understand has an implication for
> clients regarding connection strings including port numbers, aliasing etc.
> I then switched the port number back to 1433 and de-selected the hide server
> option but now i can connect to the machine using query analyzer when i know
> the machine name but if i try to hit the button which lists all servers on
> the n/w the server does not appear. I believe this list is populated based on
> a n/w port scan which builds the list based on machines that respond to a
> request on UDP port 1434.
> If anyone knows why the server will not appear i would appreciate a pointer,
> i have also attempted to find help about blocking on port 1434 but cannot
> find help on how to do so, keep getting instructions on blocking at the
> firewall, i just want to understand how this works.
>
> Thanks in advance
> Pat