Art of the DBA Rotating Header Image

dynamic management views

Standing on the Shoulders of Giants

May 19th, 2011
by Mike Fal.
3 comments

When I went to SQL Rally last week, I fully expected to be learning from many of the presenters there. The excitement about being immersed in all that awesomesauce is infectious. What I didn’t expect is that I would take my turn at teaching, handing out information like candy to twelve year olds. It was one of those times where I was talking shop with some other folks while we were all taking a session off. The group started chatting about performance tuning(something I geek out about) and I shared out some of the fundamental links, blogs, and tools I use when I get the inevitable “My query is slow!” call.

Now, just to be clear, none of the information I shared was “mine”. The beauty of the SQL Server community is that we have so much great talent and everyone is willing to help educate. What follows here is a list of my core tools and information links, resources that I use on almost a daily basis. I also see them as the start of a journey, because none of this is the last word on a particular subject. If you’re like me you’ll continue to browse the Google ‘verse with what you pick up here.

Brent Ozar:

Full disclosure, I’m a Brent Ozar fanboy. I started reading his blog about a year or so ago and he’s made me laugh and taught me a whole heck of a lot. It’s because of him that I started blogging and getting involved in the community.

Glenn Berry:

Glenn Berry is a local Denver MVP and I see him from time to time at the Denver SQL User’s Group meetings. Not only has he provided these great DMV queries, but he really understands hardware and has some great posts on the nitty-gritty of your machines.

General Community Resources

These two sites are invaluable resources for articles and tutorials on getting stuff done. When I was figuring out how to implement partitioning, I did a lot of reading on both of these sites.

Documents

Both of these docs live on my laptop and are constantly getting referenced. My only complaint about the waits and queues white paper is that it’s a little stale and I hope Microsoft does an updated one for Denali when it’s released.

If you don’t have a cheat sheet with your go to resources, hopefully this list gives you a good start. Or you can add this to your currently existing list. I have found each one of these links invaluable to my day to day work, not only enriching my general knowledge but giving me the tools to troubleshoot many of the performance problems that come my way.

T-SQL Tuesday #17: Using APPLY

Apr 12th, 2011
by Mike Fal.
2 comments

I confess, I’m one of those guys who really didn’t know much about APPLY. Sure, I saw it in the occasional query I’d find on the ‘tubes, but I never really dug into what it was and why this piece of syntax was so important. Fortunately, this month’s T-SQL Tuesday has rolled around with an opportunity for me to do a little learning, so off to the Google-mobile!

Of course, you can easily find the MSDN article explaining apply, but there’s a much more detailed one I found on Simple Talk by Robert Sheldon. Conceptually, it’s pretty simple. APPLY, in both its CROSS APPLY and OUTER APPLY forms, allows you to call a table valued function for each row in your data set. In a lot of ways it’s like a join, but instead of two tables linked on a common field, you’re linking your query with a computed set of rows that you return with your function.

Now, I wish I could say I had some cool query to share, but tend to use APPLY in some simple ways to get query information. Here’s a query that makes use of the dynamic management functions dm_exec_query_plan and dm_exec_sql_text that gets a lot of work from me:

select
	er.session_id,
	d.name database_name,
	er.status,
	er.wait_type,
	eqp.query_plan,
	est.text
from sys.dm_exec_requests er
	join sys.databases d on (er.database_id = d.database_id)
	CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) eqp
	CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) est

Using this, you can get your text and your query plan for all queries currently running on your server. I like to use the query plans to troubleshoot long running queries, and the wait types help you identify the queries you might want to look at first.

Enjoy the other T-SQL Tuesday posts out there and huge thanks to Matt Velic(blog|twitter) for hosting this month!