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

Without SP

SP1

SP2

SP3

Microsoft SQL Server 2008R2

Without SP

SP1

SP2

Microsoft SQL Server 2012

Without SP

SP1

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.

 

The following script will configure permissions:

USE master

GRANT VIEW SERVER STATE TO AppInsightUser

GRANT VIEW ANY DEFINITION TO AppInsightUser

EXEC sp_adduser @loginame = 'AppInsightUser' ,@name_in_db = 'AppInsightUser'

GRANT EXECUTE ON xp_readerrorlog TO AppInsightUser

USE msdb

EXEC sp_adduser @loginame = 'AppInsightUser' ,@name_in_db = 'AppInsightUser'

EXEC sp_addrolemember N'db_datareader', N'AppInsightUser'

EXECUTE sp_MSforeachdb 'USE [?]; EXEC sp_adduser @loginame  = ''AppInsightUser'', @name_in_db = ''AppInsightUser'''


Learn it!

12121.png

Expert Knowledge!

Every SQL counter, both in SAM and the Admin Guide, will contain expert knowledge. This will allow you to resolve issues quicker than ever!


For example:

 

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!