There is one simple query to return the count of rows which is taking ages to complete but once I remove the count function it returns the result in no time. Any idea about this strange behavior ? Query - BEGIN TRAN SELECT COUNT(LocationAddress) FROM WHMWarehouseDetails AS WHMWD WITH ( NOLOCK ) INNER JOIN MF_Storage AS MFS WITH ( NOLOCK ) ON WHMWD.Office = MFS.Office AND WHMWD.WHCode = MFS.Warehouse AND WHMWD.LocationAddress = MFS.RID AND WHMWD.Type = MFS.Type WHERE ( ISNULL(WHMWD.IsCancelled, 0) = 0 ) AND ( WHMWD.Office = 'PNLS;' ) AND ( WHMWD.WHCode = '11' ) AND ( WHMWD.IsEnabled = 1 ) AND EXISTS ( SELECT 1 FROM mf_storage WITH ( NOLOCK ) WHERE office = 'PNLS;' AND warehouse = '11' AND ISNULL(RID, '') = ISNULL(WHMWD.Code, '') ) ROLLBACK
Magnus is on the right track here - look at the execution plans to see what the difference is. When you do a `COUNT(column)`, the function will return the number of rows where column is not null, whereas removing the count and doing a `SELECT *`, removes the need for that check. Depending on the indexes, statistics, etc. for that column, you will get differing execution times.
Why do you use WITH NOLOCK? Regarding the overall performance: Have you tried to do Ctrl+L in Sql Server Management Studio instead of running the query? That would give you an estimated execution plan where you can look for clues, like table scans. Do you have an index on the LocationAddress column? What indexes do you have?