Can anyone recommend a good performance tuning tool?
-
Hi all.
My company has been experiencing a lot of performance problems lately, mostly due to data growth over the past few years. I have been using SET SHOWPLAN_ALL ON to analyse the queries we know are performing badly, but it takes time to find where in the query the problems are occuring, and what to do to solve it.
Does anyone know of a good performance tuning tool that can analyze a query and make recommendations. I know about the Index Tuning wizard, but am thinking here about a 3rd party product.
Thanks,
Wayne
-
porting your problems to 2005 is quite good. The tuning advisor is very good I understand.
I normally use Teratrax or Diagnostic Manager to collect worst performing then use a test server and profiler / query plan analysis etc. to tune the code.
All I'll say is to health check your hardware first, make sure you don't have any bottlenecks - although it may sound obvious it isn't always so. I usually find i/o bottlenecks especially where clients are using raid 5, and especially if the tran logs are on raid 5. If you can add memory this can be a very quick fix.
For sql server make sure you're rebuilding indexes, ( in general ) make sure all tables have clustered indexes. Make sure you're updating stats on your databases too.
I don't know of any quick fixes, sorry!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Here's a bit more detail taking off on Colin's advice:
Here's my 'short list' for tuning:
Round I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round III
Profiler
Query Hints
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Hi,
I am also looking for some performance tunning tool and tool that
can help to monitor my SQL2k system at my client.
In the market, there are many vendor that provide SQL Server peformance
monitoring tool like Quest Spotlight, Embarcader Performance centre, Idera diagnostic
manager and Teratrax Performance monitor.
As mentioned, I also doing auto 'update statiscs', reindexing of all the tables but
after sometime, my database will still 'slow down' as reported by my client.
Hence, I am also looking for a monitoring tool and asking around whether anyone know
of any database pattern when our database size increases.
Do update me if you find any good tool.
-
I've used Diagnostic manager since it was called SQL Probe back in 1999 or so. I find it provides a good balance of statistical trend analysis, basic alerts and performance assistance. However, it all depends what you actually want to do. I actually use a whole combination of tools and processes for performance tuning and monitoring - far far too many to cover in a forum posting.
Essentially you must set up a series of maintenance tasks, if you have the window then rebuilding indexes and updating stats every night is a good start. The most important factor however is to establish a baseline for system performance and behavoir, Against this you can then pinpoint what has changed when your system slows down or has problems.
Basic tasks such as removing ntfs fragmentation are also important.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
At the risk of sounding a bit snippy (don't mean to)... the best performance enhancement tool is your brain. You've already isolated which procs are running slow... look for and try to eliminate different forms of "RBAR" (Row By Agonizing Row) processing including correlated sub-queries than have inequalities, cursors, While loops, complex joins (divide and conquer), inadvertant cross joins, change WHERE IN to a join, etc, etc.
Then, take a look at WHERE clauses... do you have criteria columns wrapped in functions? Those will prevent the use of indexes.
Do you have complex views that are returning too much data? Do you have "RBAR" triggers on your tables? Are your transactions unnessessarily long? Do you have complex user defined functions that prevent the use of indexes? Do the UDF's have their own "RBAR"?
Use the best performance enhancement tool available... look at the code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi,
I agreed that our knowledge, skill and brain are the best tool available in most cases.
The basic tool of SQL like profiler and monitoring tool are able to help us solve our issues/problem but they are tedious and time-consuming, require great skills to use them.
On the other hand, third-party tool are good as they save us all the trouble of learning the profiler or monitor tool and help us to pin-point the issues/problem. But they are mostly expensive and still require DBA/developers to solve the issue. Most of them with auto recommendation/tunning features are still no match for our BRAIN.
That why, I think we should still have some monitoring software to monitor
our DB.
-
yup agreed monitoring software has its place, I used diagnostic manager in stress testing ( for example ), sent the output of worst performing ( for each test ) to the stress test team, they then made their own decisions of which/what should be done. For the DBA team all we had to do was point diagnostic manager at the server ( and set the "worst" criteria ) OK could have done it with profiler but hey we had lots of servers and databases, this way it all ran in the background without our intervention, and it stored stuff about i/o and cpu and so on which could be pulled back to view at any time after. I believe SQL Power tools does much the same thing.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
oh and yes Jeff I do agree but getting the basics of what needs tuning with the least effort has always seemed a good bet to me and one which I prefer initially to be done with a blanket approach tool - for the finer/critical detail then I'm in there with you in profiler, QA,perfmon, a load of my own stuff and such- and my brain!!
But to be fair, as I observed with the criticism of Steve's article on sp tuning, it's always easier when you've been doing this for years, everyone has to start somewhere and tools can be useful.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
it's always easier when you've been doing this for years, Can't argue with that, Colin... it's why us old guys get paid so much
On the flip side of things, though, I've found that people who have and rely on an optimization tool, may never take the time to learn the process of writing optimized code to begin with... they just write what they want and then lean on the tool to fix it. A lot of times, there are "tricks" that the tool may not be aware of such as when to split code from a single complicated UPDATE into an SELECT INTO/UPDATE/JOIN (for example).
I've never used such a tool so I might be talking through my hat
, but it would be interesting what an optimization tool would do with the following garbage code in the presence of 4 million records (classic running total problem in the pre-2005 world)...SELECT t1.TransactionID,
t1.Amount,
(SELECT SUM(Amount)
FROM TestTable
WHERE TransactionID <= t1.TransactionID) AS RunTotal
INTO dbo.ResultTable
FROM TestTable t1
I'm thinking that the best it will do is to warn you that you have a correlated subquery or that a triangular join is present. If it rewrites or suggests rewriting the code into a cursor or a While loop (as most humans would do), it's not really optimizing... it's grasping at straws
.Now, if it rewrites the code to something nasty fast {edit: Must be faster than the 54 second solution on 4 million rows that I came up with}, lemme know which optimizer tool you're using and I'll buy it tomorrow.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
... in addition to getting a bit more 'old farts' ahve a wry sense of humor as well !
One 'old fart' to another !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Aye... thanks Rudy... you made my day!
"Non illigitimi ie carborondum"
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
agreed, although I don't consider myself an old fart - more a grumpy old DBA - but if you're not from the UK the grumpy old bit won't make much sense! ( There have been a series of Grumpy Old Men and Grumpy Old Women tv programs where more mature celebs have moaned about modern trends - has to be seen to be understood and it helps if you have teenage and above children - anyway I digress )
I have a used a tool that rewrote sql to optimise it, it was some time ago and it did actually work - sometimes - but the interesting feature was that you could see i/o vs cpu vs time stats tabulated to see which might be the preferred route. I think Idera might have it now. I remain unconvinced that it could beat the usual methods.
When I talk about using tools to gather info - diagnostic manager collects worst performing queries - with i/o cpu/time sorted to your choice - but it doesn't make any suggestions - I found it useful to have a method of filtering , say , all the sql that had exceeded 2 secs exectution time today, and as it counts the stuff you could differentiate between frequency too. As it just runs in the background I could then analyse the output to decide what I should concentrate on. As it also captured the queries and the user details it was useful to catch people connecting with tools they shouldn't !!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Diagnostic Manager... Now THAT's useful and I understand what you mean, now! That kind of monitoring tool can be priceless! Thanks for the info, Colin.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply