I just want to share a bit of activity that we performed a couple of days before to enhance NAV and Server Performance and which literally ended with huge effect. One thing we came across was the I/O Operation which we should always consider if Server side starts performing a bit weird then before and that was our case. The I/O Operation with Read(B/sec) was more then 50 ,00,000. Check the below Screenshot
As I said the first entry was 50,00,000 which was sucking the whole systems Memory, CPU and RAM. After a bit of operation we came to a point were we got to know that there’s a SQL Procedure running which was consistently doing some kind of operation on tempdb of SQL. So now our next search operation initiated from SQL side and after struggling a bit we came to the real Query and the Procedure which was doing so. Thanks to below SQL query which dragged our attention there to that specific Stored Procedure..
After executing the above query we get the exact Stored Procedure and later on we eventually got to know that this SP was manipulating something on 500000 data and it was running every one minute as a SQL Job. Turned out to be Nightmare for us. 🙂