Sql-server – Prevent Caching in SQL Server

cachingsql serversql-server-2005

Having looked around the net using Uncle Google, I cannot find an answer to this question:

What is the best way to monitor the performance and responsiveness of production servers running IIS and MS SQL Server 2005?

I'm currently using Pingdom and would like it to point to a URL which basically mimics a 'real world query' but for obvious reasons do not want the query to run from cache. The URL will be called every 5 minutes.

I cannot clear out the cache, buffers, etc since this would impact negatively on the production server. I have tried using a random generated number within the SELECT statement in order to generate unique queries, but the cached query is still used.

Is there any way to simulate the NO_CACHE in MySQL?

Regards

Best Answer

To clear the SQL buffer and plan cache:

DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO

A little info about these commands from MSDN:

Use DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. (source)

Use DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. (source)

Introduction

The correct minimum set of headers that works across all mentioned clients (and proxies):

Cache-Control: no-cache, no-store, must-revalidate
Pragma: no-cache
Expires: 0

The Cache-Control is per the HTTP 1.1 spec for clients and proxies (and implicitly required by some clients next to Expires). The Pragma is per the HTTP 1.0 spec for prehistoric clients. The Expires is per the HTTP 1.0 and 1.1 specs for clients and proxies. In HTTP 1.1, the Cache-Control takes precedence over Expires, so it's after all for HTTP 1.0 proxies only.

If you don't care about IE6 and its broken caching when serving pages over HTTPS with only no-store, then you could omit Cache-Control: no-cache.

Cache-Control: no-store, must-revalidate
Pragma: no-cache
Expires: 0

If you don't care about IE6 nor HTTP 1.0 clients (HTTP 1.1 was introduced in 1997), then you could omit Pragma.

Cache-Control: no-store, must-revalidate
Expires: 0

If you don't care about HTTP 1.0 proxies either, then you could omit Expires.

Cache-Control: no-store, must-revalidate

On the other hand, if the server auto-includes a valid Date header, then you could theoretically omit Cache-Control too and rely on Expires only.

Date: Wed, 24 Aug 2016 18:32:02 GMT
Expires: 0

But that may fail if e.g. the end-user manipulates the operating system date and the client software is relying on it.

Other Cache-Control parameters such as max-age are irrelevant if the abovementioned Cache-Control parameters are specified. The Last-Modified header as included in most other answers here is only interesting if you actually want to cache the request, so you don't need to specify it at all.

How to set it?

Using PHP:

header("Cache-Control: no-cache, no-store, must-revalidate"); // HTTP 1.1.
header("Pragma: no-cache"); // HTTP 1.0.
header("Expires: 0"); // Proxies.

Using Java Servlet, or Node.js:

response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate"); // HTTP 1.1.
response.setHeader("Pragma", "no-cache"); // HTTP 1.0.
response.setHeader("Expires", "0"); // Proxies.

Using ASP.NET-MVC

Response.Cache.SetCacheability(HttpCacheability.NoCache);  // HTTP 1.1.
Response.Cache.AppendCacheExtension("no-store, must-revalidate");
Response.AppendHeader("Pragma", "no-cache"); // HTTP 1.0.
Response.AppendHeader("Expires", "0"); // Proxies.

Using ASP.NET Web API:

// `response` is an instance of System.Net.Http.HttpResponseMessage
response.Headers.CacheControl = new CacheControlHeaderValue
{
    NoCache = true,
    NoStore = true,
    MustRevalidate = true
};
response.Headers.Pragma.ParseAdd("no-cache");
// We can't use `response.Content.Headers.Expires` directly
// since it allows only `DateTimeOffset?` values.
response.Content?.Headers.TryAddWithoutValidation("Expires", 0.ToString());

Using ASP.NET:

Response.AppendHeader("Cache-Control", "no-cache, no-store, must-revalidate"); // HTTP 1.1.
Response.AppendHeader("Pragma", "no-cache"); // HTTP 1.0.
Response.AppendHeader("Expires", "0"); // Proxies.

Using ASP.NET Core v3

