One function causes CPU 100

Question

One function causes CPU 100

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Ross McMicken (6/30/2008)
I would love to improve the vendor package I support, but management won't let me unless I can convince the vendor to make the change. Which happens occasionally. Unfortunately, some of the vendor's development staff gets very defensive when I ask questions about the rationale behind certain processes. liek the time I asked why null was allowed in a column that should never, under any circumstances be null. Fortunately, none of the bad code causes extended processing times, so performance hasn't been an issue. Just strange results on occasion.

I know... easier said than done... but you didn't pay the vendor for "strange results"... you paid them for working, predictable product... make them make it work. 😉 If they get all offended, remind them that you're the paying customer and bad press is never a good thing...:hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SrikanthSv Ten Centuries Points: 1197 More actions · Answer 2

not at all clear

Regards
Venkat
http://sqlblogging.blogspot.com[/url]

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 3

i going to run my entire process using profiler with the SHOWPLANXML on to see what it is doing too....:)

What are the things i should look out for in the profile when viewing the XML executions...

Table scans and not using indexes ?

When selecting just the SHOWPLANXML i can see what it is doing but cannot see the SQL code is there a way to include this in the profiler trace .

Any other pointers would be appreciated.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

It ALL depends... table scans are not always a bad thing... index usage is not always a good thing.

The real problem is not going to be simple index usage... it's going to be RBAR that's the killer in the code. And, some RBAR is hidden... a simple "slotted join aggragate" can cause the equivelent of a Cartesian join. A seeming innocent correlated sub-query can produce a Triangular join. Of course, the obvious RBAR forms of any cursor or While loop all but defeats the very purpose for having a database to begin with. Other forms of hidden RBAR come in the form of functions. And poorly written aggregated views will cripple even the most expensive hardware especially if you have and aggregated view of an aggregated view.

If you want to look for bad things in the execution plan, look for extremely thin lines that show only 1 or 2 rows and extremely thick lines that show many more rows than the larger of 2 tables joined.

I wouldn't even think about indexes right now... no index can help crap code. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Another very good thing to look for in query plans is low estimates with telephone numbers for actual rows.

My first cut at a profiler run at a new client (for OLTP apps) simply has a filter of duration > NN seconds (usually 5, 20 or 60 depending on how bad the client says it is) or reads > 10K. I adjust from there depending on what I trap. It is scary how often I need to adjust that filter UP. :w00t:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 6

RBAR stands for ?

In the READS is this * 8192 / 1024 to get mgs of data.

CPU is in ms ...

Just checking.

I have a look at the thin lines rows returned only couple....

GSquared SSC Guru Points: 260824 More actions · Answer 7

TRACEY (7/1/2008)
RBAR stands for ?

Row By Agonizing Row (Jeff has it in his signature)

It refers to code that makes the database do row-by-row calculations/instructions, instead of being able to operate on swaths of data in a set-based method. SQL Server is generally MUCH faster at set-based stuff than it is at RBAR.

- 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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Jeff Moden (7/1/2008)
Ross McMicken (6/30/2008)
I would love to improve the vendor package I support, but management won't let me unless I can convince the vendor to make the change. Which happens occasionally. Unfortunately, some of the vendor's development staff gets very defensive when I ask questions about the rationale behind certain processes. liek the time I asked why null was allowed in a column that should never, under any circumstances be null. Fortunately, none of the bad code causes extended processing times, so performance hasn't been an issue. Just strange results on occasion.
I know... easier said than done... but you didn't pay the vendor for "strange results"... you paid them for working, predictable product... make them make it work. 😉 If they get all offended, remind them that you're the paying customer and bad press is never a good thing...:hehe:

I've found that volunteering to bring up these issues in a public forum, like, say, the vendor's user group, or at their user convention, or at the relevant industry's user conference, will tend to get a LOT of miles.

Playing dirty is fair game if you can't get them to fix their own stinkiness....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 9

Thanks guys i be doing some analysis in the next weeks.....i post some new threads.