The wait operation timed out in SQL Server

Question

I have a table contains 100 columns and 2,000,000 records. when I am fetching records using stored procedure from that sometimes I am getting "The wait operation timed out." error.When I am alter stored procedure and then try to fetch records it will work fine.

Can anyone let me know what is the best solution for this ?

SP AS Following

CREATE PROC GetProducts @ClientId INT=NULL
AS
  BEGIN
      SELECT TOP 10 ClientId,
                    Field2,
                    Field3,
                    Field4,
                    Field5,
                    Field6,
                    Field7,
                    Field8,
                    Field9,
                    Field10
      FROM   ProductMaster
      WHERE  ClientId = @ClientId
      ORDER  BY Field1 DESC
  END 

Answer 1

Create a composite index on to optimize this query if you haven't already done so:

CREATE NONCLUSTERED INDEX idx_ProductMaster_ClientId_Field1 
    ON dbo.ProductMaster (ClientId, Field1 DESC);

The likely reason for the symptoms you describe is parameter sniffing, where the optimal execution plan varies depending on the values supplied. The execution plan generated upon the first invocation is cached and reused for subsequent executions. Performance may be suboptimal for the later queries if the original parameter value is atypical. I would expect the index suggested will make the plan a non-brainer and result in consistent performance.

If this index does not address the problem, consider adding a query hint to avoid the parameter sniffing issue. For infrequent queries, try adding query hint OPTION(RECOMPILE) so that the plan is not cached. If it is executed often, specify OPTION (OPTIMIZE FOR (@ClientId UNKNOWN)) so that the plan is generated based on average statistics rather than the parameter value supplied. The latter will provide consistent performance but not necessarily optimal for all values.