slow DNS caused extreme SQLS memory pressure+CPU?

[aj]

Microsoft SQL Server 2005 - 9.00.3054.00 (X64)
(Build 3790: Service Pack 2)

Last Friday we had a situation where the DNS system inside
our Active Directory server went weird. I know this isn't
a DNS or AD NG, but please bear with me...

The DNS server didn't really stop working - it was just taking
a looooooonnnggggg time to service requests. During this, the SQL
server (SQLS) engine had lots of (mostly jdbc) connections to web
servers, java apps, etc...

This DNS problem made SQL Server really upset. The CPU on the SQLS
box shot up to above 85% and stayed there. It was so jammed I could
not connect to the DB inside Mgmt Studio, or on the cmd-line. I even
tried DAC, with no response there either. The sqlserver.exe process
was taking all the CPU.

I tried to shut down the SQLS service in Control Panel. It said the
service was stopping....and it stayed like that for 20 minutes....all
with the CPU at 80% or so...

During this time, I got a bunch of really nasty looking OBJECTSTORE,
USERSTORE, CACHESTORE, and MEMORYCLERK errors I also saw LazyWriter: no
free buffers found and complaints about memory pressure. All of this
continued even after the DNS issue was fixed. I finally had to reboot
the server, and it went thru crash recovery. Its been fine since.

My question: how would you expect SQLS to respond if DNS suddenly
became unresponsive? Does it make sense that the CPU would shoot up
like that? Is something not configured properly? Why would
suddenly unresponsive DNS cause memory pressure?

Any thoughts appreciated..

aj

[aj]

btw - just so folks on the NG knows I'm not a complete idiot:

It looks very likely that I simply have a problem w/ not enough
RAM in my 64-bit SQL Server box. Our propensity for
non-parameterized ad-hoc sql, and other factors, is likely
causing plan cache bloat and general memory pressure. I think
this caused the CPU spike, and MS support is now involved.

The DNS problem occurring at almost the same time is simply
a coincidence, albeit a very, very odd one. I'm a freshly minted
SQL Server dba (lots of experience on *nix and other databases), so
some of this Windows server stuff is quite new..

Anyone have any advice about memory pressure issues on 64-bit SQL
Server, which seems to be a common thing?

thanks

cheers
aj

aj wrote:

Microsoft SQL Server 2005 - 9.00.3054.00 (X64)
(Build 3790: Service Pack 2)

Last Friday we had a situation where the DNS system inside
our Active Directory server went weird. I know this isn't
a DNS or AD NG, but please bear with me...

The DNS server didn't really stop working - it was just taking
a looooooonnnggggg time to service requests. During this, the SQL
server (SQLS) engine had lots of (mostly jdbc) connections to web
servers, java apps, etc...

This DNS problem made SQL Server really upset. The CPU on the SQLS
box shot up to above 85% and stayed there. It was so jammed I could
not connect to the DB inside Mgmt Studio, or on the cmd-line. I even
tried DAC, with no response there either. The sqlserver.exe process
was taking all the CPU.

I tried to shut down the SQLS service in Control Panel. It said the
service was stopping....and it stayed like that for 20 minutes....all
with the CPU at 80% or so...

During this time, I got a bunch of really nasty looking OBJECTSTORE,
USERSTORE, CACHESTORE, and MEMORYCLERK errors I also saw LazyWriter: no
free buffers found and complaints about memory pressure. All of this
continued even after the DNS issue was fixed. I finally had to reboot
the server, and it went thru crash recovery. Its been fine since.

My question: how would you expect SQLS to respond if DNS suddenly
became unresponsive? Does it make sense that the CPU would shoot up
like that? Is something not configured properly? Why would
suddenly unresponsive DNS cause memory pressure?

Any thoughts appreciated..

aj

[Erland Sommarskog]

aj (ro****@mcdonalds.com) writes:

btw - just so folks on the NG knows I'm not a complete idiot:

It looks very likely that I simply have a problem w/ not enough
RAM in my 64-bit SQL Server box. Our propensity for
non-parameterized ad-hoc sql, and other factors, is likely
causing plan cache bloat and general memory pressure. I think
this caused the CPU spike, and MS support is now involved.

The DNS problem occurring at almost the same time is simply
a coincidence, albeit a very, very odd one. I'm a freshly minted
SQL Server dba (lots of experience on *nix and other databases), so
some of this Windows server stuff is quite new..

Anyone have any advice about memory pressure issues on 64-bit SQL
Server, which seems to be a common thing?

Actually, from what I have heard, the cachebloat thing more often happens
when you have lots of RAM. With a small amount of RAM, plans get aged out
the cache quickly. With a huge amount, they linger longer, and you can
get hash collisions.

If you have a lot of non-parameterised ad hoc-sql, ALTER DATABASE SET
PARAMETERIZATION FORCED can help. You will have to pray that you don't
become a victim of SQL injection, though.

There are also some issues with temp-table caching, and there are
some hotfixes that addresses these problems, and I guess MS will recommend
you to apply any of these.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[aj]

Thanks Erland.

We are installing 32 gigs of RAM on the server this weekend.
Is it generally recommended to set the maximum server memory (on
the Server Properties->Memory page) to something less than 32 gigs,
or leave it at the absurdly high default setting? (which I think
is like 2 petabytes)

So with ALTER DATABASE SET PARAMETERIZATION FORCED turned on,
the engine will take non-parameterized sql and parameterize
it? So that you don't get a bunch of similar master..syscacheobjects
rows w/ a USECOUNTS of 1?

aj

Erland Sommarskog wrote:

aj (ro****@mcdonalds.com) writes:

>btw - just so folks on the NG knows I'm not a complete idiot:

It looks very likely that I simply have a problem w/ not enough
RAM in my 64-bit SQL Server box. Our propensity for
non-parameterized ad-hoc sql, and other factors, is likely
causing plan cache bloat and general memory pressure. I think
this caused the CPU spike, and MS support is now involved.

The DNS problem occurring at almost the same time is simply
a coincidence, albeit a very, very odd one. I'm a freshly minted
SQL Server dba (lots of experience on *nix and other databases), so
some of this Windows server stuff is quite new..

Anyone have any advice about memory pressure issues on 64-bit SQL
Server, which seems to be a common thing?

Actually, from what I have heard, the cachebloat thing more often happens
when you have lots of RAM. With a small amount of RAM, plans get aged out
the cache quickly. With a huge amount, they linger longer, and you can
get hash collisions.

If you have a lot of non-parameterised ad hoc-sql, ALTER DATABASE SET
PARAMETERIZATION FORCED can help. You will have to pray that you don't
become a victim of SQL injection, though.

There are also some issues with temp-table caching, and there are
some hotfixes that addresses these problems, and I guess MS will recommend
you to apply any of these.

[Erland Sommarskog]

aj (ro****@mcdonalds.com) writes:

We are installing 32 gigs of RAM on the server this weekend.
Is it generally recommended to set the maximum server memory (on
the Server Properties->Memory page) to something less than 32 gigs,
or leave it at the absurdly high default setting? (which I think
is like 2 petabytes)

I tend to prefer to always cap the memory, because I've seen some
funky behaviour. 28 GB sounds good in this case. But if you later
add more memory, and forget to update the settingm you lose.

So with ALTER DATABASE SET PARAMETERIZATION FORCED turned on,
the engine will take non-parameterized sql and parameterize
it? So that you don't get a bunch of similar master..syscacheobjects
rows w/ a USECOUNTS of 1?

They still cache a shell query for some reason, so you will still see
these USECOUNT = 1.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx