Troubleshooting SharePoint Portal Databases.

Hi Guys, In between the normal posts i prefer to share something happen at work place. The real time experience are something very special which might be extremely useful for one and all.The guy beside me was responsible for production,he got the call from SharePoint portal teams that the database cluster CPU was spiked up. He was cool and analyzing things with activity monitor.  Myself and him noticed that there are more shared locks and exclusive locks too.  Meanwhile the front end site was down things got escalated bit more. Another guy on our team joined us on phone call and shared his experience.

 

Step 1. Based on his experience, he advised to change database isolation level to Read Committed Snapshot  to true. Well it was a first good step, the shared locks are gone now.

Step 2. Still we are continuing our efforts on activity monitor, but the trouble was still there are some exclusive locks the SQL was not displayed completely. The newly appointed DB Manager joined us, and gave us the below DMV.  which was very helpful to get complete SQL details, you can change the values of the where clause to fetch details about different locks [sql]

select *

from sys.dm_exec_requests  r

cross apply sys.dm_exec_sql_text(r.sql_handle)

where last_wait_type=‘LCK_M_S’

[/sql] Once we got the query the guy beside me gave an idea based on his old experience to list down TVF found inside and work on the TVF to change the table hints to with(nolock). So we have changed all such TVF and applied on production. Now cluster was stabilized.

Step 3 : The database which manages the user session details was appeared on activity monitor, with locks. The newly appointed DB manager has more experience on application side too. He told us,it was due to storing the session details on database, instead it can be configured to manage on a application side or memory level. The sharepoint team changed that configuration too. That is all, it was a nice team work. The things we done above may not be the best on SharePoint experts opinion, if you have different opinion, feel free to share with us.