IT Operations Management (ITOM)
cancel

Troubleshoot Microsoft SQL Server performance problems

Troubleshoot Microsoft SQL Server performance problems

Amit-Kumar

Database performance problems are usually a mix of system resource contention, application design problems and database bottleneck. It is always overwhelming for database administrators to deal with such performance problems, using standalone performance tools or event based monitoring tools.

Hewlett Packard Enterprise Operations Manager i (OMi) 10.10 comes with easy-to-use and simple to understand solutions to manage performance of the IT infrastructure, services and applications. Performance Data visualization and performance management experience is now enhanced with the HTML 5 based UI and dashboard views.

MSSQL_MP_PD.png

OMi Management Pack for Microsoft SQL Server update release 1.1, which comes as one of the pre-packaged content of OMi 10.10, extends its monitoring coverage in terms of High Availability monitoring, Full text search and more. However, icing on the cake comes with introduction of out-of-the-box performance dashboard, which helps you:

  • Visualize the performance of Microsoft SQL Server at a glance.
  • Refreshes automatically.
  • Overlay performance data with Event.
  • Performance bottleneck highlighted based on threshold

 

With this new performance dashboard, it is intuitive and simple to manage performance of Microsoft SQL Server. Viewing key performance counters of Microsoft SQL Server along with system performance in a single dashboard helps in identifying and isolating resource bottlenecks. Here are some of the key performance counters which are monitored by OMi Management Pack for Microsoft SQL Server and are recommended to watch on a regular basis:

  • Memory — Pages/Sec: To see how much paging SQL server is doing. Pages/sec is the number of pages read from the disk or written to the disk to resolve memory references to pages that were not in memory at the time of the reference. This should be close to zero on a dedicated SQL Server. You will see spikes during backups and restores, but this is normal. Pages/sec alone is not necessarily indicative of memory pressure, but it is recommended to monitor it along with other counters listed below, to get the complete picture. 
  • Memory\Available Mbytes: To see the amount of physical memory, in megabytes, available for running processes. If this value is less than 5 percent of the total physical RAM, that means there is insufficient memory, and that can increase paging activity. To resolve this problem, you should add more memory. 
  • SQLServer: Buffer Manager — Buffer Cache Hit Ratio: To find out if SQL Server is efficiently caching the data pages in memory. This indicates the percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. A low value is an indication of high physical reads due to bad SQL or insufficient cache size. Investigate running SQL and correct if bad SQL. If inaccurate index statistics, etc. are causing excess table scans, then remedying bad SQL will improve the cache hit ratio as well as overall server performance. 
  • SQLServer: Buffer ManagerPage life expectancy: To find out, how volatile your Buffer Pool is. Recommended Page Life Expectancy (PLE) is 300 for every 4 GB of RAM on your server.

Formula: PLE threshold = (Buffer Pool (GB) / 4 (GB))* 300

In earlier days, the SQL Server’s buffer pool was around 4GB or so. This is the reason that the recommended PLE was 300. These days the buffer pool is around the 64GB and above. So for 64 GB of memory, using above formula, the recommended PLE would be 4800.

  • SQLServer: Memory Manager — Target Server Memory (KB): To see how much memory SQL Server wants. If SQLServer:Memory Manager - Target Server Memory (KB) is the same as the SQLServer: Memory Manager - Total Server Memory (KB) counter, then SQL Server has all the memory that it wants 
  • SQLServer: Memory Manager — Total Server Memory (KB): To see the amount of memory currently assigned to SQL Server. If the Total Server Memory (KB) less than the Target Server Memory (KB), then there is a potential memory bottleneck. SQL Server needs more available memory in order to run at its optimum performance.

Taking advantage of the out-of-the-box Microsoft SQL Server Management Pack dashboard, you can create custom dashboard tailored to your needs.

If you are a using Microsoft SQL Server SPI, you can evolve to Microsoft SQL Server Management Pack and get benefited from several important new features! OMi Management Packs Evolution Guide helps you transition from HP Operations Manager (OM) SPIs to OMi MPs when moving responsibilities from an HP OM server to OMi.

The OM-to-Opsbridge evolution program including license exchange details is now live. Search on the tag OM2OpsBridge to find blogs discussing this program and evolution to OpsBridge. Search on OMiContent for other blogs on management packs and connectors.

Try OMi now! OMi 10.10 comes pre-loaded with a number of Management Packs that you can try out without the hassle of getting management pack software or evaluation license.

Read more:

HPE Operations Bridge
HPE Live Network: Operations Bridge Evolution
HPE Live Network: Operations Manager i
HPE Live Network: OMi Management Pack development kit


 

You can see demonstrations and find out more details of this and other features of our HPE Operations Bridge solution in our sessions and at our booths during HPE Discover Las Vegas.

Click on the image below or here to register.

DiscoverLogo.jpg

System Architect, OpsBridge
  • operations bridge
About the Author

Amit-Kumar

Comments
N/A

i have used the windows 10, i have installed the visual studio 2014 express , also i'm try to install the sql server management 2012, but which required the .net framework 3.5 version but in windows 10 already .net framework of version 4.6 is installed,  so windows deny to install the .net framework 3.5 version, is any solution of me to install the .net version 3.5 with sql server managnement ??

//Add this to "OnDomLoad" event