PerformanceCollector AddIns
How do the AddIns Work?
There are a series of procedures named PerformanceCollector.Addin*. PerformanceCollector.RunAddIns is tickled every 5 minutes from the SQL Agent AddIns job. This proc builds a cursor over ANY entry in PerformanceCollector.Config that is enabled and runs it.
The AddIns procedures are responsible for determining when the given AddIn is actually supposed to run (every 8 hours, once a day, etc). It determines this from querying PerformanceCollector.Config. It then calls the actual worker proc if it needs to.
AddIns are responsible for populating the other tables in the PerformanceCollector schema. The following tables store the performance metrics that I find most interesting. Each table is structured so the first columns are CurrTime and ServerName. This aids in troubleshooting. You can aggregate all of this data across multiple customers/instances to do trending in a centralized repository.
| PerformanceCollector Object Name | Default Runtime Interval | Purpose |
|---|---|---|
| AdHocPlanCacheBloat | 7 Hours | Logs single-use ad-hoc query plans that are a total waste of plan cache space. If this is really big then the solution is to run 'optimize for ad hoc workloads' which reclaims all of the memory and makes sure that ad hoc plans are not fully cached. This is a "analyze once" script. Once you've enabled 'optimize for ad hoc' then you are done and you can disable this AddIn. But we run this in case a new framework (ruby seems susceptible) is deployed which causes a radical increase in ad hoc plans. You can also use the text to determine where you are not doing parameterized queries correctly. |
| BufferStats | 5 Hours | Gathers stats on the counts and size of each object in the buffer cache. Larger values for some objects might mean a new index is required. |
| Config | Using this table you can configure certain AddIns to NOT run or change their schedule. | |
| DeadlockSnapshot | 4 Hours | Shows all deadlocks that have occurred and tries to decode the deadlock XML to show the Victim and Survivor and what they were doing. Please note that there is a lag time between when a deadlock occurs and when it is decoded here that could be AT MOST 8 hours. You can manually run the Deadlock AddIns to do the decoding at any time if you really need to see up-to-the-minute deadlock data. |
| IndexBlocking | 4 Hours | Logs the amount of time each index is spent in a blocking/waiting state. Useful to determine if better indexes may be needed. |
| IndexesFromQueryPlanDtls | Not yet implemented. This will read the missing indexes from the query plans instead of relying on the DMVs. This seems to be far more accurate. This will list the TotalCost (somewhat nebulous) as well as the table and index columns that the optimize believes would be beneficial. | |
| IndexesFromQueryPlansSummary | Not yet implemented. An aggregate of the the Dtls table that quickly shows the best indexes to be created. | |
| IOStalls | 7 Hours | Lists the IOStalls metrics by database and filetype. See more at [[Monitoring Disk Latencies with PerformanceCollector]] |
| MissingIndexes | 4 Hours | Snaps the sys.dm_db_missing_index* DMVs. However, there is a better way to do this that looks just at cached query plans (see IndexesFromQueryPlanDtls). Even so, this will show every egregious examples of missing indexes. |
| PageLifeExpectancies | 5 mins | Logs the PLEs for the instance. |
| PerfCounters | 1 Hour | Gathers instance-level perf counters in EAV format for aggregation later. |
| PlanCacheMetrics | 7 hours | Logs stored proc stats (reads/writes/CPU/duration) by Metric. By looking at procs that overlap the different metrics you can quickly determine what you should be focus on when you are looking for performance problems. -- Top Cached SPs By Execution Count -- Top Cached SPs By Avg Elapsed Time -- Top Cached SPs By CPU -- Top Cached SPs By Total Logical Reads -- Top Cached SPs By Total Physical Reads -- Top Cached SPs By Total Logical Writes |
| PlanTypeStats | 7 Hours | similar to AdHocPlanCacheBloat. This will show you the breakdown of adhoc vs prepared vs proc, etc. the USE_COUNT will show you if parameterization is working for you. |
| PossibleBadIndexes | 4 Hours | Logs cases where writes>reads. This may mean the index can be dropped. But you must understand your workload. However, if Total Reads is ZERO then you can generally drop that index. Unless you have a year-end process that needs it or some other edge case where the index is only needed occassionally. |
| TempDBLogWatch | 5 mins | Lists transactions and query plans that are using more than 50MB of tempdb log space. |
| TempDBUsage | 5 mins | Shows spids and statements that are using more than 50MB of tempdb space. |
You have just read [[PerformanceCollector AddIns]] on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed. 
Dave Wentzel CONTENT
performance performancecollector sql server