Sunday, July 29, 2007

SQL Server - Clearing Cache

For analyzing a query performance - we need to clear the SQL Server cache (both data and execution plans).

To clear all data from cache - use command DBCC DROPCLEANBUFFERS;
Check out for more details.

To clear all execution plans from cache - use command DBCC FREEPROCCACHE;
Check out for more details.

To clear execution plans of a particular database from cache - use command DBCC FLUSHPROCINDB(databaseId). FLUSHPROCINDB is an undocumented command.

Tuesday, July 24, 2007

Design Question on SQL Server Transaction Replication and Table Partitions


OLTP database (on SQL Server 2005) accessed by an online processing system and a reporting application as shown in the diagram.

1) The online processing system needs to access only subset of records (recent records).
2) The reporting application needs complete set of records and should be able to access them in almost real time.
3) For better performance, security and logical segregation – we need to have online processing system and Reporting application access different instances of the database.


To satisfy Requirement 1:
a) Partition transaction table.
b) Switch old partitions out when they are no longer needed. (Switching partitions out for getting rid of old records is much efficient than executing DELETE statement.)
c) Online Processing System to access partition tables.

To satisfy Requirement 2:
a) Create insert / update trigger on the Partitioned table
b) Insert / Update on Full Table for every Insert / Update on Partitioned table.
c) Triggers aren’t invoked during partition switching of Partitioned Table – hence all records will remain in Full Table.

To satisfy Requirement 3:
a) Setup Transaction Replication between 2 SQL Instances
b) Replicate the Full Table.
c) Reporting application to access the replicated Full Table.

Questions / Issues in this approach:
a) Is it advisable to use triggers in production systems. Are there any known drawbacks.
b) Inserts / Updates to Partitioned Table have slowed down because of insert / update triggers associated with Partitioned Table.
c) When SQL Replication is enabled – the performance of the system still goes down – Tested by having the distributor with Publisher and with Subscriber. Having the Distributor with Subscriber is found to be performing little better than having the Distributor with Publisher.

Your comments / opinions on this approach is welcome.

Are there any better approaches for satisfying the above listed requirements without hampering the performance of Online Processing System.