November 13, 2009 at 1:37 am
Does anyone have any recommendations for SQL performance monitoring tools? I'm aware there are many products out there so looking for a few suggestions to start with?
Thanks in advance!
November 13, 2009 at 12:13 pm
There's a wealth of them out there, here are a couple of already compiled lists I found:
http://www.mssqltips.com/tip.asp?tip=1067
http://www.sql-server-performance.com/software/monitor_reviews.aspx
We personally use a mix of Quest Spotlight and in-house stored procs to do our monitoring and alerting. If you have sql server 2008 or expect to upgrade soon, you might look into the performance data warehouse that comes built in with SSRS reports.
November 14, 2009 at 10:18 am
1) Confio
2) SQL Sentry Performance Advisor
The best one is actually ME! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 16, 2009 at 4:34 pm
Confio is pretty nice. I was a part of their focus group.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 17, 2009 at 8:34 am
I am using Quest Spotlight for SQL Server.
I also use Quest Spotlight for Oracle.
I love the graphics I can quickly see green is good and the closer we get to red is bad.
I can just as quickly see if the server is busy by the speed of the moving Chevrons and where the system is busy.
On both our SQL Server and Oracle critical Servers in 1 to 2 months I took CPU usage from 80% and up, to:
Oracle server below 10%
SQL Server from 25% to 50%
Logical Reads from in the billions down to millions on both systems.
Buffercache hit rates are now 99.9%
And this was with very little knowledge of the business and no prior knowledge of Spotlight.
I do confess to having over 20 years as a DBA on many different RDBM's.
November 17, 2009 at 8:40 am
bwilliams-1049831 (11/17/2009)
I am using Quest Spotlight for SQL Server.I also use Quest Spotlight for Oracle.
I love the graphics I can quickly see green is good and the closer we get to red is bad.
I can just as quickly see if the server is busy by the speed of the moving Chevrons and where the system is busy.
On both our SQL Server and Oracle critical Servers in 1 to 2 months I took CPU usage from 80% and up, to:
Oracle server below 10%
SQL Server from 25% to 50%
Logical Reads from in the billions down to millions on both systems.
Buffercache hit rates are now 99.9%
And this was with very little knowledge of the business and no prior knowledge of Spotlight.
I do confess to having over 20 years as a DBA on many different RDBM's.
So did Spotlight actually identify individual queries that were performing badly and provide you with details on execution metrics (both aggregate and individual) as well as expose query plans for review? Don't know much about the product so looking for a quick overview on how it helped you with your perf analysis/tuning efforts.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 17, 2009 at 8:58 am
Yes there is a top SQL screen.
You can view and sort by almost any parameter you wish. CPU time, Physical reads, Elapsed time, logical Reads etc.
When I am tunning I always start with logical reads. There is always fruit on that tree:-)
After Spotlight lists the top 50 or 100 statements you can click on any line and see the full statement. You can also click on any column header to sort by that column.
You can click on show query plan to see the XML queryplan dump (useless).
I cut and paste the statement into SSMS to see the query plan. There is a button to launch SSMS but when I get there the sql statement did not make it there with me.
The Spotlight for Oracle version does not have this problem. In fact the Spotlight for Oracle version is the better of the 2.
It also has an excellent index screen with bar graphs that show the Index cardinality and distribution. It is very easy to identify indexes that will cause unexpected query times.
The database tab is excellent as well. I looked at the buffercache (sql server) and noticed 1 table was using up 80% of the entire cache. I checked that table and found it had over 312 million records in it. I tracked that down and found over 311 of the records were duplicates because of a insert statement that had many 'ands' and 1 'or' withought any brackets.
These few items were where I identified and resolved most of the performance issues.
November 17, 2009 at 11:25 am
bwilliams-1049831 (11/17/2009)
Yes there is a top SQL screen.You can view and sort by almost any parameter you wish. CPU time, Physical reads, Elapsed time, logical Reads etc.
When I am tunning I always start with logical reads. There is always fruit on that tree:-)
After Spotlight lists the top 50 or 100 statements you can click on any line and see the full statement. You can also click on any column header to sort by that column.
You can click on show query plan to see the XML queryplan dump (useless).
I cut and paste the statement into SSMS to see the query plan. There is a button to launch SSMS but when I get there the sql statement did not make it there with me.
The Spotlight for Oracle version does not have this problem. In fact the Spotlight for Oracle version is the better of the 2.
It also has an excellent index screen with bar graphs that show the Index cardinality and distribution. It is very easy to identify indexes that will cause unexpected query times.
The database tab is excellent as well. I looked at the buffercache (sql server) and noticed 1 table was using up 80% of the entire cache. I checked that table and found it had over 312 million records in it. I tracked that down and found over 311 of the records were duplicates because of a insert statement that had many 'ands' and 1 'or' withought any brackets.
These few items were where I identified and resolved most of the performance issues.
Thanks for the feedback!! Good stuff there!
I do find stuff in the XML plan quite useful, although quite a PITA to get at unless you are using something like XML Spy.
Good catch on the INSERT issue! I have seen boolean logic problems before, but that one may take the cake as far as it's overall system impact goes! :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 17, 2009 at 11:31 am
If you do find good info in the XML dump please share it!
And my earlier post should have been 311 Million records were duplicated.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply