November 4, 2009 at 2:15 pm
Which tool do you use and why? Why were others ruled out? Here are the top 3 sql server magazine chose in last year's awards. We currently use spotlight but it's reporting on historical events isn't perfect. Our sister company uses Quest's Performance Analysis for SQL Server (PASS ) so I am considering that even though it wasn't in this top 3. Spotlight requires a diagnostic server and Quest databases on each monitored server, but no agent on the sql server. Quest's PASS does require an agent on each sql server.
2008 Gold winner: SQL diagnostic manager
Idera • http://www.idera.com
Silver:
Spotlight on SQL Server Enterprise
Quest Software • http://www.quest.com
Bronze:
Embarcadero Performance Center
Embarcadero • http://www.embarcadero.com
November 4, 2009 at 8:18 pm
Indianrock (11/4/2009)
Which tool do you use and why?
SQL Server Profiler, and a healthy dose of grey matter.
Why were others ruled out?
They're available for use on every SQL Server (not including express and the like) I work with, I don't have to justify them in any software budget (except for coffee), and I don't have to put up with any of the nuances you spoke of (unless the grey matter has sounded the low level alarm on coffee levels ;-)). I've also found that even when such 3rd party tools as those you speak of do find something, no one knows how to fix the performance problem(s) because most performance problems are in the T-SQL being executed either by the GUI or by the server.
Ironically, the reason for the need for such 3rd party tools is the same reason that the tools don't normally help in a lot of companies... no one knows how to properly use what they bought or built (including T-SQL). 😛 I saw such falacy occur just a couple of months ago. The 3rd party tool led them straight to the stored proc which was causing 90% of all the performance problems and no one could figure out how to make it faster (which is why they called me in).
Equally as ironic is that the 3rd party tools are just pretty flashing lights on all but the highest profile problems (because most companies just won't spend the time fixing things that could become a problem in the future) and it's the high profile problems that are the easiest to find with SQL Server Profiler. 😉 Also, such tools may or may not find the performance problem if most of the data is pushed to some type of Web Cache instead of doing hits on the SQL Server.
If it were me, I'd spend the money on coffee and start peeling one potato at a time using SQL Server Profiler. And, don't forget... whether you do it the way I would or you use a 3rd party tool, someone is actually going to have to fix the problem and more than 95% of the time, the problem will be in some code some where. That's the final irony in all of this... if the person fixing the problem is smart enough to fix it and was part of the original development team for the project, there shouldn't be a problem now.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2009 at 10:00 pm
Many of those tools also just use queries, views, and information that is already available through sql server and perfmon.
To add to the consideration, the agents that get installed with these monitoring tools can also cause performance problems themselves.
All in all, the tools might be nice, but as Jeff said - the grey matter to fix the problem still needs to be there. And if you can get the same information for free with just a little more effort - that might be the best solution.
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 5, 2009 at 5:29 am
In our company there is no on-site full-time DBA so anything that will help me check things and be alerted to problems quicker is desirable. If it can actually make recommendations of any kind, so much the better. I've used profiler and currently use Quest's Spotlight.
I guess if the company is willing to make a purchase I won't fight them, just need to recommend 3 to management -- 1st choice, 2nd, 3rd.
Right now I'm fighting a battle to stop our Systems team from adopting commvault in place of any native sql backup restore scheme I currently have in place. So, I'd rather have something like LiteSpeed than another performance tool at the moment, but that wasn't offered.
November 5, 2009 at 5:59 am
Ah... now that's a horse of a different color. You'll still have many of the problems I and others have spoken of but I'm with you... take anything they're willing to buy that will actually help. I also agree that buying good backup software would probably be more important. I've used RedGate's compressed backup software and it makes things really easy to manage especially when it comes time to doing a point-in-time restore. I can't say anything about other backup software (other than the native SQL backups) because I've not had the opportunity to use them.
What would be an even more practical investment on the part of the company is... send you to a good DBA school for a week. You sound dedicated and very practical... I think it would be an excellent investment for the company to make and I think you could easily make a case for it.
Unfortunately, I can't answer your original question because I don't use such 3rd party performance tools.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2009 at 8:16 am
I ageee with JEFF the 3rd party tools point to a direction but you still need the skills to fix it!
The ones you have listed are both good...quest and idera....
Like the other post...that fact of the matter if you know the SQL the tools are pretty much already there...the 3rd part tools make it a little faster to find..but you still got to know how to fix the dam when you fins the LEAK!!
good looks....I would use this as an opportunity don't go to training....you cant learn to be a dba in a week...that a joke...been to a sql got certified company paid all 8k...but at the end of the day I knew enough to be dangerous....
BUY books and READ READ READ....as my Database professor use to tell us STUDY STUDY STUDY no easy way out!
GOOD LUCK
November 5, 2009 at 11:43 am
The Idera tool is good.
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 5, 2009 at 1:49 pm
I am using Spotlight for SQL Server.
I am also using Spotlight for Oracle.
Spotlight for Oracle is the better of the two.
The show query plan xml dump in the SQL Server version is useless. You have to copy the sql out of spotlight and run it in ssms to get a query plan:-(
I have each installed on different desktop-like servers and remote desktop connect to both of them.
For the SQL Server version you do need to set up a diagnostics server and database (can't be sql express) but not one for each server being monitored. You do need a license for each server monitored. If you have 2 instances installed on one server you only need 1 license.
Since purchasing and installing the separate spotlight softwares my profile has gone up considerably and database issues have gone down considerably.
I do agree you still need to know what to do with the data spotlight giver you.
Bottom line I highly recommend Spotlight for either version.
November 5, 2009 at 1:59 pm
We have Spotlight and I need to learn more about it because sometimes when I look at history for a particular server and drill into User/Sessions the processes that were running at that time show, but the sql text doesn't populate.
November 5, 2009 at 4:46 pm
That is correct Spotlight does not keep history on running sql.
The alerts by time are great on Spotlight for Oracle but almost useless on SQL SERVER.
I got a blocking event a couple of days ago. Since then I have a line for every pid that was blocked.
My alerts by time for SQL Server is now hundreds of lines. I can filter them out but I want to know about blocking events. I just don't want to know about hundreds of pid that were blocked 2 days ago. it would be nice to drill down to the blocked processes.
About learning to use Spotlight. I found it very easy to learn. As well Quest did a training seminar for us on both Oracle and Sql Server.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply