Applies to SSAS Description This performance counter measures the total number of aggregation lookups into the memory cache. A high value for this performance counter indicates that most of your queries are using a shared reference set which could be an indication that most of your queries are optimal. The value of this counter is […]
Applies to SSAS Description This performance counter measures the number of times that queries unsuccessfully attempted to retrieve data from the memory cache. Expect the value of this counter to increase shortly after cube processing or Analysis Services service restart. If this number continues to grow than it may indicate insufficient memory or that queries […]
Applies to SSAS Description This performance counter measures the total number of requests to connect to the Analysis Services instance. You can go through the MSMDSRV.Log file to have a look at details of connection requests. The value of this counter is reset whenever the service is restarted. Resolved by DBAs, Server administrators, BI developers […]
Applies to SSAS Description This performance counter monitors the total number of entries that are currently stored in memory cache of the Analysis Services engine. The value of this counter is reset whenever the service is restarted. Resolved by DBAs, Server administrators, BI developers Suggested solutions Add more physical memory to the computer Partition large […]
Applies to SSAS Description This performance counter measures the current in-memory size (in kilobytes) used by the aggregation cache of the Analysis Services engine. The value of this counter is reset whenever the service is restarted. Resolved by DBAs, Server administrators, BI developers Suggested solutions Add more physical memory to the computer Partition large cubes […]
Applies to SSIS Description This performance counter monitors the total number of physical and virtual memory currently being used by data flow engine’s transformation tasks. The value for this counter is measured in bytes which can be high or low depending on the amount of transformation required in the given data flow engine. Resolved by […]
Applies to SSIS Description A transformation task such as the Sort transform may require additional physical or virtual memory at runtime. In such cases, the data flow engine will generate a private buffer required by the given data transformation. This performance counter monitors the total number of physical and virtual memory created at runtime by […]
Applies to SSAS Description This performance counter measures the total number of sub-cube queries that have been answered from data in memory cache since the Analysis Services service was started. The value of this counter is generally high. It should be compared to Total Misses performance counter to evaluate the percentage of sub-cube queries that […]
The challenge One of the main tasks of a SQL Server database administrator is performance tuning. Sometimes, though, coders or developers don’t always prioritize database performance or query optimization. Here is a typical scenario Imagine that developers create a new table and then insert some records in a test environment and test their queries to […]
The user’s read requests in SQL Server are managed and controlled by the SQL Server Relational Engine, that is responsible for determining the most optimized access method, such as index scan or table scan, to retrieve the requested data. These read requests are also optimized internally by the SQL Server Storage Engine, the buffer manager […]
Introduction In the first part of this article, we will discuss about parallelism in the SQL Server Engine. Parallel processing is, simply put, dividing a big task into multiple processors. This model is meant to reduce processing time. SQL Server can execute queries in parallel SQL Server creates a path for every query. This path […]
Quite often developers put the responsibility of the server performance on the shoulders of DBAs. But to be honest, as a developer, it is your responsibility to write code which is efficient and optimal. However, when writing code, the developer often lacks the knowledge and/or experience about SQL Server to meet this requirement. In this […]
When you define SQL Server database tables, local variables, expressions or parameters, you should specify what kind of data will be stored in those objects, such as text data, numbers, money or dates. This attribute is called the SQL Server Data Type. SQL Server provides us with a big library of system data types that […]
This articles gives you a performance comparison for NOT IN, SQL Not Exists, SQL LEFT JOIN and SQL EXCEPT.
It is a common misconception that you need real production data, or production like data, to effectively tune queries in SQL Server. I am going to explain how you can compile the same execution plans as what your production environment would compile, so that you can tune them in a non-production environment, gaining these benefits.
With the latest versions of Azure SQL database, Microsoft has introduced a number of new mechanisms to help users and administrators better optimize their workload. Automatic index management and Adaptive query processing provide us with the possibility to rely on the built-in intelligence mechanism that can automatically tune and improve the performance of our workload.
The performance of a database is the most challenging and critical part of database optimization. The challenge every DBA faces is to identify the most resource-intensive databases. This article talks about the natively available features that can accomplish the task of getting the required details of the entire database at a granular level.
Description Indexing is key to efficient query execution. Knowing what indexes are unneeded, incorrectly used, or unused can allow us to reduce disk usage and improve write performance at little cost to your organization.
Performance tuning is one of the most important and critical tasks that the SQL Server Database Administrator performs on a daily basis to keep a SQL Server running in a healthy state, by identifying the performance bottlenecks and fixing the main cause of these problems.
Basics of TOP Operator The TOP keyword in SQL Server is a non-ANSI standard expression to limit query results to some set of pre-specified rows. As an argument, it takes 0 to positive Bigint (9223372036854775807) and anything beyond or less gives an error message. TOP without an order by clause, in production, seems buggy because […]
Introduction In the articles How to partition an SSAS Cube in Analysis Services Multidimensional and Benefits of Partitioning an SSAS Multidimensional Cube, the concept of measure group partitioning is introduced and the advantages are clearly illustrated. One of the biggest advantages of partitioning is partition elimination, where only the partitions necessary to satisfy the query […]
Introduction To improve performance, it is common for DBAs to search in each aspect except analyzing storage subsystem performance even though in many times, issues are, in fact, caused by poor storage subsystem performance. Therefore, I want to give you some tools and recommendation that you can use it to prevent your storage subsystem from […]
Introduction As DBAs, we all get to the point where we are asked to setup a new server for a specific environment. Setting up a new server is not a big thing but giving the answer to the question that “how well it will work” might be tricky.
With this article, we continue part 1 of common best practices to optimize the performance of Integration Services packages. As mentioned in the previous article “Integration Services (SSIS) Performance Best Practices – Data Flow Optimization“, it’s not an exhaustive list of all possible performance improvements for SSIS packages. It merely represents a set of best […]
In this article, we’ll present a couple of common best practices regarding the performance of Integration Services (SSIS) packages. These are general guidelines for package design and development which guarantee a good performance in most use cases. However, there are always exceptional cases and outliers. The mantra of “measure twice, cut once” also applies here. […]
© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy