Sql-server – Memcached vs SQL Server cache

cachingmemcachedperformancesql server

I've been reading a lot of articles that suggest putting a Memcached (or Velocity, etc) in front of a database is more efficient than hitting the database directly. It will reduce the number of hits on the database by looking up the data in a memory cache, which is faster than hitting the database.

However, SQL Server has it's own memory cache for objects in the database. When data is retrieved, SQL Server maintains its cache and will (if necessary) pull the row from it's memory and not hit the disk.

So if SQL Server has it's own cache, what is the benefit of an external Memcached (or similar) server?

Most of the articles I have been reading are around social networking sites, that mostly use MySql. However, an article about MySpace, that uses SQL Server, suggests caching is used on that system as well.

This article explains when caching should be used and this article is a counterpoint.

Best Answer

So if SQL Server has it's own cache, what is the benefit of an external Memcached (or similar) server?

Yes SQL Server has its own cache but he caches only:
- Query plans
- pages from the database files

but he does NOT cache:
- results from a query

e.g. you have a complex query which uses some aggregation on a lot of data ( think of: how many different countries we have in our customer database : SELECT DISTINCT Country from Customers GROUP BY country )

SQL Server will scan th WHOLE customer table, but your resultset will only a few entries long. When you reissue your query, SQL Server will reuse the query plan and will rescan the customer table, ( and if you are lucky the pages are still in memory )

When you use memcached you may store the few rows of your resultset and reuse them over and over again without connecting to the database server. So it takes some load from your database server.
NOTE: Beware of some stale data, if your data changes on the 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.

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

Best Answer

Related Solutions

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

Syntax:

Example:

Notes:

Sql – How to return only the Date from a SQL Server DateTime datatype

Related Topic