Home arrow SQL Server arrow Page 2 - SQL Server Hardware Tuning and Performance Monitoring

SQL Server Hardware Tuning and Performance Monitoring

In this article, Sayed will lead the way in presenting you guys with a performance enhancing solution for your system. Discover how to optimise your system by investigating what hardware you really need for your applications.

Author Info:
By: Sayed Geneidy
Rating: 4 stars4 stars4 stars4 stars4 stars / 50
August 14, 2003
  1. · SQL Server Hardware Tuning and Performance Monitoring
  2. · Performance Monitoring Issues
  3. · Memory Tuning Issues
  4. · More Performance Monitoring Issues
  5. · SQL Server Performance Tuning Resources, Websites and Articles

print this article

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.

blog comments powered by Disqus

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials