February 22, 2017 at 5:24 pm
Siberian Khatru - Wednesday, February 22, 2017 3:05 PMSQL is the problem as my Regate SQL Monitor installation helped pinpoint the issue to a recurring query against the AllUserData table of a particular SharePoint content table. This query throws blocking process and long running query alerts nearly perpetually - all on this and the Workflow tables which are joined (and rejoined 3 times in the case of the AllUserData table) in one massive parameterized query. So the trouble lies in there somewhere as I see it. The MS engineer also wanted the SP skin to check the Max Degrees of Parallelism setting which could be it as well.Tomorrow's another day and the good thing is I'm pushing my own knowledge base!
I've seen a lot of tickets with MS opened for Sharepoint. I think nearly every time they have recommended updating stats on some table or another. It's Sharepoint's doing as they feel it performs better if they create the stats and you don't do auto create stats (not supported), varies on what they say with auto update stats. And then they have some of the pretty ugly queries you've seen. They feel their own stats and the health check that executes proc_UpdateStatistics is fine for most implementations.
I'm still waiting to see one of those implementations.
Sue
February 22, 2017 at 5:32 pm
Sue_H - Wednesday, February 22, 2017 5:24 PMSiberian Khatru - Wednesday, February 22, 2017 3:05 PMSQL is the problem as my Regate SQL Monitor installation helped pinpoint the issue to a recurring query against the AllUserData table of a particular SharePoint content table. This query throws blocking process and long running query alerts nearly perpetually - all on this and the Workflow tables which are joined (and rejoined 3 times in the case of the AllUserData table) in one massive parameterized query. So the trouble lies in there somewhere as I see it. The MS engineer also wanted the SP skin to check the Max Degrees of Parallelism setting which could be it as well.Tomorrow's another day and the good thing is I'm pushing my own knowledge base!
I've seen a lot of tickets with MS opened for Sharepoint. I think nearly every time they have recommended updating stats on some table or another. It's Sharepoint's doing as they feel it performs better if they create the stats and you don't do auto create stats (not supported), varies on what they say with auto update stats. And then they have some of the pretty ugly queries you've seen. They feel their own stats and the health check that executes proc_UpdateStatistics is fine for most implementations.
I'm still waiting to see one of those implementations.Sue
Funny you should say that, but every time I look at SharePoint databases - I think "this is no way to run a railroad". Its an enigma wrapped in a riddle and shrouded in mystery when you look into database maintenance issues. Fortunately for me, I rarely collide with SP and tend enterprise databases mostly.
February 23, 2017 at 6:34 am
Siberian Khatru - Wednesday, February 22, 2017 3:05 PMSQL is the problem as my Regate SQL Monitor installation helped pinpoint the issue to a recurring query against the AllUserData table of a particular SharePoint content table. This query throws blocking process and long running query alerts nearly perpetually - all on this and the Workflow tables which are joined (and rejoined 3 times in the case of the AllUserData table) in one massive parameterized query. So the trouble lies in there somewhere as I see it. The MS engineer also wanted the SP skin to check the Max Degrees of Parallelism setting which could be it as well.Tomorrow's another day and the good thing is I'm pushing my own knowledge base!
Your mentioning the MAXDOP for your SharePoint instance joggled my memory. A year or two back I had to support a dedicated SQL instance for a SharePoint farm, and one of the settings that burned me in a migration was MAXDOP.
The recommendation from MS for MAXDOP for Sharepoint is 1...
https://technet.microsoft.com/en-us/library/hh292622.aspx
Might be worth checking, although if it was working fine before than I'd bet it's already set correctly, but double-checking never hurts.
February 23, 2017 at 6:48 am
We had a database, a very badly designed database, where we ran UPDATE STATISTICS WITH FULL SCAN every 5 minutes on one of the tables (about 20gb). This was done because of the issues the bad design caused with recompiles when the statistics got out of date (and yeah, more than 5 minutes put the stats out of date sufficiently that we were getting bad plans, this is with the 7.0 cardinality estimator, not the new one). We got a few waits, but overall, it didn't hurt performance much.
Doing this nightly, in a low use period during your maintenance window, will lead to more resource usage during the updates. You'll want to monitor for that to see if seriously negatively impacts performance. However, overall, you'll probably see great results from this because, like our badly designed database, the Sharepoint database is a steaming pile. Assuming you don't get hit with too many waits, I'd consider running the stats update twice a day. Again, monitor, test, caution, etc.
Also, not all of the suggestions from Microsoft in regards to the Sharepoint databases are good. Much of the advice comes from the Sharepoint team, not the SQL Server team. So they recommend stuff like leaving the Cost Threshold for Parallelism at 5, but setting MAXDOP on the server to 1. I'd suggest just the opposite and change the cost threshold to a higher value, but leave parallelism intact (set to an appropriate value for your server). While some of the advice like the stats updates can seem crazy, it's OK. However, if it seems really crazy, like MAXDOP, don't listen to them and follow standard SQL Server best practices instead.
Also, for what it's worth, I regularly added indexes to my Sharepoint servers where, after investigation into the most frequently called procedures, missing index suggestions, etc., it would do the most good. You'll want to script those out so you can remove them prior to running updates or calling in Microsoft support.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 23, 2017 at 6:51 am
Siberian Khatru - Wednesday, February 22, 2017 3:05 PMSQL is the problem as my Regate SQL Monitor installation helped pinpoint the issue to a recurring query against the AllUserData table of a particular SharePoint content table. This query throws blocking process and long running query alerts nearly perpetually - all on this and the Workflow tables which are joined (and rejoined 3 times in the case of the AllUserData table) in one massive parameterized query. So the trouble lies in there somewhere as I see it. The MS engineer also wanted the SP skin to check the Max Degrees of Parallelism setting which could be it as well.Tomorrow's another day and the good thing is I'm pushing my own knowledge base!
WHOOP! SQL Monitor the rescue!
Sorry. I work for Redgate.
You can adjust the alerts so they put out less noise or even disable them for a particular database if it's problematic. I hate getting spammed by my monitoring tool.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 23, 2017 at 6:55 am
Sue_H - Wednesday, February 22, 2017 5:24 PMI've seen a lot of tickets with MS opened for Sharepoint. I think nearly every time they have recommended updating stats on some table or another. It's Sharepoint's doing as they feel it performs better if they create the stats and you don't do auto create stats (not supported), varies on what they say with auto update stats. And then they have some of the pretty ugly queries you've seen. They feel their own stats and the health check that executes proc_UpdateStatistics is fine for most implementations.
I'm still waiting to see one of those implementations.Sue
I truly dislike most of MS suggestions on Sharepoint. They violate a lot of standard practices. When I was maintaining a Sharepoint server, I ignored a lot of their advice and it was actually quite helpful to do so. I do recognize this is difficult for a lot of organizations, the politics of support, etc.. When picking which hill you want to die on, this one can be a good choice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 23, 2017 at 6:57 am
bmg002 - Wednesday, February 22, 2017 3:12 PMWhat version of redgate SQL Monitor are you on? Is it 7.0.0 or 7.0.1? If so, you may want to upgrade to 7.0.2. Redgate SQL Monitor 7.0.0 has a bug in it that killed performance on our SQL databases to the point where queries that should take less than a second were taking hours to complete and it required a restart of the SQL Instance to fix.
If it is version 6 or lower, then this likely isn't your issue or if it is version 7.0.2 then you shoudl be fine as well.
Sorry you had issues. Let me know if I can help out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 23, 2017 at 7:05 am
Grant Fritchey - Thursday, February 23, 2017 6:55 AMSue_H - Wednesday, February 22, 2017 5:24 PMI've seen a lot of tickets with MS opened for Sharepoint. I think nearly every time they have recommended updating stats on some table or another. It's Sharepoint's doing as they feel it performs better if they create the stats and you don't do auto create stats (not supported), varies on what they say with auto update stats. And then they have some of the pretty ugly queries you've seen. They feel their own stats and the health check that executes proc_UpdateStatistics is fine for most implementations.
I'm still waiting to see one of those implementations.Sue
I truly dislike most of MS suggestions on Sharepoint. They violate a lot of standard practices. When I was maintaining a Sharepoint server, I ignored a lot of their advice and it was actually quite helpful to do so. I do recognize this is difficult for a lot of organizations, the politics of support, etc.. When picking which hill you want to die on, this one can be a good choice.
I would agree with you Grant, but unfortunately here I only get involved when it's flailing away and panic sets into the bones of the SharePoint crew.
I haven't cared enough to look into why SharePoint takes such a laissez faire approach to maintaining the underlying databases. Honestly, it doesn't make much sense to me, so it'd be a matter of pure curiosity as I can't change policy here anyway.
Generally speaking, I'm pretty happy is mostly out of my lane.
February 23, 2017 at 7:36 am
Grant Fritchey - Thursday, February 23, 2017 6:57 AMbmg002 - Wednesday, February 22, 2017 3:12 PMWhat version of redgate SQL Monitor are you on? Is it 7.0.0 or 7.0.1? If so, you may want to upgrade to 7.0.2. Redgate SQL Monitor 7.0.0 has a bug in it that killed performance on our SQL databases to the point where queries that should take less than a second were taking hours to complete and it required a restart of the SQL Instance to fix.
If it is version 6 or lower, then this likely isn't your issue or if it is version 7.0.2 then you shoudl be fine as well.Sorry you had issues. Let me know if I can help out.
Oh I love the tool and all of redgate tools. Plus, since we upgarded to 7.0.2 it has been working great with the exception of reports being slow. I have a report that is scheduled to go out at midnight and I'll get it anywhere from 12:02 AM to 9:30 AM. It is a bit of a surprise.
But support is very quick in helping me with it and even with the issues I hit, I'd still recommend the tool to anybody I know.
I have a support ticket open for DLM Dashboard right now as it is being grumpy on our system but now I feel I'm getting off topic.
But back on topic - did updating statistics help?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 23, 2017 at 7:53 am
bmg002 - Thursday, February 23, 2017 7:36 AMBut back on topic - did updating statistics help?
It did not help at all, but it's ticked off the TODO tote board for now. I suspect there's system issues afoot at this point, but it's in other's hands now. Thanks everyone for the help!
February 23, 2017 at 8:27 am
Siberian Khatru - Thursday, February 23, 2017 7:53 AMbmg002 - Thursday, February 23, 2017 7:36 AMBut back on topic - did updating statistics help?It did not help at all, but it's ticked off the TODO tote board for now. I suspect there's system issues afoot at this point, but it's in other's hands now. Thanks everyone for the help!
If you feel like being the hero, you could look into it further.
Things I would check:
1 - is the latest SQL CU
2 - wait stats
3 - perfmon (Disk I/O, memory pressure, CPU)
4 - antivirus
And since the system used to work and then suddenly didn't, I'd be curious what changed. Were there any windows updates installed? antivirus updates? Etc. I'd try to find out everything that changed to try to get the system back up. Plus I'd be curious about 3rd party tools that are connected to the database. Or could a person other than you have started profiler on the database and forgot to end it (that will hurt performance drastically). Or is there a lot of memory pressure? would restarting the SQL instance to free up memory help? If so, you may want to change the max memory for the SQL instance.
If the above doesn't help, I'd dig into the SQL Query itself. Since you know which query is being slow and what the wait type is, you might even be able to fix it. You can see the query that is running slow; and since the system is currently down, why not do further investigation on your own too. Run the query with the actual execution plan and see what the row estimates are compared to the actual rows and see if you can find ways to make it better. That being said, it is a canned product so you don't really want to make changes to any stored procedures or tables or anything, but adding indexes might help and should not affect anything adversely. Make detailed notes about anything you add though as you will want to remove those things before an upgrade I imagine.
We have a piece of software (3rd party tool) that stores dates and times in 2 different columns... both of which are datetime datatypes. To me that feels very inefficient and I can't think of any logical reason to do that. BUT since it isn't an in-house application, that is something that I can't fix, just something I can rant about.
I'd be sure to work with the other teams though too just so they don't change something at the same time as you and you both think you fixed it and you don't know which of the changes actually fixed it.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 23, 2017 at 8:34 am
bmg002 - Thursday, February 23, 2017 8:27 AMSiberian Khatru - Thursday, February 23, 2017 7:53 AMbmg002 - Thursday, February 23, 2017 7:36 AMBut back on topic - did updating statistics help?It did not help at all, but it's ticked off the TODO tote board for now. I suspect there's system issues afoot at this point, but it's in other's hands now. Thanks everyone for the help!
If you feel like being the hero, you could look into it further.
Things I would check:
1 - is the latest SQL CU
2 - wait stats
3 - perfmon (Disk I/O, memory pressure, CPU)
4 - antivirusAnd since the system used to work and then suddenly didn't, I'd be curious what changed. Were there any windows updates installed? antivirus updates? Etc. I'd try to find out everything that changed to try to get the system back up. Plus I'd be curious about 3rd party tools that are connected to the database. Or could a person other than you have started profiler on the database and forgot to end it (that will hurt performance drastically). Or is there a lot of memory pressure? would restarting the SQL instance to free up memory help? If so, you may want to change the max memory for the SQL instance.
If the above doesn't help, I'd dig into the SQL Query itself. Since you know which query is being slow and what the wait type is, you might even be able to fix it. You can see the query that is running slow; and since the system is currently down, why not do further investigation on your own too. Run the query with the actual execution plan and see what the row estimates are compared to the actual rows and see if you can find ways to make it better. That being said, it is a canned product so you don't really want to make changes to any stored procedures or tables or anything, but adding indexes might help and should not affect anything adversely. Make detailed notes about anything you add though as you will want to remove those things before an upgrade I imagine.
We have a piece of software (3rd party tool) that stores dates and times in 2 different columns... both of which are datetime datatypes. To me that feels very inefficient and I can't think of any logical reason to do that. BUT since it isn't an in-house application, that is something that I can't fix, just something I can rant about.
I'd be sure to work with the other teams though too just so they don't change something at the same time as you and you both think you fixed it and you don't know which of the changes actually fixed it.
I agree with the approach, but you only have two real options (prior to 2016 or Azure SQL Database), additional indexes or plan guides. I already mentioned doing the indexes (it's a great idea). I'm not a fan of plan guides, not because they don't work, but because they're a pain in the bottom to get to work consistently (perfect match on text down to the white space is VERY problematic in a code generated environment like Sharepoint). However, they can help, depending on the situation and if you can get them to stick.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 23, 2017 at 9:45 am
Grant Fritchey - Thursday, February 23, 2017 6:55 AMI truly dislike most of MS suggestions on Sharepoint. They violate a lot of standard practices. When I was maintaining a Sharepoint server, I ignored a lot of their advice and it was actually quite helpful to do so. I do recognize this is difficult for a lot of organizations, the politics of support, etc.. When picking which hill you want to die on, this one can be a good choice.
Yup...it's bad. Part of the problem I've had which I'm guessing is some of the posters issue is that larger companies have their Sharepoint group. DBAs pretty much stay out of it except for builds (and finding the requirements all over in a few different docs) and only when there are problems. I remember when I did add some jobs and indexes. And sometime later they had some RBS issues so they opened a ticket. The support engineer told them he didn't have to support it due to the changes that had been made outside of "Sharepoint recommendations" - even though it had nothing to do with the issues. He removed the jobs and most of the indexes and I later had a fun meeting involving the "stakeholders" and all the politics. Fortunately the manager was good and that ended most of our "troubleshooting" support for Sharepoint.
It's pretty easy to find a ton of things to fix in that mess because it is so flipping bad but the larger the farm, the more issues there are and unfortunately the more politics. Kind of a bad mix. I pretty much try to stay away unless its relatively smaller one. And those aren't too problematic. It's still ugly in places but you can often throw some hardware at it to mitigate the issues. And political apps get money so often its all good there.
Now I have that Sharepoint nightmare feeling.....
Sue
February 23, 2017 at 1:10 pm
bmg002 - Thursday, February 23, 2017 8:27 AMSiberian Khatru - Thursday, February 23, 2017 7:53 AMbmg002 - Thursday, February 23, 2017 7:36 AMBut back on topic - did updating statistics help?It did not help at all, but it's ticked off the TODO tote board for now. I suspect there's system issues afoot at this point, but it's in other's hands now. Thanks everyone for the help!
If you feel like being the hero, you could look into it further.
Things I would check:
1 - is the latest SQL CU
2 - wait stats
3 - perfmon (Disk I/O, memory pressure, CPU)
4 - antivirusAnd since the system used to work and then suddenly didn't, I'd be curious what changed. Were there any windows updates installed? antivirus updates? Etc. I'd try to find out everything that changed to try to get the system back up. Plus I'd be curious about 3rd party tools that are connected to the database. Or could a person other than you have started profiler on the database and forgot to end it (that will hurt performance drastically). Or is there a lot of memory pressure? would restarting the SQL instance to free up memory help? If so, you may want to change the max memory for the SQL instance.
If the above doesn't help, I'd dig into the SQL Query itself. Since you know which query is being slow and what the wait type is, you might even be able to fix it. You can see the query that is running slow; and since the system is currently down, why not do further investigation on your own too. Run the query with the actual execution plan and see what the row estimates are compared to the actual rows and see if you can find ways to make it better. That being said, it is a canned product so you don't really want to make changes to any stored procedures or tables or anything, but adding indexes might help and should not affect anything adversely. Make detailed notes about anything you add though as you will want to remove those things before an upgrade I imagine.
We have a piece of software (3rd party tool) that stores dates and times in 2 different columns... both of which are datetime datatypes. To me that feels very inefficient and I can't think of any logical reason to do that. BUT since it isn't an in-house application, that is something that I can't fix, just something I can rant about.
I'd be sure to work with the other teams though too just so they don't change something at the same time as you and you both think you fixed it and you don't know which of the changes actually fixed it.
I will be looking into this more tomorrow. Been figuring out Amazon RDS instances today. I think the indexes need to be rebuilt as a minimum. The think here is that SharePoint magically takes care of everything itself, but I have my doubts. In any event, great advice and I thank you for it. I couldn't do much right now anyway as my duty day is over and the SP crew is online with MS Support fiddling with things anyway.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply