Performance

August 28, 2019 by

APX1092 – HAVING with non-aggregate functions Description: This rule evaluates the T-SQL script for using HAVING with non-aggregate functions. HAVING should be used only with aggregate functions. For more information visit https://www.techonthenet.com/sql/having.php Example script:

Message: HAVING clause is used in a non aggregate function APX1096 – Comparison with NULL Description: This rule evaluates the […]

Screenshot of Query Store fully enabled.

Performance Monitoring via SQL Server Query Store

August 19, 2019 by

SQL Server Query Store is a performance monitoring tool that helps us evaluate the performance of a SQL query in terms of several different performance metrics such as CPU and Memory Consumption, execution time and the I/O cycles consumed by the query. Query store is similar to the windows “Task Manager”. A task manager provides […]

Performance Monitor report showing data collection set captured for memory performance

SQL Server monitoring tools for memory performance

March 27, 2019 by

Memory pressure slowing down queries This article is the sequel in a series about SQL Server monitoring tools and common performance issues. The first article SQL Server monitoring tools for disk I/O performance is about how to detect and solve high input/output on hard disk subsystems when doing too much work during peak or maintenance […]

Comprehensive Performance review template

July 3, 2018 by

I looked around for a good performance review template for a long time but I finally just sat down and wrote my own. It is a 5 pt system with various categories that cover hard skills – your skill at your core competency i.e testing soft skills (quality, follower ship, communication, proactivity, intangibles) Performance – how you are doing, […]

SQL Server performance counters (Batch Requests/sec or Transactions/sec): what to monitor and why

June 5, 2018 by

When maintaining SQL Server, it is essential to get an accurate perception of how busy it is. Two metrics that are often considered as indicators of how busy SQL Server is are Batch Requests/sec and Transaction/sec. When those metrics trend higher, they often affect all other metrics and make them go higher as well. While […]

BI performance counter: Processing pool job rate

December 25, 2017 by

Applies to SSAS Description A processing pool is a type of an Analysis Services worker thread pool used to process an incoming query request. Threads from the processing pool are used for activity that takes place in the storage engine including dimension scans, partition scans, and aggregation scans. This performance counter monitors the rate of […]

BI performance counter: Query pool job queue length

December 25, 2017 by

Applies to SSAS Description A query pool is a type of an Analysis Services worker thread pool used to parse an incoming query request. Threads from the query pool are used for activity that takes place in the formula engine. This performance counter measures the total number of jobs waiting for a thread from the […]

BI performance counter: Query pool job rate

December 25, 2017 by

Applies to SSAS Description This performance counter keeps track of the rate of jobs through the query thread pool. When the value of this performance counter is greater than zero then it indicates that there are more queries than query threads. Resolved by DBAs, Server administrators, BI developers Suggested solutions Consider adding more CPU core […]

BI performance counter: Quota KB

December 25, 2017 by

Applies to SSAS Description Analysis Services pre-allocates a modest amount of memory at start up so that requests can be handled immediately. Additional memory is allocated as query and processing workloads increase. This performance counter gives an indication, in kilobytes (KB), of total current Analysis Services instance’s memory quota. This can also be referred to […]

BI performance counter: Requests Executing

December 25, 2017 by

Applies to SSRS Description This counter measures the total number of requests that are currently being executed i.e. the number of active working thread. The value of this performance counter is represented in a numeric format which is reset whenever the service is restarted. Once a given request execution completes, then it is removed from […]

BI performance counter: Short parsing job queue

December 25, 2017 by

Applies to SSAS Description Processing of dimensions and partitions and querying of partitions are handled by job operations. This performance counter measures the total number of jobs in the queue of the short parsing thread pool. CPU is underutilized when processing pool job queue length is greater than 0. This means that SSAS wants to […]