The Next Frontier of Performance Troubleshooting

Question

The Next Frontier of Performance Troubleshooting

Tony Davis

SSCarpal Tunnel

Points: 4405
More actions
May 2, 2009 at 1:14 pm

#204260

Comments posted to this topic are about the item The Next Frontier of Performance Troubleshooting

Viewing 11 posts - 1 through 10 (of 10 total)

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

Indianrock SSC-Insane Points: 20333 More actions · Answer 1

Is this only for sql 2008?

This tool demonstrates Response time analysis at the session or statement level including waitstats using the new Extended Events infrastructure in SQL Server 2008

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 2

I think it very ironic that people continue to find better ways to find performance problems caused by poorly written code that should never have made it to the database to begin with. Nearly as ironic, I make my living by fixing that which I hate the most. Long live spaghetti 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)

GSquared SSC Guru Points: 260824 More actions · Answer 3

I've usually found two things are more than adequate to tell me exactly what's slowing things down. The Activity Monitor in Management Studio, and a server-side trace on all scripts/procs/etc.

One tells me what's going on right this moment, well enough to debug, and the other tells me what's been going on for the last week or so.

And Jeff is totally correct, that it's MUCH easier to make sure the code is well-written in the first place. He's also right that whole careers can be made off of fixing junk code that's already in use and never should have been.

- 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

Indianrock SSC-Insane Points: 20333 More actions · Answer 4

Still waiting to hear if this is only for sql 2008. Some of us are dealing with sql generated by objects, not stored procedures. Glenn Berry has a nice list of diagnostic queries at http:\\glenberrysqlperformance.spaces.live.com ( those for sql 2005 are down the page a bit ).

But again, the ones that report on stored procedures do almost nothing in our environment since procs are hardly used.

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 5

Tony is off today, but he should respond tomorrow.

I agree we want to write code correctly the first time, but there will always be bad code. Always new people, always people learning and making mistakes.

Making it easy for them spot things is always handy.

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 6

The tool is from the SQLCAT team at Microsoft, and if you follow the link (http://sqlcat.codeplex.com/Wiki/View.aspx?title=ExtendedEventsWaitstats), it says for 2008.

If you have more questions, I'd contact the SQLCAT team.

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 7

Indianrock (5/4/2009)
But again, the ones that report on stored procedures do almost nothing in our environment since procs are hardly used.

That's why I start performance monitoring at the "Batch Completion" level.

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Indianrock (5/4/2009)
Still waiting to hear if this is only for sql 2008.

Extended events are new in SQL 2008.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Indianrock SSC-Insane Points: 20333 More actions · Answer 9

GilaMonster (5/4/2009)
Indianrock (5/4/2009)
Still waiting to hear if this is only for sql 2008.
Extended events are new in SQL 2008.

Thanks. This makes me wonder what the percentages are as far as world-wide users of sql 2005 versus sql 2008. On the other hand, it's at least one good reason to upgrade. I don't expect our company to even consider upgrading until next year some time, due to the economy.

Tony Davis SSCarpal Tunnel Points: 4405 More actions · Answer 10

Yes, Gail is right: this is a SQL 2008 technique, although you can get some wait event data out of SQL 2005, as shown in this article:

http://www.simple-talk.com/sql/performance/taking-the-guesswork-out-of-sql-server-performance-profiling-part-2/

It's true that a big part of the problem is just plain old bad code but, as Steve says, mistakes will always happen; you cannot predict exactly how your code is going to be used in the future. I believe these session-level techniques are a big step forwarrd in finding the inevitable problems.

At least, I'm feeling confident enough to commisison a short book on the topic; then people can make up their own minds from there 🙂

Cheers,

Tony.