April 5, 2011 at 6:49 am
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
April 5, 2011 at 7:18 am
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
April 5, 2011 at 7:20 am
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.
April 5, 2011 at 7:21 am
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.
April 5, 2011 at 7:23 am
April 5, 2011 at 9:38 am
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
April 5, 2011 at 9:44 am
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:.
April 5, 2011 at 4:55 pm
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
April 5, 2011 at 11:36 pm
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
April 6, 2011 at 9:53 am
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
April 6, 2011 at 4:45 pm
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
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply