cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

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

Level 15

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!

11 Comments
Level 7

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

Level 15

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

Level 12

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...

Level 15

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.

Level 12

cant wait to see the exchange appinsight

Level 12

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.

Level 8

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

Level 8

"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?

Level 7

Did you try restarting WMI and Remote Registry service?

Level 10

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

Level 15

This is a good post!

About the Author
Who am I? • I met Robert Frost at the end of the road less traveled, and then pointed him in the right direction. • Einstein asked me to define "Up," and I did. • I cliff dive from airplanes. • On Christmas, Santa comes to me for gifts. • I play three-cushion billiards with one hand. • Lions ask for my protection (I speak Lion). • Bobby Fischer and I came to a stalemate while playing chess. • I have literally given a woman the shirt off of my back. • I have also helped an old lady cross the street. • I know what a dangling participle is. • Mozart bequeathed his Requiem to me, and I corrected it. • I was thrown out of an Eric Clapton concert twice in the same night for drawing too much attention to myself. • I am a verbose minimalist. • I am Bronx. Who are you?