February 3, 2015 at 5:15 pm
What is the current thought on monitoring long queries (and performance in general) in SQL Server 2012 and/or 2014? Use extended events? Use DMV (Dynamic Management Views)? Use homegrown scripts?
I've searched google and this site, but many of the hits are from years back. I thought I would check to see what folks are using at the moment for SQL Server 2012/2014.
We would prefer not to purchase a third party tool. We are currently using the "Management Data Warehouse" feature in SQL Server Management Studio, but we should probably do more in depth monitoring.
If you suggest a solution, please also share your ideas for implementation.
Thanks,
J
February 3, 2015 at 7:09 pm
You can get Tim Ford/Louis Davidson's free performance tuning with DMVs book. Glenn Berry's SQL Server Diagnostics Scripts. sp_whoisactive. Do your own file IO stall and wait stats differential analysis. I make EVERY client I ever engage with have those last 3 at the ready to immediately fire off when a perf issue is noted.
Honestly I still use traces EXTENSIVELY for tuning work to find stuff that needs my focus. The AWESOME, free Qure tool from DBSophic.com provides some amazing aggregate analysis features. You can't touch that tools capabilities with XEs.
Since you don't want to purchase a 3rd party tool (not sure why - I HIGHLY recommend doing so), you could bring in a tuning consultant to help you set up better monitoring.
P.S. MDW is a waste of time.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 4, 2015 at 7:41 am
I too am not a fan of Management Data Warehouse. It works well enough, but misses so much you have to do a ton of extra work to get a good set of thorough monitors in place. With so much extra work, it's probably just as easy to not use it.
By and large, the monitoring, especially for performance, is largely the same in 2014 as it was in 2005. Performance Monitor counters are still very useful for server level information. Nothing has replaced them. There are more in support of the newer functionality, but that's about it. Dynamic Management Views are the way to go for accessing information about the system in real time. Nothing has replaced them. Trace, as far as I'm concerned, is dead if you're on SQL Server 2012 or better. You should be using Extended Events. It has a much lighter footprint, by many orders of magnitude. There are a much larger set of events available over Trace, especially around the new 2012/2014 functionality. The filtering is better, more extensive and more efficient. Trace is on the deprecation path. That's the one major difference between pre-2012 and post-2012 performance monitoring. (and yeah, Kevin and I disagree on this one, but he's still a great guy)
My book has been updated very extensively for SQL Server 2014. We released the book about 6 months after 2014 so that we could get it right, rather than releasing with 2014. It's got a lot of good information on how you can monitor your systems, specifically around performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 4, 2015 at 8:14 am
TheSQLGuru (2/3/2015)
You can get Tim Ford/Louis Davidson's free performance tuning with DMVs book. Glenn Berry's SQL Server Diagnostics Scripts. sp_whoisactive.
I didn't know about the Ford/Davidson book http://www.red-gate.com/community/books/dynamic-management-views. I plan to check it out. I ran across Glenn Berry and sp_whoisactive a while back. Good stuff.
The AWESOME, free Qure tool from DBSophic.com provides some amazing aggregate analysis features. You can't touch that tools capabilities with XEs.
I assume that you mean "QURE PROFILER" at http://www.dbsophic.com/products/all-products.html. I was not aware of this product. I plan to check it out.
Since you don't want to purchase a 3rd party tool (not sure why - I HIGHLY recommend doing so)
Cost and the procurement cycle at this site. We are fortunate NOT to have tuning issues. I'm just trying to be more proactive in monitoring the SQL Server farm.
February 4, 2015 at 8:17 am
Grant Fritchey (2/4/2015)
My book has been updated very extensively for SQL Server 2014.
I ran across a copy of your "SQL Server 2012 Query Performance Tuning" book this morning. This looks very useful.
February 4, 2015 at 8:42 am
shew (2/4/2015)
Grant Fritchey (2/4/2015)
My book has been updated very extensively for SQL Server 2014.I ran across a copy of your "SQL Server 2012 Query Performance Tuning" book this morning. This looks very useful.
Excellent. I think it's good. I just think the 2014 version is better. Ha! Can't help it. I keep tweeking and tuning based on my own learning, even as the technology shifts.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2015 at 3:59 pm
TheSQLGuru (2/3/2015)
The AWESOME, free Qure tool from DBSophic.com provides some amazing aggregate analysis features. You can't touch that tools capabilities with XEs.
Is DBSophic Qure Profiler still free? I am getting a "Trial Version Detected" window after the install. The web site says the Pro version is $300. Does that mean there is a free non-Pro version?
February 5, 2015 at 4:34 pm
Not sure - I haven't had to install it for some time now.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply