Monitoring Sql Server 2000 Performance: Tips And Tricks

Monitoring the performance of SQL Server 2000 can be a challenging task, but there are several tools and techniques available to help identify and resolve any issues. One common approach is to use the Performance Monitor, which is a built-in tool in Windows 2000 that allows users to monitor both Windows 2000 and SQL Server 2000 performance indicators, or counters. By tracking key counters such as CPU usage, disk I/O queuing, memory consumption, and network bandwidth, administrators can identify potential bottlenecks and fine-tune their SQL Server applications. Additionally, SQL Server 2000 itself offers various tools for performance tuning, including the Query Analyzer, Profiler, Index Wizard, and the Performance Monitor. These tools can help optimise Transact-SQL code, identify performance problems, and recommend specific indexes to improve query performance.

CPU usage

Monitoring CPU usage is one of the most important tasks in performance monitoring and troubleshooting. A high rate of CPU usage may indicate the need to upgrade the CPU or add multiple processors. Alternatively, it may indicate a poorly tuned or designed application.

Tools for Monitoring CPU Usage

SQL Server Management Studio

Performance Dashboard is a useful report that can be used to monitor CPU usage. Once you connect to your SQL Server or Azure SQL instance, you can select Reports > Performance Dashboard and see the current and historical values of CPU usage.

You can also identify the top CPU consumers by selecting another report: Reports > Standard Reports > Performance - Top Queries by Average CPU time.

Dynamic Management Views

The SQL Server Database Engine provides a set of useful system views that can be used to find CPU usage. For example, you can use the sys.dm_os_ring_buffers view to find CPU usage:

SELECT cpu_idle = record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int'), cpu_sql = record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') FROM ( SELECT TOP 1 CONVERT(XML, record) AS record FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE '% %' ORDER BY TIMESTAMP DESC ) as cpu_usage

Performance Monitor

Perf Mon is a tool that can be used to track CPU usage on the server or virtual machine where your SQL Server is running. Some of the parameters that can be monitored include:

• Processor: % Privileged Time
• Processor: %User Time
• System: Processor Queue Length

Troubleshooting High CPU Usage

If you are experiencing high CPU usage, there are several steps you can take to identify and resolve the issue:

Step 1: Verify that SQL Server is causing high CPU usage

Use one of the following tools to check whether the SQL Server process is contributing to high CPU usage:

• Task Manager: On the Process tab, check if the CPU column value for SQL Server Windows NT-64 Bit is close to 100%.
• Performance and Resource Monitor (perfmon)
• Counter: Process/%User Time, % Privileged Time

Step 2: Identify queries contributing to CPU usage

If the Sqlservr.exe process is causing high CPU usage, it is likely due to SQL Server queries that perform table or index scans, followed by sort, hash operations and loops. To get an idea of how much CPU the queries are currently using, run the following statement:

DECLARE @init_sum_cpu_time int, @utilizedCpuCount int --get CPU count used by SQL Server SELECT @utilizedCpuCount = COUNT( * ) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE' --calculate the CPU usage by queries OVER a 5 sec interval SELECT @init_sum_cpu_time = SUM(cpu_time) FROM sys.dm_exec_requests WAITFOR DELAY '00:00:05' SELECT CONVERT(DECIMAL(5,2), ((SUM(cpu_time) - @init_sum_cpu_time) / (@utilizedCpuCount * 5000.00)) * 100) AS [CPU from Queries as Percent of Total CPU Capacity] FROM sys.dm_exec_requests

Step 3: Update statistics

After identifying the queries with the highest CPU consumption, update the statistics of the tables used by these queries. You can use the sp_updatestats system stored procedure to update the statistics of all user-defined and internal tables in the current database.

Step 4: Add missing indexes

Missing indexes can lead to slower running queries and high CPU usage. You can identify and create missing indexes to improve performance.

Step 5: Investigate and resolve parameter-sensitive issues

You can use the DBCC FREEPROCCACHE command to free plan cache and check if this resolves the high-CPU-usage issue. If the issue is fixed, it indicates a parameter-sensitive problem (PSP, also known as "parameter sniffing issue").

Step 6: Investigate and resolve SARGability issues

A predicate in a query is considered SARGable (Search ARGument-able) when the SQL Server engine can use an index seek to speed up the execution of the query. Queries that prevent SARGability can lead to table or index scans and high-CPU usage.

Step 7: Disable heavy tracing

Check for SQL Trace or XEvent tracing that may be affecting the performance of SQL Server and causing high CPU usage.

Step 8: Fix high CPU usage caused by spinlock contention

If your SQL Server instance experiences high CPU usage due to spinlock contention, you can refer to the following resources for troubleshooting guidance:

• SOS_CACHESTORE spinlock contention: Enable trace flag T174 using the DBCC TRACEON (174, -1) command.
• SOS_BLOCKALLOCPARTIALLIST spinlock contention on large-memory machines: Apply Cumulative Update 21 for SQL Server 2019.
• XVB_list spinlock contention on high-end machines: Enable the trace flag TF8102 together with TF8101.

Step 9: Configure your virtual machine

If you are using a virtual machine, ensure that CPUs are not overprovisioned and are configured correctly.

Step 10: Scale up the system to use more CPUs

If individual queries are using minimal CPU capacity, but the overall workload of all queries is causing high CPU consumption, consider scaling up by adding more CPUs.

Disk IO queuing

Disk I/O queuing is a critical metric to monitor when it comes to SQL Server 2000 performance. It helps identify potential I/O performance problems and can be done through the Windows 2000 PhysicalDisk Object: Avg. Disk Queue Length counter. If the average disk queue length exceeds 2 for continuous periods (typically over 10 minutes), then an I/O bottleneck is likely occurring for that array.

To resolve this issue, several actions can be considered, such as adding drives to an array, using faster drives, adding cache memory to the controller card, employing a different version of RAID, upgrading to a faster controller, or reducing the workload on the SQL Server.

Additionally, it is important to establish baselines for Disk I/O metrics by monitoring them over time to determine trends and set a baseline for normal operation. This will help identify any deviations or abnormalities that could indicate performance issues.

Furthermore, Dynamic Management Views (DMVs) can be leveraged to pull out useful information from SQL Server regarding Disk I/O. For example, sys.dm_os_wait_stats, sys.dm_os_waiting_tasks, and sys.dm_io_virtual_file_stats can provide insights into wait times, real-time wait queue information, and I/O statistics for data and log files, respectively.

By utilizing these tools and metrics, administrators can effectively monitor and optimize Disk I/O performance in SQL Server 2000.

Memory consumption

The Performance Monitor, included with Windows 2000, is a valuable tool for monitoring both Windows 2000 and SQL Server 2000 performance. It offers the ability to track numerous performance indicators, including over 110 SQL Server 2000 counters. While monitoring, one of the key counters to observe is the "Buffer Cache Hit Ratio" under the SQL Server 2000 Buffer Manager Object. This counter indicates how often SQL Server accesses the buffer rather than the hard disk to retrieve data. For optimal performance, this ratio should exceed 90% in OLTP applications. If the ratio falls below this threshold, it may be necessary to add more RAM to the server or reduce the workload on SQL Server.

Another critical aspect of memory management in SQL Server 2000 is understanding the different editions and their memory limitations. The Standard Edition of SQL Server 2000 is limited to using a maximum of 2GB of RAM. In contrast, the Enterprise Edition can utilise up to 64GB of RAM with the appropriate configurations. To take advantage of more memory, ensure you are using the Enterprise Edition and have configured the "/PAE" switch in the boot.ini file, along with enabling AWE (Address Windowing Extensions) to allow SQL to use more memory.

Additionally, it is important to configure the "max server memory (MB)" setting appropriately. This setting determines the upper limit of memory utilisation by SQL Server. Setting it too high can cause competition for memory with other instances on the same host, while setting it too low may lead to memory pressure and performance issues. It is recommended to monitor overall memory consumption and determine an optimal value for this setting, ensuring that other applications and instances have sufficient memory available.

Furthermore, the "min server memory (MB)" setting can be used to guarantee a minimum amount of memory available to SQL Server, especially in virtualised environments where memory pressure from the underlying host can impact performance. However, it is not mandatory to set this value, as SQL Server dynamically adjusts its memory requirements based on available system resources.

