Databases Reference
In-Depth Information
LANGUAGE
NO_BROWSETABLE
NUMERIC_ROUNDABORT
QUOTED_IDENTIFIER
DBCC FREEPROCCACHE and DBCC FLUSHPROCINDB
Never run the
DBCC
FREEPROCCACHE
and
DBCC
FLUSHPROCINDB
commands on a production server.
DBCC
FREEPROCCACHE
clears the
entire SQL Server procedure cache, whereas
DBCC
FLUSHPROCINDB
clears the procedure cache for a given database. Clearing the
procedure cache causes queries to compile again in their subsequent
executions, which degrades the performance of SQL Server.
As we saw, there can be a number of reasons for compilations and recompilations; we must
identify the correct reasons for a recompilation issue.
In this chapter, we will see how we can investigate and analyze recompilation issues.
Monitoring compilations and recompilations
at instance level using Reliability and
Performance Monitor
Imagine that you have set up your production SQL server that is hosting several databases
used by different applications. Initially your SQL server responds smoothly, but as the number
of query requests increases, becoming larger day by day, and when it reaches several hundred
per second, you notice that queries take a little longer to execute and your SQL server CPU
usage is higher than what you expected.
As you may know, there can be a number of reasons for SQL server to respond poorly, such as:
F
Your physical database design and database file placement is not optimized
F
Databases are missing proper indexes
F
Queries are not optimized and are poorly written
F
Statistics are out-of-date, and query optimizer is not able to generate an optimum plan
F
Queries face blocking issues
F
You need to upgrade the CPU or increase the number of CPU cores
F
The server does not have enough memory
F
There is a problem with the disk I/O system
Search WWH ::
Custom Search