Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

AppInsight for SQL

This is pretty cool! it's like you add an individual database as a node you are monitoring! Good job! I have a could problems though...

1) Index fragmentation - can I set some specific indexes/tables to ignore?

- I think this is just wierd/stupid SQL math.... I have a table that has one row, four columns... I rebuild the index and it has 0% fragmentation, but within 5-10 minutes it shows 99.6%, still one row. It is SQL reporting it, so nothing SW can do except allow me to ignore that index - cause SQL is being lame.

2) Set Default credentials - I have been with SW for quite a while and I have not made the move from SNMP to WMI, can I globally set a credential on the "template"

3) Allow me to disable collection of some items

a) I have a event collection and correlation application I don't need the Windows event log information.

b) I have some databases that are never used, so I want SQL to "stop" the database and not waist resource on them, but Orion Starts it up each time it collects.

c) SW has always been fully customize-able, why can't I disable components...

Find more posts tagged with

Accepted answers

aLTeReGo

1) Index fragmentation - can I set some specific indexes/tables to ignore?
- I think this is just wierd/stupid SQL math.... I have a table that has one row, four columns... I rebuild the index and it has 0% fragmentation, but within 5-10 minutes it shows 99.6%, still one row. It is SQL reporting it, so nothing SW can do except allow me to ignore that index - cause SQL is being lame.

Not at this time. That's something we wanted to add in, but given the enormity of the release it simply wasn't feasible. In the meantime I suggest posting this idea in the forum so other users can vote on this.

2) Set Default credentials - I have been with SW for quite a while and I have not made the move from SNMP to WMI, can I globally set a credential on the "template"

Default credentials are inherited from the node when available. This is of course dependent upon the node being managed via WMI. For instances where AppInsight for SQL is applied to an SNMP of ICMP node you will be prompted to provide credentials when you first client on the application in the "All Applications" tree resource. There is currently no way to define "default" credentials to be used with AppInsight for SQL beyond those specified in the node.

b) I have some databases that are never used, so I want SQL to "stop" the database and not waist resource on them, but Orion Starts it up each time it collects.

If you wish not to monitor a specific database or collection of databases, these can be indefinitely unmanaged through the Orion web console. Once unmanaged, SAM will no longer collect metrics for those selected databases.

c) SW has always been fully customize-able, why can't I disable components...

AppInsight for SQL was designed from the ground up to be a simple, turn key monitoring solution for Microsoft SQL. In that pursuit certain sacrifices had to be made. It was a delicate balancing act between simplicity and flexibility, and this is where we landed. Some of these decisions were based on performance and effeminacy of polling. If we provided the ability to disable individual components, then each item would have to be its own an isolated query. By combining much of this information into a single query we could simultaneously increase the amount of information we can collect while reducing overhead to the Orion server.

All comments

aLTeReGo

1) Index fragmentation - can I set some specific indexes/tables to ignore?
- I think this is just wierd/stupid SQL math.... I have a table that has one row, four columns... I rebuild the index and it has 0% fragmentation, but within 5-10 minutes it shows 99.6%, still one row. It is SQL reporting it, so nothing SW can do except allow me to ignore that index - cause SQL is being lame.

2) Set Default credentials - I have been with SW for quite a while and I have not made the move from SNMP to WMI, can I globally set a credential on the "template"

b) I have some databases that are never used, so I want SQL to "stop" the database and not waist resource on them, but Orion Starts it up each time it collects.

c) SW has always been fully customize-able, why can't I disable components...

netlogix

Another one I found that I want to disable is the windows event log collection. I already have a SIEM, so I don't need to collect that data in Orion too.

I will create those feature requests, but I am glad I asked since you can disable a DB - nice way to keep a consistent feel.

Thank you.

LGarvin

1) Index fragmentation - can I set some specific indexes/tables to ignore?
- I think this is just wierd/stupid SQL math.... I have a table that has one row, four columns... I rebuild the index and it has 0% fragmentation, but within 5-10 minutes it shows 99.6%, still one row. It is SQL reporting it, so nothing SW can do except allow me to ignore that index - cause SQL is being lame.

Some additional insight on the impacts of fragmentation reporting on small tables/indexes. It's correct that this is SQL Server reportingthis number, but I don't know that I'd really blame SQL Server for the information. On a small table/index, the pages are typically stored on mixed extents. With a one-row table this is almost guaranteed to be the case. Because the mixed extent is shared by multiple database objects, potentially eight different objects -- one object per page -- the fragmentation calculation is negatively impacted by that shared space. This is a natural artifact of using small tables.

One technique that has been historically used and works for databases that have a nominal (or large) number of single-row or low row-count tables, is to consolidate those tables into a single table and add a column to identify the row type. This works well for lookup tables that are typically two-column tables, for blob data that typicallly has an index value and the blob, or for tables that generally have a compatible column structure. Consolidating the tables allows you force all of that data into one or more extents, which will help with the false index fragmentation reporting. (It also reduces the number of indexes that actually have to be maintained.)

For tables that don't have compatible column structures, you might consider converting those one-row tables to XML, and then each "table" can be stored as an XML column in a single table, each row of that table could have completely different data structures ... such is the advantage of using XML for data storage in the database.

The trade-off is the code modifications that would be required to use the new table structure, and whether the cost of that effort is worth the potential improvements in fragmentation reporting, reduced requirements for reindexing operations, and overall the more efficient use of database extents.

netlogix

Those are great suggestions on combining the tables, but the tables are not under our control - examples are msdb.dbo.sysjobhistory, msdb.dbo.backupmediafamily, and BlackBerry.dbo.ServerConfig

The way I am fixing it is by increasing the fill - I don't like it, but it allows me to still report on the rest of the indexes.

LGarvin

Yeah, my response was somewhat predicated on ownership of the database. :-)

It won't do you anygood on MSDB (but then, the better solution might be to just exclude MSDB from monitoring completely).

For the BlackBerry ServerConfig table.... ouch.

Regarding changing the Fill Factor.. I pondered that option (for quite a while, actually), but I'm not sure that it will work. Fill Factor affects the *page* utilization, not the *extent* utilization. Since the row-length is constrained by the page length (8k), I believe that even if Fill Factor is set to 1%, the page will still contain at least one full record, which effectively won't be any different than Fill Factor=100 (or the default Fill Factor=0).

If it does work, that would imply that your single-row is being split across pages (in order to fill the extent), and the performance hit from having to load multiple pages to get one row of data might be more harmful.

On a low row-count table, where only one row at a time is ever returned, this might actually be a viable strategy, forcing one row per page. An 8-row table would fill an extent, and fragmentation should then remain at zero.

The other option might be to populate the table with dummy rows to force it to be an 8-page table. If you still only return one row on any access, only one page would need to be returned.