Tip #1 SQL Query Timeout Configuration and Errors

--See current system configurations
SELECT * 
FROM sys.configurations 
WHERE configuration_id IN (1519,1520,1541,1557);

--See what is current setting
SELECT @@LOCK_TIMEOUT

--Wait up to 5 ms for the SQL text waiting on lock resource, then give up
SET LOCK_TIMEOUT 5;

--Return this to the default
SET LOCK_TIMEOUT -1;

--System error messages related to timeout
SELECT message_id, [text] 
FROM sys.messages 
WHERE language_id = 1033 AND ([text] LIKE '%timeout%' OR [text] LIKE '%time out%')

CS #1 Using SQL Hint OPTION RECOMPILE for improving performance

Problem Statement 

Stored procedure CS1_RECOMPILE is running slow for some input parameters combinations and running fine for other input parameters.

CREATE PROC CS1_RECOMPILE 
     @start_prod_id INT,
        @end_prod_id   INT
AS
  BEGIN
    SELECT TOP 10 ProductId
    FROM bigProduct
    ORDER BY ListPrice DESC;    

    SELECT TOP 10 *
    FROM bigTransactionHistory
    WHERE ProductID BETWEEN @start_prod_id AND @end_prod_id
    ORDER BY ActualCost DESC;
 END;
GO

Read more CS #1 Using SQL Hint OPTION RECOMPILE for improving performance