// using Microsoft.Net.Http.Headers
Response.Headers[HeaderNames.CacheControl] = "no-cache, no-store, must-revalidate";
Response.Headers[HeaderNames.Expires] = "0";
Response.Headers[HeaderNames.Pragma] = "no-cache";

Using ASP:

Response.addHeader "Cache-Control", "no-cache, no-store, must-revalidate" ' HTTP 1.1.
Response.addHeader "Pragma", "no-cache" ' HTTP 1.0.
Response.addHeader "Expires", "0" ' Proxies.

Using Ruby on Rails:

headers["Cache-Control"] = "no-cache, no-store, must-revalidate" # HTTP 1.1.
headers["Pragma"] = "no-cache" # HTTP 1.0.
headers["Expires"] = "0" # Proxies.

Using Python/Flask:

response = make_response(render_template(...))
response.headers["Cache-Control"] = "no-cache, no-store, must-revalidate" # HTTP 1.1.
response.headers["Pragma"] = "no-cache" # HTTP 1.0.
response.headers["Expires"] = "0" # Proxies.

Using Python/Django:

response["Cache-Control"] = "no-cache, no-store, must-revalidate" # HTTP 1.1.
response["Pragma"] = "no-cache" # HTTP 1.0.
response["Expires"] = "0" # Proxies.

Using Python/Pyramid:

request.response.headerlist.extend(
    (
        ('Cache-Control', 'no-cache, no-store, must-revalidate'),
        ('Pragma', 'no-cache'),
        ('Expires', '0')
    )
)

Using Go:

responseWriter.Header().Set("Cache-Control", "no-cache, no-store, must-revalidate") // HTTP 1.1.
responseWriter.Header().Set("Pragma", "no-cache") // HTTP 1.0.
responseWriter.Header().Set("Expires", "0") // Proxies.

Using Clojure (require Ring utils):

(require '[ring.util.response :as r])
(-> response
  (r/header "Cache-Control" "no-cache, no-store, must-revalidate")
  (r/header "Pragma" "no-cache")
  (r/header "Expires" 0))

Using Apache .htaccess file:

<IfModule mod_headers.c>
    Header set Cache-Control "no-cache, no-store, must-revalidate"
    Header set Pragma "no-cache"
    Header set Expires 0
</IfModule>

Using HTML:

<meta http-equiv="Cache-Control" content="no-cache, no-store, must-revalidate">
<meta http-equiv="Pragma" content="no-cache">
<meta http-equiv="Expires" content="0">

HTML meta tags vs HTTP response headers

Important to know is that when an HTML page is served over an HTTP connection, and a header is present in both the HTTP response headers and the HTML <meta http-equiv> tags, then the one specified in the HTTP response header will get precedence over the HTML meta tag. The HTML meta tag will only be used when the page is viewed from a local disk file system via a file:// URL. See also W3 HTML spec chapter 5.2.2. Take care with this when you don't specify them programmatically because the webserver can namely include some default values.

Generally, you'd better just not specify the HTML meta tags to avoid confusion by starters and rely on hard HTTP response headers. Moreover, specifically those <meta http-equiv> tags are invalid in HTML5. Only the http-equiv values listed in HTML5 specification are allowed.

Verifying the actual HTTP response headers

To verify the one and the other, you can see/debug them in the HTTP traffic monitor of the web browser's developer toolset. You can get there by pressing F12 in Chrome/Firefox23+/IE9+, and then opening the "Network" or "Net" tab panel, and then clicking the HTTP request of interest to uncover all detail about the HTTP request and response. The below screenshot is from Chrome:

Chrome developer toolset HTTP traffic monitor showing HTTP response headers on stackoverflow.com

I want to set those headers on file downloads too

First of all, this question and answer are targeted on "web pages" (HTML pages), not "file downloads" (PDF, zip, Excel, etc). You'd better have them cached and make use of some file version identifier somewhere in the URI path or query string to force a redownload on a changed file. When applying those no-cache headers on file downloads anyway, then beware of the IE7/8 bug when serving a file download over HTTPS instead of HTTP. For detail, see IE cannot download foo.jsf. IE was not able to open this internet site. The requested site is either unavailable or cannot be found.

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.