Contributors




Download 220.45 Kb.
NameContributors
page3/16
A typeDocumentation
manual-guide.com > manual > Documentation
1   2   3   4   5   6   7   8   9   ...   16

Notes from the Field

Lync Server Performance Issues due to a SQL Server Back-End Database


Premal Gandhi

Senior Escalation Engineer

Lync Server uses SQL Server as a back-end database. SQL Server performance is influenced by disk I/O. Because Lync Server uses SQL Server as a back-end database, disk I/O affects the performance of Lync Server (LSUSrv - 01 - DBStore\USrv - 004 - sproc latency in milliseconds (msec)). Ensuring that disk I/O is working optimally is the most important factor when avoiding Lync Server performance issues. I use the following troubleshooting process to rule out disk I/O as the cause of these issues:

  1. I run the following report: dbanalyze /v /sqlserver:sqlserver.xyz.com /report: disk > c:\dbanalyzereport.txt.

  2. Are Lync Server databases, such as RTC and RTCDyn, on the same physical disk? Are they on separate spindles? Answering yes to both questions helps reduce the disk I/O.

  3. Are the data and transaction logs for a particular Lync Server database on separate spindles? Answering yes to this question helps reduce the disk I/O.

Tip. In general, 15 msec is an optimal value for Avg. Disk sec/Write and Avg.Disk sec/Read. For details about these values, see http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1AF/Analyzing%20Characterizing%20and%20IO%20Size%20Considerations.docx.

Notes from the Field

Troubleshooting Lync 2010 Performance Issues Caused by SQL Server Back-End Database


Gennady Kostinsky

Senior Premier Field Engineer

Lync Server uses SQL Server as a back-end database. SQL Server performance directly affects the performance and stability of Lync 2010. This section covers some of the common areas of SQL Server to monitor, and in case of saturation and performance issues, some steps to help you troubleshoot Lync 2010 performance issues.

Following are four major areas of SQL Server performance to consider:

  • Processor

  • Memory

  • Disk I/O subsystem (covered in the previous “Notes from the Field” section in the chapter)

  • Network

Resource contention with any or a number of these areas may adversely affect both SQL Server and Lync 2010 performance. I’ll talk about processor, memory, and network.
Processor

The primary indicator of processor utilization is the % Processor Time counter in the Processor object in System Monitor (perfmon.exe). This counter monitors the amount of time the CPU spends executing a thread that is not idle. A consistent state of 80 percent to 90 percent may indicate the need to upgrade your CPU or add more processors. Alternatively, a high CPU usage rate may indicate a poorly tuned or improperly configured SQL Server. Optimizing the system and configuring SQL Server to best practices can lessen CPU utilization. %Processor Time value represents the sum of processor time on a specific processor. To determine the average for all processors, use the System: % Total Processor Time counter instead.

Some of the common causes for processor congestion include a high ratio of query compilation/recompilation (poor plan cache usage), processor-expensive query load, lack of indexes that cause high scan activity, and so on. Another System Monitor Processor object counter that should be monitored is % Privileged Time. This corresponds to the percentage of time that the processor spends on execution of Windows® kernel commands, such as processing SQL Server I/O requests. When % Privileged Time for all processors is more than 30 percent of % Processor Time for all processors and physical disk counters, it’s likely that a processor bottleneck is an indicator and/or side effect of disk subsystem contention.
Memory

Microsoft SQL Server needs plenty of memory (like all enterprise-class relational database management systems). Each instance of SQL Server has its own memory address space. This space has two parts—executable code and, more importantly, the buffer pool.

The buffer pool is a lot larger and more important SQL Server memory area. The buffer pool is the part of memory that an instance of SQL Server uses to create and manage data structures that relate to Lync 2010. In this case, they are Lync 2010 requests. The buffer pool contains important structures such as buffer cache, procedure cache, log caches, connection context, and system-level data structures.

The most important sections of the buffer pool are the buffer cache (also referred to as the data cache) and the procedure cache. The buffer cache holds the data pages in memory so that frequently accessed data can be retrieved from the cache. High utilization of buffer cache and a high hit ratio of the buffer cache are paramount to optimal performance of SQL Server (in general). An alternative to getting pages from the buffer cache is reading pages from the disk, which is obviously a lot slower and leads to higher resource constraints.

There are number of System Monitor SQL Server Buffer Manager object counters that can help you track performance and hit ratio for the buffer cache. Few most important are:

  • Buffer Cache Hit Ratio: This is the percentage of pages that are found in the buffer cache without having to read from the disk. The ratio is the total number of cache hits divided by the total number of cache lookups since an instance of SQL Server was started. After a long time, the ratio moves only a little. Because reading from the cache is much less expensive than reading from the disk, you want this ratio to be high. Ideally, as close to 100 percent as possible, at least more than 95 percent. If you see an average value not exceeding 95 percent, you may want to consider adding more memory to SQL Server to boost performance.

  • Page Life expectancy: This is the number of seconds that a page stays in the buffer pool without references. If this value becomes less than 300 seconds, this is a potential indicator that your SQL Server could use more memory to boost performance. For details about this value, see Jim Grey’s 5-minute rule, http://research.microsoft.com/en-us/um/people/gray/papers/tandemtr86.1_fiveminuterule.doc.

  • Lazy Writes/sec: This indicates the number of buffers written per second by the buffer manager's lazy writer. Lazy writer is a system process that flushes out batches of dirty, aged buffers (buffers that contain changes that must be written back to the disk before the buffer can be reused for a different page) and makes them available to user processes. Ideally, the number of these buffers should be as close to 0 (zero) as possible, generally not more than 20. In conjunction with Buffer Cache Hit Ratio and Page Life Expectancy counters more than high Lazy Writes/sec may indicate buffer memory strain.

  • Free Pages: Tracks the total number of pages that are free on all lists. In general, you are looking for these to stay at more than 640.

Note. To solve memory problems before you start adding more memory, see if SQL Server memory is correctly allocated. For more see information, see http://support.microsoft.com/kb/321363 and http://msdn.microsoft.com/en-us/library/ms178067.aspx.
Network

Looking at Network Interface object counters in System Monitor, such as bytes total/sec, current bandwidth, and output queue length, can help you see whether you are suffering from network interface bottlenecks. In general, running SQL Server as a Lync Server back-end database on a gigabit network cards is strongly advised as described in http://technet.microsoft.com/en-us/library/gg398269.aspx.

When searching for bottlenecks, look for the most common ones as previously mentioned: memory, disk I/O, and processor. Capture a performance baseline by using System Monitor, SQL Server Profiler, and SQL Server Management views to determine what is causing the bottleneck and if it can be solved by a hardware upgrade. When you have a baseline, you are ready to start diagnosing the problem. In most cases, this allows you to pinpoint the cause and make a decision as to a solution.
1   2   3   4   5   6   7   8   9   ...   16

Related:

Contributors iconContributors

Contributors icon Bibliography  Contributors Bios

Contributors iconContributors IX Acknowledgements XVIII Editor’s note XIX Acronyms and abbreviations XXI

Contributors icon2 mh370: Internet contributors assemble evidence of Hijacking to...




manual




When copying material provide a link © 2017
contacts
manual-guide.com
search