Query SharePoint database

Directly querying SharePoint databases – Brian Farnhill
July 11, 2022 – 10:47 am
SharePoint Kings: SQL Queries for SharePoint Content Database

One of the first rules of SharePoint that you learn when you start diving in to the product is that you shouldn't ever touch the SharePoint databases - it's fairly well known that you should leave them alone, but I've been asked the question a few times over the years "Well what if I'm just reading from the databases? Surely that's ok because I'm not making any changes to the data", so what I wanted to cover off in this post is why it's not a great idea to read from the databases and what the impact of doing this is.

Is reading the from the databases supported?

To answer this question the best place to start is a knowledge base article we have published at . This article discusses what is supported in regards to executing queries against the SharePoint databases, which I won't bother reproducing here but this is where the "don't touch the database" advice is formally documented. There is a section at the bottom of the page though that specifically calls out making reads from the database though, stating that this should be avoided as it can cause unexpected locking within the databases which can adversely affect performance and cause issues within the product.

What is locking in SQL Server?

So what exactly is locking and why does it matter? The short version of the answer here is that locking is how SQL server keeps resources isolated during a transaction to ensure that two transactions won't impact on each other. SQL server can apply locks automatically at different levels based on the impact of the query - for example, a query might impact on only a few rows which might result in SQL locking only just those rows, it might decide locking off a page or even up to locking a whole table. While a resource is locked other transactions won't be able to get to them, which is why SQL automatically manages where these locks apply to minimise the impact based on the query you have executed. When you execute queries against SQL server you can use the NOLOCK or READUNCOMMITTED table hints to specify that you want to make what is called a "dirty read" from the database, which will ignore current locks but you run the risk of retrieving data that isn't current which may result in errors in your logic.

Why database locks impact on performance

Putting it bluntly, if SharePoint is making requests to SQL and the queries have to wait for other locks to be released (or worse they time out all together) then it's going to appear slower to your end users while it loads the content they have requested - that simple. At any given time SharePoint will be making any number of calls to the databases for various things and this is not something you want to interfere with in ways that have not been tested and assessed by the product team - this is why SharePoint has a number of API options to query it's data, so you can get the information you need in more consistent and tested ways so that overall performance and stability of the environment won't risk being impacted by outside queries.

But using the API makes my application slower than querying SQL directly!

Yes we have an API and it lets you get to lots of data, but the reality of this matter is that the end result of using this is that it might take longer to get your data than querying the database directly. This however is the trade off you take for using SharePoint for your application. As a development platform SharePoint brings a lot to the table that you don't need to develop for yourself, but that comes at the cost of relying on using the API and it's inbuilt querying methods to get the data from SQL so that the platform can remain stable. Now this isn't to say that you can't have a negative impact on performance through using the API poorly, it's entirely possible to cause issues that way as well - but thanks to some performance management and throttling features built in to SharePoint this can be mitigated and managed, at least more so than a direct SQL query would be where the onus to get things write is entirely on the person writing the queries.

So that's a quick run through of what you should know about writing queries against SharePoint content databases - you might think that your read only queries are completely harmless, but be aware of the potential impact of what you are doing on the performance of your system, and how that might impact on support during a case. I hope that clears things up a little for you!

fenix international limited stylish tv stands microsoft worldwide telescope interactive model of communication coworking peckham how to start a dog breeding business secret society communication disorder what should i call my business artificial outdoor trees studio christmas outdoor broom curtain lights outdoor international human rights lawyer stylish ergonomic office chair uk san francisco coworking mass communication international postage royal mail private society com principality building society cardiff number of covid vaccinations worldwide how to set up mortgage broker business uk how to change instagram account from business to personal communication cartoon coventry building society mortgages parcelforce worldwide express 24 stylish fitness tracker york coworking space communication is key worldwide delivery artist coworking space stylish blinds ormskirk christmas outdoor lights how to get business address what is business rules sbar communication international compliance association best building society interest rates why coworking betting suburbs when is skype for business ending how to report an illegal business regus coworking london personal finance society verbal communication in nursing what do coworking spaces offer
Source: blogs.msdn.microsoft.com
Related Posts