Post reply

[ASK] how to measure SQL Server performance

  • June 24, 2010 at 2:08 am

    #234241

    Hi Guys,

    Is there any standard/reference for measuring SQL Server performance? I quite confuse. is my SQL Server is in good performance or not?

    it would be better if you can tell me what tools that we can use to measure our SQL Server.

    I use SQL Server 2005, 2008

    Thanks

  • June 24, 2010 at 8:30 am

    #1184539

    Marking performance good or bad mostly depends on requirements and of cause of what you are doing.

    For example, business expects that search of some data in a web based application returns results in less than 30 seconds.

    If your search does it, it is acceptable performance.

    When you are loading and transforming megabytes of data and business expectation is that the process run overnight, having it completed in just 30 minutes would be taken as very good performance (even, if it could be done in just 3 minutes).

    See, there is some common sence in it.

    I know that my opinion may not be supported by some experts here, but in reality there are no such thing as universal Good or Bad performance, or, at least, such defenition is irrelevant in the practicle life. There is acceptable or unacceptable performance and it depends on a particular case.

    The first tool to monitor SQL performance you can use would be MS SQL Performance Monitor, which is most likely part of your SQLServer EM installation. There are also many other SQL monitoring tools available (often used by DBAs). You can google for them and read reviews. I can list just some of them for you:

    SQLPower tools

    Ignite (from Confio)

    Spotlight (from Quest)

    there are many more around...

    P.S. As you have question about it, I can conclude that someone complained about performance of the system/application in your place, therefore if the reason of it lays within your database you can call it as Unacceptable performance (or bad, if you really like to call it like that). See, sometimes you don't need any tool to judge on it, because if the performance is unacceptable by business, they would not care if some performance tool will call it as "good".:-D

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • June 24, 2010 at 9:17 pm

    #1184781

    If I were you I would for sure look over Brent Ozar's website: SQL Serverpedia and view some videos that cover performance monitoring. EG:

    http://sqlserverpedia.com/search/?search=performance+monitoring

    There are three tools for monitoring performance: Perfmon, Event Logs, and SQL Profiler and which tool you use will depend on what facet of performance you want to assess.

    To capture data about the how the hardware is performing eg. System: Processor Queue Length, Network Interface:Output Queue Length, and Physical Disk: Avg. Disk Queue Length go to Perfmon. To get there type perfmon from cmd line.

    To capture data about how well stored procedures, ad hoc sql queries, and events pertaining to TSQL are doing go to SQL Profiler. To get there click on Tools menu from SSMS and select SQL Profiler

    For root cause research, pertaining to trouble starting or stopping an application, diagnose running out-of-space issues in the transaction logs or database files than go to the Windows Application Events Logs. To get there click Start->Administrative Tools->Event Viewer. Also can go to SQL Server Logs by typing sys.xp_readerrorlog which is also found in MSSQL10.MSSQL\MSSQL\LOG\errorlog.

    It's a start.:-)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply