:::: MENU ::::

Posts Categorized / Performance Tunning and Optimization

  • May 26 / 2009
  • 1
Backup and Restore, dbDigger, Indexes, Performance Tunning and Optimization

Change recovery model of SQL Srever database before index rebuild

Index rebuild may produce a lot of transaction logs. Hence it may cause of heavily populate your log file. So it would be better option to change recovery model of that specific database to simple before index rebuild process in it. Once index rebuild process is finished database may again be reverted to recovery model full. Assuming that AdventureWorks is in single user mode, we may run following commands to change and revert back the recovery model of a database for index rebuild operation.

USE AdventureWorks  
 -- Set recovery mode to simple before index rebuild  
 ALTER DATABASE AdventureWorks  
 SET recovery SIMPLE  
 -- Begin index rebuild process for selected tables  
 DBCC DBREINDEX ("HumanResources.Employee",'',90)  
 -- Set recovery model back to full  
 ALTER DATABASE AdventureWorks  
 SET recovery FULL  

While implementing above mentioned procedure make sure to reattain your backup sequence as without a new full backup and later log backup you will not be able to recover to the point if required. The reason is that backup sequence of log becomes disturb while changing recovery model.

  • May 13 / 2009
  • 0
Analysis Services SSAS, dbDigger, Performance Tunning and Optimization

Performance Configuration Issues for SQL Server Analysis Services

From an operational prospective, you can improve Analysis Services performance by keeping partition sizes reasonable, setting partition data slices, defining appropriate aggregations on all partitions, and running the Optimize Schema Wizard in the cube editor.

Partition Size

You can divide a cube into multiple partitions if you are using SQL Server 2000 Enterprise Edition (or SQL Server 7.0 Enterprise Edition) to increase query and processing performance. You should consider increasing the number of partitions if a partition file exceeds 5 GB or 20 million records. To determine the size of each partition, review the partition files in the Data folder.

Data Slice

When you partition a cube, you should define the data slice for each partition using the Partition Wizard. The Partition Wizard does not require you to set this data slice when you create a partition. As a result, it is possible (even easy) to create a partition without setting the data slice. In fact if you simply click through the Partition Wizard pages taking the default values, you end up with a partition without a data slice being set. You should verify that each partition of each cube in each Analysis Services database has a data slice defined. The only exception to this is if you have a cube with only one partition-in that case the data slice should not be set, because you want all of the cube data to be placed in that one partition. To verify that a data slice is defined, edit the partition in Analysis Manager and then step through the Partition Wizard pages. Defining the data slice enables Analysis Services to quickly eliminate irrelevant partitions from query processing. The data slice identifies the actual subset of data contained in each partition. Unless Analysis Services knows the range of data contained in each partition, it must query each partition, which negates much of the query performance benefit of partitions. To draw an analogy with SQL Server, creating a partition without a data slice is like creating a partitioned view without the CHECK clause. While you can do it, you force the query optimizer to scan all of the partitions in the view because you haven’t given it enough meta data to figure out what partition to access when a query is issued. While the Analysis Service’s runtime engine does not use a relational query optimizer (it has its own component that accomplishes a similar operation), it uses the data slice in roughly the same way: as meta data to tell it which partitions to scan if an aggregate cannot be
used or is not available. If you partition a cube by month, and have 36 months worth of data (in 36 partitions), and if you don’t specify the data slice, then the runtime engine must scan all 36 partitions to answer a query. If you specify the data slice, it could potentially only have to scan 1/36th the amount data, with an obvious improvement in
performance.. Setting a data slice also causes Analysis Services to add a join and a WHERE clause to the SQL statement used for retrieving data from the source database during processing. The WHERE clause limits the data retrieved by the SQL statement to the data that belongs in the data slice. For example, if you say that a partition’s data slice
is June 2003, then Analysis Services adds a join to the time dimension and adds the WHERE clause: WHERE = ‘June’ AND = ‘2003’
or whatever the appropriate member/level names are. If you do not define a data slice and you have multiple partitions, Analysis Services does not restrict the data that is retrieved from the source database. Without the data slice, if you just happen to have July 2003 data in the June partition. By specifying the data slice, the system can add these JOIN and WHERE clauses that assist in maintaining the integrity of the data. If Analysis Services is loading data for each partition from separate tables in the source database, this may yield some performance benefits. However, you should not disable the automatic generation
of the WHERE clause unless you are totally sure that the relational database partitioning is 100% correct when loading data. You must be 100% certain of the correctness of the data in all partitions of all cubes on the server in order to use this setting safely. Without the WHERE clause for your protection, double-counting data (or many-times counting of data) may occur, which could lead to server crashes if inconsistent data is processed. If you disable the generation of the WHERE clause, you assume all responsibility for enforcing data integrity between the data source and the data slice.


The most effective technique you can use to improve overall query responsiveness (assuming that the Analysis Services computer has sufficient memory and hard disk resources) is to design effective aggregations of fact data in each cube partition. However, too many aggregations will unnecessarily increase processing time without significantly improving query performance. When you use multiple partitions to increase query and processing performance, it is possible to deploy a new partition with no aggregations. While deploying partitions with different aggregation designs is a common optimization technique, deploying a partition with no aggregations generally indicates an error in deployment that can cause performance problems. You should verify that a minimum number of aggregations exist in each partition. You can quickly determine whether aggregations are defined on a partition by looking at the combined sizes of the .agg.flex.data and .agg.rigid.data files for each partition. Designing too many aggregations will slow processing and designing too few aggregations will slow
querying. Ensure that all partitions have a minimum number of aggregations – perhaps 10%.

Schema Optimization

Running the Optimize Schema tool on a cube eliminates unnecessary joins between dimension tables and fact tables, if certain conditions are met. By default, when you first create a cube, Analysis Services constructs a SQL query against the fact table that is a “N+1”-way join (where N is the number of dimensions). From the join, the Analysis Services query extracts the lowest-level key. From that key, Analysis Services begins the aggregation process. Having a 6-way join is typically not a major performance problem in most modern relational database systems. However, if your cube has 15 or 20 dimensions, the resulting multi-table join may suffer significant performance problems. Regardless of the number of dimensions in your cube, the Analysis Services query to the relational database is resolved faster and the data flows into Analysis Services more quickly during processing if you eliminate some of these joins. Fortunately, there is a common design technique that can greatly help the situation. Many star or snowflake schema designs are constructed in such a way that the foreign key that points from the fact table to the lowest level dimension table is not some random number, but it is also the member key itself. If that is true, then Analysis Services can “optimize away the join” and pull the member key directly from the fact table instead of using a join to the lowest-level dimension table. These conditions are checked for join removal are

  • The dimension must be a shared dimension.
  • The dimension must have been processed before you optimize the cube schema.
  • The member key column for the lowest level of the dimension must contain the keys that relate the fact table and the dimension table, and this must be the only key necessary to relate the fact table to the dimension table.
  • The keys in the member key column for the lowest level of the dimension must be unique.
  • The lowest level of the dimension must be represented in the cube; that is, the level’s Disabled property must be set to No. The level can be hidden.

If these conditions are met with respect to a dimension used in a cube, and the cube’s schema is optimized using the Optimize Schema command, Analysis Services composes a query that does not contain a join to the dimension table in the database when processing the cube. If these conditions are met for all dimensions in the cube, the Analysis server needs to read only the fact table to process the cube. Processing time reductions often can be substantial when this optimization technique is used. Cube schema optimization applies to all partitions of the cube, whether the partitions are processed independently or as a group. So, as a general rule, after you have designed the schema for a cube, you should run the Optimize Schema command. It removes the joins that meet the foregoing conditions. Next, you should determine which dimensions were not eliminated from the join and then determine how to meet the required conditions to eliminate the dimension table from the join. If you have partitioned your cube and specified the data slice, the dimension table used for the data slice cannot be eliminated. This join is set to protect you so that no additional, non-data slice data is included in the partition. If you do optimize away a dimension, you should be aware that the inner join that you have just eliminated had a side effect that may expose problems with your source data. The inner join to the dimension table eliminates fact table records that do not have matching dimension table records (this is what an inner join will do). This means that when you remove the inner join and start using the fact table member keys, you may start seeing processing errors that you were not getting before. When Analysis Services processes a record in the fact table that does not have a corresponding entry in the appropriate dimension table, Analysis Services generates an error. If you recreate a cube, add a dimension to a cube, or remove and then re-add a dimension, you must rerun the Optimize Schema command to re-optimize the cube. New dimensions are always added unoptimized.

  • May 13 / 2009
  • 0
