The SQL Server Activity chart displays information about what the SQL Server instance is doing.

Batches

The total number of select, insert, or delete statements per second, including those inside a stored procedure. The name is somewhat misleading since it doesn't represent batches (groups of multiple statements) in the traditional sense. It's one of the best measures of overall activity on a SQL Server.

Over 1000 Mb per second is generally considered moderate to high activity. A 100Mb network can reach saturation at around 3000 Mb per second.

Compiles

The total number of initial compiles and recompiles per second. The value should generally be < 10 percent of batches per second. Higher values indicate plan reuse is low, and will generally correlate with high CPU, since plan compilation is a CPU intensive operation. It may also correlate with low cache hit ratios for object and/or SQL plans.

It can also be a strong indicator of memory pressure, since there may not be enough room to keep all plans in cache.

Recompiles

The number of recompiles per second. The value should generally be < 10 percent of initial compiles per second.

Transactions

The total number of transactions per second across all databases on the server. A transaction can be either a user-defined statement block surrounded by a BEGIN TRAN and END TRAN, or an individual DML statement (insert, update or delete).

Compare with batches per second. On systems with high DML you typically want to see a low ratio of transactions to batches. A low ratio indicates that the individual statements are being bundled together, and can result in dramatically higher throughput and reduced IO due to log flushes.

Key Lookups

The number of times per second that the query processor had to perform a key lookup, across all queries. Lookups occur when the index being used is non-covering, meaning it doesn't include all of the columns required by the query. For each row returned by the index operation, the query processor has to go back to either the clustered index to perform a key lookup, or the base table to perform a RID lookup in the case of a heap.

Lookups are a high overhead operation, especially when large number of rows are involved, because each lookup incurs a random I/O and additional processing. This often correlates with higher CPU usage and page reads. Lookups can be eliminated by using a covering index, adjusting joins to reduce the set so the lookup isn't needed, or using multiple indexes (intersection).

Forwarded Records

The number of times per second that the query processor had to lookup forwarded records, across all queries. Forwarded records occur in tables with no clustered index (heaps) when rows become too large to fit on the page and have to be relocated. Over time, this can cause severe fragmentation and queries to incur much higher than normal I/Os, specifically random reads. This can correlate with high SQL Server page reads, and high SQL Server disk wait time, data file and physical disk latency if the disk system isn't keeping up with the additional reads. On many systems it's not unusual for this counter to stay at zero if all tables have a clustered index, any heaps aren't fragmented, or they just aren't accessed frequently.

Backup/Restore MB

The data rate in MB/sec for any backup operations taking place on the server.

The SQL Server Waits chart displays information about the classes and categories of waits that occurred as well as the duration of milliseconds that the waits were in effect during that time period.

Although there are hundreds of wait types, only the wait types that can be definitively attributed to one of the physical resource categories (Disk, Memory, CPU, and Network) are included in the calculations for this chart. The Other category is for a few other important wait types that can either affect performance in more than one major category, or cannot be directly attributed to any category with absolute certainty, such as backups and parallelism respectively.

SQL Server Waits is one of the most important charts on the dashboard, because it provides an instant profile of the SQL Server and where it's spending the most time waiting for physical resources. If SQL Server are consistently low, then what the other dashboard charts are showing is less important. For example, if CPU and SQL Server Activity: Batches look unusually high, but CPU waits are low, then the server hardware is effectively handling the load.

Total waits of less than 200ms is excellent. Between 200ms and 1000ms is average. Greater than 1000ms likely requires some attention to determine where the bottleneck lies. Over 5000ms may indicate severe bottle-necking.

The total wait time may be higher by virtue of a large number of processes (spids) active on the server, because wait time is summed across all processes, it isn't a per process average. This can be especially applicable to the Other category, because several processes experiencing parallelism at the same time can cause it to spike to high levels.

The SQL Server Memory chart displays information about how the Server instance is using memory that has been allocated to it.

Buffer

The current size of the buffer cache (in MB). You want this to be as large as possible for maximum performance, and on a dedicated SQL Server it should consume most of the SQL Server memory and physical memory.

Plan (SQL)

The current size of the cache used for query plans (in MB). This includes ad-hoc, auto-parameterized, and prepared plans. A high value in proportion to the buffer cache may indicate query plans aren't being effectively reused.

Plan (Objects)

The current size of the cache used for object plans (in MB). This includes stored procedures, functions, and triggers. A high value in proportion to the buffer cache may indicate query plans aren't being effectively reused.

Additional Information: Caching Mechanisms

Columnstore

The current size of the Columnstore index on the SQL Server (in MB). This includes both clustered and nonclustered columnstore indexes.

In-Mem OLTP

The current amount of memory (in MB) dedicated to In-Memory OLTP. This includes Memory-optimized tables, non-durable tables, and natively compiled T-SQL modules.

Other

The current size of the cache used for all other plans (in MB). This includes bound trees, extended stored procedures, temporary tables, and table variables. This cache size should be low in proportion to the other plan caches. If it goes over roughly 10 percent of the object or SQL plan size, further investigation may be needed.

PLE (sec)

The average lifespan of a data page. If this value is less than 600, it's an indicator of memory pressure. Ideally, it should be much higher than 600 if ample memory is available. In general, the larger the buffer cache size, the higher it should be. This is the best universal indicator of memory pressure.

Plan (SQL)

The ratio of hits to lookups for the query plan cache. This value should stay above 90 percent.

Plan (Object)

The ratio of hits to lookups for the object plan cache. This value should stay above 90 percent.

Pages: Reads

The average number of buffer data pages read from disk per second. Ideally, this value should be at or near zero most of the time. If it's above zero, it means that the data wasn't found in the buffer cache, and so it had to be retrieved from disk. If spikes in page reads correlate with high disk latency, the disk system may not be keeping up.

Querying newly created temp tables will also show up as page reads, as well as activity from internal tempdb objects. This includes hash joins, hash aggregates, sort, and query spool operations. This means that you can still see high paging from tempdb due to query activity, even though you aren't explicitly using temp tables.

When page reads and page writes correlate closely, it's a strong indicator that it's related to tempdb activity, because pages are being written to disk when the objects are created, then immediately read back in to memory for use by querying operations.

If lazy writes > zero and track closely with page reads, and page life expectancy < 600, it's a strong indicator of memory pressure, because data is being moved out of buffer to make room for new data coming in.

Lazy writes also cause page writes, but generally much less than tempdb activity. If you see high page reads, and relatively low lazy writes and page writes, it's likely memory pressure and not tempdb activity.

Pages: Writes

The average number of buffer data page writes to disk per second.

Page writes can be caused by checkpoints, lazy writes, and tempdb activity. To calculate the approximate amount of writes related to tempdb, for any given interval, subtract checkpoints and lazy writes from total page writes.

If high page writes correlate with high latency, the disk system may not be keeping up.

The Database IO chart displays information about the read and write latency for the databases.

ms/Read

The average time in milliseconds each physical disk read is taking for a particular database file. The top 10 database files (data and transaction log) with the highest latency for the specified date range are shown.

ms/Write

The average time in milliseconds each physical disk write is taking for a particular database file.

Log Flushes

Log flushes occur with every DML operation, and are a normal part of SQL Server activity.  It's important to note that log writes to physical disk from updates to buffer pages happen immediately upon transaction commit, whereas writes to physical disk from the changed buffer pages is delayed until the next checkpoint occurs. It's critical that the physical disk system where the transaction log resides is fast enough to keep up with activity. If not, it can slow down all DML operations occurring in the database.

Ideally each busy transaction log should have its own dedicated disks, so that writes can happen sequentially, which will minimize latency. If log flushes are high and latency is high for a transaction log file, then the disk system is likely under-powered for the current load.

Checkpoint Pages

The average pages per second written to disk by the checkpoint process. Checkpoints flush all dirty buffer pages for a given database to disk and are a normal part of SQL Server operations. The frequency of checkpoints and volume of checkpoint pages is dictated directly by the Recovery Interval server option. SQL Server uses checkpoints to batch writes to disk, which is generally more efficient. However, if the volume of each checkpoint is too high and you see a correlation with high disk latency, it may indicate that the disk system isn't keeping up.

Lazy Writes

The average number of writes per second by the lazy writer. The lazy writer periodically scans the buffer and evicts pages that have low use counts in order to maintain a certain number of pages on the free list. Ideally, this value should be at or near zero most of the time. When there is no memory pressure, the lazy writer will generally leave data pages in memory, even those with low use counts. However, when pressure exists, the lazy writer will continually be working to make room for new data coming into the buffer.

An indicator of memory pressure is ongoing lazy writes > zero with page reads/writes > zero and page life expectancy< 600.

Allows correlation between rows transferred and Formula and Storage Engine threads. It's important to identify if your main bottleneck is in the Formula Engine or Storage Engine.

Rows Transferred: Query Rows Sent / Sec

The rate of rows sent by the server to the clients.

Rows Transferred: Processing rows read / Sec

The rate of rows read from all relational databases. If you are connected to SQL Server, you should be reading at least 80,000 rows per sec per table being processed.

Threads: Formula Engine: Busy

The Processor performance object consists of counters that measure aspects of processor activity. The processor is the part of the computer that performs arithmetic and logical computations, initiates operations on peripherals, and runs the threads of processes.  A computer can have multiple processors.  The processor object represents each processor as an instance of the object.

Threads: Formula Engine: Job Rate

The rate of jobs through the query thread pool.

Threads: Formula Engine: Queued

The number of jobs in the queue of the query thread pool.

Threads: Storage Engine: Busy

The number of busy threads in the thread pool.

Threads: Storage Engine: Job Rate

The rate of non IO jobs through the thread pool.

Threads: Storage Engine: Queued

The number of jobs in the processing thread pool queue.

Monitors all of the activity for you continuously at a fraction of the overhead a Profiler Trace tends to impart. Average Time is a unique chart with average time metrics calculated by SQL Sentry. These counters are not available through Windows Performance Counters. Discover what the instance is spending the most time on with this chart. It divides the information into more detail for you:

• Serialization is handled by the Formula Engine and is related to NON_EMPTY behavior.
• Processing and SQL queries are handled by the Storage Engine.

Average Time (ms): Formula Engine

The average amount of time for a query to be executing in the formula engine.  

Average Time (ms): Processing

Displays the average processing time in milliseconds. Processing in SSAS Involves the SE, and can cause the Cache to become outdated and flushed. This causes a performance hit because when the query is run after processing completes, the SE has to retrieve the necessary data from the Windows file cache or from disk (cold cache). Non 0 values on the indicate that processing is taking place.

Average Time (ms): SQL Queries

The average amount of time spent executing SQL queries from the relational database.

Average Time (ms): Serialization

The average amount of time spent serializing data to disk ( as in data aggregations).

Average Time (ms): SE cached

The average amount of time spent returning data from the storage engine cache.

Average Time (ms): SE non-cached

The average amount of time spent returning non-cached data from the storage engine cache.

MDX: Calculated Covers

The total number of evaluation nodes built by MDX execution plans including active, and cached. High values for Calc Covers while the query is executing indicates that the query is doing cell by cell calculations (this is like row by agonizing row in SQL Server) instead of Subspace (block by block) execution.

MDX: Cells Calculated

The total number of cell properties calculated. High values for Cells calc’d while the query is executing indicates that the query is doing cell by cell calculations (this is like row by agonizing row in SQL Server) instead of Subspace (block by block) execution.

MDX: NE unopt

The total number of times unoptimized the NON EMPTY algorithm is used. NE unopt helps determine if performance degradation is occurring due to a slower code path. Using a non-empty algorithm can speed up performance.

MDX: NE calc memb

The total number of times the NON EMPTY algorithm was looping over calculated members. NE calc memb help determine if performance degradation is occurring due to a slower code path. Using a non-empty algorithm can speed up performance.

MDX: Recomputes

The total number of cells recomputed due to error. Recomputes indicate that there were errors in the calculations during query execution. Non 0 values indicate where unnecessary recalculations are taking place and can fall back to cell by cell executions.

MDX: Sonarsubcubes

The total number of subcubes that the query optimizer generated. High values for Sonarsubcubes while the query is executing indicates that the query is doing cell by cell calculations (this is like row by agonizing row in SQL Server) instead of Subspace (block by block) execution.

Processing: Aggregate rows created

Displays the rate Aggregate rows are created during processing. Processing in SSAS involves the SE, and can cause the Cache to become outdated and flushed. This causes a performance hit because when the query is run after processing completes, the SE has to retrieve the necessary data from the Windows file cache or from disk (cold cache).Non 0 values on the indicate that processing is taking place.

These metrics can also help determine the effectiveness of any processing tuning efforts. If your tuning efforts are effective, you should see an increase in these values accordingly.

Processing: Proc rows Written / Sec

Displays the rate of Processing rows written during processing. Processing in SSAS Involves the SE, and can cause the Cache to become outdated and flushed. This causes a performance hit because when the query is run after processing completes, the SE has to retrieve the necessary data from the Windows file cache or from disk (cold cache). Non 0 values on the indicate that processing is taking place.

Memory monitoring is very different between Multidimensional and Tabular modes, and different counters are available depending on the mode. Specific to the Tabular mode, the Mem Usage chart allows you to see memory counters, and the VertiPaq Memory Limit allows you to see where memory pressure might be causing performance issues. The chart also displays the VertiPaq paging policy that is important for monitoring the performance metrics. For more information about the VertiPaq paging policy, see the Analyzing VertiPaq Memory Usage in SSAS Tabular in SQL Sentry article.

Memory Usage (MB): Nonshrinkable

SSAS has two general categories of memory, shrinkable and nonshrinkable. Nonshrinkable memory is generally used for more essential system-related tasks such as memory allocators and metadata objects, and is not easily reduced. Nonshrinkable memory is the amount of memory in KB that's not subject to purging by the background cleaner.

Memory Usage (MB): Shrinkable

SSAS has two general categories of memory, shrinkable and nonshrinkable. Shrinkable memory can be easily reduced and returned back to the OS. Shrinkable memory is the amount of memory in KB that's subject to purging by the background cleaner.

Cache Lookups: Calc

The rate of calculation cache lookups, including global, session, and query scope calculation caches.

Cache Lookups: Dimension

The rate of cache lookups.

Cache Lookups: Flat

The rate of Flat Cache lookups, including global, session, and query scope flat caches.

Cache Lookups: Measure grp

The rate of Measure group queries.

Cache Activity: Cache Inserts

The rate of inserts from the cache.

Cache Activity: Cache Evictions

The rate of evictions from the cache.

Note:  This is per partition per cube per database.

Cache Activity: KB Shrunk

The rate of shrinking in KB / Sec.

A DTU represents the power of the database engine as a blended measure of CPU, memory, and read and write rates. This measure helps you assess the relative power of the SQL Database performance levels. Each service tier, which sets pricing and usage limits for an Azure SQL Database, expresses the amount of resource limits as a number of DTUs. The more DTUs an Azure SQL Database is allocated the more resources the database will have to service the workload.

Additional Information: See the What is Azure SQL Database? article on Microsoft Docs for more information regarding DTUs, purchasing models, and service tiers.

Total DTU %

If your database is seeing high Total DTU percentage usage it may benefit from adjusting to the next highest service tier to improve performance. If you're consistently seeing very low total DTU percentage usage you may save some money by scaling down to the next lower service tier.

Data I/O

This metric is the average Data I/O percentage based on the limit of the service tier. This is one of the metrics that makes up DTU.

Log I/O

This metric is the average log I/O percentage based on the limit of the service tier. This is one of the metrics that makes up DTU.

CPU %

This metric is the average CPU percentage based on the limit of the service tier. This is one of the metrics that makes up DTU.

Allocated Memory Usage

Each service tier has a maximum amount of memory allowed for the Azure SQL Database to use. This metric provides the percentage of the allowed memory being used for the database.

It will be very common for this metric to be high. If much of the data your applications need is in memory it means better performance because the database doesn't have to read from the physical disk to return the data.

Each service tier has a maximum allowed size for the Azure SQL Database. This chart uses that tier to determine the total space available.

Used Space

The space used by the database in MB.

Free Space

Amount of space remaining (in MB) from the total space allowed for the tier.

Transactions

The total number of transactions per second across all databases on the server. A transaction can be either a user-defined statement block surrounded by a BEGIN TRAN and END TRAN, or an individual DML statement (insert, update or delete).

Compare with batches per second. On systems with high DML you typically want to see a low ratio of transactions to batches. A low ratio indicates that the individual statements are being bundled together, and can result in dramatically higher throughput and reduced IO due to log flushes.

Backup/Restore MB

The data rate in MB/sec for any backup operations taking place on the server.

The SQL Database Waits chart displays information about the classes and categories of waits that occurred as well as the duration of milliseconds that the waits were in effect during that time period.

Although there are hundreds of wait types, only the wait types that can be definitively attributed to one of the physical resource categories (Disk, Memory, CPU, and Network) are included in the calculations for this chart. The Other category is for a few other important wait types that can either affect performance in more than one major category, or cannot be directly attributed to any category with absolute certainty, such as backups and parallelism respectively.

SQL Database Waits is an important chart on the dashboard, because it provides an instant profile of the Azure SQL DB and where it's spending the most time waiting for physical resources. If SQL DB waits are consistently low, then what the other dashboard charts are showing is less important. For example, if CPU and SQL Server Activity: Batches look unusually high, but CPU waits are low, then the server is effectively handling the load.

The total wait time may be higher by virtue of a large number of processes (spids) active on the server, because wait time is summed across all processes, it isn't a per process average. This can be especially applicable to the Other category, because several processes experiencing parallelism at the same time can cause it to spike to high levels.

The CPU chart displays CPU across your VMHost:

Total CPU

The total amount of CPU used across the VM as a percent.

Co-stop %

Co-Stop time measures when a virtual machine is ready to run, but unable to run due to co-scheduling constraints.

Ready Time %

Percentage of time when a vCPU is ready to do work, but must wait for the hypervisor to schedule that work on one or more physical CPUs. CPU ready time is a summation, and is dependent on the number of virtual machines on the host and their CPU loads.

The System Memory chart displays information about the amount of memory being used by different processes on the VMHost:

System Memory Active 

Amount of memory that is actively used, as estimated by VMkernel based on recently touched memory pages.

System Memory Consumed

Amount of machine memory used on the host. Consumed memory includes memory used by the service console, the VMkernel, vSphere services, and the total consumed metrics for all running virtual machines.

System Memory Overhead

Total of all overhead metrics (including VM overhead and VMKernel overhead) for powered-on virtual machines, and the overhead of running vSphere services on the host.

System Memory Granted

Amount of machine memory or physical memory that's mapped for the host.

System Memory Available

Amount of machine memory or physical memory that's available for the host.

System Memory Swap In

The percentage of memory read in by the VMkernel swap file from memory to disk across all the virtual machines on the VMHost.

System Memory Swap Out

The percentage of memory written by the VMKernel swap file from memory across all the virtual machines on the VMHost.

The Disk chart displays the read and write latency for each disk on the VMHost.

Disk ms/Read

The amount of time it takes in milliseconds for the VMHost to read data from each Logical Unit Number (LUN) on the datastore.

Disk ms/Write

The amount of time it takes in milliseconds for the VMHost to write data from each Logical Unit Number (LUN) on the datastore.

Disk Read

The amount of data in megabytes read by the VMHost from each Logical Unit Number (LUN) on the datastore per second.

Disk Write

The amount of data in megabytes written to the VMHost from each Logical Unit Number (LUN) on the datastore per second.

The Disk chart displays the read and write latency for each disk on the VMGuest.

Disk ms/Read

The average amount of time it takes in milliseconds for the VM to read data from the virtual disk.

Disk ms/Write

The average amount of time it takes in milliseconds for the VM to write data to the virtual disk.

Disk/MB sec Read

The average amount of data used for reads by each VM in megabytes per second.

Disk/MB sec Write

The average amount of data used for writes by each VM in megabytes per second.