Expected wait operation in SQL Server

Question

I have a table containing 100 columns and 2,000,000 records. when I retrieve records using a stored procedure, from which sometimes I get "Waiting for a wait." error.When I modify the stored procedure and then try to retrieve the records, it will work fine.

Can someone tell me what is the best solution for this?

SP AS After

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 to optimize this query if you have not already done so:

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

, , - , . , , . , . , .

, , . OPTION(RECOMPILE), . , OPTION (OPTIMIZE FOR (@ClientId UNKNOWN)), , . , .