March 18, 2011 at 6:39 am
Hi all,
looking for some feedback from people who have used the 3rd party software from people like Idera, SQLSentry, RedGate, etc...
Im new to the SQL world and im trying to work out if these tools will:
Get me some performance improvements of the SQL boxs i use.
Help me learn about SQL performance and how to tune things.
Does using the tools take away from your SQL Learning experience, would it make me reliant on the tools to solve problems, or would it open my eyes to problems i would think off as a "newbie" and then i can read up on the problems somewhere else to fix them without the tools?
Hope to hear some views
thanks
S
Edit..
Just to add, our shop only have 1 main cluster (2 nodes, 3 instances) then 5-10 instances on app servers for single apps.
March 18, 2011 at 6:49 am
there is no point in using third party tool
every tool fires the same query and gives the result
better way is using activity monitor which gives exact result and flexible
u can use activity monitor of sql server 2008
it is very good
March 18, 2011 at 6:59 am
I have found that Confio Ignite is very useful for tracking down the exact SQL statements that are causing the most performance issues on a server.
Other than that, I really don't use third party tools for performance tuning. I use the DMVs (Dynamic Management Views) built into SQL Server, plus a lot of work with execution plans. You'll want to research both of those online (this site has a ton of valuable data on performance tuning, as do several others).
I do use RedGate tools for a lot of things, but not so much for performance tuning. I haven't used SQLSentry. I have used tools from ApexSQL, and those are quite good too, but again, they're for admin work and development efficiency, not so much for performance tuning.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 18, 2011 at 7:10 am
Hmm, not sure it's fair to say that they all just run the same query against the servers. Some of them are pretty in depth about the performance counters they gather and have some uses. They're not a bad start to have a look over the sort of things they're monitoring, but don't assume that anything they identify as a problem is necassarily 'bad'.
They are out of the box tools and many thresholds that are configured for monitoring are simply not relevent to certain application uses of SQL Server.
Personally, I find them only of limited use and find myself digging into the DMV's to get my performance information in most situations.
I find Activity Monitor to be severely lacking in many ways, not least of which, the terrible performance tuning of the queries it runs mean that it's next to useless in a genuine performance troubleshooting scenario as it hangs.
March 18, 2011 at 8:58 am
hi all thanks for the replies
for those of you that have used any tools before. Did you find it helped find problems when you were less experianced and highlight where tot narrow you search using normal methods?
I mean end goal i want to be good at my job... and i really dont want to rely on some software to find problems for me.. has it stunted your learning or progressed it?
Thanks again for the input..
March 18, 2011 at 10:22 am
you are headed down the wrong direction, you just cant buy these tools in order to become a good DBA.
they might tell you things like you have a memory issue/ or high cpu....they are not gong to tell you how to fix it. like the previous post...sql has all the tools...if you know how SQL works that all you need.
the tools are there to help in certain situation, say I want to know what a qry ran slow at 1 am...then the tools might help, or maybe you are maintain a lot of instances at one time
for the most part sound like you need to understand SQL so I would say hot the books!! read them all(lots good books out there, as a good DBA its your job to study, that's what we do, this is pretty much a self taught field). and then read them all again. then find start finding good blogs and follow this sight ...learn from other peoples issue try to answer some questions.even if you get them wrong! and last thing I would do is go the PASS web site get your company to flip for the vidoes and watch listen to them all ...then do it again..
a good dba needs to know as much as possible, for 2 reasons one to do preventive maintenance( he/she needs to preACT not reACT) and then when something does go wrong you need to know what to do fast! not something is broke how and where do I get info to fix it
GOOD LUCK
March 18, 2011 at 11:36 am
Saying that these tools are useless for a "good DBA" is like saying a good carpenter shouldn't need a tablesaw, since you can do everything you need to do with wood, just by using a pocketknife. Yes, a pocketknife and plenty of time will allow a skilled person to produce anything he needs to out of wood, but he better plan on spend a lot more time on things than someone with a full-blown woodshop.
What you'll find, if you put the time into it, is that all of the performance monitoring/fixing tools work off of queries of the DMVs, and off of one or more traces. You can, by studying what they do, learn how to work with those things yourself.
Let me give you two real-life examples:
First:
One place I worked had an online report that, given certain search parameters, would time out. Given other parameters, it worked as expected. No tools. The report worked off of LINQ, not stored procedures, so I first had to set up a trace to find out what queries it was running on each set of parameters (since it was dynamic SQL). The trace showed no significant differences in the queries. So I ran the queries as scripts in Management Studio, and analyzed execution plans. Again, very minor differences, but nothing that seemed to matter. So I ran the queries with Set Statistics IO On, and found that there was a huge difference in the number of Logical Reads between the two, on a table that wasn't even in either From statement. Turns out that burried deep in the heart of both queries was a view, which called another view, which referenced that table. So I dug into the views, and found that one of them was written in a very standard, usual, normal way, but one which caused it to process an internal cartesian join in a sub-query, because of some not-so-well-known factors in the query optimizer. I rewrote the view, got the report to run in under a second no matter what parameters it was run on, and had the job done. But it took over an hour to track down the issue before I could even begin solving it. It was, fortunately, an internal report used by a couple of people in one department, and they were okay on it taking that amount of time.
Second:
Some new code passed QA and was taken live during a late-night migration. The production site was okay with it, till about 10 the next morning, and then it absolutely tanked the whole server farm. The business was completely stopped, both all internal functions and all customer access. Six clustered servers with all CPUs at 100% and RAM being swapped onto the SAN, and the I/O channels all choked. Thousands of dollars per minute were being lost. The IS manager alerted everyone on the team about this, and, while others checked to see if it was a site/server/hardware/whatever issue, I checked in Ignite, and had an answer about the cause of the problem in under a minute. A particular UDF had been modified slightly, and was creating blocking and concurrency issues like you wouldn't believe. I dove into the dev system, called the dev over who had built it, got a functional description of the desired end result, rewrote the thing from scratch, tested it, and had a working solution in about two minutes. We rolled the modified UDF into production right there, and the servers immediately went out of cardiac arrest mode and into "we're bored, give us something to do" mode. Elapsed time, under three minutes total. Why? Because I had the right tool to tell me immediately where the exact problem was, with no sleuthing needed on my part.
Am I competent DBA who can tune a server without third party tools? Absolutely. Do I use them anyway because of the advantages they give in terms of speed and efficiency? Absolutely.
It's up to you. Would you drive from New York to LA without a GPS system and maybe some AAA maps, depending on some books to teach you how to navigate by the sun and stars? It can be done. But I prefer not to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 18, 2011 at 12:31 pm
456789psw and GSquared its like you are the two little angel and demons talking to me in each ear! thanks for advice..
456789psw
Thats what im worried by to be honest... Becoming dependant on the tools to give the answers and not know how to do it without... My current role is for 1 year doing SQL work and id like it to become full time deal. Im happy to sit at home and read all night long.. But reading only gets you so far and no book gives you experience. for the next year i will have 2 people go to for help.. 1 a dev guy who is pretty good on everything (Throw him a problem on any thing IT and he will solve it) and the last SQL guy who admits that he is not a DBA and cant do a DBA job.. He has a basic understanding of SQL but cant performance tune something and cant troubleshoot a issue. So help is short.
With daily maintenance and such i think i can read from a book and blogs etc and get a pretty good idea of what i should be doing
But troubleshooting problems and tuning (which is the bit i think interesting and the building blocks to being good at this job) Im looking to improve a lot and since i have no experiance any help is a bonus.
I recently brought a good selection of books (Internals and Troubleshooting, SQL Internals, Deep drives), i have some training vids for the 2008 exams etc and hopefully they can start filling in some boxs and i start building up the skill sets needed.
If i do use a tool I dont want be "held to them for answers and responses" id want them assist in a quick and timely response and be able to go to the books web etc and get more info. This is part of the question. WIll they assist me in doing the job to a high standard and not close off my learning process.
----
GSquared
Your coming from the other side. Im new to this and the tools can help find and resolve problems quicker! Then that is an excellent thing. Never mind in the cases you said where they sound really kind of amazing for getting to answers as quickly as possible
Did you always use the tools though out your career?
If you didnt use them from the start would you have found them a help or a hindrance to your becoming the DBA you are now?
Thanks again for the insight and keep it coming 🙂
March 18, 2011 at 12:44 pm
hi there dont get me wrong I agree with GSquared!
the tools help!...I have used them before no doubt really good for trending....yes you can create your only little widget with sql blah blah...
only point I am getting out.....is if understand whats going on under the covers its hard.....
let say the Tool what ever you choose says you have a slow query...and it tells you which in the problem...ok Now what? they help but only expect them to help so far....
In in nut shell...they can only hurt or HELP...they answer is they HELP!
but it sounds like you have it figured out, they help you to become a better DBA but they will not make you a DBA!
Just keep reading ....as far as which is the best tool...I have pretty much tried them all....none of them are below a 7 and none are a 10. All the vendors will let you try them out...get a price first, that may or may not lead in better direction....
GOOD LUCK
March 18, 2011 at 12:51 pm
stebennettsjb, let me ask these questions.
Who's spending the money to get these tools? And what's the budget?
And what else would you be doing if you had these tools to "take care of things?"
If you think you're the type of person to go onto the next project and ignore monitoring duties because some third party tool should be set up to alert you when there are problems, I would advise not sinking the time and effort until the tools until you know what sorts of things you need monitored and how you can work out the problem yourself.
If you're spending your own cash on these tools, and you're not a business owner or a contractor, then don't buy them. They're too expensive, especially if you're just playing around.
If, however, you're confident you can get these tools, set them up, and then learn the manual aspect of the monitoring and fixing, then by all means, buy some third party tool to help you out. So long as someone else is paying for it, or you can write it off as a business expense, that is.
March 18, 2011 at 1:58 pm
Brandie Tarvin
It would be work paying.. we are a charity so costs for most software is relatively cheap.
Good point though if work is ok paying for it and I use it once to solve a problem they would be happy to tell you the truth.
456789psw
"They help you to become a better DBA but they will not make you a DBA!"
That sums it up by sounds of it.. thanks
Thanks all for the advice its been great
S
March 21, 2011 at 4:38 am
Glad we could help.
March 21, 2011 at 6:37 am
To answer a few questions you posed specifically for me:
Your coming from the other side. Im new to this and the tools can help find and resolve problems quicker! Then that is an excellent thing. Never mind in the cases you said where they sound really kind of amazing for getting to answers as quickly as possible
To differentiate learning from production, I always set up a Proof Of Concept server alongside my dev and test servers. I use the tools there to learn how they do things. Seeing how someone else solved a problem is always a good learning experience. Even if I have a better solution than they do, it's still good to learn from it.
Did you always use the tools though out your career?
Nope. I didn't even know there were third party tools when I started out.
If you didnt use them from the start would you have found them a help or a hindrance to your becoming the DBA you are now?
Probably helpful, but that's speculation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply