Sharepoint – many data files make light work
I recently had the pleasure of attending a course on Sharepoint. Specifically, WSS 3.0 and MOSS 2007, if you must know, but that\’s not really important right now.
The important bit was this: The tutor asked the group, \”How many people are here because you company is thinking of implementing MOSS 2007 / 2010, and you\’d like some more information on how to do it ?\”.
Cue tumbleweed moment – in a group of some 14 delegated. No-one was there for that reason…..
The next question the tutor asked was, \”How many people are here because your company bought a point-solution from a 3rd party that uses Sharepoint, and now you\’ve got performance problems ?\”
What\’s the opposite of \’tumbleweed moment\’ ? I\’m not sure, but that\’s the response he got. There were a few rueful grins, a few nodded heads, and I think I heard someone say \”Thank you, <> !\”
I recently had the pleasure of implementing some of the performance tweaks gleaned from that course (with an exceptionally talented colleague), and thought I would share one of them with you – Splitting the ContentDBs onto multiple filegroups with multiple files.
Having plumped for moving all user-generated content into it\’s own 4-file filegroup, this is what we did:
The process is actually quite simple, but you have to interrupt the normal process flow when creating a Content Database. The default is to let Sharepoint create the database and add all the required objects. Here\’s how do it the DBA way….
First, you need to create the database for Sharepoint to attach to.
CREATE DATABASE [<>] ON PRIMARY
COLLATE Latin1_General_CI_AS_KS_WS
The important point here is the collation, so don\’t leave this line out – Sharepoint will tell you soon enough if you\’ve got it wrong.
And you\’ll also need to map the Sharepoint Admin account to \’dbo\’:
USE <<contentDBName>>;
GO
if exists (select name from master.sys.databases sd where name = N\'<>\’
and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [<>].dbo.sp_changedbowner @loginame=N\’DOMAIN\\YourSPAdminServiceAccount\’, @map=false;
GO
Next, ask the SP admin to attach the database to Sharepoint. This will create all the required database objects, including those you want to move. It doesn\’t take long, so resist the urge to go walkabout…
Once these have been done, the new database will need to be detached from Sharepoint (not SQL Server) so you can make your amendments. So ask the Admin to that. If it\’s you, ask yourself nicely.
Now you have exclusive use of the database, you can add a new filegroup and data files, and then move (in this case) all the user-generated content tables to the new filegroup, in this case called [UserData]. Please note that Microsoft recommend having multiple files in the PRIMARY filegroup and leaving it at that (but, hey, it\’s only a recommendation). Note that this part of the scripting process is in SQLCMD mode, so you\’ll need to change variables as appropriate, including those associated with SIZE , MAXSIZE and FILEGROWTH , to fit your needs:
— USE SQLCMD mode !
— and change the DataPath and LogPath accordingly
:SETVAR SPContentDBName <>
:SETVAR DataPath Drive:\\DataPath\\
:SETVAR LogPath Drive:\\LogPath\\
–Do not change anything below this line –>
——————————————————
USE [master];
EXEC (\’ALTER DATABASE \’ + \’$(SPContentDBName)\’ + \’ ADD FILEGROUP [UserData]\’);
EXEC (\’ALTER DATABASE \’ + \’$(SPContentDBName)\’ + \’ ADD FILE ( NAME = \’\’\’ + \’$(SPContentDBName)\’ + \’_001\’\’ , FILENAME = \’\’\’ + \’$(DataPath)\’ + \’$(SPContentDBName)\’ + \’_001.NDF\’\’ , SIZE = 102400KB , MAXSIZE = 12058624KB , FILEGROWTH = 102400KB ) TO FILEGROUP [UserData]\’);
EXEC (\’ALTER DATABASE \’ + \’$(SPContentDBName)\’ + \’ ADD FILE ( NAME = \’\’\’ + \’$(SPContentDBName)\’ + \’_002\’\’ , FILENAME = \’\’\’ + \’$(DataPath)\’ + \’$(SPContentDBName)\’ + \’_002.NDF\’\’ , SIZE = 102400KB , MAXSIZE = 12058624KB , FILEGROWTH = 102400KB ) TO FILEGROUP [UserData]\’);
EXEC (\’ALTER DATABASE \’ + \’$(SPContentDBName)\’ + \’ ADD FILE ( NAME = \’\’\’ + \’$(SPContentDBName)\’ + \’_003\’\’ , FILENAME = \’\’\’ + \’$(DataPath)\’ + \’$(SPContentDBName)\’ + \’_003.NDF\’\’ , SIZE = 102400KB , MAXSIZE = 12058624KB , FILEGROWTH = 102400KB ) TO FILEGROUP [UserData]\’);
EXEC (\’ALTER DATABASE \’ + \’$(SPContentDBName)\’ + \’ ADD FILE ( NAME = \’\’\’ + \’$(SPContentDBName)\’ + \’_004\’\’ , FILENAME = \’\’\’ + \’$(DataPath)\’ + \’$(SPContentDBName)\’ + \’_004.NDF\’\’ , SIZE = 102400KB , MAXSIZE = 12058624KB , FILEGROWTH = 102400KB ) TO FILEGROUP [UserData]\’);
USE $(SPContentDBName);
Next, you\’ll need to script out all the tables that you want to move to the new filegroup. I did prepare a script by way of demonstration, but it\’s a long one and a little off-putting. Suffice it to say that you need to script out dropping all the constraints on your target tables, dropping the tables you wish to move (in this case AllDocs, AllUserData, AllLists, AllLinks, AllDocVersions and AllDocStreams), and another script to recreate those tables which is modified to place them on the [UserData] filegroup, not [PRIMARY]. Don\’t worry, the process to create the database objects that\’s run in Sharepoint doesn\’t put any data in these tables.
Having done that, you can then ask the SP Admin to reattach the content database to Sharepoint. Any you can finally go walkabout, because what people put in Sharepoint isn\’t really up to you, all you want to do is make sure you\’ve got optimal performance. Isn\’t it ? 🙂
In case you\’re interested, along with the other performance changes we made, we calculated that over 200 man-hours per day were saved by not having to wait for Sharepoint to deliver content to the end-user. OK, so it\’s a second or so per page, but there\’s over 450GB of data in > 25 content databases running of a single WebApp and a few Web Front Ends, and over 4 million documents… and a user base of over 15K !
Back soon !
The Modern Dataist 