Is the resource governor intelligent enough to allow us to specify that it should use X for a certain time period and Y for a different time period? For example, with a job that should run overnight, we want to ensure it finishes before business hours start the next day. So, we would allocate more resource to it. However, if it fails to finish by 7am, can it automatically adjust to a lower resource utilization to ensure no negative impact to production services?

certainly you can manage your resource in a better way.

The below link gives a more insight into your scenario

http://blog.sqlauthority.com/2012/06/04/sql-server-simple-example-to-configure-resource-governor-introduction-to-resource-governor/

--Prashanth

• Proposed as answer by Sofiya LiMicrosoft contingent staff, Moderator 4 hours 36 minutes ago

Hi HuuM,

Resource Governor is a new technology in SQL Server  that enables you to manage SQL Server workloads and resources by specifying limits on resource consumption by incoming requests. However, the Resource Governor does not automatically classify SQL Server requests. Usually, all requests are handled by the default workload group. If you want to use user-defined workload groups, you'll have to create classification functions and then register those functions with the Resource Governor.

For more information about handling workloads on SQL Server with Resource Governor, you can review the following article.
http://www.mssqltips.com/sqlservertip/1720/handling-workloads-on-sql-server-2008-with-resource-governor/

Regards,
Sofiya Li