Databases Reference
In-Depth Information
If the SQL Server service is the only server service running on your system, it is fine to have
the default value for max server memory. However, if multiple services are running on the
server, and if the domain controller uses maximum memory, SQL Server will be starved of
memory; on the other hand, if SQL Server uses maximum memory, the domain controller
will be starved of memory. Therefore, it is good to assign proper memory to SQL Server,
so it doesn't run out of memory, but it also doesn't consume memory unnecessarily.
Configuring "Optimize for Ad hoc Workloads"
Execution of any query or stored procedure for the first time creates an execution plan, which
is stored in SQL Server 's procedure cache memory. It happens many times that we execute
a simple query once, which is not even going to be used again anytime soon and it may never
run again in future too even execution plan generated for that query will consume space
in procedure cache. You may run out of cache sometimes, due to lack of memory, which
affects performance. This was really a big issue till SQL Server 2005. In order to remedy
this, Microsoft introduced "Optimize for Ad hoc Workloads" in SQL Server 2008, and it is
still available in SQL Server 2012. This setting is instance-wide in SQL Server.
In one of my performance tuning consultation projects, I had observed
the company's SQL developer making and testing a query directly on the
production server. If they didn't get the required results, they'd change
the query and re-test it on the production server, which was creating
immense pressure on the procedure cache. I pointed out and explained
the side effects of their testing on the production server; they then
cleared the procedure cache and changed their habits. I hope that none
of the readers of this topic inds themselves in this situation.
Getting ready
Before moving further, let us clean up the procedure cache and buffer on the testing server
that we are using.
1.
Before cleaning up the cache and buffer, let's look at how many rows come from our
saved plan DMV:
SELECT
CP.usecounts AS CountOfQueryExecution
,CP.cacheobjtype AS CacheObjectType
,CP.objtype AS ObjectType
,ST.text AS QueryText
FROM
sys.dm_exec_cached_plans AS CP
CROSS APPLY
Search WWH ::
Custom Search