This collection of content will describe BI performance monitor counters (OS, SSAS, SSIS, SSRS) that can be encountered more or less commonly, which includes the description, steps that should be taken and additional resources that could be used for more serious troubleshooting Operating System (OS) %   A   C  D  P % % free space % processor […]

Applies to SSIS Description SSIS controls such as the Data Flow Task requires memory to temporary store buffers during package execution. This performance counter indicates the total amount of physical system memory allocated to the SSIS service process for package execution. Memory consumption by integration service can range from 2GB to 4GB depending on whether […]

Applies to SSIS Description This performance counter monitors a total number of SQL Server Integration Services (SSIS) packages that are currently running. The value is expressed in a numeric number that is usually low. Resolved by DBAs, Server administrators, ETL developers Suggested solutions On Integration Services Catalog, go through the All Executions built-in report If […]

Applies to SSIS Description This performance counter keeps track of total number of output rows that have been written into destination adapters. This counter will give you an indication of the overall execution progress of your data flow tasks. Because this counter tracks all rows read since the counter was started, you should expect the […]

Applies to SSAS Description This performance counter relates to the measurement of Analysis Services Proactive Caching. It monitors the average rate of notifications of Analysis Services cube by the underlying source relational database. Once the cube has been notified of data changes, measure group partitions are always processed using Full Process which discards the contents […]

Applies to Operating System Description The current percentage of page file in use. If the Operating System is paging this can be problematic as SQL pages can be pushed into the page file, causing delays for SQL to read what it thinks is in memory from disk. This counter should be as close to zero […]

Applies to Operating System Description Returns the paged to disk to resolve hard page faults. If the amounts are high (above 1000) this could indicate the server is running low on memory. This counter is the sum of Pages Input/sec and Pages Output/sec. Resolved by DBAs Suggested solutions Add additional memory Adjust the Max memory […]

Applies to Operating System Description Similar to Pages/sec except returns the number of read operations on the page file to resolve hard page faults. If the Pages/sec amount is higher than 1000 this could indicate the server is running low on memory. Page Input/sec and Page Reads/sec are very similar counters, Input/sec is the total […]

Applies to Operating System Description Returns the number of requests queued on the volume. Due to SANs and virtualization this counter is no longer valid to use. Resolved by DBAs Suggested solutions To determine disk performance issues, refer to Avg. Disk Read / sec and Avg. Disk Write / sec counters Additional research Examining and […]

Applies to Operating System Description Returns the average number of requests queued on the volume. Due to SANs and virtualization this counter is no longer valid to use. Resolved by DBAs Suggested solutions To determine disk performance issues, refer to Avg. Disk Read / sec and Avg. Disk Write / sec counters Additional research High […]

Applies to Operating System Description Returns the percentage of available free space per volume. Make sure this is not too low otherwise the database may run out of space to grow. As a rough guide aim to keep 20% free space per volume. Resolved by DBAs Suggested solutions Expand storage capacity of volume Create additional […]

Applies to SSRS Description The Web Service provides two endpoints – one for report execution and another for report management. This performance counter monitors the active sessions accessed via the two endpoints. The counter is made up of total active sessions as well as inactive sessions. However, when a session has been idle for more […]

Applies to Operating System Description The percentage of time the processor is utilized. A rule of thumb is to watch out for servers with over 80% constant usage. This counter can be inaccurate in virtual environments as CPU resources can change frequently. For virtual environments use System\Processor Queue Length. Resolved by DBAs Suggested solutions Determine […]

Applies to SSRS Description This performance counter measures the total number of simultaneous connections at a given time. The value of this counter is the sum of both active running and queued tasks and it will fluctuate as connections are opened or closed. This value is measured as numeric whole number that is reset whenever […]

Applies to SSIS Description Buffers are the mechanism used by the data flow engine to process data. This performance counter monitors the total number of buffers currently used by the data flow engine. The value of this performance counter fluctuates as the amount of data being processed in the data flow changes. A low value […]

Applies to SSIS Description This performance counter monitors the total number of binary large object (BLOB) files in use for spooling by the data flow engine within a given time period. The value of this performance counter fluctuates as BLOB files are used and disposed. The value of this counter is usually high and could […]

Applies to SSAS Description An obvious benefit to having aggregations in Analysis Services is that you are likely to have fast running queries at the expense of incurring additional size increase of your Analysis Services database. Having some of the aggregations in memory cache can save you valuable resources. This performance counter measures, in kilobytes […]

Applies to Operating System Description Reports the amount of memory in MB that is available for the operating system to use. The operating system must reserve the sufficient amount of memory in order to keep the SQL Server memory paging to minimum to prevent the dramatic performance degradation. Resolved by DBAs Suggested solutions Increase the […]

Applies to Operating System Description Displays the average size in bytes of read operations on the volume. This counter can be used in conjunction with Avg. Disk Sec/Read to determine the average read throughput of the volume. This counter can be used to form a baseline of storage usage. There is no rule of thumb […]

Applies to Operating System Description Displays the average time / latency in seconds to perform volume disk write operations. Note 1 sec = 1,000 milliseconds. Microsoft recommends, for good performance, that I/O latency should not exceed 25 milliseconds. Resolved by DBAs Suggested solutions Add additional memory Increase the max memory allocation Upgrade storage Make sure […]

Applies to Operating System Description Displays the average time / latency in seconds to perform volume disk read operations. Note 1 sec = 1,000 milliseconds. Microsoft recommend for good performance I/O latency should not exceed 25 milliseconds. Resolved by DBAs Suggested solutions Add additional memory Increase the max memory allocation Upgrade storage Make sure partitions […]

Applies to SSAS Description This performance counter relates to the measurement of Analysis Services internal server memory. It monitors the total amount of memory, in kilobytes (KB), known to the background cleaner. The total amount of memory refers to both shrinkable and non-shrinkable categories of Analysis Services internal server memory. Whilst shrinkable memory can easily […]

Applies to SSAS Description This performance counter monitors the total number of Analysis Services Data Mining queries being actively worked on. This performance counter requires administrator rights on the server in order for the data to be collected by the agent. As such this performance counter is configured to leverage the “SQL Server Monitoring Account” […]

Applies to SSAS Description This performance counter measures the current number of active client connections established at a point in time. The value fluctuates as connections are established and dropped. Analyze the value of this counter in conjunction with Query Pool Thread counter. Resolved by DBAs, Network administrators Suggested solutions Verify SQL Server Analysis Services […]

Applies to SSAS Description This performance counter measures current total number of client connections waiting for a lock to be released by the Analysis Services engine. To get a complete list of current lock-waits then make use of Dynamic Management Views (DMVs). DMV queries are an alternative to running XML/A Discover commands. You must have […]