SQL Server Performance

Product Information

Discussion in 'Third Party Tools' started by DavidB, May 16, 2003.

  1. DavidB New Member

    As a pre-cursor, please keep replies to this post kind as I don't want to start a product slamming post. If there are some major negatives associated with the product please email me directly as I'm sure the site owner doesn't want to deal with negative advertisement. Ok, with that being said.....

    Has anyone used Precise InDepth for SQL Server? We are looking at some performance tools and this one came up on the radar. I am not familiar with it at all but would love to hear what others have seen.

    My major concern is that all these products say that they have a very small performance impact footprint but I have not yet seen that to be true with any of them. This may be different so, I would like to hear comments on this specifically. I would also like to hear general value-add items that the product brings to the table.

    Thanks in advance.


    David
  2. satya Moderator

    PROFILER and PERFMON from OS are one of the reliable tools to assses the performance on SQL server, so far I've used. We can name few third party tools which are relaible to use against performance bottlenecks.

    DO you see any performance bottleneck currently on your SQL Server?
    Any measures so far to overcome this situation?

    _________
    Satya SKJ
  3. DavidB New Member

    Satya,

    I definitely utilize PerfMon and Profiler as well and that is my preference to stay with the native tools if possible.

    Yes, we have a server that is problematic but it is primarily due to the configuration. Working on fixing that but have to complete application testing before upgrading and reconfiguring.

    With the configuration the way that it is today I can't run profiler against the server or it will crash with insufficient memory errors. What fun!

    Ultimately, I am not looking at the tool to fix this problem though more looking at the tool as a general monitoring tool for the DBA group to work with.

    Have you used it and does it bring any value add to the table over Profiler and PerfMon? Additionally, would it be able to run where Profiler can't because of the memory problems?

    Thanks!



    David
  4. vbkenya New Member

    Generally, I don't believe any third party tool could actually run and manage to have a smaller memory (read performance) footprint than profiler. So if Profiler is going to crash your server, then it is very likely that MOST other tools would have the sme effect on you and your server.

    The only time I would find myself using third party tools for profiling or performance monitoring is when I required some different kind of reporting (like HTML User interface features or different forms of graphical displays).

    All the same, I have nothing against third party tools.
  5. Chappy New Member

    Not sure I agree with the point about MS software necessarily producing a smaller footprint than third party tools. The way I see it is this:

    1) Third party tools: often a product such as this is a major tool in that companies revenue generation. They will clearly treat it with a lot of attention and hopefully listen to feedback. To Microsoft, IMO they are writing tools such as these merely to support their family of products. They (understandably) put far less effort into the supporting tools, than they due the tool which makeas the sale. This isnt a bad thing, it opens up a niche market for many companies.

    2) I have nothing against MS (SQL is one of my favorite pieces of software, on any platform, ive ever used). Having said that, I think MS client tools are often dreadful (from a GUI point of view), in the respect that they often miss key functionality that the vast userbase often ask for. Is this due to lack of testing in a representative environment, or due to sponsorship deals or marketing politics ? who knows!

    A typical example is perfmon. Yes it does its job, and it monitor a given systrem very well. But what it lacks is a particularly functional gui. Where are the options to see the exact point in time that a performance peak occurred? Unless im mistaken you have to set the filter via trial and error, to find the period in time you want to view in detail.

    Data Export wizard also, why does it allow you to go 'back' through the wizard, up to every point until the operation has been completed. More often that not, I perform an operation and want to adjust the parameters slightly and repeat it (ie, export a different table etc).

    Also dont forget often many companies work alongside with MS to produce very specific tools. Its easy to produce software which takes a small footprint, its harder to make the same software robust and usable, to the same requirements, in a short amount of time. So my opinion is that whilst MS supporting tools give you everything you need on a basic level, if you want quicker/more concise/more usable results, dont rule out third party software.




  6. joechang New Member

    i was at the sql connections conference last week where precise had a booth.
    one of the features of precise was that it could sort by distinct queries over various time intervals

    while it is possible to get this information from profile, getting a nice graphical display takes some work

    my observation on the server load is that most tools can be configured for low impact or high impact depending on the information being collected. most specifically, do not configure profiler to save directly to a table on the production server, even more particularly by statement level
    save it to a file preferably off the production server

    if you use stored procs exclusively, it is fairly easy to parse distinct procs from profiler, ie, just parse for the substring between "exec" and the space that follows the proc name

    if you use explicit queries (not parameterized queries)
    ex.
    select * from table1 t1, table2 t2
    where t1.x = '1' and t1.id = t2.id etc

    it is more difficult to find distinct queries since it is necessary to distinguish between join conditions and SARGs

    i think an expert ought to be able to find the performance issues with profiler, but an intermediate level dba would find it very helpful

    btw my script for sorting stored procs from table Trace (saved to a table not on the production db, first saved to a file )

    SELECT wt=SUM(CPU),cnt=count(*),CPU=AVG(CPU),Reads=AVG(Reads),rData
    FROM (SELECT CPU,Reads,SUBSTRING(sData,1,CHARINDEX(' ',sData,1)) AS rData
    FROM (SELECT CPU,Reads,SUBSTRING(tData,CHARINDEX('exec',tData,1)+5,100) AS sData
    FROM (SELECT CPU,Reads,CONVERT(VARCHAR(7900),TextData) AS tData FROM Trace) t ) s
    ) r WHERE rData <> ''
    GROUP BY rData
    ORDER BY wt DESC

Share This Page