Monday, August 10, 2009

Linked Servers


A linked server configuration allows Microsoft® SQL Server™ to execute commands against OLE DB data sources on different servers. Linked servers offer these advantages:


  1. Remote server access.
  2. The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
  3. The ability to address diverse data sources similarly.



When setting up a linked server, register the connection information and data source information with SQL Server. After registration is success, that data source can always be referred to with a single logical name.

You can manage a linked server definition with stored procedures or through SQL Server Enterprise Manager:

With stored procedures:

- Create a linked server definition using sp_addlinkedserver. To view information about the linked servers defined in a given instance of SQL Server, use sp_linkedservers. For more information, see sp_addlinkedserver and sp_linkedservers.

- Delete a linked server definition using sp_dropserver. You can also use this stored procedure to remove a remote server. For more information, see sp_dropserver.

With SQL Server Enterprise Manager:

- Create a linked server definition using the SQL Server Enterprise Manager console tree and the Linked Servers node (under the Security folder). Define the name, provider properties, server options, and security options for the linked server. For more information about the various ways a linked server can be set up for different OLE DB data sources and the parameter values to be used, see sp_addlinkedserver.

- Edit a linked server definition by right-clicking the linked server and clicking Properties.

- Delete a linked server definition by right-clicking the linked server and clicking Delete.

Saturday, August 8, 2009

How Long Stored Procedure stay in Sql Server 2000 cache?

SQL Server 2000

once the execution plan is generated for a Stored Procedure, it stays in the procedure cache. Lazy writer only keep looking and throwing out unused plans out of the cache "only when space is needed in cache".

Below are some documented and undocumented DBCC commands available in SQL Server 2000 to deal and find more information about SQL Server cache.

To Monitor the cahce:

DBCC SQLPERF (LRUSTATS)
DBCC CACHESTATS
DBCC MEMORYSTATUS
DBCC PROCCACHE
To clean the cache:
DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

You can read more from SQL 2000 Topic under "Lazy Writer", 'Freeing and Writing Buffer Pages' at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_8unn.asp