SQL SERVER – Top 10 SQL Server 2008 Features for Independent Software Vendor Applications

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.

1. Data Compression It is possible to apply Row /Page data compression partition unit, minimize Disk I / O and efficiently use buffer memory. Depending on the type of data and the distribution of data within the page, it typically provides a compression ratio of 1/2 to 1/7.

2. Backup Compression When creating backups, such as full backup generated by compression and, Disk I / O backup to minimize / can improve recovery performance. Depending on the type of data and the distribution of data within the page, it typically provides a compression ratio of 1/2 to 1/7.  

3. Transparent Data Encryption (TDE) Provides encryption for data files or backup files. When Page I / O occurs, the data is encrypted /decrypted. Because TDE does not require any code modification of the application, it is easily applicable.

4. Data Collector & Management Data Warehouse (MDW) Performance counters and dynamic management views (DMVs ) can be periodically collected to provide a data warehouse for performance management. Server usage information, disk space information, a report such as a query performance information is provided by default if you want to Report Services can be customized based.

5. Lock the Escalation the Enhancement 5
SQL Server in the Database Engine Lock escalation adjust and , sometimes, unexpected Lock escalation is Deadlock may occur such .
Starting with SQL Server 2008, itis possible to control the occurrence of lock escalation on aper-table basis through the ALTER TABLE statement .

6. Query Plan Freezing
ISV applications sometimes require that certain queries always use the same query execution plan to ensure consistent performance .
SQL Server 2008 ‘s Query Plan Freezing With the feature, you can avoid if the query plan by changing the parameter sniffing or lack of statistical information management in advance.

7. Optimize for Ad-Hoc Workloads Option SQL Server 2008provides a new option called Optimized for Ad-Hoc Workloads in the configuration option(sp_configure). If you enable this option, Plan cache can be used more efficiently in ISV applications where the usage rate of Ad-hoc queries is high.

8. Resource Governor It is a resource governor that is useful for environments where various workloads such as server consolidation are executed, or where there is high importance for certain tasks. Resource Governor Using a Workload per CPU / Memory can be dynamically distributed server resources such as may, consistent with this, you can ensure performance.

9. Table-Valued Parameter This is a function that creates a table variable and inserts the data into the variable as if it were a temporary table and then passes this variable as a parameter to the stored procedure or function. Manner than in the past was passed to create a temporary table, I / O and improve the performance and effectiveness, and can be used with excellent bulk data loading methods, depending on the scenario.

10. Filestream video is saved as a separate file server for streaming, such as music, large images, and BLOB data effectively SQL Server stored in the /Management / This function can be used. It is effective when processing 1MB or more of BLOB data ( small size is not recommended). You can directly stream binary files through the Win32 Streaming API.

Reference: Pinal Dave (https://blog.sqlauthority.com)