?
Solved

SQL blocking a Procedure causing my SharePoint site unavailable

Posted on 2014-11-26
9
Medium Priority
?
584 Views
Last Modified: 2016-06-16
Hello, I am working on a SharePoint environment and I am not very much expert in SQL databases. In my environment there are 2 App Servers, 2 WFE and 1 DB server. I have 3 site collections, and the size of the one Content DB is more than 200GB, other 2 are 120GB and 100GB.

Currently I am facing an issue, that my SharePoint Portal goes down, and on further investigation we found out that there is a SQL deadlock causing the long queue in SQL that we kill manually and afterwards the site comes up and running.
The SQL Procedure that is being blocked is a stored procedure call SPROD_Contect_Intranet.dbo.pro_getStreamsForSite.
On a daily basis we get 40 to 50 plus blocks and we have to manually kill it.

The procedure name is SPPROD_Content_Intranet.dbo.proc_GetStreamsForSite, and the function which is blocking the SQL is INDEX REORGANIZE, What I can see here is very long wait situation which may not be unlocked until “INDEX REORGANIZE” is completed while another command is inserting a record into the same table. Also the SELECT command from the proc_GetStreamsForSite locked the table to get the siteID using the clustered index DocStreams_CI which is under “INDEX REORGANIZE” status. To me the situation looks almost in a dead-locked situation if not force to kill the blocking process which is the proc_GetStreamsForSite.”

Please suggest what could be the root cause and how to resolve it? Many Thanks,
0
Comment
Question by:rjkirby
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 2000 total points
ID: 40468502
Question here is why it needs the INDEX REORGANIZE command?
Index maintenance should only occurs in non working hours because the impact is very high since it's causing locks.
When the SP is running and who is calling it?
Can you post the full code of the SP here?
0
 
LVL 1

Author Comment

by:rjkirby
ID: 40469714
Thanks Vitor for your expert opinion.

There are 4 built in rules in SharePoint that are related to fragmented indices, and these runs when SQL thinks it should run (when it needs to re organize index and have resources).  And I am not 100% sure if the 4 rules are causing the SQL Locks. The 4 SharePoint rules are :

1 - Databases used by SharePoint have fragmented indices
2 - Search – One or more property databases have fragmented indices.
3 - Search - One or more crawl databases may have fragmented indices
4 - Search - One or more crawl databases may have fragmented indices

Microsoft Link on it - http://technet.microsoft.com/en-us/library/cc262731(v=office.14).aspx 

SQL Code:

1.      There were 33 processes blocked by proc_GetStreamsForSite.

a.      proc_GetStreamsForSite is selecting information from a function called TVF_DocStreams_SiteId as below.
 
SELECT
        DS.ExpirationUTC, DS.DocId, DS.SiteId, DS.Partition, DS.BSN, NULL, DS.Type, DS.Size, CASE WHEN DS.Size > @ChunkSize AND DS.Content IS NOT NULL THEN SUBSTRING(DS.Content, 1, @ChunkSize) ELSE DS.Content END, dbo.fn_RbsReference(DS.RbsId)
    FROM
        TVF_DocStreams_SiteId(@SiteId) AS DS
    WHERE
        (@IncludeRbs = 1 OR DS.RbsId IS NULL) AND
        (@IncludeInline = 1 OR DS.Content IS NULL)
 
The function TVF_DocStreams_SiteId is selecting information from the table called DocStreams as below.
    SELECT
        *
    FROM
        [dbo].[DocStreams] WITH (INDEX=DocStreams_CI, FORCESEEK)
    WHERE
        SiteId = @SiteId
 
 
2.      Among the blocked processes, I am very much interested in this one - ALTER INDEX [DocStreams_CI] ON [dbo].[DocStreams] REORGANIZE

b.      The index DocStreams_CI is a Clustered index which physically ordering the table by the configured columns order.

3.      Also Among them, majority of them were proc_WriteStreamToSQL.

c.      This stored proc has a command “INSERT INTO DocStreams (SiteId, DocId, Partition, BSN, Size, Content, RbsId, Type, ExpirationUTC) VALUES (@SiteId, @DocId,…”
 
What I can see here is very long wait situation which may not be unlocked until “INDEX REORGANIZE” is completed while another command is inserting a record into the same table.

Also the SELECT command from the proc_GetStreamsForSite locked the table to get the siteID using the clustered index DocStreams_CI which is under “INDEX REORGANIZE” status.
To me the situation looks almost in a dead-locked situation if not force to kill the blocking process which is the proc_GetStreamsForSite.
 

Thanks,
0
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40470142
The article you posted are the usual maintenance tasks for any database (SharePoint or not) and they recommend to run those plans in non-production hours:
We recommend that you run consistency checks during non-production hours because DBCC CHECKDB is extremely resource-intensive (I/O, CPU, memory, and tempdb space).
(...)
Even if online index rebuild is used, there are still two points in the operation where table locks are held momentarily, and these could cause blocking. As a result, we recommend that you always schedule index rebuild activities during periods of low activity.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 1

Author Comment

by:rjkirby
ID: 40471356
Thanks Vitor. Are these Index Rebuild activities triggered by SharePoint or SQL itself? as I plan to disable my 4 SharePoint Rules and see if we are still facing locks, these 4 rules are related with the SharePoint Databases indices fragmentation. Many Thanks,
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40471520
Can be triggered from anywhere but the user that trigger it need to have the permissions for reindex.
0
 
LVL 1

Author Comment

by:rjkirby
ID: 40472819
Thanks Vitor. You are right, these 4 jobs that are triggered by SharePoint have Full rights on SQL DB so it could be the reason. Since 1 week we haven't experienced any locks so far.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40473218
That's good. Just schedule the reindex to run in non-productive periods and you won't have any issues with that.
0
 
LVL 1

Author Comment

by:rjkirby
ID: 40474822
Thanks Vitor.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In case you ever have to remove a faulty web part from a page , add the following to the end of the page url ?contents=1
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question