SQL Server Hardware Tuning and Performance Monitoring - Performance Monitoring Issues
(Page 2 of 5 )
Memory Performance Monitoring Now that you know the memory limitations in SQL Server imposed by the operating system, what can you do to optimise that memory and determine if you need more? This is where we get into what is popularly called The Art of Performance Monitoring .
Due to the dynamic nature of the memory in SQL Server 7.0 and SQL server 2000, Microsoft removed support for one of the most useful tools available in SQL Server 6.5, DBCC MEMUSAGE. Now, to get this information, you have to monitor a variety of performance counters in the Buffer Manager object and the Cache Manager object since the information is no longer statically mapped in a table like sysconfigures.
While this handy tool is listed as Unsupported now and no longer returns the breakdown of memory usage, try running it. You will see that this unsupported snap shot continues to return the top 20 list of buffered tables and indexes. This can be a very handy list. When analysing performance of an individual application un a test environment, it can be invaluable.
The Art of Performance Monitoring
Performance monitoring is an art, in that it is a combination of talent, experience, knowledge, and sometimes just plain luck. How do you know if you can do it? You have to try, try, and try again. Keep at it, read up on it. Keep a performance monitor continually open against your production server. Here are some good guidelines to get you started.
1. Make sure that you are running your typical processes (SQL Server) and work loads (queries and stored procedures) during your monitoring.
2. Do not just do real-time monitoring of your servers. Capture long running logs. In Windows NT, install the Datalog/Monitor service from the NT Resource Kit; this functionality is available out of the box in Windows 2000.
3. Always have disk counters turned on by running from a command prompt the command DISKPERF Y and then rebooting. Even in a production environment, the overhead is minimal; the last thing you want to do in the middle of a crisis where logical and physical disk counters are necessary is to have to reboot.
4. For routine, daily, desktop monitoring, set up the chart windows with an interval of 18 seconds. In both the Windows NT PerMon and the Windows 2000 MMC SysMon, this will give your chart a Window of 30 minutes. For me, this has proven to be the ideal interval for both seeing the past and minimizing the impact on the server.
5. Utilize SQL Profiler for individual queries and processes in coordination with PerMon or SysMon to get a good picture of the impact of individual queries.
6. Know the terminology of Performance Monitoring. Objects are lists of individual statistics available. An example is the Processor object. A counter is a single statistic that falls under the heading of an object. An example is the per centprocessor Time counter under the Processor object. An instance is further breakdown of a counter statistic into duplicate components. Not all counters will have separate instances. The per centprocessor Time counter has instances for each processor and a _Total instance as a summery of all processor activity.
7. Know your tools. While you may know how to set up a chart in PerMon, learn how to set up a log with Datalog or Performance Log. Other tools to be familiar with are DBCC MEMUSAGE, Task Manager, and SQL Enterprise Manager Current Activity.
8. Do not be afraid to experiment. The BackOffice Resource Kit has tools in it for creating test data (DataSim), creating test databases (DBGen), and simulating loading from multiple clients (SqlLS).
SQL Performance Monitoring and Bottlenecks
Bottlenecks occur when the hardware resources cannot keep up with the demands of the software. For example, when a software process or combination of processes, wants more I/O from a disk than the disk can physically deliver, a bottleneck occurs at the disk. When the CPU subsystem becomes too saturated and processes are waiting, a bottleneck has occurred. Bottlenecks are usually fixed in one of two ways.
The first is to identify the limiting hardware and increase its capabilities. In other words, get a faster hard driver or increase the speed of the CPU. The second way is to make the software processes utilize the hardware more efficiently. This could be done by putting an index on a table so that either the disk I/O necessary to service a query is reduced, or the CPU units necessary to process a join are lessened.
The following are five key areas to monitor when tracking server performance and identifying bottlenecks. Each bottleneck candidate will have varied performance monitoring objects and couners to consider.
- Memory usage SQL Server needs, relative to itself and to the operating system memory. If SQL Server has enough memory but the operating system is starved of memory such that it has to frequently swap through the pagefile to disk, overall performance will suffer dramatically.
- CPU processor utilization High CPU utilization rates indicate the CPU subsystem is underpowered. Solutions could be upgrading the CPU or increasing the number of processors.
- Disk I/O performance Failure of the disk or disk controller to satisfy read or write requirements in a timely manner impacts performance.
- User connections. Improperly setting the number of user connections could rob memory otherwise available to SQL Server.
- Blocking Locks One process keeps another process from accessing or updating data. This is particularly noticeable to users and is the cause of some of your most server performance problems from a user perspective.
Next: Memory Tuning Issues >>
More SQL Server Articles
More By Sayed Geneidy
/ 36 
