March 11, 2011 at 6:45 am
We made two changes to our system this past Sunday. Installed Sql SP4 and a Systems network change:"Cluster is now functioning on MS ISCSI Initiator configuration. No longer using the HBA offload cards."
SP4 had been tested on dev boxes so I'm sure that was not the problem. The various changes required numerous restarts so, in retrospect, it seems some bad plans got "stuck" in procedure/plan cache and when normal Monday morning load arrived, the CPU spiked and stayed there.
The upside was that our high IO problem was vastly improved. Eventually freeProcCache solved the cpu problem, so I'm looking for sort of a checklist as when this is likely to be the solution.
SQL 2005 64-bit SP4 Enterprise on active/passive cluster running Server 2008R2 64-bit, 128GB RAM with sql allowed 82GB.
The HBA cards connecting to the Netapp storage device were disabled.
March 11, 2011 at 7:50 am
From your description, it seems to me that things got different from your analysis.
Probably, before restarts the GOOD plans were cached and you lost them restarting the service.
When the users started firing statements, the new plans had to be compiled, but probably stats were stale and you got bad plans.
In other words, I think the question should be: "How to know when I need to run index/stats maintenance?"
I might be completely wrong, but, without additional details, this is what I suspect happened to your server.
Hope this helps
Gianluca
-- Gianluca Sartori
March 11, 2011 at 7:51 am
In general... I'd say that running FreeProcCache is almost never a good solution. If you rebooted the machine, the procedure cache was flushed. That is not maintained from one startup to another, so it's unlikely that you had bad plans "stuck" from the restart. What's more likely is that you have some bad parameter sniffing going on and query plans were created from parameters that were not optimized for the normal traffic. In that case a targeted recompile of the plans in question is a good approach, but nuking the entire cache is not something I'd recommend. You'll also want to identify and fix the problem that's causing the bad parameter sniffing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2011 at 8:22 am
Update stats was run just prior to this past Sunday's systems work, as always. We use a commonly available stored procedure for this ( this one I believe http://www.sqlstatistics.com/page/2/ )
Any statistics needing update are done with fullscan. Most sql against this system is generated by an ORM. It's pretty complex looking stuff with lots of parameters passed in. Looks like I need to review parameter sniffing as well as how to identify which cached plan needs to be recompiled ( and how to recompile it )
March 11, 2011 at 8:26 am
I implement the advise Kimberly provided in her blog:
Free proc cache if to much single used plans
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
March 11, 2011 at 8:29 am
ALZDBA (3/11/2011)
I implement the advise Kimberly provided in her blog:Free proc cache if to much single used plans
That link is not working atm.
March 11, 2011 at 8:38 am
The first "h" in http wasn't part of the link.
March 11, 2011 at 10:22 am
Ninja's_RGR'us (3/11/2011)
That link is not working atm.
I'm sorry.
It was indeed a faulty placement of the begin tag for the url. :blush:
Thank you Indianrock for fixing it. :Wow:
I fixed it in my original reply too.
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
March 11, 2011 at 10:34 am
I do plan to read that article even though we're not on sql 2008 yet. As well as another one shown in that article
March 11, 2011 at 12:42 pm
When I run this on our production system it generates about 12,500 rows of execution plans that are usecounts=1 About 500 are "adhoc," the rest Prepared. These seem to total about 2.7GB and our procedure cache is running at about 7.5GB generally. So what does this tell me?
SELECT text,usecounts,cacheobjtype,objtype,value as dbname,size_in_bytes,creation_time,last_execution_time
FROM sys.dm_exec_query_stats qs
JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE attribute = 'dbid'
AND DB_NAME(CAST(pa.value AS int)) = 'OurDatabaseName' and usecounts<2
March 11, 2011 at 1:52 pm
It tells you sqlserver is wasting plan cache space by storing plans that aren't being reused.
I implemented this attached job on instances where I proved they suffer this waste of ram.
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
March 11, 2011 at 2:53 pm
Indianrock (3/11/2011)
When I run this on our production system it generates about 12,500 rows of execution plans that are usecounts=1 About 500 are "adhoc," the rest Prepared. These seem to total about 2.7GB and our procedure cache is running at about 7.5GB generally. So what does this tell me?SELECT text,usecounts,cacheobjtype,objtype,value as dbname,size_in_bytes,creation_time,last_execution_time
FROM sys.dm_exec_query_stats qs
JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE attribute = 'dbid'
AND DB_NAME(CAST(pa.value AS int)) = 'OurDatabaseName' and usecounts<2
It tells me you're likely using nHibernate and that the developers didn't know about the fact that if you don't code it correctly it creates a different procedure for every possible length of a field. For example, 'pig' gets you a varchar(3) as a parameter, while 'horse' gets you a varchar(5). If it's for a field that's a varchar(150), just imagine how many different plans there will be for that one variable. Toss in more variables and you see your problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2011 at 5:15 pm
Yes the developers use an ORM to generate most of the sql. C# .net
March 12, 2011 at 4:30 am
Indianrock (3/11/2011)
Yes the developers use an ORM to generate most of the sql. C# .net
so you need to attack this from two fronts. Yes, you need to work on your server and adjust what you can, but you need to talk to the developers about their code. They need to adjust it so that it's not putting so much burden on SQL Server. Are you sure it's parameterizing the queries or is it simply executing query strings?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2011 at 6:25 am
I'm sure the generated sql is parameterized. Here is one of our worst offenders in terms of spiking CPU. I've changed a few key words from what was actually captured for this sql.
I highlighted the "select" and "from" commands since you really have to wade in there to even find them.
(@ParameterTable01_BUSINESS_OBJECT_STATUS10 varchar(7),@ParameterTable01_CLIENT_ID31 int,@ParameterTable01_REQUEST_TYPE72 varchar
(30),@ParameterTable01_REQUEST_TYPE83 varchar(30),@ParameterTable01_REQUEST_TYPE94 varchar(30),@ParameterTable01_REQUEST_TYPE105
varchar(30),@ParameterTable01_REQUEST_TYPE116 varchar(30),@ParameterTable01_BUSINESS_PROCESS_STATUS137 varchar(4),@ParameterTable01_REQUEST_TRANSACTION_TYPE158
varchar(15),@ParameterTable01_169 varchar(22),@ParameterTable01_1710 varchar(29),@ParameterTable01_1811 varchar(35),@ParameterTable01_1912
varchar(38),@ParameterTable01_2013 varchar(19),@ParameterTable01_2114 varchar(29),@ParameterTable01_2215 varchar(24),
@ParameterTable01_2316 varchar(21),@ParameterTable01_2417 varchar(31),@ParameterTable01_2518 varchar(41),@ParameterTable01_2619
varchar(24),@ParameterTable01_2720 varchar(31),@ParameterTable01_2821 varchar(41),@ParameterTable01_2922 varchar(32),
@ParameterTable01_3023 varchar(24),@ParameterTable01_3124 varchar(29),@ParameterTable01_3225 varchar(36),@ParameterTable01_3326
varchar(42),@ParameterTable01_3427 varchar(34),@ParameterTable01_3528 varchar(33),@ParameterTable01_3629 varchar(43),
@ParameterTable01_3730 varchar(47),@ParameterTable01_3831 varchar(48),@ParameterTable01_3932 varchar(28),@ParameterTable01_4033
varchar(30),@ParameterTable01_4134 varchar(32),@ParameterTable01_4235 varchar(43),@ParameterTable01_4336 varchar(38),
@ParameterTable01_4437 varchar(22),@ParameterTable01_4538 varchar(28),@ParameterTable01_4639 varchar(40),@ParameterTable01_4740
varchar(39),@ParameterTable01_4841 varchar(22),@ParameterTable01_4942 varchar(34),@ParameterTable01_CLIENT_ID943 int,
@ParameterTable01_BOOKED_DATE1144 datetime,@ParameterTable01_STATE1445 varchar(2),@ParameterTable01_STATE1546 varchar
(2),@ParameterTable01_STATE1647 varchar(2),@ParameterTable01_STATE1748 varchar(2),@ParameterTable01_STATE1849 varchar
(2),@ParameterTable01_STATE1950 varchar(2),@ParameterTable01_STATE2051 varchar(2),@ParameterTable01_STATE2152 varchar
(2),@ParameterTable01_STATE2253 varchar(2),@ParameterTable01_STATE2354 varchar(2),@ParameterTable01_STATE2455 varchar
(2),@ParameterTable01_STATE2556 varchar(2),@ParameterTable01_STATE2657 varchar(2),@ParameterTable01_STATE2758 varchar
(2),@ParameterTable01_STATE2859 varchar(2),@ParameterTable01_STATE2960 varchar(2),@ParameterTable01_STATE3061 varchar
(2),@ParameterTable01_STATE3162 varchar(2),@ParameterTable01_STATE3263 varchar(2),@ParameterTable01_STATE3364 varchar
(2),@ParameterTable01_STATE3465 varchar(2),@ParameterTable01_STATE3566 varchar(2),@ParameterTable01_STATE3667 varchar
(2),@ParameterTable01_STATE3768 varchar(2),@ParameterTable01_STATE3869 varchar(2),@ParameterTable01_STATE3970 varchar
(2),@ParameterTable01_STATE4071 varchar(2),@ParameterTable01_STATE4172 varchar(2),@ParameterTable01_STATE4273 varchar
(2),@ParameterTable01_STATE4374 varchar(2),@ParameterTable01_STATE4475 varchar(2),@ParameterTable01_STATE4576 varchar
(2),@ParameterTable01_STATE4677 varchar(2),@ParameterTable01_STATE4778 varchar(2),@ParameterTable01_STATE4879 varchar
(2),@ParameterTable01_STATE4980 varchar(2),@ParameterTable01_STATE5081 varchar(2),@ParameterTable01_STATE5182 varchar
(2),@ParameterTable01_ENTITY_TYPE5483 varchar(25),@ParameterTable01_NORMALIZED_ENTITY_CODE5684 char(10),@ParameterTable01_NORMALIZED_ENTITY_CODE5885
char(10),@ParameterTable01_6286 int,@ParameterTable01_6387 varchar(22),@ParameterTable01_6488 varchar(26),@ParameterTable01_6589
varchar(13),@ParameterTable01_6690 varchar(21))
SELECT Obfus02.Obfus_ID AS Obfus02_Obfus_ID6,
Obfus02.LAST_MODIFIER AS Obfus02_LAST_MODIFIER69,
Obfus02.LAST_MOD_DATE_TIME AS Obfus02_LAST_MOD_DATE_TIME71,
Obfus02.TRANS_SEQ_NUM AS Obfus02_TRANS_SEQ_NUM73,
Obfus02.CLIENT_ID AS Obfus02_CLIENT_ID8,
Obfus02.CATEGORY AS Obfus02_CATEGORY77,
Obfus02.SUB_CATEGORY AS Obfus02_SUB_CATEGORY79,
Obfus02.CUSTOM_ATTRIBUTE_1 AS Obfus02_CUSTOM_ATTRIBUTE_181,
Obfus02.CUSTOM_ATTRIBUTE_2 AS Obfus02_CUSTOM_ATTRIBUTE_283,
Obfus02.CUSTOM_ATTRIBUTE_3 AS Obfus02_CUSTOM_ATTRIBUTE_385,
Obfus02.CUSTOM_ATTRIBUTE_4 AS Obfus02_CUSTOM_ATTRIBUTE_487,
Obfus02.CUSTOM_ATTRIBUTE_5 AS Obfus02_CUSTOM_ATTRIBUTE_589,
Obfus02.CUSTOM_ATTRIBUTE_6 AS Obfus02_CUSTOM_ATTRIBUTE_691,
Obfus02.BOOKED_DATE AS Obfus02_BOOKED_DATE10,
Obfus02.FINANCED_DATE AS Obfus02_FINANCED_DATE95,
Obfus02.AMOUNT_FINANCED AS Obfus02_AMOUNT_FINANCED97,
Obfus02.CREATED_DATE_TIME AS Obfus02_CREATED_DATE_TIME99,
Obfus02.EXPECTED_PAYOFF_DATE AS Obfus02_EXPECTED_PAYOFF_DATE101,
Obfus02.ACTUAL_PAYOFF_DATE AS Obfus02_ACTUAL_PAYOFF_DATE103,
Obfus02.RECOVERY_STATUS AS Obfus02_RECOVERY_STATUS5,
Obfus02.BUSINESS_OBJECT_STATUS AS Obfus02_BUSINESS_OBJECT_STATUS0,
Obfus02.LAST_STATUS_CHANGE AS Obfus02_LAST_STATUS_CHANGE109,
Obfus02.PERFECTED_DATE_TIME AS Obfus02_PERFECTED_DATE_TIME2,
Obfus02.FOLLOWUP_ENTITYLEGAL_ENTITY_ID
AS Obfus02_FOLLOWUP_ENTITYLEGAL_ENTITY_ID113,
Obfus02.USER_DEFINED_2 AS Obfus02_USER_DEFINED_2115,
Obfus02.USER_DEFINED_1 AS Obfus02_USER_DEFINED_1117,
Obfus02.USER_DEFINED_3 AS Obfus02_USER_DEFINED_3119,
Obfus02.LIEN_FILING_63DAY_MESSAGE_COUNT
AS Obfus02_LIEN_FILING_63DAY_MESSAGE_COUNT121,
Obfus02.TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID
AS Obfus02_TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID123,
Obfus02.BUSINESS_UNIT_ID AS Obfus02_BUSINESS_UNIT_ID125,
Obfus02.CLOSED_REASON AS Obfus02_CLOSED_REASON127,
Obfus02.IS_CLIENT_CONVERSION AS Obfus02_IS_CLIENT_CONVERSION129,
Obfus02.LIENHOLDER_STATUS_CODE_ID
AS Obfus02_LIENHOLDER_STATUS_CODE_ID131,
Obfus02.XML_SERIALIZED_INSTANCE_ID
AS Obfus02_XML_SERIALIZED_INSTANCE_ID133
FROM Obfus AS Obfus02 INNER JOIN PROPERTY AS ObfusProperty13
ON Obfus02.Obfus_ID = ObfusProperty13.Obfus_ID
INNER JOIN
( LEGAL_ENTITY AS RecordedLegalEntity14
INNER JOIN
ADDRESS AS Address15
ON RecordedLegalEntity14.ADDRESS_ID = Address15.ADDRESS_ID)
ON Obfus02.Obfus_ID = RecordedLegalEntity14.Obfus_ID
INNER JOIN
LEGAL_ENTITY AS FollowupEntity16
ON Obfus02.FOLLOWUP_ENTITYLEGAL_ENTITY_ID =
FollowupEntity16.LEGAL_ENTITY_ID
WHERE ( (Obfus02.BUSINESS_OBJECT_STATUS =
@ParameterTable01_BUSINESS_OBJECT_STATUS10
AND Obfus02.PERFECTED_DATE_TIME IS NULL
AND ObfusProperty13.EEE_DATE_TIME IS NULL
AND Obfus02.RECOVERY_STATUS IS NULL
AND Obfus02.Obfus_ID NOT IN (SELECT ServicedObfus02_Obfus_ID50
FROM (SELECT ServicedObfus02.Obfus_ID
AS ServicedObfus02_Obfus_ID50
FROM SERVICED_COLLATERAL_GROUP_ITEM AS ServicedObfus02
INNER JOIN
( SERVICE_REQUEST AS CollateralGroupRequest13
CROSS JOIN
TITLING_AGENCY_REQUEST_TRANSACTION_TYPE AS ServiceRequestTransactionType14)
ON ServicedObfus02.COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID =
CollateralGroupRequest13.SERVICE_REQUEST_ID
WHERE ( (CollateralGroupRequest13.CLIENT_ID =
@ParameterTable01_CLIENT_ID31
AND (ServiceRequestTransactionType14.REQUEST_TRANSACTION_TYPE =
CollateralGroupRequest13.REQUEST_TRANSACTION_TYPE
AND ServiceRequestTransactionType14.REQUEST_TYPE IN (@ParameterTable01_REQUEST_TYPE72,
@ParameterTable01_REQUEST_TYPE83,
@ParameterTable01_REQUEST_TYPE94,
@ParameterTable01_REQUEST_TYPE105,
@ParameterTable01_REQUEST_TYPE116)
AND CollateralGroupRequest13.BUSINESS_PROCESS_STATUS =
@ParameterTable01_BUSINESS_PROCESS_STATUS137
OR (CollateralGroupRequest13.REQUEST_TRANSACTION_TYPE =
@ParameterTable01_REQUEST_TRANSACTION_TYPE158)))
AND ( (ServicedObfus02.CONCRETE_TYPE IN (@ParameterTable01_169))
AND (CollateralGroupRequest13.CONCRETE_TYPE IN (@ParameterTable01_1710,
@ParameterTable01_1811,
@ParameterTable01_1912,
@ParameterTable01_2013,
@ParameterTable01_2114,
@ParameterTable01_2215,
@ParameterTable01_2316,
@ParameterTable01_2417,
@ParameterTable01_2518,
@ParameterTable01_2619,
@ParameterTable01_2720,
@ParameterTable01_2821,
@ParameterTable01_2922,
@ParameterTable01_3023,
@ParameterTable01_3124,
@ParameterTable01_3225,
@ParameterTable01_3326,
@ParameterTable01_3427,
@ParameterTable01_3528,
@ParameterTable01_3629,
@ParameterTable01_3730,
@ParameterTable01_3831,
@ParameterTable01_3932,
@ParameterTable01_4033,
@ParameterTable01_4134,
@ParameterTable01_4235,
@ParameterTable01_4336,
@ParameterTable01_4437,
@ParameterTable01_4538,
@ParameterTable01_4639,
@ParameterTable01_4740,
@ParameterTable01_4841,
@ParameterTable01_4942)))))
AS ScalarQueryTable)
AND (Obfus02.CLIENT_ID = @ParameterTable01_CLIENT_ID943
AND (Obfus02.BOOKED_DATE >= @ParameterTable01_BOOKED_DATE1144)
AND ( (Address15.STATE IN (@ParameterTable01_STATE1445,
@ParameterTable01_STATE1546,
@ParameterTable01_STATE1647,
@ParameterTable01_STATE1748,
@ParameterTable01_STATE1849,
@ParameterTable01_STATE1950,
@ParameterTable01_STATE2051,
@ParameterTable01_STATE2152,
@ParameterTable01_STATE2253,
@ParameterTable01_STATE2354,
@ParameterTable01_STATE2455,
@ParameterTable01_STATE2556,
@ParameterTable01_STATE2657,
@ParameterTable01_STATE2758,
@ParameterTable01_STATE2859,
@ParameterTable01_STATE2960,
@ParameterTable01_STATE3061,
@ParameterTable01_STATE3162,
@ParameterTable01_STATE3263,
@ParameterTable01_STATE3364,
@ParameterTable01_STATE3465,
@ParameterTable01_STATE3566,
@ParameterTable01_STATE3667,
@ParameterTable01_STATE3768,
@ParameterTable01_STATE3869,
@ParameterTable01_STATE3970,
@ParameterTable01_STATE4071,
@ParameterTable01_STATE4172,
@ParameterTable01_STATE4273,
@ParameterTable01_STATE4374,
@ParameterTable01_STATE4475,
@ParameterTable01_STATE4576,
@ParameterTable01_STATE4677,
@ParameterTable01_STATE4778,
@ParameterTable01_STATE4879,
@ParameterTable01_STATE4980,
@ParameterTable01_STATE5081,
@ParameterTable01_STATE5182)))
AND (FollowupEntity16.ENTITY_TYPE = @ParameterTable01_ENTITY_TYPE5483
AND ( (FollowupEntity16.NORMALIZED_ENTITY_CODE >=
@ParameterTable01_NORMALIZED_ENTITY_CODE5684)
AND (FollowupEntity16.NORMALIZED_ENTITY_CODE <=
@ParameterTable01_NORMALIZED_ENTITY_CODE5885))))
AND (DateDiff (Day, Obfus02.BOOKED_DATE, GETDATE ()) >=
@ParameterTable01_6286))
AND ( (ObfusProperty13.CONCRETE_TYPE IN (@ParameterTable01_6387))
AND (RecordedLegalEntity14.CONCRETE_TYPE IN (@ParameterTable01_6488,
@ParameterTable01_6589))
AND (FollowupEntity16.CONCRETE_TYPE IN (@ParameterTable01_6690))))
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply