We have a query that destroys our production server when run.
It is part of a reporting functionality and the bad part looks as follows:
SELECT DISTINCT mt.ID AS ID FROM [dbo].[MyTable] mt WITH (NOLOCK) WHERE (@aVariable IS NULL OR (CONVERT(VARCHAR(22), mt.Date1, 112) >= CONVERT(VARCHAR(22), @date1, 112)) AND (@status IS NULL OR @status <> 2 OR ( @status = 2 AND ( SELECT COUNT(*) FROM MyTable mt2 WITH (NOLOCK) WHERE mt2.CaseID = mt.CaseID AND mt2.Date1 > mt.Date1 ) = 0 ) ) AND (@aSecondVariable IS NULL OR (CONVERT(VARCHAR(22), mt.Date1, 112) <= CONVERT(VARCHAR(22), @date1, 112))) AND (@aThirdVariable IS NULL OR (CONVERT(VARCHAR(22), mt.Date2, 112) >= CONVERT(VARCHAR(22), @date2, 112))) AND (@aFourtVariable IS NULL OR (CONVERT(VARCHAR(22), mt.Date2, 112) <= CONVERT(VARCHAR(22), @date2, 112)))
Furthermore, the table and indexes are created as follows:
CREATE INDEX [MyIndex] ON [dbo].[MyTable] ([AColumn], [AColumn2], [Date1], [Date2]) WITH (FILLFACTOR = 90)
MyTable has about 80 columns and a single column Primary Key:
MyTable mt consists of about 10.000.000 rows. There is an index on the columns which contain
aFourthVariable. About half of the values of the date columns are null. In the index they are on place 3 and 4.
When we run the query on one server (without users) it performs really well. When we run it on production (with users) it takes too long and times out.
We are wondering how this can be. The execution plans are the same on both servers. We thought that the result might be cached somewhere or that the memory which is free (2GB RAM) is insufficient.
We are no experts on database performance and hope some real DBAs can provide us with their views. Thanks.