In summary, monitoring memory consumption and optimising memory utilisation are crucial for maintaining the performance of SQL Server 2000. By using tools like the Performance Monitor and understanding the memory limitations and configurations specific to SQL Server 2000, administrators can ensure efficient memory management and enhance overall system performance.

Network bandwidth

Network performance metrics are often neglected when troubleshooting SQL Server issues, but they are important to monitor as network congestion can cause SQL Server problems. To get a clear picture of network performance, it is best to ensure that no applications other than SQL Server are running on the server, as this makes it easier to identify the source of excessive network traffic.

The Network Bytes Received/sec counter shows the rate at which information is received over each network adapter, including framing bytes. This can be used to calculate the incoming data rate as a percentage of the total network bandwidth, and any unexpected peaks in network activity should be investigated.

Similarly, the Network Bytes Sent/sec counter shows the rate at which bytes are sent, and can be used to calculate the outgoing data rate as a percentage of the total network bandwidth.

The sum of these two metrics is the Network Bytes Total/sec value, which shows the rate at which data is transferred over each network adapter. It is recommended that the Bytes Total/sec value for all servers on a network should be less than 50% of the total network bandwidth. If this value is constantly higher, further investigation is required to determine the cause of the network saturation.

The Current Network Bandwidth metric shows the current bandwidth of the network interface in bits per second (BPS). This can be compared to the Bytes Total/sec value to calculate the Network Utilization ratio. If this ratio is higher than 80%, it indicates very high network utilisation that should be addressed immediately, as additional traffic will cause bottlenecks and delays.

To monitor network performance metrics in real-time, you can use the Windows Resource Monitor. To do this, open the Windows Task Manager, open the Performance tab, and click on Open Resource Monitor. Select the Network tab to view the following sections: Processes with Network Activity, Network Activity, TCP Connections, and Listening Ports.

Other Network-Related Counters

In addition to the above, there are other network-related counters that can be monitored to identify potential issues with SQL Server performance:

• Server Object: Bytes Received/sec and Bytes Transmitted/sec: These counters show how much data is being sent to and from your server over the network.
• SQLServer: SQL Statistics: Batch Requests/Sec: This counter measures the amount of SQL batches per second that SQL Server is being given. If your system consistently exceeds 3000/second for a 100Mbs NIC, you may need to consider additional or faster network cards.
• Network Segment Object: % Network Utilization: This counter shows the percentage of bandwidth being used by the network connection your server is using.
• Network Interface Object: Bytes Total/Sec: This counter measures the number of bytes being sent back and forth between your server and the network, including both SQL Server and non-SQL Server traffic.

Transaction-level performance

• Query Analyzer: The SQL Server 2000 Query Analyzer is a versatile tool for developing, debugging, and performance-tuning Transact-SQL code. It offers features such as "Show Execution Plan", which allows you to visualise the execution plan used by the SQL Server Query Optimizer. This helps you understand how the query is executed step by step and optimise its performance.
• Profiler: The SQL Server 2000 Profiler is a powerful tool for capturing and analysing communication between your application and SQL Server. It can help identify performance bottlenecks and troubleshoot issues. The Profiler allows you to capture specific events, filter data, and create trace templates for repeated use.
• Index Wizard: This tool evaluates your database queries and recommends specific clustered and non-clustered indexes to improve performance. It takes into account the nature of the queries and the actual production queries running against your SQL Server.
• Performance Monitor: While not specific to SQL Server 2000, the Performance Monitor is a valuable tool for monitoring both Windows 2000 and SQL Server 2000 performance. It allows you to track key counters such as CPU utilisation, memory performance, I/O performance, and buffer cache hit ratio. Monitoring these counters can help identify potential bottlenecks and fine-tune your SQL Server applications.
• Transaction Log Management: Managing the transaction log size is crucial for maintaining optimal performance. Ensure that you regularly back up the transaction log to prevent continuous log growth. Additionally, consider setting the database recovery model to 'simple' and use the 'checkpoint' command to write records from the transaction log to the database.
• Hardware and System Configuration: Transaction performance can be influenced by the underlying hardware and system configuration. Ensure that your server has sufficient physical RAM, efficient I/O subsystems, and optimal disk queue length to handle the transaction workload.

Frequently asked questions