Explain what the query governor is and how to configure it to prevent system res

Question

Explain what the query governor is and how to configure it to prevent system resources from being consumed when execution long-term queries. Provide example using the "University" or "bank" database ?
Note: using this schema

Paragraph Schema Diagram for University Database takes student ID aTte - dept-name fof crad ID sec id ester ear grade section course id course course id title credits department advisor dept namedept name building budget time slot building time slot id room no start time end time instructor ID narrie dept name salary prereg classroom building capacity prereg id teaches ear

Explanation / Answer

The query governor does not allow the execution of any query that has a running time that exceeds a specific query cost. The query cost is the estimated time, in seconds, required to execute a query, and it is estimated prior to execution based in an analysis by the query engine. By default, the query governor is turned off, meaning there is no minimum cost.

To activate the query governor, complete the following steps:

1. In the server properties dialogue box, go to the connections page.

2. Select the option Use Query Governor to prevent long running queries.

3. In the box below the option, type a maximum Query cost limit. The valid range is 0 through 2,147,483,647. A value of 0 disables the query governor; any other value sets a maximum Query cost limit.

4. Click OK.

With sp_configure "query governor cost limit", <limit>

You can also set a per-connection query cost limit in transact-SQL using the following statement:

set query_governor_cost_limit <limit>

Note:

Before you activate the query governor, you should use the query view to estimate tthe cost of current quries you are running on the server. This will give you a good idea of a value to use for the maximum Query cost. You can also use quey view to optimize queries.

N

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.