After the changes i made 2 weeks agoregarding queries performance, few days ago i started to note that that same page was starting to perform very bad. Within this days loading time grew from 5-10 seconds to 18-20 seconds and then to over 50 seconds (to be said that the page run over 80 queries and QoQ some queries being very big with lots of joins and sub-queries). Yes there has been added rows to particular tables that made the total number of records in the play to grow essentially. Yes the DB is under a more or less serious load. But still there aren't millions of rows used so there shouldn't happen such performance decrease.
So i started to dig. Added WITH (NOLOCK)to some SELECT's, shrinked the DB, de-fragmented and re-built the indexes, tried some optimizations, etc. But without a real success. And here i noted the following structure in the query that was making problems this time:
CONVERT(varchar(10), displayDateTime, 101) = CONVERT(varchar(10), GETDATE(), 101)
Sometimes it may be ok to use such constructions, but as a general rule, datetimeconversions are quite heavy.
So what i did was i created a new field displayDateTimeFormatedas varcharand at INSERTdrop CONVERT(varchar(10), GETDATE(), 101)into it. And the above structure now becomes:
displayDateTimeFormated = CONVERT(varchar(10), GETDATE(), 101)
Just a small change, but the speed of the query increased from 56 seconds (from my tests) to just 3 seconds.

Just another things to keep in mind.