What you've been waiting for! AppInsight for SQL!!!

[Bronx]

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!

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!

[irby]

We're loving this application monitor- thanks for all the hard work in putting it together!

[Bronx]

Excellent, thanks for making your opinion public. This is what makes it all worth while. 🙂

[smiffy85]

The monitor really is great.  Just a shame you can't disable parts of the template like on others and therefore it costs 50 licenses per server...

[Bronx]

But the good part is you can have a ton of databases at no additional cost. BTW, SAM 6.1 - RC 1 comes out today and has been outfitted with AppInsight for Exchange - if anyone's interested.

[fitzy141]

cant wait to see the exchange appinsight

[smiffy85]

Yes you can monitor a load of databases.  But, still costs 50 licenses per server. Would be nice to turn off parts of the template, despite how good it is.

[franz0618]

Thanks a lot. This is really great on our monitoring.

[mosmatos]

"Physical Disk" and "Pages" -related counters do not work:

"Network connection failed. HResult: No data to return. Error: Unable to connect to the specified computer, or the computer is offline."

Following couters  affected:

Average Disk sec/Write

Average Disk sec/Read

Physical Disk Time

Page Faults/sec

Pages/sec

Available MBytes

Page Usage

Other work fine. By the way, on auturs's screenshot i see "Disk" and "Page faults/sec" are also grayed out. Is it by design?

[hash]

Did you try restarting WMI and Remote Registry service?

[tmiller_hockey]

Unfortunately this eats up component monitors and another way to make you bump your licensing up.

[jkump]

This is a good post!