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
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
>
|||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.
Quote:
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
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@.microsoft.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
|||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 to
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
>
