| Author |
Topic |
|
johnboyd
Starting Member
9 Posts |
 Posted - 2008-04-25 : 15:53:31
|
| This may be a very basic question but I really need to get to make sure and be sure 100% of this.I have a SQL server already in Production. How do I make sure it is performing normal. How do I make sure it does not have any bottlenecks with respect to CPU, Memory or Disk I/Os? How can I prove this to myself and to others?Thanks in AdvanceJohn |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-25 : 15:57:03
|
| You can check for hardware bottlenecks via Performance Monitor. You can check for long-running queries via SQL Profiler. You can use the Performance Dashboard reports in Management Studio if you install them, but you must have at least sp2 installed. There are lots of third party tools that can help you with this, but you can get all of the information for free with SQL Profiler (or SQL Trace if you prefer not to use the GUI) and Performance Monitor. You need to be fairly familiar with them in order to get any useful information. I'd suggest learning how to use them via books and sql-server-performance.com. Let us know if you have any specific questions. It would be impossible to teach you how to use these tools over an Internet site when the help is for free.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
johnboyd
Starting Member
9 Posts |
Posted - 2008-04-25 : 16:46:44
|
| All I want to know is if there is a bottleneck on the server or not with the current load? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-25 : 16:48:27
|
| And you'll find out that information by running Performance Monitor and/or SQL Profiler/SQL Trace.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
johnboyd
Starting Member
9 Posts |
Posted - 2008-04-25 : 16:56:22
|
| OK sorry about not being specific in my original post. I do know I will get the results from performance monitor. But I am looking for more then that. I am trying to find out what counters I should be using in performance monitor and what is considered is normal and what is considered as a bottleneck. How long should I be running the peformance monitor for? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-25 : 17:09:14
|
You will find that information on the sql-server-performance.com site. That's primarily how I learned to use Performance Monitor. But here is a document I wrote 5 years ago that might help you:quote:
The following counters should be monitored periodically to determine if the server is experiencing performance problems and to investigate trend analysis.Disk Usage Counters:• Physical Disk: Avg Disk Queue Length – If the number is greater than two on any disk, consider adjusting the disk configuration or rearranging data.• Physical Disk: % Read Time – If the number is greater than 90 on any disk, consider adjusting the disk configuration or rearranging data.• Physical Disk: % Write Time – If the number is greater than 90 on any disk, consider adjusting the disk configuration or rearranging data.Memory Counters:• Process: Page Faults/sec: sqlservr – This number should be at or near 0.• Process: Working Set – This counter shows the amount of memory used by a process. If this number is consistently below the amount of memory SQL Server is configured to use, SQL Server is configured for more memory than it needs.• Memory: Available Mbytes – This counter should be greater than 5MB at all times. If it isn’t, then SQL Server will start to page. Approximately, 4-10MB will be needed by the operating system. Ideally, this counter should be greater than 64MB.• Memory: Pages/sec and Page Faults/sec – If the number is high (over 20), another process may be using too much memory, or the server needs additional memory.• SQL Server: Buffer Manager: Buffer Cache Hit Ratio – The counter should be 90 percent or greater. If it isn’t, add more memory.• SQL Server: Memory Manager: Total Server Memory (KB) – If this counter is consistently high compared to the amount of physical memory in the computer, more memory may be required.Processor Counters:• System: Processor Queue Length – If this number is greater than two per CPU, then more processor power needs to be given to the server.• Processor: % Privileged Time – If this counter is greater than 20, then there is a possibility that I/O may be bottlenecked on the server.• Processor: % Processor Time – If the number is greater than 90, then either adjust how the applications are using the database, tune the database, or add more processor power to the server.• Process: % Processor Time: sqlservr – Compare this to % Processor Time. If other processes are consuming too much time, terminate the nonessential processes or move them to other servers.SQL Server and Database Counters:• SQL Server: General Statistics: User Connections – Use this counter to track the number of user connections. Set an alert to notify when the number of current user connections reaches the maximum.• SQL Server: Locks: Number of Deadlocks/sec – This counter should be 0.• SQL Server: Databases: Transactions/sec – This counter is for trend analysis.It is also important to monitor the events in SQL Server. This is accomplished by running SQL Profiler. When using SQL Profiler, you should only monitor the events that you are interested in. Monitoring too many events adds overhead to the server and can cause unnecessary performance degradation. Always run SQL Profiler on a machine other than the server that you are profiling and filter out the system processes.
Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-25 : 17:42:16
|
| Wow thats good Tara. Probably good to keep in script library. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
johnboyd
Starting Member
9 Posts |
Posted - 2008-04-25 : 17:50:50
|
| Excellent!!! That's what I was looking for. This will give me a very good starting point. I will report my findings.Thanks a lot for guiding me the right direction. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-25 : 18:08:04
|
quote:
Originally posted by Peso
Maybe this guy "borrowed" the information from you, Tara?http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64044
Nah, all of my information came from the sql-server-performance web site and a Windows performance book that I have. I trust that my sources have correct information.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
johnboyd
Starting Member
9 Posts |
Posted - 2008-04-28 : 09:53:52
|
| I just did a very basic monitoring of my Disk counters on the SQL server. I have found the following. % Disk Write Time:Min: 0.043 Max: 14496Avg: 340Disk Queue Length:Min: 0.001Max: 480Avg: 7.92Note: The results posted are just based on what I saw for like 2 min. But I can confirm that I see the same pattern throughout the day. Also the most interesting part that I noticed was the fact that most of the time counters for "Disk Queue length" and "% Disk Write Time" are 0. I just see spikes of 2-3 seconds 2-3 times in a 2 min window which has a very high value which also brings up the average of the both these counters. I have checked many other counters but nothing that makes me think there is a performance issues except for Disk writes. So posting the results here. I would appreciate if you could interpret the results for me. Per the document Tara sent, it sure does seem to me like a write perfomance issue, but want to make sure what is going on here? |
|
|
|
johnboyd
Starting Member
9 Posts |
Posted - 2008-04-28 : 16:23:46
|
| ???? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-28 : 22:11:53
|
| Disk queue length sounds too long. |
|
|
|
johnboyd
Starting Member
9 Posts |
Posted - 2008-04-29 : 08:36:06
|
quote:
Originally posted by rmiao
Disk queue length sounds too long.
100 % agreed. But like I mentioned they are caused by the high spikes. When I observer the performance monitor screen, I see the disk queue length almost "zero" all the time, except for 2-3 times when it spiked and goes upto 400-500 for around 2 seconds. This happens approximately once in every 40 seconds, but brings up the average of disk queue length to more then 8.What does this mean? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-29 : 22:31:19
|
| Happens every 40 seconds? I'll keep eye on it if it's my server. |
|
|
|
|
Performance Question