473,549 Members | 2,545 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 looooooonnngggg g 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
Nov 17 '08 #1
4 2888
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 looooooonnngggg g 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

Nov 19 '08 #2
aj (ro****@mcdonal ds.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
PARAMETERIZATIO N 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****@sommarsk og.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

Nov 19 '08 #3
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 PARAMETERIZATIO N FORCED turned on,
the engine will take non-parameterized sql and parameterize
it? So that you don't get a bunch of similar master..syscach eobjects
rows w/ a USECOUNTS of 1?

aj

Erland Sommarskog wrote:
aj (ro****@mcdonal ds.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
PARAMETERIZATIO N 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.
Nov 19 '08 #4
aj (ro****@mcdonal ds.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 PARAMETERIZATIO N FORCED turned on,
the engine will take non-parameterized sql and parameterize
it? So that you don't get a bunch of similar master..syscach eobjects
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****@sommarsk og.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

Nov 20 '08 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1486
by: Brian Henry | last post by:
I know some of you have seen this problem. Stepping through code in the debugger is slow as heck in Visual Studio 2005. Well here is the problem, Edit and continue. Because edit and continue does run time compileing of code as it executes, it causes a big slow down in execution time. To disable this and return the debugger to the normal speed...
1
3783
by: GaŽl | last post by:
Hi everybody! I have a really big problem with ASP.NET application. I noticed that the w3wp.exe memory size, increase with the time and the use of my website. When it raise a certain value, w3wp crashes and restart just after. My application is on a WebServer2003. So I have to resolve 2 problem : -Why the memory size of the w3wp increase...
14
1733
by: Andrew Bullock | last post by:
Hi, I had this problem before I reinstalled Windows XP SP2 (fully patched), but I cant remember what I had to do to fix it :( I have a loop which scans every pixel in a series of images. When compiled it completes 10 images in about 6 seconds, which is acceptable and as expected. When i run it through VS it does about 1 frame every 10...
14
9568
by: Michel Esber | last post by:
Linux RH 4.0 running DB2 V8 FP 11. I have a table with ~ 11M rows and running DELETE statements is really slow. Deleting 1k rows takes more than 3 minutes. If I run select statements on the same table, I usually fetch rows in a reasonable time. The table has the following description: MACHINE_ID VARCHAR (24)
0
1523
by: CptDondo | last post by:
I have a website that is generated on the fly from XML templates. I've been having some performance issues (pages taking 6 seconds or more to be generated) and profiled the php code. (This is an embedded system with a slow CPU.) It turns out that php::xml_parse is very, very slow. My XML stuff isn't that complex, but only 4 calls to...
5
3637
by: Jason | last post by:
Hello, I am testing a complex J2EE application on WebSphere and UDB V8.2.3. Because of the compliexity of the application, we have to run it a few hours to finish all test scenarios. One of our test objectives is to identify the top 100 most expensive SQLs (based on CPU time, the total executive time, and the number of executions). So I...
9
3811
by: =?Utf-8?B?YzY3NjIyOA==?= | last post by:
Hi all, Today, an interesting thing happened to me. When I am doing the test of an asp program, one of the pages(which I submitted several ten times before, no problem at all) caused our server cpu rising to 100% and I reset IIS and submit that page again and same thing happened again, and then reset IIS again and it happened again, the...
66
3650
by: karthikbalaguru | last post by:
Hi, Will 'free' return the memory Immediately to the OS ? Thx in advans, Karthik Balaguru
7
1492
by: Danny Ni | last post by:
Hi, I inherited a web project from other programmer. I notice on my dev machine every time I request a page in IE or FF the CPU jumps to 100%, all utilized by asp_wp.exe. I did some researches on the web, somebody suggests this could be due to database connection not properly disposed. I happened to see the following code snippet in...
0
7526
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main...
0
7457
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7723
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7483
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
5092
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3504
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3487
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1949
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
771
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.