Re: slow DNS caused extreme SQLS memory pressure+CPU?

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


.

Relevant Pages

  • Re: Performance problems -- need guidance on scaling
    ... So we're now CPU ... I have read that putting the catalog and SQL table on separate RAID ... Our current server will support only a single CPU. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: NewBie ASP.NET on a PDC securiy sugestions
    ... are about 25, I am running SQL, and imail, and DNS on the server. ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Very Small CPU use by SQL Server 2000 on Win2003
    ... Run profiler on the server an see at what rate the queries are coming ... run Windows Performance monitor and check if you are not maxing out ... Low CPU = Disks can't keep up ... Mike Epprecht, Microsoft SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Renaming cluster
    ... DNS refresh times can bite you. ... Senior SQL Infrastructure Consultant ... Microsoft SQL Server MVP ... > cluster and retire the SQL 2000 server. ...
    (microsoft.public.sqlserver.clustering)
  • RE: hardware requirements per hits
    ... if I know how many hits will make me upgrade cpu and memory then I can ... resolving server, if you are using DNSSEC, etc. ... are your running anything else on the same platform as your DNS ...
    (comp.protocols.dns.bind)