SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


[ASK] how to measure SQL Server performance


[ASK] how to measure SQL Server performance

Author
Message
b.eurika84
b.eurika84
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 14
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
Eugene Elutin
Eugene Elutin
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20864 Visits: 5478
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
KoldCoffee
KoldCoffee
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7011 Visits: 1905
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.:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search