March 12, 2011 at 8:43 am
When I look at plans that are used less than 5 times, I do see that many of the parameters passed in are of the same data type and/or length, but certainly not all. If I run the query below I get about 38,000 plans related to our client database. A simple count of sys.dm_exec_cached_plans shows about 40,000
SELECT count(*)
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)) = 'OurClientDatabase'
SELECT count(*)
FROM sys.dm_exec_cached_plans
March 12, 2011 at 1:45 pm
It does look like the parameters are just getting their lengths from the lengths of the strings that are assigned to them instead of getting set a predefined size.
i'm not sure about your ORM code but if you can, asking the devs to assign varchars a set length (say 8k) should cater for most queries and get your plan reuse up by a fair amount.
Just my thoughts, someone may well jump in and slate that advice but that's the route I'd go down.
Good luck with the testing.
Rob.
March 12, 2011 at 10:24 pm
Hmmmm... I'm thinking it may be something else. Perhaps the explicit CROSS JOIN in the ORM code is causing a wee bit of a performance problem?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2011 at 5:41 am
On the performance end, follow Jeff's advice. I usually do. On the coding side, you can tell just looking at it that the parameter sizes are based on the values, not the data structures, unless you guys have columns that are varchar(21) and varchar(37). To reduce the number of plans, and you need to, you have to talk to the developers about changing how that code is generated. They're doing it in a sub-optimal fashion.
"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 13, 2011 at 6:53 am
Rob I did email our lead developer about possibly switching the varchars to a set, large size. Or can they be tied to the column widths? The lead developer is the author of the OpenSource DomainObjects, but I'm not certain if that's still being used as our object-data mapper (ORM ) DomainObjects[/url]
Jeff, that query is one of our worst offenders but seems to impact CPU more than anything else. We have another bad boy that runs once per evening that does a cross join between a 35 million record table and a copy of itself. That one spikes tempdb IO wait time up into the thousands of milliseconds. Did I mention we expect the database to grow from 35 million records to perhaps 70 million by early next year?
Grant, I'm wondering if much of this ties in with the need to run freeproccache. It used to be used almost weekly, but much more rarely now. It seems we've been throwing hardware at the issues ( went to 64bit and 128GB of memory in the last 8 months. Just last weekend solved a very bad IO problem between the sql cluster and Netapp ). Now we need to get at the core problems in the code.
One of my friends who is a senior DBA submits findings like this as a bug ticket, since he, like most of us, either doesn't have direct access to the source code or doesn't know the language that well ( C# in our case ) I do have access to Accurev, but wouldn't really know how to find the specific bits of code we're after.
March 13, 2011 at 9:37 am
Now jou've hit another typical "project progress" issue.
It will need to come to a good collaboration between the dba(-team) and the dev(-team).
Actually the lac of this collaboration model already costs your company (upgrade 64-bit + ram extention) .
My guess you'll only see the default .net application name in your connections, so you'll only be able to tell your dev(s) which queries hurt, and not be able to provide a decent application link because they don't provide the application name in their connections.
(this app name will not show in the plan, but when monitoring online operations, you can match a plan to an existing connection)
e.g. using Adam Machanics wonderful proc http://sqlblog.com/blogs/adam_machanic/archive/2010/10/21/who-is-active-v10-00-dmv-monitoring-made-easy.aspx
It will be a collaborative tuning project by itself to fix the query issues. Start with the top 50 consumers and work from there on. Pinpoint the issue(s) and work with your dev(s) to figure out how they can solve the issue to match the optimal query you reworked for them.
Keep in mind "collaboration" is the key word for success !
Of course, you always have SSC for help :w00t:
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 13, 2011 at 10:17 am
Thanks. I do have Adam's WhoIsActive and the WhoIsActive UI that can be used with it. The worst offenders run after my bedtime 🙂 but we have both Quest Spotlight and Quest Performance Analysis so I can generally capture sql that runs at any time without having to "be there."
March 13, 2011 at 11:17 am
You have a great tool set at your disposal.
If you also have their query optimizer, your biggest challange will only be the collaborative model.
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 13, 2011 at 4:30 pm
Guys, i'm by no means playing the same sport let alone be in the same league as jeff and grant but from the the op's query he does seem to be having a real problem with plan reuse.
From the c# side of things i can say that it's not that hard to map lengths of the varchar cols into static properties so that predefining the lengths of the parameters relating to them is not an issue.
That said, that is still alot more work than finding all instances where a varchar sqlparameter is declared and just presetting them to 8k so that a build for some load testing can be prepared for a quick proof of concept report.
Anyway, as I mentioned, I'm not a sql expert by any means. Just trying to let you know what we've done. From the c# side the changes should be pretty trivial to implement. Obviously, ymmv.
Hope it helps,
Rob.
March 14, 2011 at 8:05 am
FYI - Maybe you can find some help and advise in this "Advantages to Using Stored Procedures over ORM Tools" by Idera.
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 14, 2011 at 8:23 am
I actually watched that webcast last week. I don't want to resurrect the whole debate about ORM vs. stored procedures. The fact is that many IT shops have decided not only to go with ORM, but to go without a DBA. Our new product was launched several years ago by a developer group who seem to believe in ORM, Agile development etc and want as little interference from DBAs and/or Systems groups as possible, we're not likely to change much now.
On the other hand, now that our legacy systems are about 75% migrated to this new product, problems with scalability are showing up. We were recently purchased so the sql DBAs/architects of the purchasing firm may insert their ideas soon in an aggressive manner. In the meantime I'm hoping the ideas in the thread about tuning up the ORM-generated sql will help.
March 14, 2011 at 10:44 am
Indianrock (3/14/2011)
I actually watched that webcast last week. I don't want to resurrect the whole debate about ORM vs. stored procedures. The fact is that many IT shops have decided not only to go with ORM, but to go without a DBA. Our new product was launched several years ago by a developer group who seem to believe in ORM, Agile development etc and want as little interference from DBAs and/or Systems groups as possible, we're not likely to change much now.
That is indeed a sad but know practice.
Save on the dba and scale up the hardware (again and again).
Costwize 0 op ???? 🙁
On the other hand, now that our legacy systems are about 75% migrated to this new product, problems with scalability are showing up. We were recently purchased so the sql DBAs/architects of the purchasing firm may insert their ideas soon in an aggressive manner.
Only time will tell.
In the meantime I'm hoping the ideas in the thread about tuning up the ORM-generated sql will help.
As always the general rule is "tell your system what you know".
Sadly not everybody knows what they should know about the data system.
Click and point development doesn't help much here.
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 15, 2011 at 2:14 pm
In that sql agent job that deletes single use plans, I'm curious what this line does. We only have one sql 2008 box and it returns "10.0.2531.0"
/* enkel SQL2008 */
if cast(SERVERPROPERTY('ProductVersion') as varchar(50)) like '__.%'
begin
March 15, 2011 at 2:27 pm
So that job also dumps all sql plans, not just single-use plans.
DBCC FREESYSTEMCACHE('SQL Plans')
March 15, 2011 at 3:32 pm
Indeed. Dropping a single plan can only be done as of SQL2008.
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
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply