时间：2021-07-01 10:21:17 帮助过：3人阅读
As you probably already know, SQL Server is very good at tuning itself. It has the ability to monitor itself, and through a feedback loop, it knows how to internally adjust and tune itself so that it keeps running efficiently, even when external events, such as the number of user connections or the amount of available RAM, change over time.
But as we all know, SQL Server’s ability to self-tune is not perfect and does not take into consideration every possible aspect that affects its performance. As a DBA, we need to help SQL Server along, providing it the resources it needs for it to do a good job serving up data.
As a good DBA, we don’t want to find out from our users that SQL Server is having a performance problem. Instead, we want to be proactive and catch performance problems before they arise. That is what Window’s Performance Monitor can help us do. It is a tool that allows us to monitor what is going on with our SQL Server, and to provide us the information we need to make decisions on how to best tune our SQL Servers.
Performance Monitor is an important tool, because it not only provides us with information on how SQL Server is performing, but it also lets us know how Windows Server is doing, which of course directly affects SQL Server’s performance.
*****
The “Performance Monitor” under the “Microsoft SQL Server” entry under your Start Menu is the same “Performance Monitor” under the “Administrative Tools” entry under your Start Menu. They are the same programs. What is different is that when you bring up Performance Monitor from under the “Microsoft SQL Server” entry, is that it comes up already running several pre-configured SQL Server performance counters.
The Performance Monitor under the “Administrative Tools” entry does not come with any pre-configured counters loaded. Personally, I dislike the “Microsoft SQL Server” option and always choose the Performance Monitor option under “Administrative Tools.” This way, I always get to choose the SQL Server Performance Monitor counters I prefer to use.
*****
If you are like me, you have one or two SQL Server production servers that are very important to monitor. To help me keep tabs on these “high-visibility” SQL Servers, I always run an instance of Performance Monitor in the background on my Windows NT 4.0 or Windows Server Workstation desktop pointing to these servers. I don’t log this data, but I like the ability to very quickly take a look at key performance counters (in chart mode) throughout the day.
Since Performance Monitor is always running, I don’t have any excuse not to take a peek at my SQL Server’s performance at various times throughout the day. You would be surprised at the things you find, including bottlenecks you may not know you had. In addition, after some time, you begin to better learn how your server’s perform, which makes it easier to diagnose potential problems as they arise.
In order to minimize the affect of this constant monitoring on your SQL Servers, you will not want to monitor too many counters. Here are the key counters I like to watch on a regular basis:
Based on my experiences and preferences, these are the counters I like to watch regularly. If I see something interesting in these counters, I often add additional counters as necessary to get a more detailed look at what is going on. I run Performance Monitor from my desktop, not the server I am monitoring in order to minimize overhead on the SQL Server.
By default, readings will appear every second and less than two minutes at a time will appear on the graph. I don’t find this time frame all that useful, so I change it to 36 seconds, which displays an hour on the screen of activity. This gives me a good feel of the health of my critical SQL Servers without putting any undue overhead on the server.
If you don’t already check your SQL Server’s key performance counters throughout the day, you need to start this important habit. The more you learn about how your servers run, the better DBA you will be.
*****
Also see:
Monitoring Processor Usage in Windows Server
Monitor Windows Server System Memory and Pagefile Usage
Monitoring Disk Usage in Windows Server
**************
Once you have identified the Performance Monitor counters you like to use, you can save them in a file and then later reload them when you want to see them again. This way, you won’t have to re-add the counters to Performance Monitor each time you use it. In fact, you can create different sets of counters, with different names, so you can track different types of counters at a time. In addition, each different type of Performance Monitor’s modes, such as “Chart” and “Log,” allows you to store its own set of counters.
How you use Performance Monitor to do this depends on if you are using Windows NT 4.0 or Windows 2000.
If you are using Windows NT 4.0, then use Performance Monitor’s “File” menu option to save and load your counter files.
If you are using SQL Server 2000, then you will use the “Console” menu option save and load counter files.
*****
When monitoring your server using NT Server 4.0′s “Performance Monitor,” or Windows Server’s “System Monitor” tool, keep in mind that the more counters you monitor the more overhead that is required to perform the monitoring. This is true whether you are viewing a performance chart, logging counters, or creating alerts based on counters. Because of this, don’t monitor counters you don’t need to monitor. If you are using multiple counters for your monitoring, but soon realize that one or more of them are of little value to the task at hand, then remove these counters from your monitoring.
One difference between NT Server 4.0′s Performance Monitor and Windows 2000′s System Monitor is how logging is done. In Performance Monitor, you must log entire performance objects, you can’t just log individual counters. This can lead to very large log files with a lot of data you don’t need. In System Monitor, you are now able to log individual counters, not just entire objects. This makes these logs much smaller in size, making it more practical to log over long periods of time.
*****
When monitoring your server using NT Server 4.0′s “Performance Monitor,” or Windows Server’s “Performance” tool, keep in mind that how often you set these tools to collect counter data affects the amount of overhead experienced by your server. For example, the default counter collection interval for displaying near real-time charts of performance counters for these two tools is 1 second. If you increase this to .5 seconds, then overhead is essentially doubled. But if you decrease it to 5 seconds, then overhead is substantially reduced).
So how often should you collect performance counter data? It depends on what your goals are. In some cases, you need to collect data in near real-time (every second), and it other cases, collecting it every 5, 15, 30 seconds is adequate. The key is to not collect data more often than you really need to. Personally, when I collect performance counter data to display as a chart, I use 36-second intervals. Experience has proven to me that this time interval best meets my needs for monitor SQL Server’s performance, and it also allows exactly one hour of chart data to be on the screen at one time. Of course, your mileage may vary.
*****
When monitoring a SQL Server using Performance Monitor, don’t run Performance Monitor on the same server you are monitoring. Instead, run it on a different server or workstation and remotely monitor the SQL Server. Running Performance Monitor on the same server you are monitoring will skew the results.
Along the same train of thought, don’t run both Performance Monitor and Profiler at the same time, even if you are running them remotely. This is too much overhead and will cause your SQL Server to suffer some performance degradation. In addition, running both together can cause Performance Monitor to produce less than accurate data because of the overhead of Profiler running at the same time.
Continues…
http://www.sqlservergeeks.com/
sql 性能优化
标签：