April 3, 2008 at 1:05 pm
One of the stranger issues I have seen in a while.
Server is a SQL 2005 SP2 (on W2K3) on active/active cluster. Currently instance1 is on node1 and instance2 is on node2. Problem is occurring on instance1 only (not on instance2) or any other instance I have ever seen. Instance1 is configured to use 2 GB of RAM (even though there's 4GB on the box). I have thought about rebooting and throwing the /3GB switch in, but it is a production box and further research shows that this likely won't help.
So, I notice CPU is very high (don't ask when it changed, this is my first week on the new job), and I begin troubleshooting. Sure enough as expected I discover via perfmon that "SQL Compilations /sec" is directly the cause on the high CPU.
I run a trace (capturing all SP:CacheInsert, SP:CacheMiss, SP:CacheRemove, SP:CacheHit)... and this is where it starts getting weird. I see SP:CacheInsert for every SQL statement that is executed and every stored procedure that is called.
I run a simple "Select * from dbo.table" in my dba database multiple times and each time it compiles and the trace shows a SP:CacheInsert. All SQL statements are doing this.... NOT just the normal offenders (dynamic SQL, not fully quallified stuff, etc).
So then I look in sys.syscacheobjects and see between 3 and 8 rows depending on when I run the select * from sys.syscacheobjects.
I notice via perfmon that Memory Manager (SQL Cache Memory (KB) is 336! I think "WTF?".
So I think... wow buffer cache must be hammered, thus taking away from proc cache memory. Wrong. Buffer cache hit ratio is 99.8% + and page life expectancy is running constant at 181000.
So, now, after 3 hours of troubleshooting, I am here asking my fellow SQL gurus for advice.
A.J.
DBA with an attitude
April 4, 2008 at 1:19 am
32 bit or 64 bit?
Run this, and see what the cache sizes are.
SELECT name, SUM(single_pages_kb + multi_pages_kb)/1024 AS SizeInKB
FROM sys.dm_os_memory_clerks
GROUP BY name
In profiler, have a look at the Target and Total server memory. SQL may be allocated 2GB, but there's no guarentee that it will use 2GB
Is this ad-hoc code of stored procs? Do the objects referenced in the queries have the schemas specified? (dbo.object or just object)
Is the data these queries depend on changing rapidly?
Are any of the queries specified WITH RECOMPILE?
Do you have anything calling sp_recompile at all?
If you look in the error log, do you see any messages about cache store flush?
What does @@version return?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2008 at 3:01 am
In addition to Gail's advise:
- Did someone enable PARAMETERIZATION FORCED for some or all databases (maybe even model db)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 4, 2008 at 9:22 am
So, I discovered what this was eventually, and thought I'd post it here. Like I said the procedure cache was < 1 MB and thus causing everything to compile every time. The reason for this is yes... what you were all thinking ...MemToLeave.
So, this server does alot of Linked Server calls as well as using maintenance plans. We all know that the default 256 MB for MemToLeave can not be enough for servers that do lots of Linked Server calls, use of sp_OA, extended stored procedures, etc.
I have seen MemToLeave problems before, but not in this fashion. The key to figuring this out was a single informational message in the SQL Server log of "Downgrading backup log buffers from 1024K to 64K" when running Tlog backups.
So we added the -g512 switch and did a failover and failed back to the original box and all is good now.
A.J.
DBA with an attitude
April 4, 2008 at 10:48 am
Thank you for the feedback.
Add it to the list of reasons NOT to use linked servers 😉
cfr http://www.sqlservercentral.com/articles/Linked+Servers/62246/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 6, 2008 at 11:59 pm
Any IBM DB2 linked servers by any chance?
As a possible other option, you can run linked servers out of process. Then they get their own memory spaces and won't hog the memtoleave.
It's a configuration option on the driver. In management studio - Server object - Linked servers - providers.
Not all work out of process, but it may be worth a try
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply