Sunday, September 27, 2015

SQL Timeout Exception Prevention and Tuning Stored Procedures


Prevent Stored Procedure Timeout

Write efficient Stored Procedures

Prevent More Disk Reads

Prevent Dead Locks


System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.


The following are the collection of my learning as a SQL developer.

Because of large data on the tables, the join operations and other operations will take up lot of time hence causing the application to time out, also reducing the performance.


Parameter Sniffing
In short, parameter sniffing is one exec plan for all parameter combinations. 

- This option causes the compilation process to ignore the value for the specified variable and use the specified value instead.
- Unlike the other workaround (Option(Recompile)), this option avoid the overhead of recompiling the query every time.


This is the most common problem, The workaround is to declare the input params  again with different variable name and use the
 new variable through out the SP.

In the Example @local_SetupId

CREATE PROCEDURE [dbo].[pr_get_user_ids]
(
@in_SetupId int
)
As
begin
Declare@local_SetupIdint=@in_SetupId
Select UserIdfrom UserDetailwhere SetupId=@local_SetupId

end




Use Local Temp Tables




Store the data from large tables such as Product / Orders/ Hierarchyinto Local temp Tables and Use them when joining – This is will Prevent the joining of heavy tables.

This is Subject to temp Db Size. Consider the temp Db size.

Select only the needed columns for your Transaction operation and put it into a local temp  table. Hence on performing joins the tables will be lighter  and preventing the use of excess Resource.

In the Example We have filtered out few columns  from productHierarchytable using setupid in Where  clause and used it to join with Product table.

SELECT * into #TempProductHierarchyFROM
(SELECT ProductHierarchy,ProductHierarchyId,ProductIdfromProductHierarchyDetails where SetupId=@local_SetupId)ASTPH          

SELECT ProductCodeInterfacefrommasters.ProductP
inner join #tempProductHierarchyonP.ProductId=#TempProductHierarchy.ProductId

Instead of
SELECT ProductCodeInterface frommasters.Product P
inner join masters.ProductHierarchyDetail PHD onP.ProductId=PHD.ProductId
where P.SetupId=@local_SetupId

Use NON Clustered Index
Use Non Clustered Index on temp tables for quicker Execution

Once you have created a temp table, set index on the primary key of the table – on which you will be performing the join operation.
In the example we have read the Product Hierarchy  table into a temp table and we have created a non-clustered  index onto the primary key i.e ProductId

 SELECT*into#TempProductHierarchyFROM
(SELECT ProductHierarchy,ProductHierarchyId,ProductIdfromProductHierarchyDetails
where SetupId=@local_SetupId)ASTPH                     

Create NonClusteredIndexIDX_TP4 On#TempProductHierarchy(ProductId)



Use With(NOLOCK)
NOLOCK typically (depending on your DB engine) means give me your data, and I don't care what state it is in, and don't bother holding it still while you read from it. It is all at once faster, less resource-intensive

Use NOLOCK on master tables and not everywhere.
Using NoLockmay become dangerous sometimes, so check the query exec plan when in doubt.

Select * from  ProductHierarchy  WITH(NOLOCK)











Join On the InputParameter
This will reduce the operation size and the disk read

Join on input parameter instead of filtering in where condition.
As you can see here we have joined on  setupid i.e the input parameter instead of  joining on the setupid of the other table  and then filtering in where condition. 

SELECT * from trans.PromotionP
inner join masters.ProductPP with(NOLOCK)   on PP.SetupId=@local_SetupId
inner join masters.CustomerCC with(NOLOCK)onCC.SetupId=@local_SetupId

Instead Of
SELECT * from trans.PromotionP
inner join masters.ProductPP with(NOLOCK)   on PP.SetupId=P.SetupId
inner join masters.CustomerCC with(NOLOCK)onCC.SetupId=P.SetupId
Where PP.SetupId=@local_SetupId


Prevent the use of “Select *”

Causes Indexing issues and Binding issues

Dont use "SELECT *" ina SQLquery

Insead use Select ProductId, ProductName from Products



Use EXISTS

To check if any data exists in a particular table, use EXISTS  instead of relying on Count its more effective.


SELECT OrderId,AmendVersionFROMtrans.OrdersWHEREEXISTS(SELECTtop 1 OrderId FROMtrans.OrderExtract WHERESetupId=1099)



Use Local Temp Tables (#TempTableName)







Prevent using Global hash tables (##)


SELECT * into #TempProductFROM(SELECTProductId,LevelId,ProductCodeInterface,NamefromProduct whereSetupId=@local_SetupIdandIsActive=1)ASTP                                          








Use Local Variables to Store the FunctionCall return

Prevent calling the string functions or date functions Over and over again, instead store ‘em in local variables  if you are going to reuse the value.

Declare@sampleStringvarchar(max)='nevermind the promotions'
Declare  @sizeOfStringint
set @sizeOfString=len(@sampleString)
SELECT @sizeOfString




Use Try - Catch

BEGIN TRY
-- Logic / Query here
END TRY           
            
BEGIN CATCH           
 -----------------------------------------------------------------------           
           
  DECLARE           
      @ErrMsg  VARCHAR(255)-- Error Message           
     ,@ErrNo   INT    -- Error Number           
     ,@ErrSeverityINT    -- Error Severity           
     ,@ErrProc  VARCHAR(255)-- Error Procedure           
     ,@ErrLine  INT    -- Error Line           
               
  SELECT           
      @ErrMsg  = ERROR_MESSAGE()           
     ,@ErrNo   = ERROR_NUMBER()           
     ,@ErrSeverity=17           
     ,@ErrProc  = ERROR_PROCEDURE()           
     ,@ErrLine  = ERROR_LINE()           
                           
  RAISERROR  (           
      @ErrMsg           
     ,@ErrSeverity           
     ,1           
     ,@ErrNo           
     ,@ErrLine           
     ,@ErrProc           
     )           
 -----------------------------------------------------------------------           
 END CATCH           
END           
           
           
           
     
 Use  SET NOCOUNT ON

Whenever we write any procedure and execute it a message appears in message window that shows number of rows affected with the statement written in the procedure.

When SET NOCOUNT is ON, the count is not returned.

SETNOCOUNTON
Select PromotionId from Promotion 


Prevent Usage of DDL Statements

Do not try to use DDL statements inside a stored procedure that will reduces the chance to reuse the execution plan.
DDL statements like CREATE,ALTER,DROP,TRUNCATE etc.


Use Alias



If an alias is not present, the engine must resolve which tables own the specified columns. A short alias is parsed more quickly than a long table name or alias. If possible, reduce the alias to a single letter

--Wrong Statement
SELECT PromotionId,P.VersionedPromotionId,Name,PIE.InvestmentTypeIdfromPromotion P
Inner join PromotionInvestmentPIE onPIE.VersionedPromotionId=P.VersionedPromotionId
where P.Name='Blah'

--Correct Statement
SELECT P.PromotionId,P.VersionedPromotionId,P.Name,PIE.InvestmentTypeIdfromPromotion P
Inner join PromotionInvestmentPIE onPIE.VersionedPromotionId=P.VersionedPromotionId
where P.Name='Blah'



Don't use UPDATE instead of CASE

Take this scenario, for instance: You're inserting data into a temp table and need it to display a certain value if another value exists. Maybe you're pulling from the Customer table and you want anyone with more than $100,000 in orders to be labeled as "Preferred." Thus, you insert the data into the table and run an UPDATE statement to set the CustomerRankcolumn to "Preferred" for anyone who has more than $100,000 in orders. The problem is that the UPDATE statement is logged, which means it has to write twice for every single write to the table. The way around this, of course, is to use an inline CASE statement in the SQL query itself. This tests every row for the order amount condition and sets the "Preferred" label before it's written to the table.






Avoid Functions on RHS

Dont use this

select *
from Promotion
where YEAR(StartDate)=2015
and  MONTH(StartDate)=

Use this

Select *
From Promotion
Where StartDatebetween'6/1/2015'
    and'6/30/2015'



 Specify optimizer hints in SELECT

most cases the query optimizer will pick the appropriate index for a particular table based on statistics, sometimes it is better to specify the index name in your SELECT query.
Do not use this unless you know what you are doing.

SELECT *
FROM Promotion
WITH ( Index(IdxPromotionId))
WHERE Name ='blah'
and Setupid=1099 




Hope these tips will help you prevent and solve the timeout exception  you face. If you want to add any please mention in the comments.

No comments: