I could find , one of the table is having million of records and when we tried to open index fragmentation for same table sql server is not responding and...

Andrey,

I checked default trace and found missing stats in tempdb:

"NO STATS:([tempdb].[dbo].[#calculateRequestNewResourceNestedSetStatementTransitionsNonMemberSet_______________________________________________0000000E4593].[SetKey])"

Is this a causing the issue? Please confirm.

Yes, its growing abnormally. Due to space issue on temp drive , we thought tempdb require more space so we asked server team to uplift the space . They have...

Hi Chris,

Thanks for the response. I ran second query and got the same stored procedure which I mentioned in my first post. The code has written as Waitfor and Receive...