SharePoint – SQL Interaction – performance troubleshooting from SQL Side

Case: While working on one of custom SharePoint portal on SP 2010 we started facing lot of performance related hit, we started investigating different areas like Windows Crash dump, SharePoint ULS log, Multiple Third-party tool e.g. Jmeter and then we realize that something going wrong from SQL side and we have used below to get more detail about who is consuming more resource in SQL side and what is blocking in SQL:

Note: user required sysadmin and view server state permission on db server.

Note: sp_who is provides information about current MS SQL users and it processes

1. sp_who2 (system stored procedure) or sp_who3 to find out which SPID is taking a lot of resources.

2. DBCC INPUTBUFFER(SPID)

“EventInfo” column is showing last statement/current statement executed by SPID

More Detail – SQL Server slowness

 

Cheers!!