Top 10 Lists

Top 10 Performance and Productivity Reasons to Use SQL Server 2008 for Your Business Intelligence Solutions

CarlRabeler — Tue, 24 Feb 2009 19:09:15 GMT

Author: Carl Rabeler

Reviewers: Mark Souza, Prem Mehra, Lindsey Allen, Mike Weiner, James Podgorski, Donald Farmer, Robert Bruckner, Sanjay Mishra, Lukasz Pawlowski, Jeff Bernhardt, Nicholas Dritsas

Microsoft SQL Server 2008 provides new functionality not found in previous versions and numerous performance and usability enhancements that are specifically designed for business intelligence (BI) solutions. These features are designed to improve performance, increase developer productivity, and enrich the end-user experience. The intent of this article is to provide a list, with some details, of the top performance reasons to use SQL Server 2008 for your new business intelligence solutions and to upgrade to SQL Server 2008 for your existing business intelligence solutions. The reasons begin with the performance enhancements that do not require modifications to your existing business intelligence solutions and then proceed to technology.

Important: For information about upgrading your existing Business Intelligence solutions to SQL Server 2008, see the SQL Server 2008 Upgrade Technical Reference Guide.

1. Scalability and Resource Management Enhancements in Reporting Services

With SQL Server 2005 Reporting Services, the reporting engine has limited ability to exercise control over resource management. As a result, very large reports and heavy workloads by a large number of concurrent users can result in out-of-memory conditions. In addition, with SQL Server 2005 Reporting Services, long reports can take a long time to render, because every page is processed before the first page is returned to the user.

With SQL Server 2008 Reporting Services, the Report Engine has been redesigned to enable Reporting Services to control resources (threads, memory and state) to ensure that sufficient memory is always available to execute very large reports and heavy workloads from a large number of concurrent users (by paging and releasing memory in response to memory pressure). This is accomplished by:

Embedding HTTP capabilities into the Report Engine and removing the dependency of Reporting Services on Internet Information Services (IIS). As a result, configuring Reporting Services is much easier because it is isolated from other Web applications.
Calculating report items on demand.
Rendering only the pages requested by users (unless the total page count is referenced within the report), which improves first page response time.

In working with SQL Server 2008 Reporting Services in our lab, SQL Server 2008 Reporting Services was able to respond to 3–4 times the total number of users and their requests on the same hardware without HTTP 503 Service Is Unavailable errors compared with SQL Server 2005 Reporting Services, regardless of the type of renderer. In stark contrast, SQL Server 2005 Reporting Services generated excessive HTTP 503 Service Is Unavailable errors as the number of users and their requests increased, regardless of the report renderer.

Our tests clearly demonstrated that the new resource management architecture of the report server enables SQL Server 2008 Reporting Services to scale very well, particularly on the new four-processor, quad-core processors. With our test workload, SQL Server 2008 Reporting Services consistently outperformed SQL Server 2005 with the PDF and XLS renderers on the four-processor, quad-core hardware platform (16 cores) both in terms of response time and in terms of total throughput. As a result, we recommend with SQL Server 2008 Reporting Services that you scale up to four-processor, quad-core servers for performance and scale out to a two-node deployment for high availability. Thereafter, as demand for more capacity occurs, add more four-processor, quad-core servers. With SQL Server 2005 Reporting Services, we recommend that you scale out above four processors due to resource bottlenecks. Our tests clearly demonstrate that SQL Server 2008 Reporting Services utilizes your hardware resources more efficiently than SQL Server 2005 Reporting Services, particularly with the new four-processor, quad-core servers. For more information, see Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned.

2. Subspace Computations in Analysis Services

The SQL Server Analysis Services formula engine devises a query execution plan to retrieve data requested by a query. Each node of the query execution plan utilizes one of two types of evaluation modes: a subspace computation mode or a cell-by-cell evaluation mode. If the subspace computation mode is used, the Analysis Services formula engine operates only on cells with data that contribute to the result. If the cell-by-cell evaluation mode is used, the Analysis Services formula engine operates on each cell that theoretically could contribute to the result, regardless of whether it contains data. In a sparse cube, which is typical, retrieving data using the cell-by-cell evaluation mode is inefficient for calculation-intensive queries, because many null cells (which do not contribute to the result) are operated on.

In SQL Server 2005, before Service Pack 2 (SP2), the query execution plans devised by the Analysis Services formula engine primarily contained nodes that utilized the cell-by-cell evaluation mode. In SP2 of SQL Server 2005, a small number of MDX functions support the subspace computation mode. In SQL Server 2008 Analysis Services, the vast majority of MDX functions use the subspace computation mode. In working with our customers with SQL Server 2008 Analysis Services, we have observed tremendous performance gains with the following:

Sparsely populated cubes
Cubes with complex calculations

These performance gains with SQL Server 2008 Analysis Services are achieved without modification of your business intelligence solution. In addition, you can frequently further increase the performance of cubes with complex calculations by performing additional tuning of your MDX calculations in the cube. For more information, see Performance Improvements in MDX in SQL Server 2008 Analysis Services and the SQL Server 2008 Analysis Services Performance Guide.

3. Pipeline Parallelism in Integration Services

In SQL Server 2005 Integration Services, each execution tree in a DataFlow task within a package is generally assigned a single worker thread, and, under certain conditions, each execution tree actually shares a thread with other execution trees. This approach has the following benefits:

All thread scheduling is done during the pre-execution phase, rather than consuming time during the execution of the package.
The buffer’s data stays in cache because a thread runs a full buffer over a complete execution tree.

However, this approach has the following downsides that frequently outweigh the preceding benefits, particularly with long or branched execution trees:

The scheduling of threads is not always optimal, because the relative amount of work for each execution tree is not known before execution.
Not all cores are utilized on a multiprocessor computer if there are fewer execution trees than processor cores. The lack of utilization of all cores is particularly an issue if an execution tree has many synchronous components, because in this scenario all of the synchronous components share a single thread, and they can become processor bound on a single core. This same issue occurs if you split the data into multiple paths by using Multicast, because Multicast is a synchronous component. As a result, even though it visually appears that you are parallelizing your package, the paths are executed serially, because the output paths belong to the same execution tree and only a single thread is allocated to the execution tree.

Note: Inserting an asynchronous transformation in the data flow to increase parallelism by creating a second execution tree (a performance design trick on high-performance multiprocessor computers) has performance overhead, because all data must be copied for the new execution tree.

In SQL Server 2008 Integration Services, pipeline parallelism has been enhanced with architecture improvements to the data flow engine. Now worker threads are dynamically assigned to individual components from a common threadpool. As a result, the data flow engine automatically utilizes all cores on multicore computers without the need to resort to design tricks. The increased pipeline parallelism in SQL Server 2008 Integration Services from threadpooling increases the speed at which packages can execute, and it makes better use of your existing resources, provided there are no other resource bottlenecks (such as I/O or memory). In working with our customers with SQL Server 2008 Integration Services, we learned that packages with long chains of synchronous transformations and most packages running on multiprocessor computers saw significant performance increases.

Note: For more information about SQL Server 2008 Integration Services from the SQL Server Integration Services team, see the SSIS Team Blog. For additional performance information, see ETL World Record and Top 10 SQL Server Integration Services Best Practices.

4. Scalability and Availability Enhancements in Analysis Services

In SQL Server 2005, backups of large Analysis Services databases have limited scalability, and Analysis Services supports only a single location for all Analysis Services databases within an instance of Analysis Services, although measure group partitions within a database can be distributed across multiple drives to distribute the I/O. Furthermore, after processing a single database for an Analysis Services instance on a processing server, you cannot detach the processed database and then attach it to a query server for increased scalability and availability. In addition, in SQL Server 2005 Analysis Services, attaching read-only copy of an Analysis Services database to multiple servers for increased scalability and availability is not natively supported; this capability is supported only through the use the SAN snapshot technologies (see Scale-Out Querying with Analysis Services Using SAN Snapshots).

In SQL Server 2008 Analysis Services, these issues are resolved with the following scalability and availability enhancements:

You can back up an Analysis Services database of any size. The performance of Analysis Services database backups scales linearly, and this performance is comparable to file-based backups.
You can deploy each database within an Analysis Services instance to its own storage location, enabling you to place each Analysis Service database within an instance on separate drives.
You can detach an entire database from an Analysis Services instance, copy or move it to a new location, and then attach it to another Analysis Services instance. This feature increases scalability and performance by enabling you to process new data into an Analysis Services database on a processing server while users query the current version of the Analysis Services database on a query server. After processing is complete, you can detach the processed database from a processing server and then attach it to the query server. Furthermore, if you utilize two Analysis Services instances on the query server and use a load balancer to distribute queries to the appropriate instance, you can direct new user connections to the newly attached database without affecting availability, because currently connected users can complete their currently running queries against the previously attached database. This feature also enables you to detach a database from an Analysis Services instance for archiving or backup purposes.
You can attach an Analysis Services database in read-only mode to multiple Analysis Services instances for increased scalability and availability. The read-only database can be stored on a SAN and mounted to multiple instances, or it can be shared via a file system share and mounted to multiple instances.

Note: With large databases in which history partitions do not change, you will want to use high-speed SAN copy capabilities to clone the detached database or use Robocopy. For information about using Robocopy, see Sample Robocopy Script to customer synchronize Analysis Services databases.

The following diagram illustrates the use of these new features with a single processing server attached to a database in read/write mode and four query servers attached to a single read-only database. A load balancer distributes queries among the four query servers.

Figure 1: Query Scale-Out using Read-only Databases

5. Optimized Design Experience in Analysis Services

You design SQL Server Analysis Services solutions in Business Intelligence Development Studio (BIDS). In SQL Server 2005, when you design your Analysis Services solution in Business Intelligence Development Studio, it is too easy to design a solution that does not follow best practices performance guidelines and as a result have performance issues. For example, the failure to design appropriate attribute relationships is a significant design and performance issue, but SQL Server 2005 Business Intelligence Development Studio does not notify you if you fail to create such relationships. In addition, with SQL Server 2005 Analysis Services, you frequently have to manually design aggregations to achieve optimum performance.

In SQL Server 2008 Analysis Services, the Business Intelligence Development Studio development environment includes the following improvements to promote best practices and help developers design high-performing solutions:

Best practice alerts called AMO warnings have been added to the AMO object model. These warning messages surface in Business Intelligence Development Studio to alert developers when their designs are not consistent with any of over 40 best practices. These warnings are integrated into real-time designer checks through the use of blue squiggly alert lines, and they providea nonintrusive (and dismissible) way for developers to detect potential problems with their design long before deployment. You can also use this new capability to review your existing SQL Server 2005 Analysis Services cubes for compliance with design best practices guidelines, either by opening your Analysis Services database in the SQL Server 2008 version of Business Intelligence Development Studio or by running the newest version of the SQL Server 2005 Best Practices Analyzer (which incorporates these same AMO warnings). You can also access these AMO warnings from PowerShell or a custom client application.
A new attribute relationship designer has been added for viewing and editing attribute relationships, with built-in validations to help in creating optimal dimension designs.
The dimension editor has been streamlined with a single dimension wizard, better detection and classification of dimension attributes and member properties, and the automatic detection of parent-child relationships.
A new aggregation designer has been added within Business Intelligence Development Studio with a new algorithm for creating new aggregations. The aggregation designer is optimized to work with usage driven aggregations. You can now easily look at your existing aggregations, add to those aggregations, or remove and replace them. Intelligent support is provided to help with merging existing and new aggregation designs. For more information about the new aggregation designer and using it with usage-driven aggregations, see Reintroducing Usage-Based Optimization in SQL Server 2008 Analysis Services.

6. New and Enhanced Authoring Capabilities and Visualizations in Reporting Services

In SQL Server 2005 Reporting Services, you can create powerful and functional charts and graphs that enable you to hyperlink or drill down to other reports and Web sites. You can also integrate these reports and graphs into dashboards. However, making these reports and graphs pop and sizzle is sometimes difficult and time-consuming.

SQL Server 2008 Reporting Services offers many authoring enhancements that increase developer productivity. In addition, full-featured versions of the Dundas chart and gauge controls are now included, giving you the ability to create beautifully rendered charts and gauges.

Authoring Enhancements

In addition, with SQL Server 2008 Reporting Services, there are many additional authoring enhancements. These include:

A new data representation format called Tablix. By using Tablix,you can now author reports with any structure, with the best of table merged with the best of matrix.
Enhanced formatting capabilities. You can now create text boxes with mixed styles, paragraphs, and hyperlinks, and you can use expressions to implement inline formatting.
Improved design experience. The report designer in Microsoft Visual Studio is redesigned to simplify the design experience.
The Report Builder 2.0 authoring environment. This stand-alone authoring environment provides the intuitive and familiar Microsoft Office look.

Chart Enhancements

Chart enhancements include scale breaks, annotations, custom color palettes, merged charts, and multiple axes. New chart types include the following types of charts:

Stepped Line
Spline Area
Pie Callout
Polar
Radar
Gantt
Range Line/Column
Funnel
Pyramid
Histogram
Box

Gauges

The new gauges include the following gauge types:

Circular
Linear
Angular
Thermometer
Numeric Indicators
State Indicators

For more information, see Reporting Services and What’s New (Reporting Services).

7. Data Compression in the Relational Engine

The disk I/O subsystem is one of the most common bottlenecks for many relational data warehouse implementations. Additional disks are frequently added to reduce read/write latencies. This can be expensive, especially on high-performing storage systems. At the same time, the need for storage space continues to increase due to rapid growth of the data, as does the cost of managing databases (backup, restore, transfer, and so on).

In SQL Server 2008, data compression addresses some of these problems. With data compression, you can selectively compress any table, table partition, or index, resulting in a smaller on-disk footprint, smaller working-set memory size, and reduced I/O. Working with customers, we have seen that enabling data compression has resulted in a 50-80% saving in disk space in many environments. This savings in disk space directly translates into less money being required for high speed disk storage. Relational data warehouses with I/O bottlenecks and without processor saturation may also see an increase in overall performance.

SQL Server supports two types of data compression: row compression, which compresses the individual columns of a table, and page compression, which compresses data pages using row, prefix, and dictionary compression. The compression results are highly dependent on the data types and data contained in the database; however, in general we’ve observed that using row compression results in lower processor overhead but saves less space. Page compression, on the other hand, results in higher processor overhead, but it results in much larger space savings. Page compression is a superset of row compression, implying that an object or partition of an object that is compressed using page compression also has row compression applied to it. Compressed pages remain compressed in memory until rows or columns on the pages are accessed.

Both row and page compression can be applied to a table or index in an online mode without interruption to the application availability, but this compression can take a long time. Partitions of a partitioned table cannot be compressed or uncompressed online, however. In our testing we found that using a hybrid approach where only the largest few tables were compressed resulted in the best overall performance – this approach saved significant disk space but had a minimal impact on performance. Because additional disk space is required during the compression process, we found that it is best begin by compressing the smallest of the objects that you choose to compress. By doing so, you utilize the least disk space during the compression process and avoid running out of disk space during the compression process.

To determine how compressing an object will affect its size, you can use the sp_estimate_data_compression_savings system stored procedure. Database compression is supported only in the SQL Server 2008 Enterprise and Developer editions. It is fully controlled at the database level, and it does not require any application change.

8. Resource Governor in the Relational Engine

Maintaining a consistent level of service by preventing runaway queries and guaranteeing resources for mission-critical workloads has been a challenge for the SQL Server relational engine. In the past there was easy way to guarantee a certain amount of resources to a set of queries and to prioritize access to SQL Server relational engine resources – all queries had equal access to all the available relational engine resources.

For example:

A single large data warehouse query can be granted as much as 25% of workspace memory; three concurrent large queries of this type will block any additional large queries.
Specific workloads benefit from reduced parallelism. During partition processing, SQL Server Analysis Services may issue a large number of concurrent queries, and for best performance, we recommend that you configure your Analysis Services processing jobs to issue one query per core on the relational data warehouse computer. However, if the relational engine parallelizes each of these processing queries, threads may thrash. One solution is to run each processing query using MAXDOP=1. However, before SQL Server 2008, this was a server-wide setting that affected all queries, because you could not add the MAXDOP query hint to Analysis Services processing queries. Using MAXDOP=1 for all queries negatively affected queries that derived substantial benefit from parallelization, forcing you to choose which type of query to optimize.

In SQL Server 2008, Resource Governor addresses these issues by enabling users to differentiate workloads, allocating resources as they are requested, controlling parallelism for particular workloads (for example, by user name or computer name), and substantially increasing concurrency with resource intensive queries. The Resource Governor limits can easily be reconfigured in real time with minimal impact on currently executing workloads. The allocation of the workload to a resource pool is configurable at the connection level, and the process is completely transparent to applications. This new functionality is particularly useful on systems with large memory and many cores.

The diagram below depicts the resource allocation process. In this scenario, three workload pools (the Admin workload, the OLTP workload, and the Report workload) are configured, and the OLTP workload pool is assigned a high priority. In parallel, two resource pools (the Admin pool and the Application pool) are configured with specific memory and processor (CPU) limits as shown. Finally, the Admin workload is assigned to the Admin pool and the OLTP and Report workloads are assigned to the Application pool.

Figure 2: Using Resource Governor to Allocate Resources

Below are some other points you need to consider when you use Resource Governor:

·Because Resource Governor relies on login credentials, the host name, or the application name as a resource pool identifier, ISV applications that use a single login to connect multiple application users to SQL Server cannot use Resource Governor without reworking the application. This rework would require the application to utilize one of the resource identifiers from within the application differentiate workloads.
Database level object grouping, in which resource governing is done based on the database objects being referenced, is not supported in the current version.
Resource Governor allows resource management within a single SQL Server instance only. For multiple instances, consider using Windows System Resource Manager.
Only processor and memory resources can be configured – I/O resources cannot be controlled.
Dynamically switching workloads between resource pools after a connection is made is not possible in the current version.
Resource Governor is only supported in SQL Server 2008 Enterprise and Developer editions and can only be used for the SQL Server Database Engine; resource usage by SQL Server Analysis Services, SQL Server Integration Services, and SQL Server Reporting Services cannot be controlled by Resource Governor. For these other SQL Server services, consider using Windows System Resource Manager.

9. Minimally Logged Operations in the Relational Engine

While bulk load operations have substantial performance benefits over row-by-row operations, the logging of these bulk load operations (typically inserts) in high-volume data loading scenarios is frequently the cause of disk I/O bottlenecks. Unlike fully logged operations, which use the transaction log to keep track of every changed row, minimally logged operations keep track of extent and page allocations and metadata changes only. Because much less information is tracked in the transaction log, a minimally logged operation is often faster than a fully logged operation if logging is the bottleneck. Furthermore, because fewer writes go the transaction log, a much smaller log file with a lighter I/O requirement becomes viable. Minimally logged operations are available only if your database is in bulk-logged or simple recovery mode. For more information, see Operations That Can Be Minimally Logged. Note that performing a bulk operation in a bulk-logged database has impact on the backup strategy for the database. Additionally, the rollback of a minimally logged operation is fully logged, which can result in the rollback of a minimally logged operation taking significantly longer than the originally logged operation. For more information about the implications, see Backup Under the Bulk-Logged Recovery Model .

SQL Server 2008 introduces INSERT…SELECT as a new way to perform minimally logged insert operations, allowing Transact-SQL based INSERT statements to be minimally logged under certain circumstances:

Heaps – An INSERT statement that takes its rows from a SELECT operation and inserts them into a heap is minimally logged if a WITH (TABLOCK) hint is used on the destination table.
Clustered index – Whether an INSERT… SELECT operation into clustered indexes is minimally logged depends on the state of trace flag 610. Not every row inserted in a clustered index with trace flag 610 is minimally logged. If the bulk load operation causes a new page to be allocated within an extent, all of the rows sequentially filling that new page are minimally logged. Rows inserted into pages that are allocated before the bulk load operation occurs are still fully logged, as are rows that are moved as a result of page splits during the load. This means that for some tables, you may still get some fully logged inserts. If trace flag 610 causes minimal logging to occur, you should generally see a performance improvement. But as always with trace flags, make sure you test for your specific environment and workload. Consider these two examples:

Example 1: You have a table clustered in a key that contains even integer key values 0-16. The table has four leaf pages, the pages are not full, and they can hold two more rows on each page. You bulk load eight new rows, with uneven key values 1-15. The new rows fit in the existing pages. The illustration below shows how this table will look before and after the load operation.

Figure 3: A fully logged insert under trace flag 610

In this example, no new pages are allocated and trace flag 610 will not give you any minimal logging.

Example 2: Consider an alternative scenario: The table initially now has two pages, both full, containing the key values 0-7. You bulk load rows with key values 8-16.

Figure 4: A minimally logged insert under trace flag 610

In this example, the pages holding key values 8-15 (in light blue above) will be minimally logged with trace flag 610.

For more information on using this new functionality to improve data loading performance, see The Data Loading Performance Guide.

10. Other Great Features

SQL Server 2008 introduces the following additional features that improve the performance of business intelligence solutions.

Star Query Enhancements

Most data warehouse queries are designed to follow a star schema and can process hundreds of millions of rows in a single query. By default, the query optimizer detects queries against star schemas and creates efficient query plans for them. One method the optimizer can use to generate an efficient plan is to use bitmap filtering. A bitmap filter uses a compact representation of a set of values from a table in one part of the operator tree to filter rows from a second table in another part of the tree. Essentially, the filter performs a semi-join reduction; that is, only the rows in the second table that qualify for the join to the first table are processed. In SQL Server 2008, bitmap filtering can be introduced in the query plan after optimization, as in SQL Server 2005, or introduced dynamically by the query optimizer during query plan generation. If the filter is introduced dynamically, it is referred to as an optimized bitmap filter. Optimized bitmap filtering can significantly improve the performance of data warehouse queries that use star schemas by removing nonqualifying rows from the fact table early in the query plan. Without optimized bitmap filtering, all rows in the fact table are processed through some part of the operator tree before the join operation with the dimension tables removes the nonqualifying rows. If optimized bitmap filtering is applied, the nonqualifying rows in the fact table are eliminated immediately. Optimized bitmap filtering is available only in the Enterprise, Developer, and Evaluation editions of SQL Server. For more information, see Optimizing Data Warehouse Query Performance Through Bitmap Filtering.

Partitioned Table Parallelism

SQL Server 2008 improves query processing performance on partitioned tables for many parallel plans, changes the way parallel and serial plans are represented, and enhances the partitioning information provided in both compile-time and run-time execution plans. SQL Server 2005 uses a single thread per partition parallel query execution strategy, but SQL Server 2008 can allocate multiple threads to a single partition. This improvement is of particular importance to data warehouse environments; fact tables are often candidates for partitioning, because they typically contain a few columns with a very large number of records. For more information, see Query Processing Enhancements on Partitioned Tables and Indexes.

The MERGE Statement

In SQL Server 2008, you can perform multiple data manipulation language (DML) operations in a single statement by using the MERGE statement. For example, you may need to synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table. Typically, this is done by executing a stored procedure or batch that contains individual INSERT, UPDATE, and DELETE statements. However, this means that the data in both the source and target tables is evaluated and processed multiple times—at least once for each statement. By using the MERGE statement, you can replace the individual DML statements with a single statement. This can improve query performance because the operations are performed within a single statement, minimizing the number of times the data in the source and target tables are processed. However, performance gains depend on having correct indexes, joins, and other considerations in place. For more information, see Optimizing MERGE Statement Performance.

Change Data Capture

In SQL Server 2008, this new feature provides an easy way to capture changes to data in a set of database tables so these changes can be transferred to a second system such as a data warehouse. Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred. Change data capture is available only in the Enterprise, Developer, and Evaluation editions of SQL Server. For more information, see Tuning the Performance of Change Data Capture in SQL Server 2008.

ExecutionLog2

In SQL Server 2008, the Reporting Services database stores additional information that enables you to analyze report execution log data to help answer questions about performance, such as:

Which reports might be good for caching?
How many reports were returned from cache vs. live execution vs. execution snapshot?
What was the most popular report for a specified time period?
What are my worst-performing reports?
Which reports were run by the SQL Server 2008 Reporting Services engine and which reports were run by the SQL Server 2005 Reporting Services engine?
Which reports were under memory pressure?

This detailed performance information appears in the ExecutionLog2 view. For more information, see ExecutionLog2 View – Analyzing and Optimizing Reports.

Conclusion

SQL Server 2008 is a significant release that delivers new functionality not found in previous versions and numerous performance and usability enhancements that are specifically designed for business intelligence solutions. For a full list of features and detailed descriptions, see SQL Server Books Online and the SQL Server Web site.

Top 10 SQL Server 2008 Features for the Database Administrator (DBA)

Mike Weiner — Sat, 31 Jan 2009 00:17:46 GMT

Author: Mike Weiner

Contributor: Burzin Patel,Sanjay Mishra

Reviewers: Lubor Kollar, Kevin Cox, Bill Emmert, Greg Husemeier, Paul Burpo, Joseph Sack, Denny Lee, Lindsey Allen, Mark Souza

Microsoft SQL Server 2008 provides a number of enhancements and new functionality, building on previous versions. Administration, database maintenance, manageability, availability, security, and performance, among others, all fall into the roles and responsibilities of the database administrator. This article provides the top ten new features of SQL Server 2008 (referenced in alphabetical order) that can help DBAs fulfill their responsibilities. In addition to a brief description of each feature, we include how this feature can help and some important use considerations.

Activity Monitor

When troubleshooting a performance issue or monitoring a server in real time, it is common for the DBA to execute a number of scripts or check a number of sources to collect general information about what processes are executing and where the problem may be. SQL Server 2008 Activity Monitor consolidates this information by detailing running and recently executed processes, graphically. The display gives the DBA a high-level view and the ability to drill down on processes and view wait statistics to help understand and resolve problems.

To open up Activity Monitor, just right-click on the registered server name in Object Explorer and then click Activity Monitor, or utilize the standard toolbar icon in SQL Server Management Studio. Activity Monitor provides the DBA with an overview section producing output similar to Windows Task Manager and drilldown components to look at specific processes, resource waits, data file I/Os, and recent expensive queries, as noted in Figure 1.

Figure 1: Display of SQL Server 2008 Activity Monitor view from Management Studio

NOTE: There is a refresh interval setting accessed by right-clicking on the Activity Monitor. Setting this value to a low threshold, under 10 seconds, in a high-volume production system can impact overall system performance.

DBAs can also use Activity Monitor to perform the following tasks:

Pause and resume Activity Monitor with a simple right-click. This can help the DBA to “save” a particular point-in-time for further investigation without it being refreshed or overwritten. However, be careful, because if you manually refresh, expand, or collapse a section, the data will be refreshed.
Right-click a line item to display the full query text or graphical execution plan via Recent Expensive Queries.
Execute a Profiler trace or kill a process from the Processes view. Profiler events include RPC:Completed, SQL:BatchStarting, and SQL:BatchCompleted events, and Audit Login and Audit Logout.

Activity Monitor also provides the ability to monitor activity on any SQL Server 2005 instance, local or remote, registered in SQL Server Management Studio.

[SQL Server] Audit

Having the ability to monitor and log events, such as who is accessing objects, what changes occurred, and what time changes occurred, can help the DBA to meet compliance standards for regulatory or organizational security requirements. Gaining insight into the events occurring within their environment can also help the DBA in creating a risk mitigation plan to keep the environment secure.

Within SQL Server 2008 (Enterprise and Developer editions only), SQL Server Audit provides automation that allows the DBA and others to enable, store, and view audits on various server and database components. The feature allows for auditing at a granularity of the server and/or database level.

There are server-level audit action groups, such as:

FAILED_LOGIN_GROUP, which tracks failed logins.
BACKUP_RESTORE_GROUP, which shows when a database was backed up or restored.
DATABASE_CHANGE_GROUP, which audits when a database is created, altered, or dropped.

Database-level audit action groups include:

DATABASE_OBJECT_ACCESS_GROUP, which is raised whenever a CREATE, ALTER, or DROP statement is executed on database objects.
DATABASE_OBJECT_PERMISSION_CHANGE_GROUP, which is raised when GRANT, REVOKE, or DENY is utilized for database objects.

There are also audit actions, such as SELECT, DELETE, or EXECUTE. For more information, including a full list of the audit groups and actions, see SQL Server Audit Action Groups and Actions.

Audit results can be sent to a file or event log (Windows Security or System) for viewing. Audit information is created utilizing Extended Events, another new SQL Server 2008 feature.

By using SQL Server 2008 audits, the DBA can now answer questions that were previously very difficult to retroactively determine, such as “Who dropped this index?”, “When was the stored procedure modified?”, “What changed which might not be allowing this user to access this table?”, or even “Who ran SELECT or UPDATE statements against the [dbo.Payroll] table?”

For more information about using SQL Server Audit and some examples of implementation, see the SQL Server 2008 Compliance Guide.

Backup Compression

This feature has long been a popular request of DBAs for SQL Server. The wait is finally over, and just in time! Many factors, including increased data retention periods and the need to physically store more data have contributed to the recent explosion in database size. Backing up a large database can require a significant time window to be allotted to backup operations and a large amount of disk space allocated for use by the backup file(s).

With SQL Server 2008 backup compression, the backup file is compressed as it is written out, thereby requiring less storage, less disk I/O, and less time. In lab tests conducted with real customer data, we observed in many cases a reduction in the backup file size between 70% and 85%. Testing also revealed around a 45% reduction in the backup and restore time. It is important to note that the additional processing results in higher processor utilization. To help segregate the CPU intensive backup and minimize its effect on other processes, one might consider utilizing another feature mentioned in this paper, Resource Governor.

The compression is achieved by specifying the WITH COMPRESSION clause in the BACKUP command (for more information, see SQL Server Books Online) or by selecting it in the Options page in the Back Up Database dialog box. To prevent having to modify all existing backup scripts, there is also a global setting to enable compressing all backups taken on a server instance by default. (This setting is accessed by using the Database Settings page of the Server Properties dialog box or by running sp_configure with backup compression default set to 1.) While the compression option on the backup command needs to be explicitly specified, the restore command automatically detects that a backup is compressed and decompresses it during the restore operation.

Backup compression is a very useful feature that can help the DBA save space and time. For more information about tuning backup compression, see the technical note on Tuning the Performance of Backup Compression in SQL Server 2008. NOTE: Creating compressed backups is only supported in SQL Server 2008 Enterprise and Developer editions; however, every SQL Server 2008 edition allows for a compressed backup to be restored.

Central Management Servers

DBAs are frequently responsible for managing not one but many SQL Server instances in their environment. Having the ability to centralize the management and administration of a number of SQL Server instances from a single source can allow the DBA to save significant time and effort. The Central Management Servers implementation, which is accessed via the Registered Servers component in SQL Server Management Studio, allows the DBA to perform a number of administrative tasks on SQL Servers within the environment, from a single management console.

Central Management Servers allow the DBA to register a group of servers and apply functionality to the servers, as a group, such as:

Multiserver query execution: A script can now be executed from one source, across multiple SQL Servers, and be returned to that source, without the need to distinctly log into every server. This can be extremely helpful in cases where data from tables on two or more SQL Servers needs to be viewed or compared without the execution of a distributed query. Also, as long as the syntax is supported in earlier server versions, a query executed from the Query Editor in SQL Server 2008 can run against SQL Server 2005 and SQL Server 2000 instances as well. For more information, see the SQL Server Manageability Team Blog, specifically Multiple Server Query Execution in SQL Server 2008 .
Import and evaluate policies across servers: As part of Policy-Based Management (another new SQL Server 2008 feature discussed in this article), SQL Server 2008 provides the ability to import policy files into particular Central Management Server Groups and allows policies to be evaluated across all of the servers registered in the group
Control Services and bring up SQL Server Configuration Manager: Central Management Servers help provide a central place where DBAs can view service status and even change status for the services, assuming they have the appropriate permissions
Import and export the registered servers: Servers within Central Management Servers can be exported and imported for use between DBAs or different SQL Server Management Studio instance installations. This is an alternative to DBAs importing or exporting into their own local groupings within SQL Server Management Studio.

Be aware that permissions are enforced via Windows authentication, so a user might have different rights and permissions depending on the server registered within the Central Management Server group. For more information, see Administering Multiple Servers Using Central Management Servers and a Kimberly Tripp blog: SQL Server 2008 Central Management Servers-have you seen these?

Data Collector and Management Data Warehouse

Performance tuning and troubleshooting are a time-consuming tasks that can require in-depth SQL Server skills and an understanding of database internals. Windows System monitor (Perfmon), SQL Server Profiler, and dynamic management views (DMVs) helped with some of this, but they were often intrusive, laborious to use, or the dispersed data collection methods were cumbersome to easily summarize and interpret.

To provide actionable performance insight, SQL Server 2008 delivers a fully extensible performance data collection and warehouse tool also known as the data collector. The tool includes several out-of-the-box data collection agents, a centralized data repository for storing performance data called management data warehouse, and several precanned reports to present the captured data. The data collector is a scalable tool that can collect and assimilate data from multiple sources such as dynamic management views , Perfmon, Transact-SQL queries, by using a fully customizable data collection frequency. The data collector can be extended to collect data for any measurable attribute of an application.

Another helpful feature of the management data warehouse is that it can be installed on any SQL Server and then collect data from one or more SQL Server instances within the environment. This can help minimize the performance impact on production systems and improve the scalability in terms of monitoring and collecting data from a number of servers. In lab testing we observed around a 4% reduction in throughput when running the agents and the management data warehouse on a server running at capacity (via an OLTP workload). The impact can vary based on the collection interval (as the test was over an extended workload with 15-minute-pulls into the warehouse), and it can be exacerbated during intervals of data collection. Finally, some capacity should be considered, because the DCExec.exe process will take up some memory and processor resources, and writes to the management data warehouse will increase the I/O workload and space allocation needed where the data and log files are located.

The diagram (Figure 2) below depicts a typical data collector report.

Figure 2: Display of SQL Server 2008 Data Collector Report

This report shows SQL Server processing over the period of time data was collected. Events such as waits, CPU, I/O, memory usage, and expensive query statistics are collected and displayed. A DBA can also drill down into the reports to focus on a particular query or operation to further investigate, detect, and resolve performance problems. This data collection, storage, and reporting can allow the DBA to establish proactive monitoring of the SQL Server(s) in the environment and go back over time to understand and assess changes to performance over the time period monitored. The data collector and management data warehouse feature is supported in all editions (except SQL Server Express) of SQL Server 2008.

Data Compression

The ability to easily manage a database can greatly enhance the opportunity for DBAs to accomplish their regular task lists. As table, index, and file sizes grow and very large databases (VLDBs) become commonplace, the management of data and unwieldy file sizes has become a growing pain point. Also, with more data being queried, the need for large amounts of memory or the necessity to do physical I/O can place a larger burden on DBAs and their organizations. Many times this results in DBAs and organizations securing servers with more memory and/or I/O bandwidth or having to pay a performance penalty.

Data compression, introduced in SQL Server 2008, provides a resolution to help address these problems. Using this feature, a DBA can selectively compress any table, table partition, or index, resulting in a smaller on-disk footprint, smaller memory working-set size, and reduced I/O. The act of compression and decompression will impact CPU; however, this impact is in many cases offset by the gains in I/O savings. Configurations that are bottlenecked on I/O can also see an increase in performance due to compression.

In some lab tests, enabling data compression resulted in a 50-80% saving in disk space. The space savings did vary significantly with minimal savings on data that did not contain many repeating values or where the values required all the bytes allocated by the specified data type. There were also workloads that did not show any gains in performance. However, on data that contained a lot of numeric data and many repeating values, we saw significant space savings and observed performance increases from a few percentage points up to 40-60% on some sample query workloads.

SQL Server 2008 supports two types of compressions: row compression, which compresses the individual columns of a table, and page compression, which compresses data pages using row, prefix, and dictionary compression. The amount of compression achieved is highly dependent on the data types and data contained in the database. In general we have observed that using row compression results in lower overhead on the application throughput but saves less space. Page compression, on the other hand, has a higher impact on application throughput and processor utilization, but it results in much larger space savings. Page compression is a superset of row compression, implying that an object or partition of an object that is compressed using page compression also has row compression applied to it. Also, SQL Server 2008 does support the vardecimal storage format of SQL Server 2005 SP2. However, because this storage format is a subset of row compression, it is a depreciated feature and will be removed from future product versions.

Both row and page compression can be applied to a table or index in an online mode that is without any interruption to the application availability. However, a single partition of a partitioned table cannot be compressed or uncompressed online. In our testing we found that using a hybrid approach, where only the largest few tables were compressed, resulted in the best performance in terms of saving significant disk space while having a minimal negative impact on performance. Because there are disk space requirements, similar to what would be needed to create or rebuild an index, care should be taken in implementing compression as well. We also found that compressing the smallest objects first, from the list of objects you desire to compress, minimized the need for additional disk space during the compression process.

Data compression can be implemented via Transact-SQL or the Data Compression Wizard. To determine how compressing an object will affect its size, you can use the sp_estimate_data_compression_savings system stored procedure or the Data Compression Wizard to calculate the estimated space savings. Database compression is only supported in SQL Server 2008 Enterprise and Developer editions. It is implemented entirely within the database and does not require any application modification.

For more information about using compression, see Creating Compressed Tables and Indexes.

Policy-Based Management

In a number of business scenarios, there is a need to maintain certain configurations or enforce policies either within a specific SQL Server, or many times across a group of SQL Servers. A DBA or organization may require a particular naming convention to be implemented on all user tables or stored procedures that are created, or a required configuration change to be defined across a number of servers in the same manner.

Policy-Based Management (PBM) provides DBAs with a wide variety of options in managing their environment. Policies can be created and checked for compliance. If a target (such as a SQL Server database engine, a database, a table, or an index) is out of compliance, the administrator can automatically reconfigure it to be in compliance. There are also a number of evaluation modes (of which many are automated) that can help the DBA check for policy compliance, log and notify when a policy violation occurs, and even roll back the change to keep in compliance with the policy. For more information about evaluation modes and how they are mapped to facets (a PBM term also discussed in the blog), see the SQL Server Policy-Based Management blog.

The policies can be exported and imported as .xml files for evaluation and implementation across multiple server instances. Also, in SQL Server Management Studio and the Registered Servers view, policies can be evaluated across multiple servers if they are registered under a local server group or a Central Management Server group.

Not all of the functionality of Policy-Based Management can be implemented on earlier versions of SQL Server. However, the policy reporting feature can be utilized on SQL Server 2005 and SQL Server 2000. For more information about administering servers by using Policy-Based Management, see Administering Servers by Using Policy-Based Management in SQL Server Books Online. For more information about the technology itself, including examples, see the SQL Server 2008 Compliance Guide.

Predictable Performance and Concurrency

A significant problem many DBAs face is trying to support SQL Servers with ever-changing workloads, and achieving some level of predictable performance (or minimizing variance in plans and performance). Unexpected query performance, plan changes, and/or general performance issues can come about due to a number of factors, including increased application load running against SQL Server or version upgrades of the database itself. Getting predictable performance from queries or operations run against SQL Server can greatly enhance the DBAs ability to meet and maintain availability, performance, and/or business continuity goals (OLAs or SLAs).

SQL Server 2008 provides a few feature changes that can help provide more predictable performance. In SQL Server 2008, there exist some enhancements to the SQL Server 2005 plan guides (or plan freezing) and a new option to control lock escalation at a table level. Both of these enhancements can provide a more predictable and structured interaction between the application and the database.

First, plan guides:

SQL Server 2005 enabled greater query performance stability and predictability by providing a new feature called plan guides to enable specifying hints for queries that could not be modified directly in the application. For more information, see the Forcing Query Plans white paper. While a very powerful feature, the USE PLAN query hint only supported SELECT DML operations and were often cumbersome to use due to the sensitivity of the plan guides to the formatting.

SQL Server 2008 builds on the plan guides mechanism in two ways: It expands the support for the USE PLAN query hint to cover all DML statements (INSERT, UPDATE, DELETE, MERGE), and it introduces a new plan freezing feature that can be used to directly create a plan guide (freeze) any query plan that exists in the SQL Server plan cache, as in the following example.

sp_create_plan_guide_from_handle

@name = N'MyQueryPlan',

@plan_handle = @plan_handle,

@statement_start_offset = @offset;

A plan guide created by either means has a database scope and is stored in the sys.plan_guides table. Plan guides are only used to influence the query plan selection process of the optimizer and do not eliminate the need for the query to be compiled. A new function, sys.fn_validate_plan_guide, has also been introduced to validate existing SQL Server 2005 plan guides and ensure their compatibility with SQL Server 2008. Plan freezing is available in the SQL Server 2008 Standard, Enterprise, and Developer editions.

Next, lock escalation:

Lock escalation has often caused blocking and sometimes even deadlocking problems, which the DBA is forced to troubleshoot and resolve. Previous versions of SQL Server permitted controlling lock escalation (trace flags 1211 and 1224), but this was only possible at an instance-level granularity. While this helped some applications work-around the problem, it caused severe issues for others. Another problem with the SQL Server 2005 lock escalation algorithm was that locks on partitioned tables were directly escalated to the table level, rather than the partition level.

SQL Server 2008 offers a solution for both of these problems. A new option has been introduced to control lock escalation at a table level. By using an ALTER TABLE command, option locks can be specified to not escalate, or escalate to the partition level for partitioned tables. Both these enhancements help improve the scalability and performance without having negative side-effects on other objects in the instance. Lock escalation is specified at the database-object level and does not require any application change. It is supported in all editions of SQL Server 2008.

Resource Governor

Maintaining a consistent level of service by preventing runaway queries and guaranteeing resources for mission-critical workloads has been a challenge. In the past there was no way of guaranteeing a certain amount of resources to a set of queries and prioritizing the access. All queries had equal access to all the available resources.

SQL Server 2008 introduces a new feature called Resource Governor, which helps address this issue by enabling users to differentiate workloads and allocate resources as they are requested. Resource Governor limits can easily be reconfigured in real time with minimal impact on the workloads that are executing. The allocation of the workload to a resource pool is configurable at the connection level, and the process is completely transparent to the application.

The diagram below depicts the resource allocation process. In this scenario three workload pools (Admin Workload, OLTP Workload, and Report Workload) are configured, and the OLTP Workload pool is assigned a high priority. In parallel, two resource pools (Admin Pool and Application Pool) are configured with specific memory and processor (CPU) limits as shown. As a final step the Admin Workload is assigned to the Admin Pool and the OLTP and Report workloads are assigned to the Application Pool.

Below are some other points you need to consider when using Resource Governor.

Resource Governor relies on login credentials, host name, or application name as a ‘resource pool identifier’, so using a single login for an application, depending on the number of clients per server, might make creating pools more difficult.

Database-level object grouping, in which the resource governing is done based on the database objects being referenced, is not supported.

Resource Governor only allows resource management within a single SQL Server instance. For managing multiple SQL Server instances or processes within a server from a single source, Windows System Resource Manager should be considered.

Only processor and memory resources can be configured. I/O resources cannot be controlled.

Dynamically switching workloads between resource pools once a connection is made is not possible.

Resource Governor is only supported in SQL Server 2008 Enterprise and Developer editions and can only be used for the SQL Server database engine; SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS) cannot be controlled.

Transparent Data Encryption (TDE)

Security is one of the top concerns of many organizations. There are many different layers to securing one of the most important assets of an organization: its data. In most cases, organizations do well at securing their active data via the use of physical security, firewalls, and tightly controlled access policies. However, when physical medium such as the backup tape or disk on which the data resides is compromised, the above security measures are of no use, because a rouge user can simply restore the database and get full access to the data.

SQL Server 2008 offers a solution to this problem by way of transparent data encryption (TDE). TDE performs real-time I/O encryption and decryption of the data and log files by using a database encryption key (DEK). The DEK is a symmetric key secured by using a certificate stored in the master database of the server, or an asymmetric key protected by an Extensible Key Management (EKM) module.

TDE is designed to protect data ‘at rest’, which means the data stored in the .mdf, .ndf, and .ldf files cannot be viewed using a hex editor or other means. However, data that is not at rest, such as the results of a SELECT statement in SQL Server Management Studio, will continue to be visible to users who have rights to view the table. Also, because TDE is implemented at the database level, the database can leverage indexes and keys for query optimization. TDE should not be confused with column-level encryption, which is a separate feature that allows encryption of data even when it is not at rest.

Encrypting a database is a one-time process that can be initiated via a Transact-SQL command or SQL Server Management Studio, and it is executed as a background thread. You can monitor the encryption or decryption status using the sys.dm_database_encryption_keys dynamic management view. In a lab test we conducted, we were able to encrypt a 100 GB database using the AES_128 encryption algorithm in about an hour. While the overhead of using TDE is largely dictated by the application workload, in some of the testing conducted that overhead was measured to be less than 5%. One potential performance impact to be aware of is this: If any database within the instance does have TDE applied, the tempDB system database is also encrypted. Finally, of note when combining features:

When backup compression is used to compress an encrypted database, the size of the compressed backup is larger than if the database were not encrypted, because encrypted data does not compress well.
Encrypting the database does not affect data compression (row or page).

TDE enables organizations to meet the demands of regulatory compliance and overall concern for data privacy. TDE is only supported in the SQL Server 2008 Enterprise and Developer editions and can be enabled without changing existing applications. For more information, see Database Encryption in SQL Server 2008 Enterprise Edition or the SQL Server 2008 Compliance Guide discussion on Using Transparent Data Encryption.

In conclusion, SQL Server 2008 offers features, enhancements, and functionality to help improve the Database Administrator experience. While a Top 10 list was provided above, there are many more features included within SQL Server 2008 that help improve the experience for DBA and other users alike. For a Top 10 feature set for other SQL Server focus areas, see the other SQL Server 2008 Top 10 articles on this site. For a full list of features and detailed descriptions, see SQL Server Books Online and the SQL Server 2008 Overview Web site.

Top 10 SQL Server 2008 Features for ISV Applications

Burzin — Mon, 24 Nov 2008 18:24:00 GMT

Author: Burzin Patel

Contributor: Kun Cheng

Reviewers: Sanjay Mishra, Denny Lee, Mike Ruthruff, Sharon Bjeletich, Mark Souza, Peter Carlin, Hermann Daeubler, Peter Scharlock, Wanda He

Microsoft® SQL Server® 2008 has hundreds of new and improved features, many of which are specifically designed for large scale independent software vendor (ISV) applications, which need to leverage the power of the underlying database while keeping their code database agnostic. This article presents details of the top 10 features that we believe are most applicable to such applications based on our work with strategic ISV partners. Along with the description of each feature, the main pain-points the feature helps resolve and some of the important limitations that need to be considered are also presented. The features are grouped into two categories: ones that do not require any application change (features 1-8) and those that require some application code change (features 9-10). The features are not prioritized in any particular order.

Data Compression

The disk I/O subsystem is the most common bottleneck for many database implementations. More disks are needed to reduce the read/write latencies; but this is expensive, especially on high-performing storage systems. At the same time, the need for storage space continues to increase due to rapid growth of the data, and so does the cost of managing databases (backup, restore, transfer, etc.).

Data compression introduced in SQL Server 2008 provides a resolution to address all these problems. Using this feature one can selectively compress any table, table partition, or index, resulting in a smaller on-disk footprint, smaller memory working-set size, and reduced I/O. Configurations that are bottlenecked on I/O may also see an increase in performance. In our lab test, enabling data compression for some ISV applications resulted in a 50-80% saving in disk space.

SQL Server supports two types of compressions: ROW compression, which compresses the individual columns of a table, and PAGE compression which compresses data pages using row, prefix, and dictionary compression. The compression results are highly dependent on the data types and data contained in the database; however, in general we’ve observed that using ROW compression results in lower overhead on the application throughput but saves less space. PAGE compression, on the other hand, has a higher impact on application throughput and processor utilization, but it results in much larger space savings. PAGE compression is a superset of ROW compression, implying that an object or partition of an object that is compressed using PAGE compression also has ROW compression applied to it. Compressed pages remain compressed in memory until rows/columns on the pages are accessed.

Both ROW and PAGE compression can be applied to a table or index in an online mode that is without any interruption to the application availability. However, partitions of a partitioned table cannot be compressed or uncompressed online. In our testing we found that using a hybrid approach where only the largest few tables were compressed resulted in the best overall performance, saving significant disk space while having a minimal negative impact on performance. We also found that compressing the smallest objects first minimized the need for additional disk space during the compression process.

To determine how compressing an object will affect its size you can use the sp_estimate_data_compression_savings system stored procedure. Database compression is only supported in SQL Server 2008 Enterprise and Developer editions. It is fully controlled at the database level and does not require any application change.

Backup Compression

The amount of data stored in databases has grown significantly in the last decade. resulting in larger database sizes. At the same time the demands for applications to be available 24x7 have forced the backup time-windows to shrink. In order to speed up the backup procedure, database backups are usually first streamed to fast disk-based storage and moved out to slower media later. Keeping such large disk-based backups online is expensive, and moving them around is time consuming.
With SQL Server 2008 backup compression, the backup file is compressed as it is written out, thereby requiring less storage, less disk I/O, and less time, and utilizing less network bandwidth for backups that are written out to a remote server. However, the additional processing results in higher processor utilization. In a lab test conducted with an ISV workload we observed a 40% reduction in the backup file size and a 43% reduction in the backup time.The compression is achieved by specifying the WITH COMPRESSION clause in the backup command (for more information, see SQL Server Books Online). To prevent having to modify all the existing backup scripts, there is also a global setting (using the Database Settings page of the Server Properties dialog box) to enable compression of all backups taken on that server instance by default; this eliminates the need to modify existing backup scripts. While the compression option on the backup command needs to be explicitly specified, the restore command automatically detects that a backup is compressed and decompresses it during the restore operation. Overall, backup compression is a very useful feature that does not require any change to the ISV application. For more information about tuning backup compression, see the technical note on Tuning the Performance of Backup Compression in SQL Server 2008.
Note: Creating compressed backups is only supported in SQL Server 2008 Enterprise and Developer editions; however, every SQL Server 2008 edition can restore a compressed backup.

Transparent Data Encryption

In most cases, organizations do well at securing their active data via the use of firewalls, physical security, and tightly controlled access policies. However, when the physical media such as the backup tape or disk on which the data resides is compromised, the above security measures are of no use, since a rogue user can simply restore the database and get full access to the data.
SQL Server 2008 offers a solution to this problem by way of Transparent Data Encryption (TDE). TDE performs real-time I/O encryption and decryption of the data and log files using a database encryption key (DEK). The DEK is a symmetric key secured by using a certificate stored in the master database of the server, or an asymmetric key protected by an Extensible Key Management (EKM) module.TDE is designed to protect data ‘at rest’; this means that the data stored in the .mdf, .ndf, and .ldf files cannot be viewed using a hex editor or some other such means. However, data that is not at rest, such as the results of a select statement in SQL Server Management Studio, continues to be visible to users who have rights to view the table. TDE should not be confused with column-level encryption, which is a separate feature that allows encryption of data even when it is not at rest.Encrypting a database is a one-time process that can be initiated via a Transact-SQL command and is executed as a background thread. You can monitor the encryption/decryption status using the sys.dm_database_encryption_keys dynamic management view (DMV).
In a lab test we conducted we were able to encrypt a 100-gigabyte (GB) database using the AES_128 encryption algorithm in about one hour. While the overheads of using TDE are largely dictated by the application workload, in some of the testing we conducted, the overhead was measured to be less than 5%.

One point worth mentioning is when backup compression is used to compress an encrypted database, the size of the compressed backup is larger than if the database were not encrypted; this is because encrypted data does not compress well.

TDE enables organizations to meet the demands of regulatory compliance and overall concern for data privacy.

TDE is only supported in the SQL Server 2008 Enterprise and Developer editions, and it can be enabled without changing an existing application.

Data Collector and Management Data Warehouse

Performance tuning and troubleshooting is a time-consuming task that usually requires deep SQL Server skills and an understanding of the database internals. Windows® System monitor (Perfmon), SQL Server Profiler, and dynamic management views helped with some of this, but they were often too intrusive or laborious to use, or the data was too difficult to interpret.

To provide actionable performance insights, SQL Server 2008 delivers a fully extensible performance data collection and warehouse tool also known as the Data Collector. The tool includes several out-of-the-box data collection agents, a centralized data repository for storing performance data called management data warehouse (MDW), and several precanned reports to present the captured data. The Data Collector is a scalable tool that can collect and assimilate data from multiple sources such as dynamic management views, Perfmon, Transact-SQL queries, etc., using a fully customizable data collection and assimilation frequency. The Data Collector can be extended to collect data for any measurable attribute of an application. For example, in our lab test we wrote a custom Data Collector agent job (40 lines of code) to measure the processing throughput of the workload.

The diagram below depicts a typical Data Collector report.

The Performance data collection and warehouse feature is supported in all editions of SQL Server 2008.

Lock Escalation

Lock escalation has often caused blocking and sometimes even deadlocking problems for many ISV applications. Previous versions of SQL Server permitted controlling lock escalation (trace flags 1211 and 1224), but this was only possible at an instance-level granularity. While this helped some applications work around the problem, it caused severe issues for others. Another problem with the SQL Server 2005 lock escalation algorithm was that locks on partitioned tables were directly escalated to the table level, rather than the partition level.

SQL Server 2008 offers a solution for both these issues. A new option has been introduced to control lock escalation at a table level. If an ALTER TABLE command option is used, locks can be specified to not escalate, or to escalate to the partition level for partitioned tables. Both these enhancements help improve the scalability and performance without having negative side-effects on other objects in the instance. Lock escalation is specified at the database-object level and does not require any application change. It is supported in all editions of SQL Server 2008.

Plan Freezing

SQL Server 2005 enabled greater query performance stability and predictability by providing a new feature called plan guides to enable specifying hints for queries that could not be modified directly in the application (for more information, see the white paper Forcing Query Plans). While a very powerful feature, plan guides were often cumbersome to use due to the sensitivity of the plan guides to the formatting, and only supported SELECT DML operations when used in conjunction the USE PLAN query hint.

SQL Server 2008 builds on the plan guides mechanism in two ways: it expands the support for plan guides to cover all DML statements (INSERT, UPDATE, DELETE, MERGE), and introduces a new feature, Plan Freezing, that can be used to directly create a plan guide (freeze) for any query plan that exists in the SQL Server plan cache, for example:

sp_create_plan_guide_from_handle

@name = N'MyQueryPlan',

@plan_handle = @plan_handle,

@statement_start_offset = @offset;

A plan guide created by either means have a database scope and are stored in the sys.plan_guides table. Plan guides are only used to influence the query plan selection process of the optimizer and do not eliminate the need for the query to be compiled. A new function sys.fn_validate_plan_guide has also been introduced to validate existing SQL Server 2005 plan guides and ensure their compatibility with SQL Server 2008. Plan freezing is available in the SQL Server 2008 Standard, Enterprise, and Developer editions.

Optimize for Ad hoc Workloads Option

Applications that execute many single use ad hoc batches (e.g., nonparameterized workloads) can cause the plan cache to grow excessively large and result in reduced efficiency. SQL Server 2005 offered the Parameterization Forced database option to address such scenarios, but that sometimes resulted in adverse side-effects on workloads that had a large skew in the data and had queries that were very sensitive to the underlying data.

SQL Server 2008 introduces a new option, optimize for ad hoc workloads, which is used to improve the efficiency of the plan cache. When this option is set to 1, the SQL Server engine stores a small stub for the compiled ad hoc plan in the plan cache instead of the entire compiled plan, when a batch is compiled for the first time. The compiled plan stub is used to identify that the ad hoc batch has been compiled before but has only stored a compiled plan stub, so that when this batch is invoked again the database engine compiles the batch, removes the compiled plan stub from the plan cache, and replaces it with the full compiled plan.

This mechanism helps to relieve memory pressure by not allowing the plan cache to become filled with large compiled plans that are not reused. Unlike the Forced Parameterization option, optimizing for ad hoc workloads does not parameterize the query plan and therefore does not result in saving any processor cycles by way of eliminating compilations. This option does not require any application change and is available in all editions of SQL Server 2008.

Resource Governor

Maintaining a consistent level of service by preventing runaway queries and guaranteeing resources for mission-critical workloads has been a challenge for SQL Server. In the past there was no way of guaranteeing a certain amount of resources to a set of queries and prioritizing the access; all queries had equal access to all the available resources.

SQL Server 2008 introduces a new feature, Resource Governor, which helps address this issue by enabling users to differentiate workloads and allocate resources as they are requested. The Resource Governor limits can easily be reconfigured in real time with minimal impact on the workloads that are executing. The allocation of the workload to a resource pool is configurable at the connection level, and the process is completely transparent to the application.

The diagram below depicts the resource allocation process. In this scenario three workload pools (Admin workload, OLTP workload, and Report workload) are configured, and the OLTP workload pool is assigned a high priority. In parallel two resource pools (Admin pool and Application pool) are configured with specific memory and processor (CPU) limits as shown. As final steps, the Admin workload is assigned to the Admin pool, and the OLTP and Report workloads are assigned to the Application pool.

Below are some other points you need to consider when using resource governor:

Since Resource Governor relies on login credentials, host name, or application name as a resource pool identifier, most ISV applications that use a single login to connect multiple application users to SQL Server will not be able to use Resource Governor without reworking the application. This rework would require the application to utilize one of the resource identifiers from within the application to help differentiate the workload.

Database-level object grouping, in which the resource governing is done based on the database objects being referenced, is not supported.

Resource Governor only allows resource management within a single SQL Server instance. For multiple instances. Windows System Resource Manager should be considered.

Only processor and memory resources can be configured. I/O resource cannot be controlled.

Dynamically switching workloads between resource pools once a connection is made is not possible.

Resource Governor is only supported in SQL Server 2008 Enterprise and Developer editions and can only be used for the SQL Server database engine; SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS) cannot be controlled.

Table-Valued Parameters

Often one of the biggest problems ISVs encountered while developing applications on earlier versions of SQL Server was the lack of an easy way to execute a set of UPDATE, DELETE, INSERT operations from a client as a single batch on the server. Executing the set of statements as singleton operations resulted in a round trip from the client to the server for each operation and could result in as much as a 3x slowdown in performance.

SQL Server 2008 introduces the table-valued parameter (TVP) feature, which helps resolve this problem. Using the new TVP data type, a client application can pass a potentially unlimited sized array of data directly from the client to the server in a single-batch operation. TVPs are first-class data types and are fully supported by the SQL Server tools and SQL Server 2008 client libraries (SNAC 10 or later). TVPs are read-only, implying that they can only be used to pass array-type data into SQL Server; they cannot be used to return array-type data.

The graph below plots the performance of executing a batch of insert statements using a parameter array (sequence of singleton operations) vs. executing the same batch using a TVP. For batches of 10 statements or less, parameter arrays perform better than TVPs. This is due to the one-time overhead associated with initiating the TVP, which outweighs the benefits of transferring and executing the inserts as a single batch on the server.

However, for batches larger than 10 statements, TVPs outperform parameter arrays, because the entire batch is transferred to the server and executed as a single operation. As can be seen in the graph for a batch of 250 inserts the amount of time taken to execute the batch is 2.5 times more when the operations are performed using a parameter array versus a TVP. The performance benefits scale almost linearly and when the size of the batch increases to 2,000 insert statements, executing the batch using a parameter array takes more than four times longer than using a TVP.

TVPs can also be used to perform other functions such as passing a large batch of parameters to a stored procedure. TVPs are supported in all editions of SQL Server 2008 and require the application to be modified.

Filestream

In recent years there has been an increase in the amount of unstructured data (e-mail messages, documents, images, videos, etc.) created. This unstructured data is often stored outside the database, separate from its structured metadata. This separation can cause challenges and complexities in keeping the data consistent, managing the data, and performing backup/restores.

The new Filestream data type in SQL Server 2008 allows large unstructured data to be stored as files on the file system. Transact-SQL statements can be used to read, insert, update and manage the Filestream data, while Win32® file system interfaces can be used to provide streaming access to the data. Using the NTFS streaming APIs allows efficient performance of common file operations while providing all of the rich database services, including security and backup. In our lab tests we observed the biggest performance advantage of streaming access when the size of binary large objects (BLOBs) was greater than 256 kilobytes (KB). The Filestream feature is initially targeted to objects that do not need to be updated in place, as that is not yet supported.

Filestream is not automatically enabled when you install or upgrade SQL Server 2008. You need to enable it by using SQL Server Configuration Manager and SQL Server Management Studio. Filestream requires a special dedicated filegroup to be created to store the Filestream (varbinary(max)) data that has been qualified with the Filestream attribute. This filegroup points to an NTFS directory on a file system and is created similar to all the other filegroups. The Filestream feature is supported in all editions of SQL Server 2008, and it requires the application to be modified to leverage the Win32 APIs (if required) and to migrate the existing varbinary data.

SQL Server 2008 is a significant release that delivers many new features and key improvements, many of which have been designed specifically for ISV workloads and require zero or minimal application change.

This article presented an overview of only the top-10 features that are most applicable to ISV applications and help resolve key ISV problems that couldn’t easily be addressed in the past. For more information, including a full list of features and detailed descriptions, see SQL Server Books Online and the SQL Server web site.

Six Failover Clustering Benefits Realized from Migrating to SQL Server 2008

Lindsey.allen — Thu, 20 Nov 2008 23:45:19 GMT

Author: Joseph Sack

Technical Reviewer: Burzin Patel, Juergen Thomas, Lindsey Allen, Justin Erickson

SQL Server 2008 failover clustering introduces several supportability, reliability, and availability improvements. The following list details the more significant and immediate benefits of making the move to SQL Server 2008 Failover Clustering.

Reliable Setup

The installation process for SQL Server 2008 Failover Clusters has changed significantly. Essentially, you have two options for installation, integrated install or advanced/enterprise install. Integrated install involves the installation of a single-node SQL Server 2008 failover cluster instance. If you want the instance to be able to failover to other nodes, you follow a separate “add node” install for each node.

The advanced/enterprise install differs from the integrated install in that you prepare each node with SQL binaries and services, and then select the active node that owns the SQL Server shared disk, and then bring the SQL Server instance online in a separate step. The advanced/enterprise install is intended for third party enterprise deployment solutions (yet to hit the market), or adding the ability to prepare each node prior to configuring the Windows Cluster. You may decide to use the advance option if you prefer, but overall the integrated install option will provide less steps and will allow you to make the SQL Server instance available sooner.

From a “number of steps” perspective, the integrated install option requires less effort. For example, a two node cluster integrated install would require one “Install” step on the first node, and then an “Add node” step on the second node. An advanced/enterprise install would require a “prepare” operation on each node (two steps), followed with a third step, “completing” the SQL Server instance and bringing it online.

At first blush, this seems like more work for the DBA, so where is the benefit to this new process? Unlike with SQL Server 2005 failover clusters, SQL Server 2008 Failover Cluster installs do not involve remote-node operations. This translates to more discrete install steps on your part, but helps reduce several installation and patching problems that occur due to remote node permission issues, remote offline services, terminal services connections, and other communication issues that can leave you with a partial or failed installation. By moving to a SQL Server 2008 Failover Cluster, the reliability of your install will increase significantly by eliminating the several remote-node variables that once hampered a solid install of a SQL Server failover cluster.

Improved availability with rolling upgrades

Prior to SQL Server 2008, installing a service pack or cumulative update could require a several-minute outage for the SQL Server instance. This is due to the fact that in order to update a SQL Server instance to the latest Service Pack or Cumulative update, SQL Server services were stopped until the upgrade was completed. With SQL Server 2008 failover clustering, your outage period can be significantly reduced if you follow the proper “rolling update” process. Specifically, you can avoid prolonged outages of a SQL Server instance by applying Service Packs or Cumulative Updates against the passive nodes of a failover cluster. After applying the patches to the passive nodes, you can then failover the SQL Server instance to a newly upgraded node. Upon failover, the SQL Server instance is then upgraded. You can then proceed with updating the formerly active (now passive) nodes.

In my own testing of a two-node cluster hosting a single SQL Server 2008 failover cluster instance, I started off my patching process by installing a Cumulative Update on the passive node of the cluster. While this Cumulative Update was installed, the SQL Server instance remained up. After applying the cumulative update, I failed over the instance of SQL Server 2008 to the newly upgraded node, and then applied the cumulative update to the new passive node. The total down time for the upgrade was 15 seconds, which was the amount of time it took to fail over the SQL Server instance to the newly upgraded node.

Availability with adding or removing nodes

As with SQL Server 2005, adding a new node for a SQL Server failover cluster instance or removing a node does not require an outage of the SQL Server instance. Like all cluster setup actions, AddNode needs to be run on the node to be added, as opposed to on the active node for 2005. This results in increased reliability since 2008 AddNode does not rely on remote task scheduling and execution. The only user inputs to the 2008 AddNode are: instance selection, service account passwords on the UI (service account names and passwords on the command line), Error and Usage Reporting options. All feature selection is retrieved from the existing instance where the current node is being added.

Also, in my own testing when adding a new node to a SQL Server failover cluster, I received the following notification during install:

“The current node TX147913-3 is at patch level [10.0.1600.22], which is lower than that of active node TX147913-2: patch level [10.0.1763.0]. After completing setup, you must download and apply the latest SQL Server 2008 service pack and/or patch and bring all nodes to the same version and patch level.”

This helpful warning let me know that I needed to update the newly added SQL Server failover cluster to match the existing and already-upgraded SQL Server failover cluster node. Patching the newly added passive node did not require a restart of the SQL Server 2008 failover cluster.

Service SIDs instead of Domain Groups on Windows Server 2008

A pain point for many DBAs was the introduced requirement in SQL Server 2005 Failover Clustering for using domain groups for SQL Server services. These domain groups were used to manage the permissions of the SQL Server service accounts; however they required that each domain group already contained the service accounts as members prior to install. Changing the domain group for a clustered service, although possible, was not a trivial procedure (see KB 915846, “Best practices that you can use to set up domain groups and solutions to problems that may occur when you set up a domain group when you install a SQL Server 2005 failover cluster”).

If you are creating a new SQL Server 2008 failover cluster on Windows Server 2008, you can now bypass the use of domain groups by designating Service SIDs during the install. Service SID functionality was introduced in Windows Vista and Windows Server 2008, and allows the provisioning of ACLs to server resources and permissions directly to a Windows service. On the "Cluster Security Policy" dialog during install of a SQL Server failover cluster, you still have the option to use domain groups, however selecting "Use service SIDS" is the recommended choice for SQL Server 2008 on Windows Server 2008 and allows you to bypass provisioning of domain groups and associated service account membership additions prior to installation.

Windows server 2008 Integration improvements

In addition to Service SIDs, running SQL Server 2008 on Windows Server 2008 provides other significant benefits. For example, Windows Server 2008 clustering removes the requirement for having all hardware in a cluster solution be listed in the Hardware Compatibility List (HCL). Finding and validating your exact cluster solution in the HCL was often a difficult task. For Windows Server 2008, you no longer need to validate your exact solution in the HCL. Instead, your Windows Server 2008 logo cluster solution must pass validation using the Windows Server 2008 Cluster Validation Tool. Prior to configuration of your cluster, you can use this tool to scan the server nodes and storage you plan on using for your cluster solution. The tool checks for any issues that may impact support of a Failover Cluster. Any blocking issues across the hardware, network components and configurations, storage resources, and Operating System configurations will be identified in a final report and will allow you to address issues prior to deployment.

Windows Server 2008 Failover Clustering also added new quorum options, moving from a single-point-of-failure to a consensus-based quorum model. Windows Server 2008 Failover Clustering also offers iSCSI disk support, up to 16-node clusters, and ipv6 internet layer protocol support.

ConfigurationFile.ini automatic generation

SQL Server 2008 Failover cluster allows for the use of a configuration file used in conjunction with a command line setup. For example – the following command line execution initiates an integrated install of a single-node failover cluster, referencing a configuration file with the required command line options:

Setup.exe /q /ACTION=InstallFailoverCluster /Configurationfile="C:\temp\ConfigurationFile.ini"

What’s more, performing a non-command line install of SQL Server 2008 automatically generates a ConfigurationFile.ini which is saved to the following directory:

<drive letter>:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMMSS\ConfigurationFile.ini.

Please note that as of this writing, ConfigurationFile.ini does not automatically include the FAILOVERCLUSTERIPADDRESSES setup option – however this is easy to add manually. For example:

FAILOVERCLUSTERIPADDRESSES="IPv4;172.29.10.160;Cluster Network 1;255.255.248.0"

Using command-line setup in conjunction with a configuration file can help streamline your SQL Server 2008 failover cluster installation process, particularly for large enterprise environments.

Top 10 SQL Server Integration Services Best Practices

denny.lee — Wed, 01 Oct 2008 07:32:00 GMT

Authors: Thomas Kejser, Denny Lee
Contributors: Runying Mao
Technical Reviewers: Burzin Patel, Kevin Cox, Peter Carlin, Mark Souza, Richard Tkachuk, Len Wyatt, Lindsey Allen, Prem Mehra, Nicholas Dritsas, Carl Rabeler, Kun Cheng, Stuart Ozer, Donald Farmer

Published: 10/1/2008
Updated: 12/12/2008

