Query Performance Tuning

Question

Query Performance Tuning

Viewing 11 posts - 16 through 25 (of 25 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Grant Fritchey (4/4/2011)
Grubb (4/4/2011)
Craig Farrell (4/4/2011) SELECT * FROM master..sysprocesses.
Thanks, Craig. The problem with that is that the query only runs ~400 ms. Do you know of a way to kick off a query when a particular process runs for some (sub-second) amount of time?
I've found sys.dm_os_wait_stats. The best I can think of right now is to get a percentage of the time spent in the top 10 lock states, before a performance anomoly, and then run it again as soon as I can after, and hopefully the slight bump can tip me off :unsure:
-Grubb
No, to really get the wait states based on a particular event, you need to go to extended events, and I'm just learning them myself, so I'm not going to be able to advise well here. But that is the answer.

Grant is correct in that to get information on individual short-duration events such as this scenario you need to go to XEvents. 2 things though:

1) the volume of calls you are speaking of could cause some serious issues if you set up an XEvent to fire on them

2) it is likely that you can observe what is going on by evaluating aggregate waits stats. Take a snapshot of waits, do a waitfor for 3 mins, take another snapshot and do a delta between your 2 sets of data. Search the web for track_waitstats_2005 and you will find a whitepaper with just such a set of code ready made for you. It (and its associated whitepaper) is one of my bread and butter tools as a professional tuning consultant!!

3) I suspect that simply running sp_whoisactive (awesome free script from Adam Machanic) can probably capture point-in-time blocking too.

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

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

Ninja's_RGR'us (4/4/2011)
It's actually "worse" than that. If you want a garanteed index seek, the predicate needs to return 0.3% OR LESS of the data in the table.

Have you got an MS URL for that statement, Remi?

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Jeff Moden (4/5/2011)
Ninja's_RGR'us (4/4/2011)
It's actually "worse" than that. If you want a garanteed index seek, the predicate needs to return 0.3% OR LESS of the data in the table.
Have you got an MS URL for that statement, Remi?

Read that from Gail's blog, don't have the exact url.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Jeff Moden (4/5/2011)
Ninja's_RGR'us (4/4/2011)
It's actually "worse" than that. If you want a garanteed index seek, the predicate needs to return 0.3% OR LESS of the data in the table.
Have you got an MS URL for that statement, Remi?

Let me know if I have pork comming my way, I'll unsnow the bbq and heat it up.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Ninja's_RGR'us (4/4/2011)
It's actually "worse" than that. If you want a garanteed index seek, the predicate needs to return 0.3% OR LESS of the data in the table.
Needless to say that a few of the values might break that rule.
Where it could explain your case is that you may have values in the 1% range where a seek is still faster than a whole table scan, but still way more expansive than any other values in the table.

I don't think the optimizer makes the switch from seek/lookup to scan at a fixed percentage, but it is indeed a VERY low number - MUCH lower than most people out there think it would be.

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Ok guys, IT DEPENDS. Now go test on your queries and stop beating this to death.

Tho 0.3% looks like a good starting figure :hehe:.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Ninja's_RGR'us (4/5/2011)
Jeff Moden (4/5/2011)
Ninja's_RGR'us (4/4/2011)
It's actually "worse" than that. If you want a garanteed index seek, the predicate needs to return 0.3% OR LESS of the data in the table.
Have you got an MS URL for that statement, Remi?
Let me know if I have pork comming my way, I'll unsnow the bbq and heat it up.

nah... I'll save the high velocity pork for someone who desparately deserves it. 😛

Ordinarily I'd still insist on an MS URL because most people don't actually prove what they say but Gail sure did. I love demonstrable code! You know what they say... "One good test is worth a thousand expert opinions." :w00t:

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

Jeff Moden (4/5/2011)
Ordinarily I'd still insist on an MS URL because most people don't actually prove what they say but Gail sure did. I love demonstrable code! You know what they say... "One good test is worth a thousand expert opinions." :w00t:

Just note that I proved 0.3% in that case. It's not going to be the same in every case. It's going to be a small %, how small will depend on the table structure. (nother blog post planned there)

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

@SixStringSQL Ten Centuries Points: 1026 More actions · Answer 10

TheSQLGuru (4/5/2011)
2) it is likely that you can observe what is going on by evaluating aggregate waits stats. Take a snapshot of waits, do a waitfor for 3 mins, take another snapshot and do a delta between your 2 sets of data. Search the web for track_waitstats_2005 and you will find a whitepaper with just such a set of code ready made for you. It (and its associated whitepaper) is one of my bread and butter tools as a professional tuning consultant!!

Thanks, 'Guru! This white page has been a treasure-trove for me...I'm still mining.

One thing that is unfortunate: DBAs don't get local admin access to the boxes, so things like System Monitor are out of reach 🙁

-Grubb

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

GilaMonster (4/5/2011)
Jeff Moden (4/5/2011)
Ordinarily I'd still insist on an MS URL because most people don't actually prove what they say but Gail sure did. I love demonstrable code! You know what they say... "One good test is worth a thousand expert opinions." :w00t:
Just note that I proved 0.3% in that case. It's not going to be the same in every case. It's going to be a small %, how small will depend on the table structure. (nother blog post planned there)

Thanks for the heads up but I already knew that "It Depends". 🙂

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