Sunday, May 29, 2011

Minimally Logged Inserts in sql server 2008

This is another great feature that was introduced in sql server 2008. This is one of the enhanced features of ‘SELECT INTO ‘ statements. All you need to do is to make sure that the following conditions are met

  1. ensure that the database recovery model is bulk-logged / simple
  2. enable the Trace flag 610.
  3. insert the data ordered by the clustered index

So what is exactly happening behind the scenes ? Initial inserts may be fully logged if the data pages they are filling already contain data, but however any new data pages added to the table will be minimally logged as long as the above 3 criteria are satisfied.

Here is an example

   1: DBCC TRACEON (610)



   2: INSERT INTO TargetTable



   3: SELECT * 



   4: FROM sourceTable



   5: ORDER BY <ClusteredIndex>



   6: DBCC TRACEOFF (610)


Monday, May 23, 2011

Parameter Sniffing

When a stored procedure is first executed SQL Server looks at the input parameters and uses this as guidance to build the query plan. This is known as "parameter sniffing". This is good as long as the input parameters for the first invocation are typical for future invocations. But if that is not the case this will cause performance problems.

Okay, I understand parameter sniffing, How should I handle it ?

There are different approaches to handle parameter sniffing. but we need an example to work with.

CREATE PROCEDURE dbo.getShippingInfo
@OrderID int
AS
BEGIN  
	SET NOCOUNT ON    
	SELECT CustomerName, Address, zipCode, province, country    
	FROM dbo.Orders    
	WHERE OrderID = @OrderID 
END




1. Replace parameters with local variables


This solution is based on assigning the stored procedure parameters to local variables and then using the local variables in the query. This works because SQL Server is not sniffing local variables and using the local variables in place of parameters forces plan generated based on statistics (in effect this disables parameter sniffing).





   1: CREATE PROCEDURE dbo.getShippingInfo



   2: @OrderID int



   3: AS



   4: BEGIN  



   5:     SET NOCOUNT ON



   6:     DECLARE @spOrderID int =  @OrderID   



   7:     SELECT CustomerName, Address, zipCode, province, country    



   8:     FROM dbo.Orders    



   9:     WHERE OrderID = @spOrderID 



  10: END




2.Execute using WITH RECOMPILE


This solution forces recompile of the stored procedure on each run, that way forcing a fresh query plan for the current parameters. Note that this will recompile all statements inside the stored procedure.





   1: exec dbo.getShippingInfo @orderID = 12345 WITH RECOMPILE




3.Query hint RECOMPILE



SQL Server 2005 offers the new query hint RECOMPILE which will force recompilation of the individual query. This method is better than the prior method because recompilation will affect only one statement and all other queries in the stored procedure will not be recompiled.





   1: CREATE PROCEDURE dbo.getShippingInfo



   2: @OrderID int



   3: AS



   4: BEGIN  



   5:     SET NOCOUNT ON



   6:     SELECT CustomerName, Address, zipCode, province, country    



   7:     FROM dbo.Orders    



   8:     WHERE OrderID = @OrderID 



   9:     OPTION (RECOMPILE)



  10: END




4.Query hint OPTIMIZE FOR


Another new query hint in SQL Server 2005 is OPTIMIZE FOR. It allows specifying a constant that will be used to optimize the query plan instead of the variable. This could be useful if it is known that particular selective value is frequently used to invoke the stored procedure. However, any other parameter value will suffer the same performance problems.





   1: CREATE PROCEDURE dbo.getShippingInfo



   2: @OrderID int



   3: AS



   4: BEGIN  



   5:     SET NOCOUNT ON



   6:     SELECT CustomerName, Address, zipCode, province, country    



   7:     FROM dbo.Orders    



   8:     WHERE OrderID = @OrderID 



   9:     OPTION (OPTIMIZE FOR UNKNOWN)



  10: END



  11:  




Plan Guides


Plan guides in SQL Server 2005 provide the opportunity to optimize a query without changing the actual code of the query. This is especially useful when dealing with third party vendor applications where access to code may not be available. A plan guide allows associating query hints with a query without changing the query.





   1: EXEC sp_create_plan_guide      



   2:     @name = N'SolveParameterSniffing',     



   3:     @stmt = N'SELECT CustomerName, Address, zipCode, province, country FROM dbo.Orders WHERE OrderID = @OrderID',     



   4:     @type = N'OBJECT',     



   5:     @module_or_batch = N'getShippingInfo',     



   6:     @params = NULL,     



   7:     @hints = N'OPTION (RECOMPILE)';




USE PLAN query hint


Another plan stability feature in SQL Server 2005 is the USE PLAN "xml_plan" query hint, which allows forcing the use of a specific plan every time the query is run.

Thursday, May 5, 2011

find the tables having composite clustered index

script to find the tables having composite clustered index

;WITH cte AS (
SELECT   OBJECT_NAME(i.object_id) NAME, type_desc, i.index_id ,c.name columnName ,p.rows 
FROM sys.indexes i 
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c on ic.column_id = c.column_id AND c.object_id = i.object_id
WHERE i.type = 1 
AND OBJECT_NAME(i.object_id) NOT LIKE  'sys%'
),
cte2 AS (
SELECT *, rn = ROW_NUMBER()OVER ( PARTITION BY NAME ORDER BY columnName desc )
FROM cte 
)
SELECT DISTINCT name, rows 
FROM cte2 b 
WHERE EXISTS (SELECT 1 FROM cte2 a WHERE a.name = b.name AND a.rn >=2 )
ORDER BY 2 

Wednesday, May 4, 2011

Historic information – sql backups

Need to find out the historic information of backups , try this script

 

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  
ORDER BY  
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_finish_date