Select * from Tbl_TempCashTrxNse With(Nolock) Left Join Client_MAster With(Nolock) On cm_cd = Tmp_Loginid where cm_freezeyn = 'Y' when i checked the perfmon for this statement it showed me an warning 'sort warning' with event subclass '1-single pass' .can anyone tell me in this query where sorting takes place and kindly tell solution for this warning
A sort warning is basically an indicator for SQL Server having to sort data and the memory requirements are so high that the data is spilled to tempdb. This will probably turn up as a SORT operator in the execution plan. There is little we can advise without knowing the table design, indexes, data volume, data spread or seeing an execution plan for this query. I suggest you look at seeing if you have sensible indexes for this query (think about other queries that run against the tables too). Do you really need to perform a `SELECT *`? Could you leave out some of the columns to maybe allow a covering index to be used?