Query optimization

update statistics tb1 with fullscan

update statistics  tb2 with fullscan

use this in the select statement in order to introduce computer scalar and

ensure estimated row size increases or not based on adjusting the varchar size and also we can avoid spills out on tempdb.

convert(varchar(7000), Name)as Name,

convert(varchar(7000), Value)as value,

Make it a while-loop on the slow running query.

e.g.

while 1=1

select * from logdatain

1.inspect the activity of tempdb over the slow running queries.

select num_of_reads,num_of_writes,

num_of_bytes_read,num_of_bytes_written

from sys.dm_io_virtual_file_stats(db_id('tempdb'),1)

2.Inspect whether max_memory and used memory are the same in mumber corresponds to granted memory that confirms sort warnings.

select granted_memory_kb,used_memory_kb,max_used_memory_kb from

 sys.dm_exec_query_memory_grants

3.Inspect the wait_type shows IO_completion suspended that confirms temodb is writing /reading Megabytes and memory spills occurs.

 select status,wait_type from sys.dm_exec_requests

 where session_id=60