Sluggish Snow License Manager tables, reports taking too long to load? This article aims to provide a starting point when diagnosing possible performance issues, particularly for larger environments.
If you have the following issues, you may need to look at your SQL Server platform:
- Snow License Manager IIS web app usage generally slow and sluggish
- Reports taking too long to generate
- SLM website hangs
- .SNOWPACK agent scan files in \Incoming\Data\Error folder with SQL timeout errors
This article aims to provide advice on where to improve performance in the following areas:
- Speed of Inventory Agent .SNOWPACK file processing
- Accessibility and usability of the Snow License Manager web application
- Speed in which the over-night Data Update Job takes to run
- Other general performance issues
Snow Software do not have any best practice guidelines for monitoring performance and usage. This task must be carried out by the customer. The advice offered in this document is largely based on previous engagements with similarly sized customers as well as the application of widely recognised industry best practices.
Dressed to Compress
SQL Server 2008-2014 Enterprise, or SQL Server 2016 Standard SP1 is recommended to allow Data Compression – this will reduce database size. This may increase the CPU usage as data is decompressed, but will not affect the IOs, so performance should not be affected overall and the decrease in database side should off-set this overhead.
A recommended disk configuration for large environments (over 100k seats) suggestion is as follows:
C: OS Drive
D: SnowInventory Database Data File, High-Speed Disk
E: SnowLicenseManager Database Data File, High-Speed Disk
F: TempDB Data File, High-Speed Disk
G: Log Files, High-Speed Disk
A configuration such as the above has been shown to give good performance for such large environments. The drives above should be separate physical disks and not separately partitioned volumes.
Monitoring - What's Going On?
To aid in identifying where the performance issue may lie, customers should investigate the SQL server. As a start, the monitoring counters common to all Windows Servers can be used.
Third-party monitoring tools may also be used to get further analytical information. Some suggestions on areas to monitor could be:
- Processor(Total)\% Processor Time – This is a basic indicator that should demonstrate that the server is running within the accepted parameters. The counter being in the 20-40% range would be considered acceptable, but spikes of over 80% could be a concern.
- Memory\Available MBs – tracking the available MB of memory can highlight if the amount of memory installed on the server is the issue. It can also help establish if other processes are using memory that could be used by SQL Server.
- Paging File (Total)\% Usage – If a lack of memory is causing issues, it could be that the Page File is being used. Reading and writing to and from disk instead of memory will cause noticeable performance decreases.
- PhysicalDisk(Total)\Avg. Disk Sec (Read & Write) – Two counters displaying this metric for both read and write can show how fast the I/O subsystem can respond to data requests. Latency values of more than 20ms may be an issue and should not be expected is SSDs are used.
- System\Processor Queue Length – If this counter reports on the number of threads that are queued for processor. If this is above 0 then there are too many requests per core than the processor can handle, which will have an impact on performance.
Many of our customers now have large environments of 80-150k seats. For this amount of data and users, they would see improved performance by carrying out some or all of the following:
- Ensure a 16 core processor is used
- Ensure RAM size is 128GB
- Ensure storage volumes are split across enough physical disks and that the disks are high performance SSDs
- Migrate to SQL Server 2014 Enterprise or SQL Server 2016 Standard SP1 in order to enable SQL Data Compression
- Monitor SQL server performance for an extended length of time to ascertain the times and extent of the performance issues