July 29, 2008 at 7:59 am
SQL 2005 Sp2 32bit on Server 2003 Sp1 4-way 36 Gigs of RAM.
~terabyte mixed OLTP/DSS data ~500 concurrent user base.
Off of a fresh startup system runs well for about a week. Suddenly, processors start averaging 50% and SQL compilations suddenly escalates, prformance degrades noticeably. If allowed to continue, about another week, processors hike up to 70% and production goes down.
Server task manager indicates sqlservr.exe has all the cpu
SQL shows spid #1 as process consuming most cpu.
No errors in event log on server.
Only clue possibly in SQL logs is........"Message
Downgrading backup log buffers from 1024K to 64K" that seems to correlate with cpu escalation event.
Help will be appreciated
Paul D Ensor Sr.DBA
July 29, 2008 at 8:07 am
Did you configure your memory properly during installation of SQL 2005?
1. How much RAM do you have in that server?
2. What is your server specification?
3. Also what is your SQL Server edition? Any service packs applied?
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
July 29, 2008 at 8:09 am
SQL 2005 Sp2 32bit on Server 2003 Sp1 4-way 36 Gigs of RAM.
already stated in post?
AWE enabled /3gb switch in boot.ini
July 29, 2008 at 8:27 am
Well, sorry for that. Did not notice it since it was all the way on top. Anyway, what SQL Server edition? (Enterprise? Standard?)
Also, when I said RAM, how much is it allocated for SQL Server to use?
To check this:
sp_configure
under that, look at AWE enabled (does it show 1 on the max)
also look at the max server memory MB (this should be configured near to your memory value for SQL Server to take advantage of "so much" RAM in the server otherwise there is no point of having so much of RAM because by default, SQL Server uses the default value which is only around 2GB)---What a waste if you have not been using it (in your case, 36GB)
Check all these on your server first...
By the way, is there any other application other than SQL in that particular server?
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
July 29, 2008 at 9:05 am
Enterprise Edition. AWE is enabled. 30 Gigs Max Memory 10 Gigs Min.
July 29, 2008 at 9:09 am
Have you run a trace (from Profiler or your own) to find what that SPID is doing when it starts eating up all the RAM?
Is it possible that there's an outside DLL (CLR proc/function maybe) that's being called and not cleaned up?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2008 at 9:42 am
Have run several traces. No working process seems to add up to the CPU cycles during the problem.
No external executeables appear in the Task Manager process list, only sqlsvr.exe as most of CPU consumption.
July 29, 2008 at 1:55 pm
An external DLL, like a CLR function/proc, might not show up as a separate process from SQL Server.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2008 at 4:16 pm
Have you checked
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=236203&SiteID=1
Try using the view written by Slava Oks under the below mentioned link:
Tracking VAS in Yukon:
http://blogs.msdn.com/slavao/archive/2005/01/26/360759.aspx
Hope it provides the necessary info about VAS status on your 2005 instance.
Some helpful points available at
http://www.sqlservercentral.com/Forums/Topic432976-360-1.aspx
Manu
July 29, 2008 at 4:23 pm
What Windows edition are you running: Standard, Enterprise, etc.
July 30, 2008 at 8:28 am
Thank you. Links were very useful.
We had identified during the incident that the available memory in VAS was greatly diminished, and the available block size was much smaller than those on the server we were monitoring as a control.
The question is, what caused the memory in the VAS to be so completely consumed that it was forced to use much smaller block sizes which resulted in a drastic escalation of raw SQL compilations?
July 30, 2008 at 8:35 am
I used procmon.exe to break down the individual threads of the exe. I didnt recognize anything strange but will admit it sure was alot of information. None of the developers here think that any custom dll or clr ops are running.
July 30, 2008 at 8:37 am
Microsoft Windows Server 2003 Enterprise Edition Service Pack 1
July 30, 2008 at 8:50 am
pensor (7/30/2008)
I used procmon.exe to break down the individual threads of the exe. I didnt recognize anything strange but will admit it sure was alot of information. None of the developers here think that any custom dll or clr ops are running.
Use Process Explorer from Microsoft(or SysInternals) to get the list of DLLs included in the sqlserver.exe process.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 30, 2008 at 2:39 pm
Are you making use of linked servers a lot? Other way round, are there distributed queries that are running from this server?
What value do you see under Process object(Virtual Bytes counter for sql server instance) in performance monitor?
Manu
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply