Sharepoint Slow, Queries Recompiling everytime
-
We have a sharepoint 2010 server running in native mode. The backend server is SQL 2008 R2 RTM.
We recently changed the credentials a report was using to connect to SSAS. After that change we have been getting timeouts regularly for the report. We changed the credentials back, but the timeouts persist.
When I run SP_WHOISACTIVE I see queries that appear to be recompiling, blocking the select queries.
The stored Procs do not have the 'with recompile' option in them. The Create statements run for more than 2 minutes, which exceeds the Sharepoint timeout.
I found this reference - http://support.microsoft.com/kb/2691331 And based on it I redeployed the entire solution, however I'm still seeing these problems.
-
There's nothing there to suggest anything is recompiling. In fact, if those procedures were in the middle of a recompile they couldn't be causing blocking as they'd still be in the optimiser before running.
Your trace shows two stored procedures executing, three updates running and two selects running. Whatever the procedure is busy executing is taking locks and slowing things down. Start by seeing why the execution of the procedures is holding long-term locks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thank you for the quick response.
I was under the impression that when I would see the 'create procedure' in the sp_whoisactive that meant the SP's were recompiling. My bad. What does that mean though, for curiosity's sake?
I do have a trace running, and when I pull out the actual exec statement and run it manually it completes in a couple seconds, however this query blocks for 120+ seconds.
Any thoughts?
-
thisted (10/7/2013)
I was under the impression that when I would see the 'create procedure' in the sp_whoisactive that meant the SP's were recompiling. My bad. What does that mean though, for curiosity's sake?That the procedure is executing.
Check SET options, there must be something different.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
When I am asked to look into a SQL Server performance issue I want to know more about the SQL Server box, hardware resources (Memory, CPUs, Disks), how is TEMPDB configured, maintenance jobs for reindexing, statistics updates, on and on and on. Most importantly what changed!
Often times I will find that the performance issues may exist elsewhere and not with a specific query or queries. One case in point, I was given the task to move a database from one server to another. The new server was about 3 times the resources compared to the old server. After a day of operation with the database on the new server I was getting complaints about performance was worse than it was on the old server. I knew that the database was not starving for system resources. What I discovered was a network latency issue because the new database server was no longer on the same subnet than the old server. So it was not a SQL Server problem at all. I engaged our Network Admin and he was able to move the database server onto the same subnet as the application server. Performance issue was resolve.
Hope this gives some insight.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NYhttp://www.linkedin.com/in/kurtwzimmerman
-
It's almost like it is blocking itself.
The Select queries are the actual work within the stored procedure itself. I broke out the SP, and ran it as a batch, it worked fine as long as those 'create procedures' weren't running. As soon as one of those was running, it starts blocking and everything goes to poop.
--CREATE PROCEDURE [dbo].[GetDataSets]
Declare @ItemID [uniqueidentifier] = 'B556EE75-80C6-406E-92C1-E97DEFB018E2'
Declare @AuthType int = 1
SELECT
DS.ID,
DS.LinkID,
DS.[Name],
C.Path,
SD.NtSecDescPrimary,
C.Intermediate,
C.[Parameter]
FROM
ExtendedDataSets AS DS
LEFT OUTER JOIN Catalog C ON DS.[LinkID] = C.[ItemID]
LEFT OUTER JOIN [SecData] AS SD ON C.[PolicyID] = SD.[PolicyID] AND SD.AuthType = @AuthType
WHERE
DS.[ItemID] = @ItemID
-
thisted (10/7/2013)
It's almost like it is blocking itself.One execution of the procedure can block other executions of the same procedure, but it's not blocking itself, there are different session IDs for the blocker and the blocked sessions.
The Select queries are the actual work within the stored procedure itself. I broke out the SP, and ran it as a batch, it worked fine as long as those 'create procedures' weren't running.
CREATE PROCEDURE is just what the DMVs show when a procedure is executing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I'm at a loss. I can't find the actual source of the slowdown. I've been working on this for 3 days now.
When I run a profiler trace it's like I'm not actually seeing what's going on. When I look at sp_whoisactive I see it hanging on the GetDataSets SP, but when I search my profiler traces the duration for that SP is less that 2 seconds and usually faster than that.
-
Could the procedure's execution be leaving open transactions?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
When I refresh the sharepoint page, and then run whoisactive I do see that the SP does have 1 in the open_tran_count counter, and that seems to stay at 1 until sharepoint times out at 2 minutes.
-
So for some reason the procedure's not committing the transactions that it starts, or it's starting within a transaction already. That means the locks are held until the connection is closed even if the procedure only takes a couple of seconds to execute.
Why that's happening you'll have to investigate. Check whether it's setting implicit transactions on to start with, see if anyone's messed with the default SET options on the database.
You may need to drill into details, profiler should have events for starting transactions so you can see exactly what's happening.
Maybe start by terminating all connections or restarting SQL. Ensures there's no stray transactions that have been sitting around for days.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply