Processes locking themselves after SP4

Hi All,

We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4.  Users are complaining that the system is running slowly, looking a
t
the processes, the active processes appear to be blocking themselves.  The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.

Any ideas what might be causing the blocking?  I've checked for lock
escalation (using profiler) and we're not experiencing it!

--
Dave Wall

Are you using the very latest SP4 for SQL2K? Does your system use RAM
above 4GB and is AWE-enabled?

This may not be the issue I know but better to check rather than
assume. I know the first SP4 release problems didn't necessarily
manifest in the way your system is but you can never be sure ....

ALI

We're using the patch on SP4 to allow us to run AWE with RAM of 5GB
(restricted from 8GB)

Dave

--
Dave Wall


"zashah@gmail.com" wrote:

> Are you using the very latest SP4 for SQL2K? Does your system use RAM
> above 4GB and is AWE-enabled?
>
> This may not be the issue I know but better to check rather than
> assume. I know the first SP4 release problems didn't necessarily
> manifest in the way your system is but you can never be sure ....
>
> ALI
>
>

Hi

Have a look at this explanation by Santeri Voutilainen  from MSFT

http://groups-beta.google.com/group...
513ab281?hl=en

--
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Dave Wall" < DaveWall@discussions
.microsoft.com> wrote in message
news:B746F00D-1908-4D9B-9934- F35F24304D54@microso
ft.com...
> Hi All,
>
> We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
> 200 SP4.  Users are complaining that the system is running slowly, looking
> at
> the processes, the active processes appear to be blocking themselves.  The
> configuration of SQL is identical to before and the hardware is the same,
> just the OS and the SP version of SQL has changed.
>
> Any ideas what might be causing the blocking?  I've checked for lock
> escalation (using profiler) and we're not experiencing it!
>
> --
> Dave Wall

Processes appearing to lock themselves is a change in reporting in SP4,
not an actual issue. Basically the process reports itself as a blocking
process when it goes to grab disk allocation (as I understand it). The
Wait Type should display as PAGELATCH IO if I recall correctly.

Took me by surprise too but it isn't an actual blocked process.

As far as performance goes, I haven't seen any performance hits on
databases up to 400 GB in size.

Dave Wall  wrote:
> *Hi All,
>
> We've recently rebuilt our failover cluster to Windows 2003 and SQL
> Server
> 200 SP4.  Users are complaining that the system is running slowly,
> looking at
> the processes, the active processes appear to be blocking themselves.
> The
> configuration of SQL is identical to before and the hardware is the
> same,
> just the OS and the SP version of SQL has changed.
>
> Any ideas what might be causing the blocking?  I've checked for lock
> escalation (using profiler) and we're not experiencing it!
>
> --
> Dave Wall *



--
Kyle Quinby
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message1756427.html

Thanks Kyle,

and thanks to everyone else for your input.  Turns out that while our
networking team assured me that the server had been set up to use PAE it
wasn't entriely true. Once it was set and the server rebooted, SQL started t
o
use the additional memory, IO dropped to nothing (as it can now hold around
1/3 of the DB in memory) and the blocking spids disappeared.

Thanks again for everyones input!

Dave
--
Dave Wall


"Kyle Quinby" wrote:

>
> Processes appearing to lock themselves is a change in reporting in SP4,
> not an actual issue. Basically the process reports itself as a blocking
> process when it goes to grab disk allocation (as I understand it). The
> Wait Type should display as PAGELATCH IO if I recall correctly.
>
> Took me by surprise too but it isn't an actual blocked process.
>
> As far as performance goes, I haven't seen any performance hits on
> databases up to 400 GB in size.
>
> Dave Wall wrote: 
>
>
>
> --
> Kyle Quinby
> ------------------------------------------------------------------------
> Posted via http://www.mcse.ms
> ------------------------------------------------------------------------
> View this thread: http://www.mcse.ms/message1756427.html
>
>