SQL server thrashing?

[sql_server_user]

I have a loading process that periodically inserts a lot of relatively
large files (up to 400MB) into a few substantial tables (about 50GB).
Sometimes, after inserting many files with no problem, the DB server
memory usage will suddenly jump from about 4.5GB to about 5.5GB (the
server has 4GB physical), the normally heavy disk activity will stop,
and the normally low CPU utilization will spike to about 90%, and stay
there, and the DB will become totally unresponsive to any connection
attempt - sqlcmd dedicated admin connections included.

Has anyone seen this before? Does anyone have any idea what it might
be, or how I can avoid it? If I kill the sqlserver process and start it
up again, which entails a lengthy recovery process, I incur an
unacceptable service outage.

Thanks,
Seth

[Erland Sommarskog]

Re: SQL server thrashing?

sql_server_user (kaioptera@gmai l.com) writes:[color=blue]
> I have a loading process that periodically inserts a lot of relatively
> large files (up to 400MB) into a few substantial tables (about 50GB).
> Sometimes, after inserting many files with no problem, the DB server
> memory usage will suddenly jump from about 4.5GB to about 5.5GB (the
> server has 4GB physical), the normally heavy disk activity will stop,
> and the normally low CPU utilization will spike to about 90%, and stay
> there, and the DB will become totally unresponsive to any connection
> attempt - sqlcmd dedicated admin connections included.
>
> Has anyone seen this before? Does anyone have any idea what it might
> be, or how I can avoid it? If I kill the sqlserver process and start it
> up again, which entails a lengthy recovery process, I incur an
> unacceptable service outage.[/color]

That sounds bad. Not the least that the SQL Server process gets more
virtual memory that the available physical memory. And it sounds even
worse that the dedicated admin connection does not provide access.

It's impossible to tell what might be going on, but it smells bug to
me. I urge to open a case with Microsoft on this, not the least they
need to know about it.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[sql_server_user]

Re: SQL server thrashing?

Thanks, Erland. I'll look into it further.