Need help to understand tuning up a slow SQL server query For one of our databases the query like below is extremely slow.
Due to security reasons I am not able to share the actual query or plan but just to give an idea how query is written is below
SELECT [Id]
      ,[AboutMe]
      ,[Age]
      ,[CreationDate]
      ,[DisplayName]
      ,[DownVotes]
      ,[EmailHash]
      ,[LastAccessDate]
      ,[Location]
      ,[Reputation]
      ,[UpVotes]
      ,[Views]
      ,[WebsiteUrl]
      ,[AccountId]
  FROM [StackOverflow2010].[dbo].[Users]
  WHERE DisplayName IN (

 SELECT DisplayName from dbo.Users
 WHERE CAST(LastAccessDate AS DATE) = CAST ('20160814' AS DATE)
 AND CreationDate&gt;= DATEADD (DAY, -30,LastAccessDate)
 AND CreationDate&lt;= LastAccessDate)

In stackoverflow database this does not return any rows but for our existing 6 TBS database its really slow.
CreationDate and LastAccessdate columns are both datetime (10)
and DisplayName is Varchar(50)
Please suggest how can I improve performance if above can be re-written
You can improve things greatly without rewriting the query by just adding indexes, from what I can tell.
Here's the execution plan I get on SQL Server 2019 (note that I changed the date to '20090814' just so it would return some results):

Table 'Users'. Scan count 18, logical reads 15430

 SQL Server Execution Times:
   CPU time = 297 ms,  elapsed time = 94 ms.

This scans the entire Users table once to get the users that meet the date criteria, and then several more times to get the rest of the users that have matching display names.
Not ideal.
The query only takes 94 ms on my machine, mostly because everything is in RAM and the query is running at degrees of parallelism (DOP) 8.
However, adding these two indexes helps the situation a lot:
CREATE NONCLUSTERED INDEX IX_LastAccessDate 
ON dbo.Users (LastAccessDate)
INCLUDE (CreationDate, DisplayName);

CREATE NONCLUSTERED INDEX IX_DisplayName
ON dbo.Users (DisplayName);

Now I get this execution plan:

Table 'Users'. Scan count 15, logical reads 153

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server is able to produce a seek plan here by using the internal GetRangeThroughConvert function to determine the range of possible datetime values that are equivalent to CAST(LastAccessDate AS DATE) = CAST ('20160814' AS DATE).  Essentially, it rewrites the query in the background to match the suggestion made by Mo64.
Note: you are probably better off rewriting to explicitly use that range query, rather than depending on the hidden implicit conversion
You can read more details about this sort of execution plan on Paul White's blog: Dynamic Seeks and Hidden Implicit Conversions
The situation could be improved even further if you eliminate the key lookups - either by selecting less columns, or including all the necessary columns in the first index (on LastAccessDate).

I realize this is kind of a toy example, but hopefully it illustrates the general solution that can be applied to your real situation:

Add an index that will allow SQL Server to jump to the right date in the subquery
potentially rewrite the CAST from datetime to date to be two inequality conditions.  In other words, replaced this:

WHERE CAST(LastAccessDate AS DATE) = CAST ('20160814' AS DATE)

With this:
WHERE LastAccessDate &gt;= '20160814' AND LastAccessDate &lt; '20160815' 


Add an index that will allow SQL Server to jump to the matching varchar(50) column values