dm_os_performance_counters<\/code>, collect metrics, and store them in a permanent location.<\/p>\nThe data can be stored in a similar fashion as it is returned in the view: one row per counter per sample, but I personally find it far easier to work with this data when it is pivoted so that there is a single column per counter per sample. It creates a smaller data set and one that benefits more from compression and columnstore indexes.<\/p>\n
For the remainder of this article, a selection of performance counters be used to demonstrate their storage, collection, and usage. Assume that the list of counters used in any organization will vary from these 12 (possibly by a lot):<\/p>\n
\n- Page life expectancy<\/strong> (Static value. Can be sampled at any point in time)<\/li>\n
- Page reads<\/strong> (Cumulative value. Must be diffed between two points in time)<\/li>\n
- Page writes<\/strong> (Cumulative value. Must be diffed between two points in time)<\/li>\n
- Lazy writes<\/strong> (Cumulative value. Must be diffed between two points in time)<\/li>\n
- Page lookups<\/strong> (Cumulative value. Must be diffed between two points in time)<\/li>\n
- Target pages<\/strong> (Static value. Can be sampled at any point in time)<\/li>\n
- Logins<\/strong> (Cumulative value. Must be diffed between two points in time)<\/li>\n
- Logouts<\/strong> (Cumulative value. Must be diffed between two points in time)<\/li>\n
- Temp Tables Created<\/strong> (Cumulative value. Must be diffed between two points in time)<\/li>\n
- Batch Requests<\/strong> (Cumulative value. Must be diffed between two points in time)<\/li>\n
- Compilations<\/strong> (Cumulative value. Must be diffed between two points in time)<\/li>\n
- Recompilations<\/strong> (Cumulative value. Must be diffed between two points in time)<\/li>\n<\/ul>\n
Note that the collection method is included in the list above to aid later in this article.<\/p>\n
A table will be created to store this data that contains a row per sample time and a column per counter. This table includes a clustered columnstore index as it could get quite large and would benefit greatly from the added compression and rowgroup\/segment elimination offered by it when performing analytics:<\/p>\n
CREATE TABLE dbo.performance_counters\r\n(\tsample_time_utc DATETIME2(3) NOT NULL,\r\n\tpage_life_expectancy_seconds BIGINT NOT NULL,\r\n\tpage_reads_per_second DECIMAL(18,4) NOT NULL,\r\n\tpage_writes_per_second DECIMAL(18,4) NOT NULL,\r\n\tlazy_writes_per_second DECIMAL(18,4) NOT NULL,\r\n\tpage_lookups_per_second DECIMAL(18,4) NOT NULL,\r\n\ttarget_pages BIGINT NOT NULL,\r\n\tlogins_per_second DECIMAL(18,4) NOT NULL,\r\n\tlogouts_per_second DECIMAL(18,4) NOT NULL,\r\n\ttemp_tables_created_per_second DECIMAL(18,4) NOT NULL,\r\n\tbatch_requests_per_second DECIMAL(18,4) NOT NULL,\r\n\tcompilations_per_second DECIMAL(18,4) NOT NULL,\r\n\trecompilations_per_second DECIMAL(18,4) NOT NULL);\r\nCREATE CLUSTERED COLUMNSTORE INDEX CCI_performance_counters \r\nON dbo.performance_counters;<\/pre>\nNote that while the counter values stored in dm_os_performance_counters<\/code> are BIGINTs<\/code>, DECIMAL(18,4)<\/code> is used for many counters to support the math needed to calculate per-second numbers, which provides more value than raw numbers would. Units are included in all counters to make it easier to understand what they mean and how to use them.<\/p>\nIf metrics are to be collected and aggregated across many servers, then adding a database server column would be an easy way to facilitate that. An additional non-clustered index on that column (plus the sample time) would be valuable if there were a need to perform calculations based on server rather than time.<\/p>\n
Collecting performance counter data<\/h2>\n
With a table created to accept data from dm_os_performance_counters<\/code>, a process can be built that regularly collects the data and stores it here. For the sake of brevity, this section will cover the key components to data collection only. The full script<\/a> can be downloaded, reviewed, and tested at the reader’s leisure.<\/p>\nOne key concern in this data collection is differentiating between the collection of static and cumulative counters. Most of the counters chosen to demo are cumulative, and thus the only way to measure them is to take the current sample, compare it to the last sample, and perform the necessary math to determine the metric over that time span. There are a handful of ways to manage this, but for simplicity and reliability, a new table will be created:<\/p>\n
\tCREATE TABLE dbo.performance_counters_staging\r\n\t(\tsample_time_utc DATETIME2(3) NOT NULL,\r\n\t\tpage_reads_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\tpage_writes_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\tlazy_writes_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\tpage_lookups_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\tlogins_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\tlogouts_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\ttemp_tables_created_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\tbatch_requests_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\tcompilations_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\trecompilations_cumulative DECIMAL(18,4) NOT NULL,\r\nCONSTRAINT PK_performance_counters PRIMARY KEY CLUSTERED \r\n(sample_time_utc));<\/pre>\nIn addition, little history needs to be saved to this table, therefore it will not grow large and a standard clustered primary key is adequate. Realistically, only one previous sample is required to calculate what is needed for our process, but to play it safe and keep retention straightforward, this example will retain a single day of data.<\/p>\n
In an effort to structure this project, the steps needed to collect this data are as follows:<\/p>\n
\n- Collect a snapshot of all counters of interest into a temporary table.<\/li>\n
- Determine the last sample time for the cumulative counters.<\/li>\n
- Join the new counter data to the previous cumulative counter data.<\/li>\n
- Insert the results into
dbo.performance_counters<\/code>.<\/li>\n- Insert the raw cumulative data into
dbo.performance_counters_staging<\/code>.<\/li>\n- Remove any staging data older than a day.<\/li>\n<\/ol>\n
Note that there is no retention on dbo.performance_counters<\/code>. This is intentional as it is built as a table to store performance counter data long-term. Retention may be added at the end of the process to limit storage to a reasonable time frame if needed. It\u2019s advisable to keep at least 1-2 years of data, if it is to be used for capacity planning or other long term trending. Given the table\u2019s small size, retaining it forever would not be expensive.<\/p>\nThe process can run as often as is prudent, and that frequency would be determined by an organization and the environment being monitored. For an average database server, I\u2019d consider starting at samples every minute and adjust more\/less often as needed.<\/p>\n
Collect a Snapshot<\/h3>\n
To reduce the performance impact of querying system views, there is a benefit in collecting counter data in a single query and putting it into a temporary table:<\/p>\n
DECLARE @sample_time_utc DATETIME2(3) = GETUTCDATE();\r\n\tCREATE TABLE #performance_counters\r\n\t(\tsample_time_utc DATETIME2(3) NOT NULL,\r\n\t\tpage_life_expectancy_seconds BIGINT NOT NULL,\r\n\t\tpage_reads_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\tpage_writes_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\tlazy_writes_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\tpage_lookups_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\ttarget_pages BIGINT NOT NULL,\r\n\t\tlogins_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\tlogouts_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\ttemp_tables_created_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\tbatch_requests_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\tcompilations_cumulative DECIMAL(18,4) NOT NULL,\r\n\t\trecompilations_cumulative DECIMAL(18,4) NOT NULL);\r\n\tINSERT INTO #performance_counters\r\n\t\t(sample_time_utc, page_life_expectancy_seconds, \r\n page_reads_cumulative, page_writes_cumulative,\r\n\t lazy_writes_cumulative, page_lookups_cumulative, \r\n target_pages, logins_cumulative,\r\n\t\t logouts_cumulative, temp_tables_created_cumulative, \r\n batch_requests_cumulative, compilations_cumulative,\r\n\t\t recompilations_cumulative)\r\n\tSELECT\r\n\t\t@sample_time_utc,\r\n\t\tSUM(CASE WHEN dm_os_performance_counters.counter_name = 'Page life expectancy' THEN dm_os_performance_counters.cntr_value ELSE 0 END) AS page_life_expectancy_seconds,\t\r\n\t\tSUM(CASE WHEN dm_os_performance_counters.counter_name = 'Page reads\/sec' THEN dm_os_performance_counters.cntr_value ELSE 0 END) AS page_reads_cumulative,\t\r\n\t\tSUM(CASE WHEN dm_os_performance_counters.counter_name = 'Page writes\/sec' THEN dm_os_performance_counters.cntr_value ELSE 0 END) AS page_writes_cumulative,\t\r\n\t\tSUM(CASE WHEN dm_os_performance_counters.counter_name = 'Lazy writes\/sec' THEN dm_os_performance_counters.cntr_value ELSE 0 END) AS lazy_writes_cumulative,\t\r\n\t\tSUM(CASE WHEN dm_os_performance_counters.counter_name = 'Page lookups\/sec' THEN dm_os_performance_counters.cntr_value ELSE 0 END) AS page_lookups_cumulative,\t\r\n\t\tSUM(CASE WHEN dm_os_performance_counters.counter_name = 'Target pages' THEN dm_os_performance_counters.cntr_value ELSE 0 END) AS target_pages,\t\r\n\t\tSUM(CASE WHEN dm_os_performance_counters.counter_name = 'Logins\/sec' THEN dm_os_performance_counters.cntr_value ELSE 0 END) AS logins_cumulative,\r\n\t\tSUM(CASE WHEN dm_os_performance_counters.counter_name = 'Logouts\/sec' THEN dm_os_performance_counters.cntr_value ELSE 0 END) AS logouts_cumulative,\r\n\t\tSUM(CASE WHEN dm_os_performance_counters.counter_name = 'Temp Tables Creation Rate' THEN dm_os_performance_counters.cntr_value ELSE 0 END) AS temp_tables_created_cumulative,\r\n\t\tSUM(CASE WHEN dm_os_performance_counters.counter_name = 'Batch Requests\/sec' THEN dm_os_performance_counters.cntr_value ELSE 0 END) AS batch_requests_cumulative,\r\n\t\tSUM(CASE WHEN dm_os_performance_counters.counter_name = 'SQL Compilations\/sec' THEN dm_os_performance_counters.cntr_value ELSE 0 END) AS compilations_cumulative,\r\n\t\tSUM(CASE WHEN dm_os_performance_counters.counter_name = 'SQL Re-Compilations\/sec' THEN dm_os_performance_counters.cntr_value ELSE 0 END) AS recompilations_cumulative\r\n\tFROM sys.dm_os_performance_counters\r\n\tWHERE (dm_os_performance_counters.object_name LIKE '%Buffer Manager%' AND dm_os_performance_counters.counter_name IN ('Page life expectancy', 'Page reads\/sec', 'Page writes\/sec', 'Lazy writes\/sec', 'Page lookups\/sec', 'Target pages'))\r\n\tOR (dm_os_performance_counters.object_name LIKE '%General Statistics%' AND dm_os_performance_counters.counter_name IN ('Logins\/sec', 'Logouts\/sec', 'Temp Tables Creation Rate'))\r\n\tOR (dm_os_performance_counters.object_name LIKE '%SQL Statistics%' AND dm_os_performance_counters.counter_name IN ('Batch Requests\/sec', 'SQL Compilations\/sec', 'SQL Re-Compilations\/sec'))<\/pre>\nThe syntax looks a bit messy but performs as well as a PIVOT<\/code> or some other method would. The goal is to hit dm_os_performance_counters<\/code> a single time and not need to revisit it for different subsets of counters. The T-SQL above will generate a single row of data with all of the requested metrics placed into the temporary table.<\/p>\nDetermine the Last Sample Time for Cumulative Counters<\/h3>\n
For the cumulative counters, it is necessary to compare the values just collected against the last sample taken. If no previous sample has been taken, then this sample time will be NULL<\/code> and results for these counters will not be collected until the next time this process runs.<\/p>\n\tDECLARE @last_sample_time datetime2(3);\r\n\tSELECT\r\n\t\t@last_sample_time = \r\n MAX(performance_counters_staging.sample_time_utc)\r\n\tFROM dbo.performance_counters_staging;\r\n\t-- Do not create performance data for the first run. \r\n --Instead, only seed the cumulative counters for the next run.\r\n\tIF @last_sample_time IS NOT NULL\r\n\tBEGIN\r\n\t\tDELETE performance_counters_staging\r\n\t\tFROM dbo.performance_counters_staging\r\n\t\tWHERE performance_counters_staging.sample_time_utc \r\n < DATEADD(DAY, -1, @last_sample_time);\r\n\t\tDECLARE @seconds_since_last_execution INT;\r\n\t\tSELECT @seconds_since_last_execution = \r\n DATEDIFF(SECOND, @last_sample_time, @sample_time_utc);<\/pre>\nThis is straightforward and checks the staging table for the last sample time and stores it in a variable for use soon. The remainder of this process will be nested within an IF statement, ensuring that the first run of this code will not generate any performance counter data. Instead, staging data will be populated and a full data set generated on the next run.<\/p>\n
This includes the deletion of any staging data more than a day older than the last sample time, which ensures this table does not get overly large. An administrator may wish to keep the raw numbers for telemetry purposes for a longer period of time, and if so, this retention can be adjusted accordingly here.<\/p>\n
Calculate Metrics and Insert into Performance Counter Table<\/h3>\n
For the two point-in-time metrics, the work is simple, but for the cumulative counters, some logic needs to be added to compare with the previous value and also calculate the per-second metric.<\/p>\n
\t\tINSERT INTO dbo.performance_counters\r\n\t\t\t(sample_time_utc, page_life_expectancy_seconds, \r\n page_reads_per_second, page_writes_per_second, \r\n lazy_writes_per_second,\r\n\t\t\t page_lookups_per_second, target_pages, \r\n logins_per_second, logouts_per_second, \r\n temp_tables_created_per_second,\r\n\t\t\t batch_requests_per_second, \r\n compilations_per_second, \r\n recompilations_per_second)\r\n\t\tSELECT\r\n\t\t\tperformance_counters.sample_time_utc,\r\n\t\t\tperformance_counters.page_life_expectancy_seconds,\r\n\t\t\tCASE WHEN performance_counters.page_reads_cumulative >= performance_counters_staging.page_reads_cumulative\r\n\t\t\t\tTHEN performance_counters.page_reads_cumulative - performance_counters_staging.page_reads_cumulative\r\n\t\t\t\tELSE performance_counters.page_reads_cumulative\r\n\t\t\tEND \/ @seconds_since_last_execution AS page_reads_per_second,\r\n\t\t\tCASE WHEN performance_counters.page_writes_cumulative >= performance_counters_staging.page_writes_cumulative\r\n\t\t\t\tTHEN performance_counters.page_writes_cumulative - performance_counters_staging.page_writes_cumulative\r\n\t\t\t\tELSE performance_counters.page_writes_cumulative\r\n\t\t\tEND \/ @seconds_since_last_execution AS page_writes_per_second,\r\n\t\t\tCASE WHEN performance_counters.lazy_writes_cumulative >= performance_counters_staging.lazy_writes_cumulative\r\n\t\t\t\tTHEN performance_counters.lazy_writes_cumulative - performance_counters_staging.lazy_writes_cumulative\r\n\t\t\t\tELSE performance_counters.lazy_writes_cumulative\r\n\t\t\tEND \/ @seconds_since_last_execution AS lazy_writes_per_second,\r\n\t\t\tCASE WHEN performance_counters.page_lookups_cumulative >= performance_counters_staging.page_lookups_cumulative\r\n\t\t\t\tTHEN performance_counters.page_lookups_cumulative - performance_counters_staging.page_lookups_cumulative\r\n\t\t\t\tELSE performance_counters.page_lookups_cumulative\r\n\t\t\tEND \/ @seconds_since_last_execution AS page_lookups_per_second,\r\n\t\t\tperformance_counters.target_pages,\r\n\t\t\tCASE WHEN performance_counters.logins_cumulative >= performance_counters_staging.logins_cumulative\r\n\t\t\t\tTHEN performance_counters.logins_cumulative - performance_counters_staging.logins_cumulative\r\n\t\t\t\tELSE performance_counters.logins_cumulative\r\n\t\t\tEND \/ @seconds_since_last_execution AS logins_per_second,\r\n\t\t\tCASE WHEN performance_counters.logouts_cumulative >= performance_counters_staging.logouts_cumulative\r\n\t\t\t\tTHEN performance_counters.logouts_cumulative - performance_counters_staging.logouts_cumulative\r\n\t\t\t\tELSE performance_counters.logouts_cumulative\r\n\t\t\tEND \/ @seconds_since_last_execution AS logouts_per_second,\r\n\t\t\tCASE WHEN performance_counters.temp_tables_created_cumulative >= performance_counters_staging.temp_tables_created_cumulative\r\n\t\t\t\tTHEN performance_counters.temp_tables_created_cumulative - performance_counters_staging.temp_tables_created_cumulative\r\n\t\t\t\tELSE performance_counters.temp_tables_created_cumulative\r\n\t\t\tEND \/ @seconds_since_last_execution AS temp_tables_created_per_second,\r\n\t\t\tCASE WHEN performance_counters.batch_requests_cumulative >= performance_counters_staging.batch_requests_cumulative\r\n\t\t\t\tTHEN performance_counters.batch_requests_cumulative - performance_counters_staging.batch_requests_cumulative\r\n\t\t\t\tELSE performance_counters.batch_requests_cumulative\r\n\t\t\tEND \/ @seconds_since_last_execution AS batch_requests_per_second,\r\n\t\t\tCASE WHEN performance_counters.compilations_cumulative >= performance_counters_staging.compilations_cumulative\r\n\t\t\t\tTHEN performance_counters.compilations_cumulative - performance_counters_staging.compilations_cumulative\r\n\t\t\t\tELSE performance_counters.compilations_cumulative\r\n\t\t\tEND \/ @seconds_since_last_execution AS compilations_per_second,\r\n\t\t\tCASE WHEN performance_counters.recompilations_cumulative >= performance_counters_staging.recompilations_cumulative\r\n\t\t\t\tTHEN performance_counters.recompilations_cumulative - performance_counters_staging.recompilations_cumulative\r\n\t\t\t\tELSE performance_counters.recompilations_cumulative\r\n\t\t\tEND \/ @seconds_since_last_execution AS recompilations_per_second\r\n\t\tFROM #performance_counters performance_counters\r\n\t\tINNER JOIN dbo.performance_counters_staging\r\n\t\tON performance_counters_staging.sample_time_utc = @last_sample_time;\r\nEND<\/pre>\nSince a service restart will reset cumulative counters, it is necessary to check if the current value is larger than the previous value. If so, then subtract to get the difference. If the current value is less, then simply use that value as the counter value.<\/p>\n
The counter rate per second is calculated by dividing the amount that the counter incremented by since the last sample by the number of seconds since the last sample.<\/p>\n
Populate Raw Cumulative Counter Data<\/h3>\n
This final INSERT<\/code> ensures that a snapshot of cumulative counters is retained for posterity.<\/p>\n\tINSERT INTO dbo.performance_counters_staging\r\n\t\t(sample_time_utc, page_reads_cumulative, \r\n page_writes_cumulative, lazy_writes_cumulative, \r\n page_lookups_cumulative,\r\n\t\t logins_cumulative, logouts_cumulative, \r\n temp_tables_created_cumulative, \r\n batch_requests_cumulative,\r\n\t\t compilations_cumulative, recompilations_cumulative)\r\n\tSELECT\r\n\t\tsample_time_utc,\r\n\t\tpage_reads_cumulative,\r\n\t\tpage_writes_cumulative,\r\n\t\tlazy_writes_cumulative,\r\n\t page_lookups_cumulative,\r\n\t\tlogins_cumulative,\r\n\t\tlogouts_cumulative,\r\n\t\ttemp_tables_created_cumulative,\r\n\t batch_requests_cumulative,\r\n\t\tcompilations_cumulative,\r\n\t\trecompilations_cumulative\r\nFROM #performance_counters;<\/pre>\nPutting it All Together<\/h3>\n
When the entire process is pieced together and executed for the first time, it will place data into the staging table only:<\/p>\n
![\"collecting](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/07\/word-image.jpeg\")
<\/p>\n
These represent raw, cumulative counters for ten of the twelve metrics we are going to track.<\/p>\n
When the same process is executed again, the staging table gets a second row, and the primary counter table is populated with its first complete row:<\/p>\n
![\"The](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/07\/word-image-1.jpeg\")
<\/p>\n
With this and each subsequent run, a row will be placed in dbo.performance_counters<\/code> (the bottom result set) that contains both the point-in-time and cumulative counters. The calculation that converts raw\/cumulative numbers into rates can be observed with the following example from the data above:<\/p>\n17:25:26.783: Page Lookup Count (Cumulative):<\/strong> 70515235<\/p>\n17:28:05.320: Page Lookup Count (Cumulative):<\/strong> 70521221<\/p>\nDifference<\/strong>: 5986 Page Lookups<\/p>\nSeconds Between Samples<\/strong>: 159<\/p>\nPage Lookups Per Second<\/strong> = 5986 \/ 159 = 37.65<\/p>\nThe final step to collecting this data is to schedule a job or task that executes this code regularly, adding a row to dbo.performance_counters<\/code> on each run.<\/p>\nThe script<\/a> included in this article contains the full stored procedure code for dbo.populate_performance_counters<\/code>, which takes all of the code above and packages it into a single stored procedure for easy use by a developer or administrator.<\/p>\nThis process is meant for customization and is of most value when tailored to the environment that is to use it. If multiple servers are to be monitored, then run the stored procedure presented here on each server and then (if needed) have a centralized process that collects this data regularly and stores it for central analysis.<\/p>\n
The only alteration to the table structure for the centralized table would be the addition of a column to store the source database server name and possibly an index to support searches on that column.<\/p>\n
Customization<\/h2>\n
Since dm_os_performance_counters<\/code> contains a wide variety of performance counters, it is natural that only a small subset will be useful to any one person, and that specific subset will vary from organization to organization. For the purpose of this article, a selection of what I considered to be some of the more useful performance counters were chosen.<\/p>\nAdding or removing counters can follow the patterns illustrated in this code and should not be difficult. To add a counter, follow a process similar to this:<\/p>\n
\n- Add a column to
dbo.performance_counters<\/code> for the new counter.<\/li>\n- If the counter is cumulative, then also add it to
dbo.performance_counters_staging<\/code>.<\/li>\n<\/ol>\nWithin the stored procedure:<\/p>\n
\n- Add the counter to
#performance_counters<\/code>.<\/em><\/li>\n- Add the counter to the
INSERT\/SELECT<\/code> into #performance_counters<\/code><\/li>\n- Add the counter to the
INSERT<\/code> into dbo.performance_counters<\/code>.<\/li>\n- If the counter is cumulative, then also add it to the
INSERT<\/code> into dbo.performance_counters_staging<\/code>.<\/li>\n<\/ol>\nTo remove a counter, follow a similar process to what is outlined above, but instead of adding a counter, remove it instead.<\/p>\n
While no database-scoped counters were included in the examples above, they can be added via similar processes. If the number of databases to be tracked is small and finite, then adding them to the tables already presented here makes sense. If the number of databases is large or variable, then creating a separate table for them is a more scalable solution.<\/p>\n
In the latter scenario, the database name would be added as a column in a new table and a row would be inserted per sample time per database. A separate table for database-scoped counters is the easiest way to avoid confusion between server and database metrics and a need to differentiate between them whenever querying one single table.<\/p>\n
Monitoring vs. capacity planning and analytics<\/h2>\n
Before wrapping up this discussion of performance monitor data, it is important to differentiate between two functional uses of these metrics:<\/p>\n
\n- Monitoring and Alerting<\/li>\n
- Analytics and Capacity Planning<\/li>\n<\/ul>\n
The code in this article captures performance monitor metrics for long-term storage and is ideally used for capacity planning and analytics. It can help identify long-term trends and allow developers and administrators to plan ahead, avoiding resource crunches and emergencies.<\/p>\n
For example, a long-term downward trend of page life <\/a>expectancy indicates an impending memory shortage. This may be caused by code, increased usage, more data, or something else. Seeing the long-term trend and identifying this challenge with months to spare would allow for additional memory to be purchased and allocated. If a release were to blame, it would provide the time needed to research and resolve those code changes before becoming a systemic problem. If the cause were unclear, time would be available to research and find it before it is too late.<\/p>\nWhile this data can be used for monitoring, it is not in of itself a monitoring tool. Having a reliable SQL Server monitoring tool is an invaluable resource that can:<\/p>\n
\n- Alert in the event of immediate problems.<\/li>\n
- Help identify code or resource problems.<\/li>\n
- Let you know when resources run low at certain times of the day.<\/li>\n
- Correlate an app problem to the code or configuration causing it.<\/li>\n<\/ul>\n