Analysis Services SSAS, dbDigger, Performance Tunning and Optimization

Paging Files for SQL Server Analysis Services

By default, Windows uses a single paging file equal to approximately 1.5 times the amount of physical memory in your computer. However, because Analysis Services makes extensive use of Windows paging files, you should always add a second paging file equal to the amount of physical memory on your computer. The SQL Server relational and multidimensional run time engines work with memory very differently. The SQL Server relational engine directly maps and controls physical memory usage, while the Analysis Services multidimensional engine relies on the Windows operating system to allocate additional memory.
Analysis Services may need to use the paging file for its memory needs. You must ensure that the total paging file space is more than that configured by default, so that Analysis Services has sufficient virtual memory if the Windows operating system has insufficient physical memory. While the Windows operating system has provisions for effectively controlling the general use of memory, Microsoft strongly recommends that customers configure servers with an adequate amount of memory so that extensive paging does not occur. If the main processing component of Analysis Services, the msmdsrv process, causes extensive paging, processing performance suffers.

  • May 13 / 2009
  • 0
Analysis Services SSAS, dbDigger, Performance Tunning and Optimization

How to Tune the Process Buffer Size for SQL Server Analysis Server

Perform the following steps to tune the process buffer size on an Analysis server:
If you have 4 gigabytes (GB) or more of physical memory on the computer, you are running Microsoft Windows Advanced Server or Windows Datacenter Server, and either large dimensions or large process buffers are causing memory concerns, then enable the /3GB switch for the operating system and enable Analysis Services to use this additional memory.
Set Performance Monitor to collect the Temp file bytes written/sec counter for the Analysis Services:Proc Aggs object. By using Analysis Manager, configure the Analysis server properties to assign the Temporary file folder (on the General tab of the Server Properties dialog box) to an unused physical drive, and configure the process buffer size (on the Processing tab) to a minimal value, such as 32 megabytes (MB).
Restart Analysis Services and then use Performance Monitor or Windows Task Manager to determine what the virtual memory usage stabilizes at for the Analysis Services process (msmdsrv.exe).
Process the cube or partitions under consideration and observe the Temp file bytes written/sec counter you added to Performance Monitor. Once the aggregation calculation phase starts, you will start to see I/O to the Temporary files.
Gradually increase the process buffer size and re-process (restarting the Analysis Services service each time) until the Temp file bytes written/sec counter shows that the Temporary file is not being used. Then increase the number by 10 percent. If the virtual memory allocation for the Analysis Services service exceeds the HighMemoryLimit threshold, increase that value as well.
Repeat these steps for any large partitions (or groups of partitions) to determine the best system-wide process buffer size.

  • Apr 29 / 2009
  • 0
DBA best practices, dbDigger, Performance Tunning and Optimization, Publications of Atif Shehzad on MSSQLTips.com, SSMS tips and tricks

Controlling changes in SQL Server 2008 that require table re-creation (saving changes is not permitted)

My article related to Controlling changes in SQL Server 2008 that require table re-creation (saving changes is not permitted) is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Controlling changes in SQL Server 2008 that require table re-creation (saving changes is not permitted

  • Apr 29 / 2009
  • 0
dbDigger, Performance Tunning and Optimization, T-SQL Interview Questions

Choosing between UNION and UNION ALL

When using the UNION statement, the fact should be considered that, by default, it performs the equivalent of a SELECT DISTINCT on the final result set. In other words, UNION takes the results of two like record sets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. This process occurs even if there are no duplicate records in the final record set.
If you know that there are duplicate records, and this presents a problem for your application, then by all means use the UNION statement to eliminate the duplicate rows.
On the other hand, if you know that there will never be any duplicate rows, or if there are, and this presents no problem to your application, then you should use the UNION ALL statement instead of the UNION statement.

Advantage of UNION ALL

The advantage of the UNION ALL is that is does not perform the SELECT DISTINCT function, which saves a lot of unnecessary SQL Server resources from being using. The better performance of UNION ALL in suitable cases can be verified through execution plans.

Consult us to explore the Databases. Contact us