Systems Engineering and RDBMS

Archive for July 21st, 2007

New Features in SQL Server 2008 – I

Posted by decipherinfosys on July 21, 2007

We have blogged before about the new upcoming release of SQL Server. You can search for “Katmai” or “SQL Server 2008” on this blog for more information. There are a lot of new features that are being introduced in this release as well as improvements to the existing features like the SSIS pipeline improvements and SSIS persistent look-ups. In our blog posts, we will start covering some of those new features as we start playing with the CTP’s. One of the new features that is going to be introduced is the Resource Governor. The resource governor allows the DBA to define resource limits and priorities for different workloads which will help in enabling concurrent workloads to provide a consistent performance to the applications. This feature has been available in Oracle for quite some time now and has been available in IIS 6.0 as well. This is a very nice feature to have since you will now be able to allocate and limit the usage of SQL Server resources based on priorities and the workload needs of different applications.

In applications that allow the adhoc query formation against the databases (decision support systems or adhoc end user reporting for example) will benefit from it and at the same time if the instance is being used for several applications, one can throttle the usage by creating policies for usage. You should be able to do this for an application or a user. More details from MSFT are still to come through but if this feature is going to be anything like the Oracle’s database resource manager, then one should be able to get these benefits (this is just a wish list):

1) Should be able to set policies based on different criteria like the machine from where the connections are coming from, the type of application, the user name, group of users etc.

2) After policy configuration, one should be able to guarantee specific applications and users a minimum amount of processing resources regardless of the load on the system and the number of users on the system.

3) One should be able to set up policies to be able to distribute available processing resources by allocating percentages of CPU time to different applications and/or different users. For example: In a data warehousing application, a higher percentage of the processing power can be given to the ROLAP (Real Time OLAP) application as compared to the batch jobs that happen only during the night.

4) One should be able to decrease the parallelism execution plan generation for any operation performed by an user or members of a group of users.

5) Dynamic management of changing the allocation of resources without requiring a shutdown or re-start.

6) Throttle and log the execution of certain operations that violate the policy settings. Proper error reporting and logging is necessary for troubleshooting the issues.

7) Should be able to allow for automatic switching of users from one group to a fallback group based on the defined criteria in the policies. Say, a higher priority is assigned to a particular application or user and the session executed by that application is executing for more than the specified threshold, that particular session should be allowed to automatically switch to another group of users with different resource requirements. This will ensure that they higher priority tasks even though they reached their threshold get to completion.

8 ) Allowing for creation of thresholds on connections per application. Any additional connections should be allowed to queue with a timeout limit that should be configurable.

Posted in SQL Server | 2 Comments »