Understand Sharepoint

Question

Post reply

Understand Sharepoint

mceventphoto

Ten Centuries

Points: 1299
More actions
April 14, 2016 at 8:05 am

#306374

We are running Sharepoint 2013 on a SQL 2014 Server. I discovered a query recently and the execution plan is indicating that the query performance will improve by about 90% if a non-clustered index is added. The query in question is causing blocking, excessive RESOURCE_SEMAPHORE and shared lock wait type (LCK_M_S).
According to the following Microsoft KB article, I can't even add an index to the database. How do I deal with this situation?
https://support.microsoft.com/en-us/kb/841057
Thanks,
MC

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

tripleAxe SSCertifiable Points: 5706 More actions · Answer 1

Go ahead and add the index. If you do ever have a support call with Microsoft you drop the index.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

How do I deal with this situation?

Use a product that works properly, instead? :w00t:

Sorry, couldn't resist.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 3

I agree with adding an index. Often the indexing by vendors, including Microsoft, is poor.

Support won't like it, so you'll need scripts ready to remove anything you add, and re-create it later after a call.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Add the index.

A lot of the recommendations for SharePoint databases are not correct. Do what you know is the right thing, deal with Microsoft support if you have to.

However, as Steve already said, make sure you have a script to remove everything you add or modify. You'll want to run this before any upgrades or patching. Then, have a script to reapply your stuff after the upgrade/patch. Although, you may need to test those scripts carefully because things may have changed underneath you.

Isn't maintaining third party products (even from Microsoft) fun?

"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

mceventphoto Ten Centuries Points: 1299 More actions · Answer 5

Thanks all for the advice and suggestions. I just have to convince the application owner that adding the index is the right way to go.

Thanks,

MC

renato.afonso SSC Journeyman Points: 77 More actions · Answer 6

But you can create indexes through the sharepoint portal.

I think it would be your best option.

Applying indexes in the database may cause more harm than good.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

renato.afonso (4/15/2016)
--
Applying indexes in the database may cause more harm than good.

Can you justify this comment?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

renato.afonso SSC Journeyman Points: 77 More actions · Answer 8

Sorry, rephrasing: creating indexes on the sharepoint database directly, can do more harm than good.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 9

renato.afonso (4/15/2016)
Sorry, rephrasing: creating indexes on the sharepoint database directly, can do more harm than good.

What sort of harm do you have in mind?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 10

renato.afonso (4/15/2016)
Sorry, rephrasing: creating indexes on the sharepoint database directly, can do more harm than good.

I'd disagree here. The indexes are going to be created in the same manner. Whether you use T-SQL or PoSh/SMO, the code will be the same. Your choices can be the same as well. Not sure there's any harm in doing this one way over the other, but happy to be proven wrong.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

renato.afonso (4/15/2016)
Sorry, rephrasing: creating indexes on the sharepoint database directly, can do more harm than good.

When making such a claim, you need to provide some form of additional information explaining the extra harm that may come to pass. Do you have any such information or an experience anecdote that you'd care to share?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)