Who Controls Database Resources?
With the advent of SQL Server 2008 and later the answer is increasingly, “You.” That is because Microsoft introduced the Resource Governor capability in SQL Server 2008. This capability allows you to define resource groups and allocate work on your server to them. This grouping allows you to manage precious resources on your SQL Server machine allocating more resources to some kinds of queries, and perhaps less to other kinds of queries.
Actually, there is nothing that requires one group to have more resources than another. The difference is that queries will complete for Governor restricted resources only with other queries within their own resource pool. So, even if you wish each kind of query to share a pool of equal capacity, all queries are not competing with every other query on the machine; only within their pool.
Resource pools are user configured. Queries are assigned to a resource pool by means of a scalar function. You can use SQL Functions exposing properties about queries to determine to which pool the query should be assigned through your scalar function. For example, you could assign a query based on the APP_NAME() function or the SUSER_NAME() function. These functions return information specific to the process on which these queries are being executed.
This is a great technique for segregating CPU and Memory resources so that online processes are not overwhelmed with runaway ad hoc queries, or reports that do heavy lifting.
Have you used the Resource Governor? Is it working as described by the Microsoft gurus? Have you found that breaking work up into pools results in some queries being starved while many possible resources could be available in a pool that is under-utilized? Share your experience with us by writing btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
XML Support and SQL Server (Part 3)
This article is the third part of the XML support and SQL Server article series. In this part, I’ll first talk about what fundamental problems we have in integration XML and SQL Server. Then, I’ll cover what storage options we have in SQL Server to store the XML data in SQL Server. Finally, I’ll demonstrate how we can open, create and edit XML documents using SQL Server Management Studio.
Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues
read more)