I have a table (UserDeptMapping) which conatins 129000000 (one hundred twenty-nine millions records). Department details table which has 500 entries. ID DeptId WorldLocationId RegionId DistrictId Table UserDeptMapping which contains below data like Id UsertId MappingId 1 1 1 2 1 2 3 1 3 4 2 1 Suppose execute below query against Department Id 1 like Select * from UserDeptMapping Where MappingId = 2 Output comes with total 300000 rows which is taking 30 seconds. How can I optimiase the query., so it will reduce the time.

No any user will read all the 3,00,000 records at a time, you can use OFFSET and FETCH NEXT. Showing records in chunks will have good readability and will fetch records within seconds/milliseconds

try with (nolock) with your query Select * from UserDeptMapping with (nolock) Where MappingId = 2

Yes you can try with SQL NoLock hint. It will help to improve select statement performance. Also go through the link : https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/