How many of you have heard the myth that Microsoft® SQL Server® Integration Services (SSIS) does not scale? The first question we would ask in return is: “Does your system need to scale beyond 4.5 million sales transaction rows per second?” SQL Server Integration Services is a high performance Extract-Transform-Load (ETL) platform that scales to the most extreme environments. And as documented in SSIS ETL world record performance, SQL Server Integration Services can process at the scale of 4.5 million sales transaction rows per second.

	SSIS is an in-memory pipeline, so ensure that all transformations occur in memory. The purpose of having Integration Services within SQL Server features is to provide a flexible, robust pipeline that can efficiently perform row-by-row calculations and parse data all in memory. While the extract and load phases of the pipeline will touch disk (read and write respectively), the transformation itself should process in memory. If transformations spill to disk (for example with large sort operations), you will see a big performance degradation. Construct your packages to partition and filter data so that all transformations fit in memory. A great way to check if your packages are staying within memory is to review the SSIS performance counter Buffers spooled, which has an initial value of 0; above 0 is an indication that the engine has started swapping to disk. For more information, please refer to Something about SSIS Performance Counters.
	Plan for capacity by understanding resource utilization. SQL Server Integration Services is designed to process large amounts of data row by row in memory with high speed. Because of this, it is important to understand resource utilization, i.e., the CPU, memory, I/O, and network utilization of your packages. CPU Bound Seek to understand how much CPU is being used by Integration Services and how much CPU is being used overall by SQL Server while Integration Services is running. This latter point is especially important if you have SQL Server and SSIS on the same box, because if there is a resource contention between these two, it is SQL Server that will typically win – resulting in disk spilling from Integration Services, which slows transformation speed. The perfmon counter that is of primary interest to you is Process / % Processor Time (Total). Measure this counter for both sqlservr.exe and dtexec.exe. If SSIS is not able to drive close to 100% CPU load, this may be indicative of: Application contention: For example, SQL Server is taking on more processor resources, making them unavailable to SSIS. Hardware contention: A common scenario is that you have suboptimal disk I/O or not enough memory to handle the amount of data being processed. Design limitation: The design of your SSIS package is not making use of parallelism, and/or the package uses too many single-threaded tasks. Network Bound SSIS moves data as fast as your network is able to handle it. Because of this, it is important to understand your network topology and ensure that the path between your source and target have both low latency and high throughput. The following Network perfmon counters can help you tune your topology: Network Interface / Current Bandwidth: This counter provides an estimate of current bandwidth. Network Interface / Bytes Total / sec: The rate at which bytes are sent and received over each network adapter. Network Interface / Transfers/sec: Tells how many network transfers per second are occurring. If it is approaching 40,000 IOPs, then get another NIC card and use teaming between the NIC cards. These counters enable you to analyze how close you are to the maximum bandwidth of the system. Understanding this will allow you to plan capacity appropriately whether by using gigabit network adapters, increasing the number of NIC cards per server, or creating separate network addresses specifically for ETL traffic. I/O Bound If you ensure that Integration Services is minimally writing to disk, SSIS will only hit the disk when it reads from the source and writes to the target. But if your I/O is slow, reading and especially writing can create a bottleneck. Because tuning I/O is outside the scope of this technical note, please refer to Predeployment I/O Best Practices. Remember that an I/O system is not only specified by its size ( “I need 10 TB”) – but also by its sustainable speed (“I want 20,000 IOPs”). Memory bound A very important question that you need to answer when using Integration Services is: “How much memory does my package use?” The key counters for Integration Services and SQL Server are: Process / Private Bytes (DTEXEC.exe) – The amount of memory currently in use by Integration Services. This memory cannot be shared with other processes. Process / Working Set (DTEXEC.exe) – The total amount of allocated memory by Integration Services. SQL Server: Memory Manager / Total Server Memory: The total amount of memory allocated by SQL Server. Because SQL Server has another way to allocate memory using the AWE API, this counter is the best indicator of total memory used by SQL Server. To understand SQL Server memory allocations better, refer to Slava Ok’s Weblog. Memory / Page Reads / sec – Represents to total memory pressure on the system. If this consistently goes above 500, the system is under memory pressure.
	Baseline source system extract speed. Understand your source system and how fast you extract from it. After all, Integration Services cannot be tuned beyond the speed of your source – i.e., you cannot transform data faster than you can read it. Measure the speed of the source system by creating a very simple package reading data from your source with the a destination of “Row Count”: Execute the package from the command line (DTEXEC) and measure the time it took for it to complete its task. Use the Integration Services log output to get an accurate calculation of the time. You want to calculate rows per second: Rows / sec = Row Count / Time_{Data Flow} Based on this value, you now know the maximum number of rows per second you can read from the source – this is also the roof on how fast you can transform your data. To increase this Rows / sec calculation, you can do the following: Improve drivers and driver configurations: Make sure you are using the most up-to-date driver configurations for your network, data source, and disk I/O. Often the default network drivers on your server are not configured optimally for the network stack, which results in performance degradations when there are a high number of throughput requests. Note that for 64-bit systems, at design time you may be loading 32-bit drivers; ensure that at run time you are using 64-bit drivers. Start multiple connections: To overcome limitations of drivers, you can try to start multiple connections to your data source. As long as the source can handle many concurrent connections, you may see an increase in throughput if you start several extracts at once. If concurrency is causing locking or blocking issues, consider partitioning the source and having your packages read from different partitions to more evenly distribute the load. Use multiple NIC cards: If the network is your bottleneck and you’ve already ensured that you’re using gigabit network cards and routers, then a potential solution is to use multiple NIC cards per server. Note that you will have to be careful when you configure multiple NIC environments; otherwise you will have network conflicts.
	Optimize the SQL data source, lookup transformations, and destination. When you execute SQL statements within Integration Services (as noted in the above Data access mode dialog box), whether to read a source, to perform a look transformation, or to change tables, some standard optimizations significantly help performance: Use the NOLOCK or TABLOCK hints to remove locking overhead. To optimize memory usage, SELECT only the columns you actually need. If you SELECT all columns from a table (e.g., SELECT * FROM) you will needlessly use memory and bandwidth to store and retrieve columns that do not get used. . If possible, perform your datetime conversions at your source or target databases, as it is more expensive to perform within Integration Services.. In SQL Server 2008 Integration Services, there is a new feature of the shared lookup cache. When using parallel pipelines (see points #8 and #10 below), it provides a high-speed, shared cache. . If Integration Services and SQL Server run on the same server, use the SQL Server destination instead of the OLE DB destination to improve performance.. Commit size 0 is fastest on heap bulk targets, because only one transaction is committed. If you cannot use 0, use the highest possible value of commit size to reduce the overhead of multiple-batch writing. Commit size = 0 is a bad idea if inserting into a Btree – because all incoming rows must be sorted at once into the target Btree—and if your memory is limited, you are likely to spill. Batchsize = 0 is ideal for inserting into a heap. For an indexed destination, I recommend testing between 100,000 and 1,000,000 as batch size. Use a commit size of <5000 to avoid lock escalation when inserting; note that in SQL Server 2008 you can now enable/disable lock escalation at the object level, but use this wisely. Heap inserts are typically faster than using a clustered index. This means that you may want to drop indexes and rebuild if you are changing a large part of the destination table; you will want to test your inserts both by keeping indexes in place and by dropping all indexes and rebuilding to validate.. Use partitions and partition SWITCH command; i.e., load a work table that contains a single partition and SWITCH it in to the main table after you build the indexes and put the constraints on.. Another great reference from the SQL Performance team is Getting Optimal Performance with Integration Services Lookups.
	Tune your network. A key network property is the packet size of your connection. By default this value is set to 4,096 bytes. This means a new network package must be assemble for every 4 KB of data. As noted in SqlConnection.PacketSize Property in the .NET Framework Class Library, increasing the packet size will improve performance because fewer network read and write operations are required to transfer a large data set. If your system is transactional in nature, with many small data size read/writes, lowering the value will improve performance. Since Integration Services is all about moving large amounts of data, you want to minimize the network overhead. This means that the value 32K (32767) is the fastest option. While it is possible to configure the network packet size on a server level using sp_configure, you should not do this. The database administrator may have reasons to use a different server setting than 32K. Instead, override the server settings in the connection manager as illustrated below. Another network tuning technique is to use network affinity at the operating system level. At high throughputs, you can sometimes improve performance this way. For the network itself, you may want to work with your network specialists to enable jumbo frames to increase the default payload of 1,500 bytes to 9,000 bytes. By enabling jumbo frames, you will further decrease the amount of network operation required to move large data sets.
	Use data types – yes, back to data types! –wisely. Of all the points on this top 10 list, this is perhaps the most obvious. Yet, it is such an important point that it needs to be made separately. Follow these guidelines: Make data types as narrow as possible so you will allocate less memory for your transformation. Do not perform excessive casting of data types – it will only degrade performance. Match your data types to the source or destination and explicitly specify the necessary data type casting.. Watch precision issues when using the money, float, and decimal types. Also, be aware the money is faster than decimal, and money has fewer precision considerations than float.
	Change the design. There are some things that Integration Services does well – and other tasks where using another tool is more efficient. Your tool choice should be based on what is most efficient and on a true understanding of the problem. To help with that choice, consider the following points: Do not sort within Integration Services unless it is absolutely necessary. In order to perform a sort, Integration Services allocates the memory space of the entire data set that needs to be transformed. If possible, presort the data before it goes into the pipeline. If you must sort data, try your best to sort only small data sets in the pipeline. Instead of using Integration Services for sorting, use an SQL statement with ORDER BY to sort large data sets in the database – mark the output as sorted by changing the Integration Services pipeline metadata on the data source. There are times where using Transact-SQL will be faster than processing the data in SSIS. As a general rule, any and all set-based operations will perform faster in Transact-SQL because the problem can be transformed into a relational (domain and tuple) algebra formulation that SQL Server is optimized to resolve. Also, the SQL Server optimizer will automatically apply high parallelism and memory management to the set-based operation – an operation you may have to perform yourself if you are using Integration Services. Typical set-based operations include: Set-based UPDATE statements - which are far more efficient than row-by-row OLE DB calls. Aggregation calculations such as GROUP BY and SUM. These are typically also calculated faster using Transact-SQL instead of in-memory calculations by a pipeline. Delta detection is the technique where you change existing rows in the target table instead of reloading the table. To perform delta detection, you can use a change detection mechanism such as the new SQL Server 2008 Change Data Capture (CDC) functionality. If such functionality is not available, you need to do the delta detection by comparing the source input with the target table. This can be a very costly operation requiring the maintenance of special indexes and checksums just for this purpose. Often, it is fastest to just reload the target table. A rule of thumb is that if the target table has changed by >10%, it is often faster to simply reload than to perform the logic of delta detection.
	Partition the problem. One of the main tenets of scalable computing is to partition problems into smaller, more manageable chunks. This allows you to more easily handle the size of the problem and make use of running parallel processes in order to solve the problem faster. For ETL designs, you will want to partition your source data into smaller chunks of equal size. This latter point is important because if you have chunks of different sizes, you will end up waiting for one process to complete its task. For example, looking at the graph below, you will notice that for the four processes executed on partitions of equal size, the four processes will finish processing January 2008 at the same time and then together continue to process February 2008. But for the partitions of different sizes, the first three processes will finish processing but wait for the fourth process, which is taking a much longer time. The total run time will be dominated by the largest chunk. To create ranges of equal-sized partitions, use time period and/or dimensions (such as geography) as your mechanism to partition. If your primary key is an incremental value such as an IDENTITY or another increasing value, you can use a modulo function. If you do not have any good partition columns, create a hash of the value of the rows and partition based on the hash value. For more information on hashing and partitioning, refer to the Analysis Services Distinct Count Optimization white paper; while the paper is about distinct count within Analysis Services, the technique of hash partitioning is treated in depth too. Some other partitioning tips: Use partitioning on your target table. This way you will be able to run multiple versions of the same package, in parallel, that insert data into different partitions of the same table. When using partitioning, the SWITCH statement is your friend. It not only increases parallel load speeds, but also allows you to efficiently transfer data. Please refer to the SQL Server Books Online article Transferring Data Efficiently by Using Partition Switching for more information. As implied above, you should design your package to take a parameter specifying which partition it should work on. This way, you can have multiple executions of the same package, all with different parameter and partition values, so you can take advantage of parallelism to complete the task faster. From the command line, you can run multiple executions by using the “START” command. A quick code example of running multiple robocopy statements in parallel can be found within the Sample Robocopy Script to custom synchronize Analysis Services databases technical note.
	Minimize logged operations. When you insert data into your target SQL Server database, use minimally logged operations if possible. When data is inserted into the database in fully logged mode, the log will grow quickly because each row entering the table also goes into the log. Therefore, when designing Integration Services packages, consider the following: Try to perform your data flows in bulk mode instead of row by row. By doing this in bulk mode, you will minimize the number of entries that are added to the log file. This reduction will improve the underlying disk I/O for other inserts and will minimize the bottleneck created by writing to the log. If you need to perform delete operations, organize your data in a way so that you can TRUNCATE the table instead of running a DELETE. The latter will place an entry for each row deleted into the log. But the former will simply remove all of the data in the table with a small log entry representing the fact that the TRUNCATE occurred. In contrast with popular belief, a TRUNCATE statement can participate in a transaction. Use the SWITCH statement and partitioning. If partitions need to be moved around, you can use the SWITCH statement (to switch in a new partition or switch out the oldest partition), which is a minimally logged statement. Be careful when using DML statements; if you mix in DML statements within your INSERT statements, minimum logging is suppressed.
	Schedule and distribute it correctly. After your problem has been chunked into manageable sizes, you must consider where and when these chunks should be executed. The goal is to avoid one long running task dominating the total time of the ETL flow. A good way to handle execution is to create a priority queue for your package and then execute multiple instances of the same package (with different partition parameter values). The queue can simply be a SQL Server table. Each package should include a simple loop in the control flow: Pick a relevant chunk from the queue: “Relevant” means that is has not already been processed and that all chunks it depends on have already run. If no item is returned from the queue, exit the package. Perform the work required on the chunk. Mark the chunk as “done” in the queue. Return to the start of loop. Picking an item from the queue and marking it as “done” (step 1 and 3 above) can be implemented as stored procedure, for example. The queue acts as a central control and coordination mechanism, determining the order of execution and ensuring that no two packages work on the same chunk of data. Once you have the queue in place, you can simply start multiple copies of DTEXEC to increase parallelism.

Top 10 Best Practices for Building a Large Scale Relational Data Warehouse

SanjayMishra — Wed, 06 Feb 2008 23:53:00 GMT

Contributors: Stuart Ozer, with Prem Mehra and Kevin Cox

Technical Reviewers: Lubor Kollar, Thomas Kejser, Denny Lee, Jimmy May, Michael Redman, Sanjay Mishra

Building a large scale relational data warehouse is a complex task. This article describes some design techniques that can help in architecting an efficient large scale relational data warehouse with SQL Server. Most large scale data warehouses use table and index partitioning, and therefore, many of the recommendations here involve partitioning. Most of these tips are based on experiences building large data warehouses on SQL Server 2005.

Consider partitioning large fact tables

Consider partitioning fact tables that are 50 to 100GB or larger.
Partitioning can provide manageability and often performance benefits.

Faster, more granular index maintenance.
More flexible backup / restore options.
Faster data loading and deleting

Faster queries when restricted to a single partition..
Typically partition the fact table on the date key.

Enables sliding window.

Enables partition elimination.

Build clustered index on the date key of the fact table

This supports efficient queries to populate cubes or retrieve a historical data slice.
If you load data in a batch window then use the options ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCKS = OFF for the clustered index on the fact table. This helps speed up table scan operations during query time and helps avoid excessive locking activity during large updates.
Build nonclustered indexes for each foreign key. This helps ‘pinpoint queries' to extract rows based on a selective dimension predicate.Use filegroups for administration requirements such as backup / restore, partial database availability, etc.

Choose partition grain carefully

Most customers use month, quarter, or year.
For efficient deletes, you must delete one full partition at a time.
It is faster to load a complete partition at a time.

Daily partitions for daily loads may be an attractive option.
However, keep in mind that a table can have a maximum of 1000 partitions.

Partition grain affects query parallelism.

For SQL Server 2005:

Queries touching a single partition can parallelize up to MAXDOP (maximum degree of parallelism).
Queries touching multiple partitions use one thread per partition up to MAXDOP.

For SQL Server 2008:

Parallel threads up to MAXDOP are distributed proportionally to scan partitions, and multiple threads per partition may be used even when several partitions must be scanned.

Avoid a partition design where only 2 or 3 partitions are touched by frequent queries, if you need MAXDOP parallelism (assuming MAXDOP =4 or larger).

Design dimension tables appropriately

Use integer surrogate keys for all dimensions, other than the Date dimension. Use the smallest possible integer for the dimension surrogate keys. This helps to keep fact table narrow.
Use a meaningful date key of integer type derivable from the DATETIME data type (for example: 20060215).

Don't use a surrogate Key for the Date dimension
Easy to write queries that put a WHERE clause on this column, which will allow partition elimination of the fact table.

Build a clustered index on the surrogate key for each dimension table, and build a non-clustered index on the Business Key (potentially combined with a row-effective-date) to support surrogate key lookups during loads.
Build nonclustered indexes on other frequently searched dimension columns.
Avoid partitioning dimension tables.
Avoid enforcing foreign key relationships between the fact and the dimension tables, to allow faster data loads. You can create foreign key constraints with NOCHECK to document the relationships; but don’t enforce them. Ensure data integrity though Transform Lookups, or perform the data integrity checks at the source of the data.

Write effective queries for partition elimination

Whenever possible, place a query predicate (WHERE condition) directly on the partitioning key (Date dimension key) of the fact table.

Use Sliding Window technique to maintain data

Maintain a rolling time window for online access to the fact tables. Load newest data, unload oldest data.
Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement.
Avoid split or merge of populated partitions. Splitting or merging populated partitions can be extremely inefficient, as this may cause as much as 4 times more log generation, and also cause severe locking.
Create the load staging table in the same filegroup as the partition you are loading.
Create the unload staging table in the same filegroup as the partition you are deleteing.
It is fastest to load newest full partition at one time, but only possible when partition size is equal to the data load frequency (for example, you have one partition per day, and you load data once per day).
If the partition size doesn't match the data load frequency, incrementally load the latest partition.
Various options for loading bulk data into a partitioned table are discussed in the whitepaper http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/loading_bulk_data_partitioned_table.mspx.
Always unload one partition at a time.

Efficiently load the initial data

Use SIMPLE or BULK LOGGED recovery model during the initial data load.
Create the partitioned fact table with the Clustered index.
Create non-indexed staging tables for each partition, and separate source data files for populating each partition.
Populate the staging tables in parallel.

Use multiple BULK INSERT, BCP or SSIS tasks.

Create as many load scripts to run in parallel as there are CPUs, if there is no IO bottleneck. If IO bandwidth is limited, use fewer scripts in parallel.
Use 0 batch size in the load.
Use 0 commit size in the load.
Use TABLOCK.
Use BULK INSERT if the sources are flat files on the same server. Use BCP or SSIS if data is being pushed from remote machines.

Build a clustered index on each staging table, then create appropriate CHECK constraints.
SWITCH all partitions into the partitioned table.
Build nonclustered indexes on the partitioned table.
Possible to load 1 TB in under an hour on a 64-CPU server with a SAN capable of 14 GB/Sec throughput (non-indexed table). Refer to SQLCAT blog entry http://blogs.msdn.com/sqlcat/archive/2006/05/19/602142.aspx for details.

Efficiently delete old data

Use partition switching whenever possible.
To delete millions of rows from nonpartitioned, indexed tables

Avoid DELETE FROM ...WHERE ...

Huge locking and logging issues
Long rollback if the delete is canceled

Usually faster to

INSERT the records to keep into a non-indexed table
Create index(es) on the table
Rename the new table to replace the original

As an alternative, ‘trickle' deletes using the following repeatedly in a loop

DELETE TOP (1000) ... ;

COMMIT

Another alternative is to update the row to mark as deleted, then delete later during non critical time.

Manage statistics manually

Statistics on partitioned tables are maintained for the table as a whole.
Manually update statistics on large fact tables after loading new data.
Manually update statistics after rebuilding index on a partition.
If you regularly update statistics after periodic loads, you may turn off autostats on that table.
This is important for optimizing queries that may need to read only the newest data.
Updating statistics on small dimension tables after incremental loads may also help performance. Use FULLSCAN option on update statistics on dimension tables for more accurate query plans.

Consider efficient backup strategies

Backing up the entire database may take significant amount of time for a very large database.

For example, backing up a 2 TB database to a 10-spindle RAID-5 disk on a SAN may take 2 hours (at the rate 275 MB/sec).

Snapshot backup using SAN technology is a very good option.
Reduce the volume of data to backup regularly.

The filegroups for the historical partitions can be marked as READ ONLY.
Perform a filegroup backup once when a filegroup becomes read-only.
Perform regular backups only on the read / write filegroups.

Note that RESTOREs of the read-only filegroups cannot be performed in parallel.

Storage Top 10 Best Practices

Lindsey.allen — Wed, 21 Nov 2007 20:40:00 GMT

Proper configuration of IO subsystems is critical to the optimal performance and operation of SQL Server systems. Below are some of the most common best practices that the SQL Server team recommends with respect to storage configuration for SQL Server.

Understand the IO characteristics of SQL Server and the specific IO requirements / characteristics of your application.

In order to be successful in designing and deploying storage for your SQL Server application, you need to have an understanding of your application’s IO characteristics and a basic understanding of SQL Server IO patterns. Performance monitor is the best place to capture this information for an existing application. Some of the questions you should ask yourself here are:

What is the read vs. write ratio of the application?
What are the typical IO rates (IO per second, MB/s & size of the IOs)? Monitor the perfmon counters:

Average read bytes/sec, average write bytes/sec
Reads/sec, writes/sec
Disk read bytes/sec, disk write bytes/sec
Average disk sec/read, average disk sec/write
Average disk queue length

How much IO is sequential in nature, and how much IO is random in nature? Is this primarily an OLTP application or a Relational Data Warehouse application?

To understand the core characteristics of SQL Server IO, refer to SQL Server 2000 I/O Basics.

More / faster spindles are better for performance

Ensure that you have an adequate number of spindles to support your IO requirements with an acceptable latency.
Use filegroups for administration requirements such as backup / restore, partial database availability, etc.
Use data files to “stripe” the database across your specific IO configuration (physical disks, LUNs, etc.).

Try not to “over” optimize the design of the storage; simpler designs generally offer good performance and more flexibility.

Unless you understand the application very well avoid trying to over optimize the IO by selectively placing objects on separate spindles.
Make sure to give thought to the growth strategy up front. As your data size grows, how will you manage growth of data files / LUNs / RAID groups? It is much better to design for this up front than to rebalance data files or LUN(s) later in a production deployment.

Validate configurations prior to deployment

Do basic throughput testing of the IO subsystem prior to deploying SQL Server. Make sure these tests are able to achieve your IO requirements with an acceptable latency. SQLIO is one such tool which can be used for this. A document is included with the tool with basics of testing an IO subsystem. Download the SQLIO Disk Subsystem Benchmark Tool.
Understand that the of purpose running the SQLIO tests is not to simulate SQL Server’s exact IO characteristics but rather to test maximum throughput achievable by the IO subsystem for common SQL Server IO types.
IOMETER can be used as an alternative to SQLIO.

Always place log files on RAID 1+0 (or RAID 1) disks. This provides:

Better protection from hardware failure, and
Better write performance.
Note: In general RAID 1+0 will provide better throughput for write-intensive applications. The amount of performance gained will vary based on the HW vendor’s RAID implementations. Most common alternative to RAID 1+0 is RAID 5. Generally, RAID 1+0 provides better write performance than any other RAID level providing data protection, including RAID 5.

Isolate log from data at the physical disk level

When this is not possible (e.g., consolidated SQL environments) consider I/O characteristics and group similar I/O characteristics (i.e. all logs) on common spindles.
Combining heterogeneous workloads (workloads with very different IO and latency characteristics) can have negative effects on overall performance (e.g., placing Exchange and SQL data on the same physical spindles).

Consider configuration of TEMPDB database

Make sure to move TEMPDB to adequate storage and pre-size after installing SQL Server.
Performance may benefit if TEMPDB is placed on RAID 1+0 (dependent on TEMPDB usage).
For the TEMPDB database, create 1 data file per CPU, as described in #8 below.

Lining up the number of data files with CPU’s has scalability advantages for allocation intensive workloads.

It is recommended to have .25 to 1 data files (per filegroup) for each CPU on the host server.
This is especially true for TEMPDB where the recommendation is 1 data file per CPU.
Dual core counts as 2 CPUs; logical procs (hyperthreading) do not.

Don’t overlook some of SQL Server basics

Data files should be of equal size – SQL Server uses a proportional fill algorithm that favors allocations in files with more free space.
Pre-size data and log files.
Do not rely on AUTOGROW, instead manage the growth of these files manually. You may leave AUTOGROW ON for safety reasons, but you should proactively manage the growth of the data files.

Don’t overlook storage configuration bases

Use up-to-date HBA drivers recommended by the storage vendor
Utilize storage vendor specific drivers from the HBA manufactures website
Tune HBA driver settings as needed for your IO volumes. In general driver specific settings should come from the storage vendor. However we have found that Queue Depth defaults are usually not deep enough to support SQL Server IO volumes.
Ensure that the storage array firmware is up to the latest recommended level.
Use multipath software to achieve balancing across HBA’s and LUN’s and ensure this is functioning properly
Simplifies configuration & offers advantages for availability
Microsoft Multipath I/O (MPIO): Vendors build Device Specific Modules (DSM) on top of Driver Development Kit provided by Microsoft.

Top SQL Server 2005 Performance Issues for OLTP Applications

Lindsey.allen — Wed, 21 Nov 2007 20:30:00 GMT

OLTP work loads are characterized by high volumes of similar small transactions.

It is important to keep these characteristics in mind as we examine the significance of database design, resource utilization and system performance. The top performance bottlenecks or gotchas for OLTP applications are outlined below.

Database Design issue if….

Too many table joins for frequent queries. Overuse of joins in an OLTP application results in longer running queries & wasted system resources. Generally, frequent operations requiring 5 or more table joins should be avoided by redesigning the database.
Too many indexes on frequently updated (inclusive of inserts, updates and deletes) tables incur extra index maintenance overhead. Generally, OLTP database designs should keep the number of indexes to a functional minimum, again due to the high volumes of similar transactions combined with the cost of index maintenance.
Big IOs such as table and range scans due to missing indexes. By definition, OLTP transactions should not require big IOs and should be examined.
Unused indexes incur the cost of index maintenance for inserts, updates, and deletes without benefiting any users. Unused indexes should be eliminated. Any index that has been used (by select, update or delete operations) will appear in sys.dm_db_index_usage_stats. Thus, any defined index not included in this DMV has not been used since the last re-start of SQL Server.

CPU bottleneck if…

Signal waits > 25% of total waits. See sys.dm_os_wait_stats for Signal waits and Total waits. Signal waits measure the time spent in the runnable queue waiting for CPU. High signal waits indicate a CPU bottleneck.
Plan re-use < 90% . A query plan is used to execute a query. Plan re-use is desirable for OLTP workloads because re-creating the same plan (for similar or identical transactions) is a waste of CPU resources. Compare SQL Server SQL Statistics: batch requests/sec to SQL compilations/sec. Compute plan re-use as follows: Plan re-use = (Batch requests - SQL compilations) / Batch requests. Special exception to the plan re-use rule: Zero cost plans will not be cached (not re-used) in SQL 2005 SP2. Applications that use zero cost plans will have a lower plan re-use but this is not a performance issue.
Parallel wait type cxpacket > 10% of total waits. Parallelism sacrifices CPU resources for speed of execution. Given the high volumes of OLTP, parallel queries usually reduce OLTP throughput and should be avoided. See sys.dm_os_wait_stats for wait statistics.

Memory bottleneck if…

Consistently low average page life expectancy. See Average Page Life Expectancy Counter which is in the Perfmon object SQL Server Buffer Manager (this represents is the average number of seconds a page stays in cache). For OLTP, an average page life expectancy of 300 is 5 minutes. Anything less could indicate memory pressure, missing indexes, or a cache flush.
Sudden big drop in page life expectancy. OLTP applications (e.g. small transactions) should have a steady (or slowly increasing) page life expectancy. See Perfmon object SQL Server Buffer Manager.
Pending memory grants. See counter Memory Grants Pending, in the Perfmon object SQL Server Memory Manager. Small OLTP transactions should not require a large memory grant.
Sudden drops or consistenty low SQL Cache hit ratio. OLTP applications (e.g. small transactions) should have a high cache hit ratio. Since OLTP transactions are small, there should not be (1) big drops in SQL Cache hit rates or (2) consistently low cache hit rates < 90%. Drops or low cache hit may indicate memory pressure or missing indexes.

IO bottleneck if…

High average disk seconds per read. When the IO subsystem is queued, disk seconds per read increases. See Perfmon Logical or Physical disk (disk seconds/read counter). Normally it takes 4-8ms to complete a read when there is no IO pressure. When the IO subsystem is under pressure due to high IO requests, the average time to complete a read increases, showing the effect of disk queues. Periodic higher values for disk seconds/read may be acceptable for many applications. For high performance OLTP applications, sophisticated SAN subsystems provide greater IO scalability and resiliency in handling spikes of IO activity. Sustained high values for disk seconds/read (>15ms) does indicate a disk bottleneck.
High average disk seconds per write. See Perfmon Logical or Physical disk. The throughput for high volume OLTP applications is dependent on fast sequential transaction log writes. A transaction log write can be as fast as 1ms (or less) for high performance SAN environments. For many applications, a periodic spike in average disk seconds per write is acceptable considering the high cost of sophisticated SAN subsystems. However, sustained high values for average disk seconds/write is a reliable indicator of a disk bottleneck.
Big IOs such as table and range scans due to missing indexes.

Top wait statistics in sys.dm_os_wait_stats are related to IO such as ASYNCH_IO_COMPLETION, IO_COMPLETION, LOGMGR, WRITELOG, or PAGEIOLATCH_x.

Blocking bottleneck if…

Index contention. Look for lock and latch waits in sys.dm_db_index_operational_stats. Compare with lock and latch requests.
High average row lock or latch waits. The average row lock or latch waits are computed by dividing lock and latch wait milliseconds (ms) by lock and latch waits. The average lock wait ms computed from sys.dm_db_index_operational_stats represents the average time for each block.
Block process report shows long blocks. See sp_configure “blocked process threshold” and Profiler “Blocked process Report” under the Errors and Warnings event.
Top wait statistics are LCK_x. See sys.dm_os_wait_stats.
High number of deadlocks. See Profiler “Graphical Deadlock” under Locks event to identify the statements involved in the deadlock.

Network bottleneck if…

High network latency coupled with an application that incurs many round trips to the database.
Network bandwidth is used up. See counters packets/sec and current bandwidth counters in the network interface object of Performance Monitor. For TCP/IP frames actual bandwidth is computed as packets/sec * 1500 * 8 /1000000 Mbps.

Top 10 SQL Server 2005 Performance Issues for Data Warehouse and Reporting Applications

Lindsey.allen — Wed, 21 Nov 2007 20:25:00 GMT

Relational Data Warehouse or Reporting work loads are characterized by low volumes of very large transactions. These applications are often identified as having predominately read workloads (e.g. Decision Support, Analysis, and Reporting) with periodic feeds or batch loads. It is important to keep these characteristics in mind as we examine the significance of database design, resource utilization and system performance. The top performance bottlenecks or gotchas to avoid for Data Warehouse or Reporting applications are outlined below.

Database Design issue if….

Excessive sorting operations are performed. If you continually perform the same sorting operations over and over, you can avoid these with appropriate indexing.
Excessive RID lookups are performed on heap tables. RID lookups mean extra IOs are required to retrieve columns that are not in the index used. This can be avoided with covered nonclustered indexes.
Key lookups against the clustering keys look like joins however they are marked as “lookups” only in the XML showplan. These can be avoided with covered nonclustered indexes.
A potentially beneficial index is missing on join columns resulting in HASH joins. Indexes on join columns may avoid the hash.

CPU gotchas….

If signal waits > 25% of total waits, there is a CPU bottleneck. See sys.dm_os_wait_stats for Signal waits and Total waits. Signal waits measure the time spent in the runnable queue waiting for CPU. High signal waits indicate a CPU bottleneck.
Avoid inappropriate plan re-use. If the query is identical, then plan re-use is a good thing. However, query parameterization that allows plan re-use is only appropriate when the result set (and intermediate work tables) are of similar size to the original plan. If result set sizes vary significantly due to differing parameter values which are common in data warehouse scenarios, plan re-use can be detrimental. Bad plans can also lead to longer running queries and IO or memory pressure. Therefore, the cost of plan generation in such cases is preferable to plan re-use. Unlike OLTP, data warehouse queries are not always identical in terms of result sets or optimal query plans.

Memory bottleneck if….

Sudden big drop in page life expectancy. DW applications (e.g. big transactions) could experience big drops in page life expectancy. This is due to a cache flush from a big read. See Perfmon object SQL Server Buffer Manager.
Pending memory grants. See counter Memory Grants Pending, in the Perfmon object SQL Server Memory Manager. Large memory grants can be common in Data Warehouse applications. More memory may help, otherwise the user cannot execute until memory grant occurs.
Sudden drops or consistently low SQL Cache hit ratio. Drops or low cache hit may indicate memory pressure or missing indexes.

IO bottleneck if…

The best metric for write performance is disk seconds per read and disk seconds per write. When the IO system is NOT under significant load, there will be no disk queuing and thus disk seconds per read or write should be as good as it gets Normally it takes 4-8 milliseconds to complete a read when there is no IO pressure. Factors for IO throughput are the number of spindles, and drive throughput such as sequential and random IOs per second (according to the vendor). As the IO requests increase, you may notice disk queuing. The effects of queuing are reflected in high disk seconds per read or write. Periodic higher values for disk seconds/read may be acceptable for many applications. For high performance OLTP applications, sophisticated SAN subsystems provide greater IO scalability and resiliency in handling spikes of IO activity. Sustained high values for disk seconds/read (>15ms) does indicate a disk bottleneck.
High average disk seconds per write. See Perfmon Logical or Physical disk. Data Warehouse loads can be either logged with inserts, updates or deletes, or non-logged using bulk copy. Logged operations require transaction log writes. A transaction log write can be as fast as 1ms (or less) for high performance SAN environments. For many applications, a periodic spike in average disk seconds per write is acceptable considering the high cost of sophisticated SAN subsystems. However, sustained high values for average disk seconds/write is a reliable indicator of a disk bottleneck.
Big IOs such as table and range scans may be due to missing indexes.

Blocking bottleneck if….

Index contention. Look for high lock and latch waits in sys.dm_db_index_operational_stats. Compare with lock and latch requests.
High average row lock or latch waits. The average row lock or latch waits are computed by dividing lock and latch wait milliseconds (ms) by lock and latch waits. The average lock wait ms computed from sys.dm_db_index_operational_stats represents the average time for each block.
Block process report shows long blocks. See sp_configure “blocked process threshold” and Profiler “Blocked process Report” under the Errors and Warnings event.
High number of deadlocks. See Profiler “Graphical Deadlock” under Locks event to identify the statements involved in the deadlock.

Network bottleneck if….

High network latency coupled with an application that incurs many round trips to the database.
Network bandwidth is used up. See counters packets/sec and current bandwidth counters in the network interface object of Performance Monitor. For TCP/IP frames actual bandwidth is computed as packets/sec * 1500 * 8 /1000000 Mbps.

Wait statistics gotchas...

Since DataWarehouse and Reporting workloads are largely reads which are compatible with other reads, incompatible exclusive lock waits would ordinarily only come into play during batch loads or periodic feeds. If the top wait statistics are LCK_x. or PAGELATCH_EX, see “SQL Server 2005 Performance Tuning using Waits & Queues” for an explanation of sys.dm_os_wait_stats.
There is an IO bottleneck if top wait statistics in sys.dm_os_wait_stats are related to IO such as ASYNCH_IO_COMPLETION, IO_COMPLETION, LOGMGR, WRITELOG, or PAGEIOLATCH_x.

Indexing gotchas.

Large data warehouse can benefit from more indexes. Indexes can be used to cover queries and avoid sorting. For a data warehouse application, the cost of index overhead is only paid when data is loaded.
Check for missing indexes in sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_details

Watch out for fragmentation.

Excessive fragmentation is problematic for big IO operations. The Dynamic Management table valued function sys.dm_db_index_physical_stats returns the fragmentation percentage in the column avg_fragmentation_in_percent. Fragmentation should not exceed 25%. Reducing index fragmentation can benefit big range scans, common in data warehouse and Reporting scenarios

Consider Table Partitioning for fast loads

For the large tables common in Data Warehouses, table partitioning offers important performance and manageability advantages. For example, the fastest type of load is a non-logged bulk copy. The requirements for non-logged bulk copies are that indexes must be dropped. This is not feasible on a huge billion row table UNLESS you use table partitioning. This allows one to create a staging table identical to the large table (minus indexes). A fast non-logged bulk copy is used to load data. Thereafter, indexes are added to the staging table followed by constraints. Then, a meta-data only SWITCH IN operation switches pointer locations for the populated staging table and the empty target partition of the partitioned table resulting in an fully populated partition and empty staging table. Besides a fast bulk copy, the staging table allows us to eliminate blocking in the large partitioned table during the load. For more information refer to “Loading Bulk Data into Partitioned Tables”. In addition to fast loads, partitioned tables allow fast deletes (or archiving purposes or sliding window deletes) where large logged deletes are replaced with meta-data only partition SWITCH OUT operations that switches pointer locations for the full partition (to be ‘deleted’) and an empty monolithic table. The SWITCH OUT results in an empty partition and a fully populated monolithic staging table. Thereafter the monolithic table can either be dropped or added to a partitioned archive table using SWITCH IN. Partitions also provide manageability improvements when combined with specific filegroup placement, allowing for customized backup and restore strategies.

In contrast to OLTP, a data warehouse or Reporting application is characterized by a small number of large SELECT transactions, each very different. The implications are significant for database design, resource usage, and system performance. These distinctions result in very different objectives and resource utilization profiles.

Top 10 Hidden Gems in SQL Server 2005

Lindsey.allen — Wed, 21 Nov 2007 20:19:00 GMT

By Cihan Biyikoglu

Technical Reviewers: Lindsey Allen, Peter Scharlock, Burzin Patel, Eric Hanson, Mark Souza, Sanjay Mishra, Michael Thomassy

SQL Server 2005 has hundreds of new and improved components. Some of these improvements get a lot of the spotlight. However there is another set that are the hidden gems that help us improve performance, availability or greatly simplify some challenging scenarios. This paper lists the top 10 such features in SQL Server 2005 that we have discovered through the implementation with some of our top customers and partners.

The order in the list does not have much significance except the specific instances we used them and the impact we saw. I will use a practical analogy; I started with the utility-knife size features that can help make life very easy at the right moment and build up to chain-saw size features that can help you implement a full scenario.

TableDiff.exe

Table Difference tool allows you to discover and reconcile differences between a source and destination table or a view. Tablediff Utility can report differences on schema and data. The most popular feature of tablediff is the fact that it can generate a script that you can run on the destination that will reconcile differences between the tables. TableDiff.exe takes 2 sets of input;

Connectivity - Provide source and destination objects and connectivity information.
Compare Options - Select one of the compare options
Compare schemas: Regular or Strict
Compare using Rowcounts, Hashes or Column comparisons
Generate difference scripts with I/U/D statements to synchronize destination to the source.

TableDiff was intended for replication but can easily apply to any scenario where you need to compare data and schema.

You can find more information about command line utilities and the Tablediff Utility in Books Online for SQL Server 2005.

Triggers for Logon Events (New in Service Pack 2)

With SP2, triggers can now fire on Logon events as well as DML or DDL events.
Logon triggers can help complement auditing and compliance. For example, logon events can be used for enforcing rules on connections (for example limiting connection through a specific username or limiting connections through a username to a specific time periods) or simply for tracking and recording general connection activity. Just like in any trigger, ROLLBACK cancels the operation that is in execution. In the case of logon event that means canceling the connection establishment. Logon events do not fire when the server is started in the minimal configuration mode or when a connection is established through dedicated admin connection (DAC).
The following code snippet provides an example of a logon trigger that records the information about the client connection.

CREATE TRIGGER connection_limit_trigger
          ON ALL SERVER FOR LOGON
          AS
          BEGIN
          INSERT INTO logon_info_tbl SELECT EVENTDATA()
          END;

You can find more information about this feature in updated Books Online for SQL Server Services Pack 2 un the heading “Logon Triggers”.

Boosting performance with persisted-computed-columns (pcc).

Btree Indexes provide great compromise for tuning queries vs redundant storage of data and added cost of modifying data (insert/update/delete). A less known capability for tuning in SQL Server 2005 is persisted computed columns (PCC). Computed columns can help you shift the runtime computation cost to data modification phase. The computed column is stored with the rest of the row and is transparently utilized when the expression on the computed columns and the query matches. You can also build indexes on the PCC’s to speed up filtrations and range scans on the expression.
The following sample can demonstrate the benefits of a persisted computed column applied to a complex expression. The same TSQL query run against the following table schema with and without the DayType column will demonstrate the effect of the transparent expression matching with persisted computed columns. The output from the sys.dm_exec_query_stats DMV also shows the difference in the IO and CPU characteristics of the query.

Query

SELECT [Ticker] ,[Date] , [DayHigh] ,[DayLow] ,[DayOpen] ,[Volume] ,[DayClose] ,[DayAdjustedClose], 
CASE 
WHEN volume > 200000000 and dayhigh-daylow /daylow > .05 THEN 'heavy volatility'
WHEN volume > 100000000 and dayhigh-daylow /daylow > .03 THEN 'volatile'
WHEN volume > 50000000 and dayhigh-daylow /daylow > .01 THEN 'fair'
ELSE 'light'
END as [DayType]
FROM dbo.MarketData
WHERE 
CASE 
WHEN volume > 200000000 and dayhigh-daylow /daylow > .05 THEN 'heavy volatility'
WHEN volume > 100000000 and dayhigh-daylow /daylow > .03 THEN 'volatile'
WHEN volume > 50000000 and dayhigh-daylow /daylow > .01 THEN 'fair'
ELSE 'light'
END = 'heavy volatility'

Table Schema

CREATE TABLE [dbo].[MarketData](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Ticker] [nvarchar](5) NOT NULL,
[Date] [datetime] NOT NULL,
[DayHigh] [decimal](38, 6) NOT NULL,
[DayLow] [decimal](38, 6) NOT NULL,
[DayOpen] [decimal](38, 6) NOT NULL,
[Volume] [bigint] NOT NULL,
[DayClose] [decimal](38, 6) NOT NULL,
[DayAdjustedClose] [decimal](38, 6) NOT NULL,
-- PERSISTED COMPUTED COLUMN --
[DayType] AS (
CASE 
WHEN volume > 200000000 and dayhigh-daylow /daylow > .05 THEN 'heavy volatility'
WHEN volume > 100000000 and dayhigh-daylow /daylow > .03 THEN 'volatile'
WHEN volume > 50000000 and dayhigh-daylow /daylow > .01 THEN 'fair'
ELSE 'light'
END) PERSISTED NOT NULL
) ON [PRIMARY]

Output From The Sys.Dm_Exec_Query_Stats Dynamic Management View (DMV)

See full-sized image.

In the above picture, the output from sys.dm_exec_query_stats dynamic management view shows the difference in CPU and IO statistics between the same query hitting MarketData_Computed and MarketData tables. Line 1 represents the query run against the table with the persisted computed column. Line 2 is the table without the persisted computed column. With the complex expression pre-calculated in the DayType column, total worker time and overall elapsed time is lower compared to the table without the DayType persisted computed column.

Another way to verify that the persisted computed column is utilized, is to use the execution plan and look at the scan or the seek operator for the table with the computed column and check the output list, which should contain the column. In the example below you can see the DayType, the name for the PCC, in the output list under #9.

See full-sized image.

DEFAULT_SCHEMA setting in sys.database_principles

SQL Server provides great flexibility with name resolution. However name resolution comes at a cost and can get noticeably expensive in adhoc workloads that do not fully qualify object references. SQL Server 2005 allows a new setting of DEFEAULT_SCHEMA for each database principle (also known as “user”) which can eliminate this overhead without changing your TSQL code. Here is an example:

In SQL Server 2005, the following query when executed by user1 that has a DEFAULT_SCHEMA of ‘dbo’ will directly resolve to dbo.tab1, instead of the extra search for user1.tab1.

SELECT * FROM tab1

Whereas the same query will search for ‘user1.tab1’ in SQL Server 2000 and if that does not exist it will resolve to ‘dbo.tab1’.

This setting can be especially useful for databases upgraded from SQL Server 2000 to SQL Server 2005. To preserve the original behavior, databases upgraded from SQL Server 2000 will get the username as the DEFAULT_SCHEMA for each database principle. That means, in a database upgraded from a previous version to SQL Server 2005, ‘user1’ will get a DEFAULT_SCHEMA values of ‘user1’. To take advantage of the performance benefits, administrators can set the DEFAULT_SCHEMA through ALTER USER command and change it to the schema that most of the of the objects reside. Be aware this may break queries that may be utilizing objects in other schemas than the one set in the DEFAULT_SCHEMA setting and has not qualified the object names.
DEFULT_SCHEMA is documented in Book Online under the “CREATE USER (Transact-SQL)” heading.

Forced Parameterization

Parameterization allows SQL Server to take advantage of query plan reuse and avoid compilation and optimization overheads on subsequent executions of similar queries. However there are many applications out there that, for one reason or another, still suffer from ad-hoc query compilation overhead. For those cases with high number of query compilation and where lowering CPU utilization and response time is critical for your workload, force parameterization can help.
Force parameterization forces most queries to be parameterized and cached for reuse in subsequent submissions. Forced parameterization will remove the literal values and replaces them with parameters. This minimizes the compilation overhead for queries that are the same except the literal values in the query text. Forced parameterization is typically enabled at the database level. However it is also possible to hint FORCED PARAMETERIZATION on individual queries.
In a number of cases, we have witnessed improvements in performance up to 30% due to forced parameterization. However forced parameterization can cause inappropriate plan sharing in cases where a single execution plan does not make sense. For those cases, you can utilize features like plan guides or query hints.
You can find more information on Forced Parameterization in Books Online.

Vardecimal Storage Format

In Service Pack 2, SQL Server 2005 adds a new storage format for numeric and decimal datatypes called vardecimal. Vardecimal is a variable-length representation for decimal types that can save unused bytes in every instance of the row. The biggest amount of savings come from cases where the decimal definition is large (like decimal(38,6)) but the values stored are small (like a value of 0.0) or there is a large number of repeated values or data is sparsely populated.
SQL Server 2005 also includes a stored procedure that can estimate the savings before you enable the new storage format.

master.dbo.sp_estimate_rowsize_reduction_for_vardecimal ‘tablename’

To enable vardecimal storage format, you need to first allow vardecimal storage on the database;

exec sys.sp_db_vardecimal_storage_format N'databasename', N'ON'

Once the database option is enabled, you can then turn on vardecimal storage at a table level using the following procedure;

exec sp_tableoption 'tablename', 'vardecimal storage format', 1

Vardecimal storage format presents an overhead due to the complexity inherent in variable length data processing. However in IO bound workloads, savings on IO bandwidth due to efficient storage can far exceed this processing overhead.
If you would like more information on this topic, updated SQL Server 2005 Books Online for Service Pack 2 contains extensive information on the new vardecimal format.

Indexing made easier with SQL Server 2005

The new Dynamic Management Views have improved monitoring and trouble shooting greatly. A few of the dynamic management views (DMVs) deserve special attention.
Through sys.dm_index_usage_stats you can find out how much maintenance and traversal you have for each index. Indexes with high maintenance numbers and low traversal numbers can be considered as good candidates for dropping.
Through sys.dm_db_missing_index_* collection of DMVs, you can get recommendations on what new indexes could benefit the queries running on your server. The recommendations come with a estimate on how much improvement you can expect from the new index.
If you’d like to automate creation and dropping of indexes, SQL Server Query Optimization Team has blogged about how to automate index recommendations into actions: http://blogs.msdn.com/queryoptteam/archive/2006/06/01/613516.aspx

Figuring out the most popular queries in seconds

Another great DMV that can help save you a lot of work is sys.dm_exec_query_stats. In previous version of SQL Server to find out the highest impact queries on CPU or IO in system, you had to walk through a long set of analyses steps including getting aggregated information out of the data you collected from profiler.
With sys.dm_exec_query_stats, you can figure out many combinations of query analyses by a single query. Here are some of the examples;
Find queries suffering most from blocking –
(total_elapsed_time – total_worker_time)
Find queries with most CPU cycles – (total_worker_time)
Find queries with most IO cycles –
(total_physical_reads + total_logical_reads + total_logical_writes)
Find most frequently executed queries –
(execution_count)
You can find more information on how to use dynamic management views for performance troubleshooting in the “SQL Server 2005 Waits and Queues” whitepaper located at: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx

Scalable Shared Databases

Scalable Shared Databases provide an alternative scale out mechanism for Read-Only environments. Through Scalable Shared Databases one can mount the same physical drives on commodity machines and allow multiple instances of SQL Server 2005 to work off of the same set of data files. The setup does not require duplicate storage for every instance of SQL Server and allows additional processing power through multiple SQL Server instances that have their own local resources like cpu, memory, tempdb and potentially other local databases. However this type of setup does limit the IO bandwidth since all instances point to the physical set of files.

See full-sized image.

Book Online for SQL server 2005 contains details on Scalable Shared Databases.

Steps to setup:

http://support.microsoft.com/kb/910378

Soft-NUMA

Highly concurrent workloads hit a contention point around global state they maintain at some point. That point in many cases happen to be ‘8’. One way around this contention has been to eliminate the global state and create hierarchies. NUMA architectures allow us to eliminate the contention around global resources by moving main resources closer to each other and forming nodes. SQL Server 2005 recognizes the NUMA architecture and self manages allocation of resources to adhere and take advantage of the hardware NUMA setup at the time of startup. By aligning with the HW setup SQL Server partitions its internal management to improve throughput.
Some workloads benefit greatly from the partitioning concept, especially mixed workloads that have to run varying characteristics of data access concurrently (example: OLTP and Reporting). Soft-NUMA allows partitioning configuration to be extended into the software level and defined either on top of a NUMA enabled environment to further divide the hardware partitions into smaller chunks or on a machine that does not utilize NUMA concepts to enable partitioning for the configuration. By configuring partitions through Soft-NUMA, the administrator can control the allocation of schedulers and memory managers for each node and can configure specific TCP/IP ports for the nodes. Then, clients can be configured to connect using the specific ports to access specific partitions.

See full-sized image.

Soft-NUMA topic is extensively covered in Book Online. You can also read more about the details of Soft-NUMA at Slava Oks’s Weblog at http://blogs.msdn.com/slavao/

Analysis Services Query Performance Top 10 Best Practices

admin — Thu, 13 Sep 2007 16:08:00 GMT

Author: Carl Rabeler

