How can i tune below proc either by changing query or adding index I am having issues with one of the proc running very slow approx an hour:
Please pardon as I have to write entire code out of system to paste here. I will be editng the question with more details on the table schema and indexes as i get to copy that info:
SP is something like below
CREATE PROCEDURE [dbo].[testsp1]
@parameter1 VARCHAR(MAX),
@parameter2 DATETIME

AS

DECLARE @Today DATETIME =GETDATE()
IF @parameter2 IS NULL
 SET @parameter2 = Getdate()

SET @parameter2 = CONVERT(VARCHAR, @parameter2 , 101)

DECLARE @table1 TABLE ( tabletypeID BIGINT)

Insert into @table1
SELECT Value from stringsplit(@parameter1, '|')

UPDATE bigtable
    SET value1 = 33, statusdate = @today
from 
    bigtable t
    INNER JOIN anotherbigtable t1 on t.id=t1.id
    INNER JOIN @table1 t3 on t.tabletypeid = t3.tabletypeid
WHERE
    t1.paydate &lt;= @parameter2
    AND t.value1=32
    AND t1.payid =2

bigtable  is approx 800 M rows and has clustered as PK on id and NC on userid( 1st key), tabletypeid (2nd key)
anotherbigtable  is approx. 300 M rows
@parameter2 is most of times received as NULL while @parameter1 varies
cached plan looks something like below
The first thing I would do is avoid using table variables in heavy SQL operations. You should use a temp table instead. Either initially or select your table variable @table1 into a temp table then use that temp table in your update statement.
Table variables are historically known to be bottlenecks because statistics aren't maintained on them, and even with Microsoft's improvements to them in SQL 2014 they can still perform poorly. Here's a good Brent Ozar article about them: https://www.brentozar.com/archive/2014/04/table-variables-good-temp-tables-sql-2014/
You might also benefit from using the force seek hint on &quot;anotherbg table&quot;. But start with the above change first.
For reference: http://nisalbi.blogspot.com/2015/11/what-is-forceseek-and-forcescan-table.html?m=1
And MS Docs on Table Hints: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15
Typically query hints should only be used in very situational cases by people with experience, but I have found that with larger tables, sometimes the SQL Engine attempts to do an index scan when an index seek truly is a better choice. (Updating the table statistics on &quot;anotherbg table&quot; might more naturally fix your issue too.)
So it's situational and you'll need to test but it's possible a force seek index hint will help.
This is what your update query would look like with the index hint:
UPDATE bigtable
    SET value1 = 33, statusdate = @today
from 
    bigtable t
    INNER JOIN anotherbigtable t1 WITH (FORCESEEK) on t.id=t1.id
    INNER JOIN @table1 t3 on t.tabletypeid = t3.tabletypeid
WHERE
    t1.paydate &lt;= @parameter2
    AND t.value1=32
    AND t1.payid =2