Clean Cache in sql

on

SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer

March 23, 2007 by pinaldave
Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache. If observing through SQL Profiler, one can watch the Cache Remove events occur as DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.
Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
est queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
Sql New Features :

Grouping Sets
Grouping Sets is an extension to the GROUP BY clause that lets users define multiple grouping in the same query. Grouping Sets produce a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster.
Example:
SELECT year (order_date) AS Year, quarter (order_date) AS Quarter, COUNT (*) AS Orders FROM sales_order GROUP BY GROUPING SETS ((Year, Quarter), (Year))
ORDER BY Year, Quarter   


SQL merge operators

SQL merge operators are table target operators that emulate a DB2® MERGE command. A merge is a combination of the SQL INSERT and UPDATE operations, otherwise known as an "upsert."
The SQL merge operator loads a DB2 table by checking the target table for a row that matches each row from the input data set. Two conditions define the operation of a SQL merge operator. The first condition, called the input match, matches rows from the input with existing rows in the target table. If no matching row is found, the row is inserted as a new row. If an input match is found, the second condition, called the target table match, checks a value in each input row against the associated row in the target table. If the condition matches, the row in the target table is updated. Separate column mapping properties allow you to choose one set of columns for any rows that meet the insert criteria and a different set of columns for the updates to act upon.
The following SQL MERGE statement is an example of the code that is generated from a simple data flow that ends with a SQL merge operator. In this case, the source table is STAGE_PRCHS_PROFILE and the merge target table is NEW_PRCHS_PROFILE. The input match and target table match conditions are highlighted in bold.
MERGE INTO DB2ADMIN.NEW_PRCHS_PROFILE
      	USING (
      	SELECT 
      		STR_IP_ID AS STR_IP_ID, 
      		PD_ID AS PD_ID, 
      		TIME_ID AS TIME_ID, 
      		NMBR_OF_MRKT_BSKTS AS NMBR_OF_MRKT_BSKTS, 
      		NUMBER_OF_ITEMS AS NUMBER_OF_ITEMS, 
      		PRDCT_BK_PRC_AMUNT AS PRDCT_BK_PRC_AMUNT, 
      		CST_OF_GDS_SLD_CGS AS CST_OF_GDS_SLD_CGS, 
      		SALES_AMOUNT AS SALES_AMOUNT
      	FROM 
      		 DB2ADMIN.STAGE_PRCHS_PROFILE INPUT_051) INPUT_01
      	ON (DB2ADMIN.NEW_PRCHS_PROFILE.STR_IP_ID = INPUT_01.STR_IP_ID)
      	WHEN MATCHED AND DB2ADMIN.NEW_PRCHS_PROFILE.SALES_AMOUNT > 10000 THEN 
      	UPDATE SET 
      	(STR_IP_ID, PD_ID, TIME_ID, NMBR_OF_MRKT_BSKTS, NUMBER_OF_ITEMS, 
PRDCT_BK_PRC_AMUNT, CST_OF_GDS_SLD_CGS, SALES_AMOUNT)
      	 = (INPUT_01.STR_IP_ID, INPUT_01.PD_ID, INPUT_01.TIME_ID, 
INPUT_01.NMBR_OF_MRKT_BSKTS, INPUT_01.NUMBER_OF_ITEMS, 
INPUT_01.PRDCT_BK_PRC_AMUNT, INPUT_01.CST_OF_GDS_SLD_CGS, INPUT_01.SALES_AMOUNT)
      	WHEN NOT MATCHED THEN 
      	INSERT 
      	(STR_IP_ID, PD_ID, TIME_ID, NMBR_OF_MRKT_BSKTS, NUMBER_OF_ITEMS, 
PRDCT_BK_PRC_AMUNT, CST_OF_GDS_SLD_CGS, SALES_AMOUNT)
      	VALUES (INPUT_01.STR_IP_ID, INPUT_01.PD_ID, INPUT_01.TIME_ID, 
INPUT_01.NMBR_OF_MRKT_BSKTS, INPUT_01.NUMBER_OF_ITEMS, 
INPUT_01.PRDCT_BK_PRC_AMUNT, INPUT_01.CST_OF_GDS_SLD_CGS, INPUT_01.SALES_AMOUNT)
      	ELSE IGNORE

Comments

Post a Comment