Proper cube design, efficient multidimensional expressions (MDX), and sufficient hardware resources are critical to optimal performance of MDX queries issued against a SQL Server 2005 Analysis Services instance. This article lists the ten most common best practices that the Microsoft SQL Server development team recommends with respect to optimizing Analysis Services query performance. For additional discussions about Analysis Services best practices related to query performance, see The Analysis Services Performance Guide and OLAP Design Best Practices for Analysis Services 2005.

	Optimize cube and measure group design Define cascading attribute relationships (for example Day > Month > Quarter > Year) and define user hierarchies of related attributes (called natural hierarchies) within each dimension as appropriate for your data. Attributes participating in natural hierarchies are materialized on disk in hierarchy stores and are automatically considered to be aggregation candidates. User hierarchies are not considered to be natural hierarchies unless the attributes comprising the levels are related through cascading attribute relationships. With SQL Server 2005 Service Pack 2 (SP2), a warning appears in Business Intelligence Development Studio with each user hierarchy that is not defined as a natural hierarchy. Remove redundant relationships between attributes to assist the query execution engine in generating the appropriate query plan. Attributes need to have either a direct or an indirect relationship to the key attribute, not both. Keep cube space as small as possible by only including measure groups that are needed. Place measures that are queried together in the same measure group. A query that retrieves measures from multiple measure groups requires multiple storage engine operations. Consider placing large sets of measures that are not queried together into separate measure groups to optimize cache usage, but do not explode the number of measure groups. Minimize the use of large parent-child hierarchies. In parent-child hierarchies, aggregations are created only for the key attribute and the top attribute (for example, the All attribute) unless it is disabled. As a result, queries returning cells at intermediate levels are calculated at query time and can be slow for large parent-child dimensions. If you are in a design scenario with a large parent-child hierarchy (more than 250,000 members), you may want to consider altering the source schema to reorganize part or all of the hierarchy into a user hierarchy with a fixed number of levels. Optimize many-to-many dimension performance, if used. When you query the data measure group by the many-to-many dimension, a run-time “join” is performed between the data measure group and the intermediate measure group using the granularity attributes of each dimension that the measure groups have in common. Where possible, reduce the size of the intermediate fact table underlying the intermediate measure group. To optimize the run-time join, review the aggregation design for the intermediate measure group to verify that aggregations include attributes from the many-to-many dimension. To understand how to optimize dimensions to increase query performance, refer to the articles SQL Server 2005 Analysis Services Performance Guide and OLAP Design Best Practices for Analysis Services 2005. For assistance in analyzing your design for compliance with best practices, see the February 2007 Community Technology Preview (CTP) release of the SQL Server 2005 Best Practices Analyzer (the final version should be released soon).
	Define effective aggregations Define aggregations to reduce the number of records that the storage engine needs to scan from disk to satisfy a query. If SQL Server Profiler traces indicate that most user queries that are not resolved from cache are resolved by partition reads rather than aggregation reads, consider using the Aggregation Manager sample application to design custom aggregations. This sample is available on CodePlex at http://www.codeplex.com/MSFTASProdSamples and a version of this sample that has been updated by the community is available on CodePlex at http://www.codeplex.com/bidshelper. Avoid designing an excessive number of aggregations. Excessive aggregations reduce processing performance and may reduce query performance. While the optimum number of aggregations varies, the SQL Server Best Practices team’s experience has been that the optimum number is in the tens, not hundreds or thousands in almost all cases. Enable the Analysis Services query log to capture user query patterns and use this query log when designing aggregations. For more information, see Configuring the Analysis Services Query Log. To understand how to design aggregations to increase query performance, refer to the articles SQL Server 2005 Analysis Services Performance Guide and OLAP Design Best Practices for Analysis Services 2005.
	Use partitions Define partitions to enable Analysis Services to query less data to resolve a query when it cannot be resolved from the data cache or from aggregations. Also define the partitions to increase parallelism when resolving queries. For optimum performance, partition data in a manner that matches common queries. A very common choice for partitions is to select an element of time such as day, month, quarter, year or some combination of time elements. Avoid partitioning in a way that requires most queries to be resolved from many partitions. In most cases, partitions should contain fewer than 20 million records size and each measure group should contain fewer than 2,000 total partitions. Also, avoid defining partitions containing fewer than two million records. Too many partitions causes a slowdown in metadata operations, and too few partitions can result in missed opportunities for parallelism. Define a separate ROLAP partition for real-time data and place real-time ROLAP partition in its own measure group. To understand how to design partitions to increase query performance, refer to the SQL Server 2005 Analysis Services Performance Guide, the Microsoft SQL Server Customer Advisory Team blog, and OLAP Design Best Practices for Analysis Services 2005.
	Write efficient MDX Remove empty tuples from your result set to reduce the time spent by the query execution engine serializing the result set. Avoid run-time checks in an MDX calculation that result in a slow execution path. If you use the Case and IF functions to perform condition checks which must be resolved many times during query resolution, you will have a slow execution path. Rewrite these queries using the SCOPE function to quickly reduce the calculation space to which an MDX calculation refers. For more information, see Budget Variance - A study of MDX optimizations: evaluation modes and NON_EMPTY_BEHAVIOR, Comparing Levels in MDX and CONDITION vs. SCOPE in cell calculations, and Multiselect friendly MDX calculations. Use Non_Empty_Behavior where possible to enable the query execution engine to use bulk evaluation mode. However, if you use Non_Empty_Behavior incorrectly, you will return incorrect results. For more information, see Budget Variance - A study of MDX optimizations: evaluation modes and NON_EMPTY_BEHAVIOR and Averages, ratios, division by zero and NON_EMPTY_BEHAVIOR. Use EXISTS rather than filtering on member properties to avoid a slow execution path. Use the NonEmpty and Exists functions to enable the query execution engine to use bulk evaluation mode. Perform string manipulations within Analysis Services stored procedures using server-side ADOMD.NET rather than with string manipulation functions such as StrToMember and StrToSet. Rather than using the LookupCube function, use multiple measure groups in the same cube wherever possible. Rewrite MDX queries containing arbitrary shapes to reduce excessive subqueries where possible. An arbitrary shaped set is a set of members that cannot be resolved as a crossjoin of sets with a single hierarchality. For example, the set {(Gender.Male, Customer.USA), (Gender.Female, Customer.Canada)} is an arbitrary set. You can frequently use the Descendants function to resolve arbitrary shapes by using a smaller number of subqueries than queries that return the same result that are written using other functions. Rewrite MDX queries that result in excessive prefetching where possible. Prefetching is a term used to describe cases where the query execution engine requests more information from the storage engine than is required to resolve the query at hand for reasons of perceived efficiency. Generally, prefetching is the most efficient data retrieval choice. However, occasionally it is not. In some cases you may be able to eliminate excessive prefetching by rewriting queries with a subselect in the FROM clause rather than a set in the WHERE clause. When you cannot eliminate excessive prefetching, you may need to disable prefetching and warm the cache using the Create Cache statement. For more information, see How to Warm up the Analysis Services data cache using Create Cache statement. Filter a set before using it in a crossjoin to reduce the cube space before performing the crossjoin.
	Use the query engine cache efficiently Ensure that the Analysis Services computer has sufficient memory to store query results in memory for re-use in resolving subsequent queries. To monitor, use the MSAS 2005: Memory/Cleaner Memory Shrinkable DB and the MSAS 2005: Cache/Evictions/sec Performance Monitor counters. Define calculations in the MDX script. Calculations in the MDX script have a global scope that enables the cache related to these queries to be shared across sessions for the same set of security permissions. However, calculated members defined using Create Member and With Member within user queries do not have global scope and the cache related to these queries cannot be shared across sessions. Warm the cache by executing a set of predefined queries using the tool of your choice. You can also use a Create Cache statement for this purpose. For more information on using the Create Cache statement, see How to Warm up the Analysis Services data cache using Create Cache statement. For information on how to use SQL Server 2005 Integration Services to warm the cache, see Build Your Own Analysis Services Cache-Warmer in Integration Services. Rewrite MDX queries containing arbitrary shapes to optimize caching. For example, in some cases you can rewrite queries that require non-cached disk access such that they can be resolved entirely from cache by using a subselect in the FROM clause rather than a WHERE clause. In other cases, a WHERE clause may be a better choice.
	Ensure flexible aggregations are available to answer queries. Note that incrementally updating a dimension using ProcessUpdate on a dimension drops all flexible aggregations affected by updates and deletes and, by default, does not re-create them until the next full process. Ensure that aggregations are re-created by processing affected objects, configuring lazy processing, performing ProcessIndexes on affected partitions, or performing full processing on affected partitions. To understand how to ensure flexible aggregations are not dropped, refer to the SQL Server 2005 Analysis Services Performance Guide.
	Tune memory usage Increase the size of the paging files on the Analysis Services server or add additional memory to prevent out–of-memory errors when the amount of virtual memory allocated exceeds the amount of physical memory on the Analysis Services server. Use Microsoft Windows Advanced Server® or Datacenter Server with SQL Server 2005 Enterprise Edition (or SQL Server 2005 Developer Edition) when you are using SQL Server 2005 (32-bit) to enable Analysis Services to address up to 3 GB of memory. To enable Analysis Services to address more than 2 GB of physical memory with either of these editions, use the /3GB switch in the boot.ini file. If you set the /3GB switch in the boot.ini file, the server should have at least 4 GB of memory to ensure that the Windows operating system also has sufficient memory for system services. Reduce the value for the Memory/LowMemoryLimit property below 75 percent when running multiple instances of Analysis Services or when running other applications on the same computer. Reduce the value for the Memory/TotalMemoryLimit property below 80percent when running multiple instances of Analysis Services or when running other applications on the same computer. Keep a gap between the Memory/LowMemoryLimit property and the Memory/TotalMemoryLimit property – 20 percent is frequently used. When query thrashing is detected in a multi-user environment, contact Microsoft Support for assistance in modifying the MemoryHeapType. When running on non-uniform memory access (NUMA) architecture and VirtualAlloc takes a very long time to return or appears to stop responding, upgrade to SQL Server 2005 SP2 and contact Microsoft Support for assistance with appropriate settings for pre-allocating NUMA memory. To understand when to consider changing default memory use, refer to the SQL Server 2005 Analysis Services Performance Guide and Microsoft SQL Server Customer Advisory Team blog.
	Tune processor usage To increase parallelism during querying for servers with multiple processors, consider modifying the Threadpool\Query\MaxThreads and Threadpool\Process\MaxThreads options to be a number that depends on the number of server processors. A general recommendation is to set the Threadpool\Query\MaxThreads to a value of less than or equal to two times the number of processors on the server. For example, if you have an eight-processor server, the general guideline is to set this value to no more than 16. In practical terms, increasing the Threadpool\Query\MaxThreads option will not significantly increase the performance of a given query. Rather, the benefit of increasing this property is that you can increase the number of queries that can be serviced concurrently. A general recommendation is to set the Threadpool\Process\MaxThreads option to a value of less than or equal to 10 times the number of processors on the server. This property controls the number of threads used by the storage engine during querying operations as well as during processing operations. For example, if you have an eight-processor server, the general guideline is setting this value to no more than 80. Note that even though the default value is 64, if you have fewer than eight processors on a given server, you do not need to reduce the default value to throttle parallel operations. While modifying the Threadpool\Process\MaxThreads and Threadpool\Query\MaxThreads properties can increase parallelism during querying, you must also consider the additional impact of the CoordinatorExecutionMode option. For example, if you have a four-processor server and you accept the default CoordinatorExecutionMode setting of -4, a total of 16 jobs can be executed at one time across all server operations. So if 10 queries are executed in parallel and require a total of 20 jobs, only 16 jobs can launch at a given time (assuming that no processing operations are being performed at that time). When the job threshold has been reached, subsequent jobs wait in a queue until a new job can be created. Therefore, if the number of jobs is the bottleneck to the operation, increasing the thread counts may not necessarily improve overall performance.
	Scale up where possible Use a 64-bit architecture for all large systems. Add memory and processor resources and upgrade the disk I/O subsystem, to alleviate query performance bottlenecks on a single system. Avoid linking dimensions or measure groups across servers and avoid remote partitions whenever possible because these solutions do not perform optimally.
	Scale out when you can no longer scale up If your performance bottleneck is processor utilization on a single system as a result of a multi-user query workload, you can increase query performance by using a cluster of Analysis Services servers to service query requests. Requests can be load balanced across two Analysis Services servers, or across a larger number of Analysis Services servers to support a large number of concurrent users (this is called a server farm). Load-balancing clusters generally scale linearly. When using a cluster of Analysis Services servers to increase query performance, perform processing on a single processing server and then synchronize the processing and the query servers using the XMLA Synchronize statement, copy the database directory using Robocopy or some other file copy utility, or use the high-speed copy facility of SAN storage solutions.

Top 10 Best Practices for SQL Server Maintenance for SAP

admin — Thu, 13 Sep 2007 02:54:00 GMT

Author: Takayuki Hoshino
Contributor: Juergen Thomas
Technical Reviewer: Sanjay Mishra

SQL Server provides an excellent database platform for SAP applications. The following recommendations provide an outline of best practices for maintaining SQL Server database for an SAP implementation.

	Perform a full database backup daily Technically there are no problems to backing up SAP databases online. This means that end users or nightly batch jobs can continue to use SAP applications without problems. SQL Server Backup consumes few CPU resources. However, SQL Server Backup does require I/O bandwidth because SQL Server will try to read every used extent to the backup device. Everything that is required for SAP (business data, metadata and ABAP applications etc) is included in one database named “<SID>”. Sometimes the time needed to take a full backup (generally a few hours) might become a problem, especially in SQL Server 2000 where no transaction log backups can be made while an Online Database Backup was performed. SQL Server 2005 does not have this issue. To create faster online backups using SAN Technology, SQL Server offers interfaces for SAN vendors to perform a Snapshot Backup or to create clones of a SQL Server database. However, backing up terabytes of data every night may overload the backup infrastructure. Another possibility would be to do differential backups of the SAP database on a daily basis and do a full database backup on the weekend only.
	Perform transaction log backup Every 10 to 30 minutes In case of a disaster happening on the production server, it is vital that the most recent status can be restored using online or differential database backups plus a series of transaction log backups which ideally cover as close as possible to the time of the disaster. For this purpose it is vital to perform transaction log backups on a regular basis. If you only create a transaction log backup every two hours, the in the case of a disaster, up to two hours of committed business transactions would not be able to be restored. Therefore it is vital to do transaction log backups often enough to reduce the risk of losing a large number of committed business transactions in case of a disaster. In many productive customer scenarios, a time frame of 10-30 minutes proved to be an acceptable frequency. However, in combination with SQL Server log shipping, you can create SQL Server transaction log backups even every two or five minutes. The finest granularity achievable is to perform SQL Server transaction log backups scheduled by SQL Agent every minute. Besides reducing the risk of losing business transactions, transaction log backups also truncate log data in the SQL Server transaction log, and reducing the possibility of the transaction log becoming full.
	Back up system partition in case of configuration changes Back up the system partition after any configuration changes. Use Windows Server 2003 Automated System Recovery (ASR), or other tools such as Symantec Ghost or SAN boot to restore the system partitions.
	Back up system databases in case of configuration changes Back up the system databases (master, msdb, model) after any configuration changes. In SQL Server 2005, the resource database does not need to be backed up because it does not experience any changes and is installed with the SQL Server 2005 installation.
	Run DBCC CHECKDB periodically (ideally before the full database backup) Ideally, a consistency check using DBCC CHECKDB sould be run before performing an online database backup. However, please note that DBCC CHECKDB is a very time and resource consuming activity that puts heavy workload on SAP production systems, especially on databases over one terabyte. On commodity hardware with a good I/O subsystem, I/O throughputs in the range of 100-150 GB/h can be achieved. Given such I/O throughputs, and the fact that there are many SAP databases up to 10 terabytes or more, it is clear that running a DBCC CHECKDB on a production system is not always practical. Therefore, many people choose not to run DBCC CHECKDB. Although all components of hardware and software have become more reliable over the last decade, physical corruptions can still happen. One reason for physical corruptions is a catastrophic power outage without having battery backup for hardware components. Another reason could be physical damage to connections or hardware components. In massive cases there is no other way than to go back to a backup and restore the SAP database and then apply all the transaction logs up to the most recent. However, to detect physical inconsistencies at an early state, or to know that the backup method is reliable, or to minimize impact of physical corruptions, the following three major measures should be considered: Consider running DBCC CHECKDB on a regular basis. This could be on a sandbox system that runs a restored image of the production environment. On such a system, time and resource consumption of DBCC CHECKDB would not be a concern and would not affect production users. Test actually restoring the SAP database from an online or differential and transaction log backup. The fact that a backup is on tape does not necessarily mean that it is consistent on tape or that it can be read from tape. Tape hardware or tape cassettes may fail over the years, and you do not want to be in a position where you have tapes that cannot be read anymore. Having a backup in a vault does not say anything about the ability to be able to restore in case of a disaster. The backup must also be proven to be readable. For databases with terabytes of volume, maintain a second copy of the database at the most recent status, using either log shipping or database mirroring. Both of these high-availability methods will de-couple hardware components and hence may provide a physical consistent image of the production database at a secondary site.
	Evaluate security patches monthly (and install them if they are necessary) For most of SAP customers, availability is the most important requirement. Especially if they need to serve a single SAP instance globally, they don’t want to stop and restart the SAP servers to apply security patches. Plus, some testing in these environments is definitely necessary before installing the security patches. Therefore one of realistic scenarios for SAP customers is carefully evaluating patches and reducing the frequency of patch installations, hopefully almost to zero. Filtering unnecessary packets, disabling unnecessary services, and so forth are good security measures. If you have real time anti-virus monitoring, it is recommended that you exclude the SQL Server database files (including data files, transaction log files, tempdb and other system database files) from real time monitoring. If you perform backups to disks, exclude database backup files as well as transaction log backup files.
	Evaluate update modules of hardware drivers and firmwares and install if necessary There have been critical issues due to bugs in hardware drivers and firmwares within the commodity servers. It is sometimes difficult to find this kind of issue within Microsoft, and furthermore hardware companies sometimes don’t provide enough support services to commodity server customers. So it is a customer’s responsibility to manage updates on drivers and firmwares regularly. Before updating the drivers on production commodity servers, thorough tests must be conducted on test and sandbox systems. Like nearly no other software component, a little flaw in a driver of an Host Bus Adapter (HBA) or SCSI card can be responsible for physical inconsistency within a database.
	Update statistics on the largest tables weekly or monthly SQL Server provides two options to keep the statistics current: auto create statistics and auto update statistics. These two options are ON by default. SAP recommends keeping them ON. There may be some cases where auto update statistics may not be able to provide satisfactory performance. A specific case came up in SAP BW. The issue was resolved by the functionality in SAP BW that is documented in SAP OSS note #849062. Please keep in mind that auto update statistics is run only on tables with more than 500 rows. In some very specific cases of data developing into one direction, it is recommended to explicitly run update statistics on specific columns of the table on a scheduled basis. However, you should not perform a general manual update statistics. If performance problems are analyzed and the root cause is found in an index, or some column statistics not being recent enough, then the solution often is simply to have a certain column or index statistics updated on a more frequent basis.
	Rebuild or defrag the most important indexes The impact of reorganizing tables and indexes on performance is highly dependent on the type of query that is executed and the I/O bandwidth that is available on the system. Simply going along measures like (1) Average page density < 80 percent or (2) Logical scan fragmentation > 40 percent as thresholds to start reorganizing are a waste of time and resources. Reasons are: Some SAP queuing tables will always show up as being highly fragmented A query reading a single row or a small number of rows which represents the majority of SAP queries do not benefit from reorganizing a table. If there is enough I/O bandwidth and memory for SQL Server on the database server, the impact of table fragmentation might be limited. Many people never reorganize tables to speed up query performance. However, there are also people who reorganize tables to compress them after they archived SAP data. Not all the tables are organized or sorted according to the archiving criteria. Hence it can happen that despite deleting 25 percent of a table, the table only decreased its volume by 10 percent. To maximize space reduction after archiving, you can run DBCC INDEXDEFRAG on the affected tables. DBCC INDEXDEFRAG will compress the data content on the pages of a table. DBCC INDEXDEFRAG treats every move of a bunch of rows to one page as a single transaction. Hence DBCC INDEXDEFRAG will result in many small transactions as opposed to creating an index which treats the entire index creation task as one large transaction. DBCC INDEXDEFRAG does not consume much CPU resources, but it does create significant I/O traffic. Therefore do not run too many DBCC INDEXDEFRAG commands in parallel. Completely reorganizing tables by re-creating their clustered indexes should not be done on large tables because this will generate huge amount of transaction log.
	Use a health check monitoring tool for performance, availability, and so forth Unplanned downtime depends on how quickly system failures are notified to administrators and how soon they can start the recovery process. For availability, SAP administrators should be aware that automatic failover mechanism of Microsoft Clustering Services (MSCS) or database mirroring (DBM) is able to provide continuous availability of the system. However, a failover itself will cause rollback of open transactions on the database side which again will cause rollbacks on business transactions on the SAP side. The impact of these batch processes breaking and data not being available might be serious (for example, with a payroll calculation). Therefore monitoring the system and notification after failovers can be vital to having interrupted SAP Business processes restarted as quickly as possible.

The document SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Performance, and Scalability (5.5 MB) describes best practices for tuning and configuring SAP on SQL Server 2005 in more detail.

Top 10 Lists

Top 10 Performance and Productivity Reasons to Use SQL Server 2008 for Your Business Intelligence Solutions

1. Scalability and Resource Management Enhancements in Reporting Services

2. Subspace Computations in Analysis Services

3. Pipeline Parallelism in Integration Services

4. Scalability and Availability Enhancements in Analysis Services

5. Optimized Design Experience in Analysis Services

6. New and Enhanced Authoring Capabilities and Visualizations in Reporting Services

Authoring Enhancements

Chart Enhancements

Gauges

7. Data Compression in the Relational Engine

8. Resource Governor in the Relational Engine

9. Minimally Logged Operations in the Relational Engine

10. Other Great Features

Star Query Enhancements

Partitioned Table Parallelism

The MERGE Statement

Change Data Capture

ExecutionLog2

Conclusion

Top 10 SQL Server 2008 Features for the Database Administrator (DBA)

Top 10 SQL Server 2008 Features for ISV Applications

Six Failover Clustering Benefits Realized from Migrating to SQL Server 2008

Reliable Setup

Improved availability with rolling upgrades

Availability with adding or removing nodes

Service SIDs instead of Domain Groups on Windows Server 2008

Windows server 2008 Integration improvements

ConfigurationFile.ini automatic generation

Top 10 SQL Server Integration Services Best Practices

Top 10 Best Practices for Building a Large Scale Relational Data Warehouse

Storage Top 10 Best Practices

Top SQL Server 2005 Performance Issues for OLTP Applications

Top 10 SQL Server 2005 Performance Issues for Data Warehouse and Reporting Applications

Top 10 Hidden Gems in SQL Server 2005

Analysis Services Query Performance Top 10 Best Practices

Optimize cube and measure group design

Define effective aggregations

Use partitions

Write efficient MDX

Use the query engine cache efficiently

Ensure flexible aggregations are available to answer queries.

Tune memory usage

Tune processor usage

Scale up where possible

Top 10 Best Practices for SQL Server Maintenance for SAP