SAM offers a detailed view of your SQL databases' performance without the use of agents or templates by using the AppInsight for SQL embedded application. AppInsight for SQL provides a level of detail and expert knowledge far beyond what a SQL template can provide, allowing you to monitor virtually every aspect of your SQL instances and databases.
Like any unassigned application in SAM, AppInsight for SQL is considered a template until it is applied. Therefore, it is a member of the Application Monitor Templates collection. Once applied to a node, AppInsight for SQL is considered an application. Like any SAM application, AppInsight for SQL is comprised of multiple component monitors, also known as performance counters.
Make Sure You're Ready for it!
AppInsight for SQL Requirements and Permissions
AppInsight for SQL data is collected at the same default five minute polling interval as traditional application templates. Following are the requirements and permissions needed for AppInsight for SQL:
AppInsight for SQL Requirements
AppInsight for SQL supports the following versions of Microsoft SQL Server:
Microsoft SQL Server 2008
Microsoft SQL Server 2008R2
Microsoft SQL Server 2012
AppInsight for SQL Permissions
The minimum SQL permissions required to use AppInsight for SQL are as follows:
Must be a member of the db_datareader role on the msdb system database.
Must have VIEW SERVER STATE
View any definition.
Connect permission to Master database.
Execute permission on the Xp_readerrorlog
Connect permission to the Msdb
Must be member of db_datareader role in the Msdb
Connect permission to all databases.
Note:AppInsight for SQL supports both the SNMP and WMI protocols and uses SQL to gather information about the application. Additional information is available for nodes managed via WMI.
Every SQL counter, both in SAM and the Admin Guide, will contain expert knowledge. This will allow you to resolve issues quicker than ever!
Lazy Writes/ Sec
Definition: The lazy writer is a system process that flushes out buffers that contain changes that must be written back to disk before the buffer can be reused for a different page and makes them available to user processes.
Information: This counter tracks how many times per second that the Lazy Writer process is moving dirty pages from the buffer to disk in order to free up buffer space. The Lazy Writer eliminates the need to perform frequent checkpoints in order to create available buffers.
Generally speaking, this should not be a high value, say more than 20 per second. Ideally, it should be close to zero. If it is zero, this indicates that your SQL Server's buffer cache is large and your SQL Server does not need to free up dirty pages.
Possible problems: If the returned value is high, this can indicate that your SQL Server's buffer cache is small and that your SQL Server needs to free up dirty pages.
Remediation: Check your SQL server and verify its memory is being used efficiently. Applications other than SQL may be using a great deal of memory. Try and recover memory by closing unnecessary applications. Installing additional memory may also help.
You will find this type of information on the Component Details page for every AppInsight for SQL performance counter!
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community.
More than 150,000 members are here to solve problems, share technology and best practices, and directly
contribute to our product development process.