#ProjectServer optimisations for #SQL Server and #PS2010 databases #SP2010 #MSProject

September 14, 2012 at 9:25 pm | Posted in Administration, Configuration, Installation, T-SQL | 2 Comments
Tags: , , ,

https://technet.microsoft.com/en-us/library/cc298801(v=office.14).aspx#Section6_3A quick blog post to show the recommended SQL Server and database settings for optimal performance of your Project Server environment. See the SQL queries below along with the TechNet documents for reference.

SQL CLR:

sp_configure ‘clr enabled’, 1
go
reconfigure
go
Print ‘CLR Enabled’

https://technet.microsoft.com/en-us/library/ee662108(v=office.14).aspx#section3

SQL Server MAX Degree of Parallelism (Specific for SharePoint 2010):

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure ‘max degree of parallelism’, 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
Print ‘Max degree of parallelism set to 1’

https://technet.microsoft.com/en-us/library/cc298801(v=office.14).aspx#Section6_3

Server-wide default fill factor:

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘fill factor’, 70;
GO
RECONFIGURE;
GO
PRINT ‘Default server-wide fill factor set to 70 %, restart the SQL Service’

Auto_Close and Auto_update_statistics_async:

Update the database names for your Project Server PWA databases.

Alter Database VM353_PWA_Archive
Set AUTO_CLOSE OFF, AUTO_UPDATE_STATISTICS_ASYNC ON
Alter Database VM353_PWA_Draft
Set AUTO_CLOSE OFF, AUTO_UPDATE_STATISTICS_ASYNC ON
Alter Database VM353_PWA_Published
Set AUTO_CLOSE OFF, AUTO_UPDATE_STATISTICS_ASYNC ON
Alter Database VM353_PWA_Reporting
Set AUTO_CLOSE OFF, AUTO_UPDATE_STATISTICS_ASYNC ON
PRINT ‘Project Server databases auto close set to off and auto update stats asynchronous set to on ‘

http://technet.microsoft.com/en-us/library/ee662107.aspx

Please note, if copying and pasting the SQL queries from this post you may need to delete and re-enter the apostrophes.

Also remember the database maintenance plans:

Project Server 2010: http://technet.microsoft.com/en-us/library/cc973097.aspx

SharePoint 2010: http://technet.microsoft.com/en-us/library/cc262731.aspx

Advertisements

2 Comments »

RSS feed for comments on this post. TrackBack URI

  1. […] This article has been cross posted from pwmather.wordpress.com (original article) […]

  2. Hi Paul.

    Thanks for the advice on this. I was just wondering what the significance is of “clr enabled” and how this would improve overall performance?

    Kind Regards
    Mark


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: