D. Buck (triumpht5@yahoo.com) writes:
[quoted text, click to view] > First question:
> Since I have Dynamic Memory Managment setup, Is it usuall that the
> sqlsrv.exe process on the server steadily climbs and is in the 800 to
> 900 MB range. There is only about 20 MB free. In theory this is how
> DMM can work, but do people really see it work this way.
This is perfectly normal. The more data SQL Server can hold in cache, the
better chances for a swift response to queries.
[quoted text, click to view] > Second question:
> I had users complaining about lockups in the app I have to support
> that connects to this dB. At first I thought it was the large use of
> memory, but once I was able to see in Enterprise Manager that there
> was process blocking several other processes. EM then locked up and I
> couldn't get to the details of what the exact process was that was
> doing the blocking. After restarting SQL services things were fine.
> When I checked the logs there was nothing there about a hung process.
> The logs seemed very sparse. Why would there not be anything in the
> logs about it. The logs actually seem very thin on any information.
SQL Server does not log blocking situations. You can set some trace
flags to log deadlock situations, but the situation you describe does
not seem to have been a case of deadlock.
EM is not a very good tool to analyse blocking situations, because if
there is a process that has created a temp table in a transaction, that
will block a stored procedure that EM uses.
It's better to use the stored procedures sp_who and sp_who2, together
with sp_lock and DBCC INPUTBUFFER to see what is going. Sometimes this
information is sufficient, but at times it can be difficult to digest,
because there is a lot of numeric object ids that you need to translate.
As an alternative, I offer aba_lockinfo, which gathers snapshot information
about a blocking situations, so that you can quickly identify the
offending processes and kill these without having to stop SQL Server.
You can also analyse the output later, to see what the cause for the
blocking was. You find aba_lockinfo at
http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html.
--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se
Books Online for SQL Server SP3 at
