April 25

Food for thought: my favourite #ISCLondon quotes

by Benjamin.Athawes

on 25/04/2012 22:07

I have spent the last 3 days at the International SharePoint Conference in London, organised by Combined Knowledge.

The event was well attended and by all accounts was a big success. Interestingly the organiser (a friendly chap named Steve Smith) made a decision to structure the event in such a way that each session was a continuation of the previous one, allowing an overall story to evolve over the course of the event. Personally I think the format worked and from what I have heard the speakers enjoyed collaborating to achieve this (I wonder if they used a SharePoint site?). It certainly allowed a more in-depth look at topics that might have otherwise been skimmed over (this was particularly the case for the PowerShell sessions that kicked off the IT PRO track).

Although I did take a bunch of notes, I thought that a concise, albeit slightly terse way of documenting the highlights would be a list of my favourite quotes from each of the sessions that I attended. This is really for my own future reference but hopefully it's useful for those that weren't able to attend too. You will notice that the sessions aren't purely technical – I dipped into a few "fluffy" (read: business) sessions to see what all the fuss is about.

The number of bullet points is not representative of the quality of the sessions – it really depends on the speaker style, whether the sessions were demo heavy and whether or not a point could be generalised to avoid misinterpretation.

Let me know if you spot any mistakes or have any queries.

PowerShell (IT101-IT102)

Gary Lapointe, Spencer Harbar, Chandima Kulathilake

Utilising a combination of PowerShell with a separate XML input file allows for "static scripts and dynamic parameters", avoiding the need to re-test for each environment.
"Using SQL aliases is a no brainer". – you can't point to a specific SQL instance using DNS

SQL (IT103-IT104)

Wayne Ewington, Ben Curry, Neil Hodgkinson

When troubleshooting disk performance issues, always ask "what else is on the SAN?"
"Different LUNs does not mean different spindles" – when troubleshooting disk throughput issues
"A large number of smaller disks will perform better than a small number of large disks"
"Disk performance is about throughput, not capacity"
"Say NO to virtual disks for SQL server" – mount LUNs directly
Ask yourself "What specific problem are you trying to solve?" – when considering RBS
"The closer your RPO/RTO numbers are to 0, the more expensive your solution will be"
"Consider multiple farms" when naming databases – e.g. SP_Farm1_Content_Intranet
"It depends on the provider" – the answer to most RBS questions
"SQL indexes are based on GUIDs" – which contributes to fragmentation in SharePoint

User Profile Service (IT105)

Spence Harbar, Kimmo Forss

"User profiles can be augmented using BCS" – e.g. to add data from a HR databases
"Identity management is primarily a political discussion, not technical"
"AD assessments should be performed up front, prior to a SharePoint project starting"
"You cannot do identity management without a metadirectory" – FIM being an example of a metadirectory solution
"Logging on as the farm account is one of the top 5 worst practices in SharePoint administration"

Delivering Business Applications (CS706)

Ian Woodgate

"SharePoint is typically a higher up front cost but lower long term cost, meaning it's a strategic investment" – compared to traditional custom built apps.
"Sometimes scoping the problem is more work than solving it"

Building a new Intranet (IW507-IW508)

Mark Orange

Your Intranet should be a "many sites experience" – as opposed to one monolithic portal
"Analogous to a shopping mall" – i.e. food labels (metadata), "get in, get out" ideal
"Focus on definitions, not labels" – e.g. not "How we work", focus on "The place to find out what I need to perform my role"
"SharePoint is not a solution – it's a dirty word that should be removed from our vocabulary"
"Enable people for solutions, not SharePoint"
"Consider content publishers, not just consumers" – how will the content be edited?
"Bend > buy > build" – start with bend, only build if absolutely necessary.
"Prove before you move" – through prototyping, to justify moving from "bend" towards "buy" and "build"
"Train the trainer allows for scalable education"
"Searching everything doesn't work" – use scopes.

Search (IT109-IT110)

Neil Hodgkinson

"The default search config is not recommended – split it" – 1 schedule and 1 content source is inflexible.
"Instant indexing is not possible" – it takes 1 minute to spin up
"Crawler impact rules are an easy way to DOS a farm"
"Search results removal is instant" – URL is dropped from the index
"Configuring search with PowerShell can be invasive" – e.g. due to DB moves

Capacity planning and performance testing (IT112-IT113)

Steve Smith and Ben Curry

"Try to break your farm to establish a baseline and thresholds" – obviously not in production hours J
"Visual Studio 2010 is a great tool for load testing and is not just for developers"
"An old client may not be able to tax a new server" – ensure your test rig(s) have enough hardware
"Build in think times" to ensure more accurate testing.
"Test search queries when load testing" as more developers start to utilise search in custom code
"Additional hardware can have an order of magnitude improvement" – in the demo we added an additional CPU core to each WFE, which drastically improved our RPS figures
"Test third party products too"

Exploring SharePoint Enterprise features (BUS314)

Andrew Woodward

"It's difficult to partition Enterprise and Standard functionality" – e.g. site templates that include Ent features
"Best bets are analogous to search engine ads"
"Put effort into reviewing search queries, especially failed ones"
"Don't buy Enterprise just for chart part Web parts"
"SPD is a great BA prototyping tool" – e.g. for workflows
"InfoPath is great for validating user input"
"The Microsoft BI stack delivers functional, zero vanity output – PerformancePoint adds shine" – for flashy exec dashboards J
"SharePoint should not be considered mature yet" – compared to some other products/vendors

What's next? (BUS315)

Bill English

"Business requirements should always be technology agnostic"
"SharePoint will surface business dysfunction" – probably my favourite quote.
"Politics can screw up a great technical design"

Office 365 (IT116)

Spence Harbar, Kimmo Forss

"The Office 365 Dedicated team are doing what every IT PRO team should be doing" – when it comes to validating custom solutions being deployed to the environment.

0 Comment(s)

January 23

SharePoint, SQL server fill factor and index rebuilds – a correction

by Benjamin.Athawes

on 23/01/2012 22:35

Category: SQL

Overview

Today we are going to be taking a look at the fill-factor option that is available within SQL Server 2005 and later, in the context of Microsoft SharePoint.

Aside from providing reasons for caring about fill-factor, I point out a mistake in the SQL maintenance guidance for both SharePoint 2007 and 2010 that relates to index rebuilds that I have made Microsoft aware of.

Disclaimer

I'm relatively new to SQL server configuration and would class myself as an "accidental DBA". I like to think that this blog post is well researched – and the recommendations contained within worked in my specific environment - but be warned that I am certainly no expert. If you plan on making changes in response to this post I suggest you seek professional guidance and TEST everything!

What is fill factor anyway?

According to MSDN, the fill factor option "determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth". The idea is that an appropriate fill factor should reduce page splits whilst maintaining performance and using space efficiently.

In case you are wondering, a "page" in this context isn't something that sits in a Pages library after activating the SharePoint publishing infrastructure. It is the basic storage building block in SQL Server and is exactly 8192 bytes in size.

The default fill factor option for SQL server 2005 and later is "0", which really means "completely fill each leaf-level page so as not to waste space and potentially optimise read performance". A fill factor setting of 0 or 100 both result in pages being 100% full.

I'm a SharePoint admin – why should I care about this?

According to Kimberly L. Tripp, fill factor is "the MOST IMPORTANT thing to understand about index maintenance and reducing fragmentation (especially in databases that are prone to it)". I'm not going to doubt this given Kimberley's credentials.
SharePoint uses GUIDs (unique identifiers) as primary keys for all tables which causes page splits and massive fragmentation (there is a good article on this here which highlights the fact that non-sequential GUID based identifiers are unnecessarily wide, resulting in wasted space). Thanks to Jonathan Kehayias for explaining this concisely.

Therefore, a non-default fill factor is – according to various clever SQL bods - appropriate for SharePoint in order to reduce fragmentation and improve performance.

How do I check my fill factor?

As mentioned in the two database maintenance documents that I link to below, you can check your fill factor by querying the sys.indexes catalog view. Here is a simple example (replace DatabaseName with the name of your DB):

use DatabaseName

select name,fill_factor from sys.indexes

order by fill_factor desc

And, for good measure here is a screenshot of the results:

Determine Fill Factor using sys.indexes

Determining fill factor using sys.indexes

Of course, you can also determine the default server-wide fill factor using the server properties dialog:

Determining default server-wide fill factor (this setting may not be appropriate for your specific environment)

OK, I know my fill factor. So what?

Now that you know your fill factor you probably want to determine your fragmentation level to work out if a change might help. The "Database Maintenance for Office SharePoint Server 2007 (white paper)" document link below includes instructions on how to do this. Below is an example.

1. Determining the database ID (replace DatabaseName with the name of your DB):

select DB_ID(N'DatabaseName') as [Database ID]

2. Determining average fragmentation (replace DBID with the integer ID found in step 1):

select database_id, index_type_desc, alloc_unit_type_desc, avg_fragmentation_in_percent, page_count from sys.dm_db_index_physical_stats (
DBID
, 0
, NULL
, 0
, NULL
)
order by avg_fragmentation_in_percent desc

I'll leave out discussion of the specific parameters above as it's probably beyond the scope of this blog post, but Technet contains plenty of information on sys.dm_db_index_physical_stats.

The result of the above script is as follows. As you can see a number of indexes are heavily fragmented in this SharePoint 2007 content database:

Determining index fragmentation using sys.dm_db_index_physical_stats.

Can't we just set the fill factor to something really small to prevent the issue?

Unfortunately this isn't a realistic option because – based on the MSDN article above – fill factor is roughly proportional to read performance and a low value will result in a lot of wasted space. For example, if we were to set this value to 10% our read performance might suffer by up to 90%.

Notice that I say "really small" instead of 0 because 0 is the default setting which as we now know sets fill_factor to 100%J.

The Microsoft guidance

If you were to read through the following documentation, you might think that the guidance from Microsoft on an appropriate fill factor is quite clear:

Database maintenance for Office SharePoint Server 2007 (white paper)

Database maintenance for SharePoint Server 2010

The links recommend a fill factor of 70% and 80% for SharePoint 2007 and 2010 respectively.

However if, like a lot of "accidental" SharePoint DBAs you decide to follow the guidance to implement an appropriate maintenance plan, you will soon come across the following screenshots. The big, idiot proof dialogues are really there for me so that I don't refer to this blog later on and copy the wrong settings into my environment:

Incorrect fill factor for SharePoint 2007 Free space per page percentage appears to be the inverse of the fill factor guidance for SharePoint 2007…

Incorrect fill factor setting for SharePoint 2010
And it's the same issue for SharePoint 2010!

Just to be clear, the above screenshots show free space per page percentage values that are the exact opposite of the written guidance contained within their respective documents. E.g. in the case of the SharePoint 2010 recommendation, the screenshot suggests that you change free space per page to 80% - the written guidance states that pages should be 80% full. In other words, the number in the screenshots above should – as far as I'm concerned - be 30% and 20%.

Being relatively new to the world of SQL server configuration I scratched my head for a few minutes trying to work out whether I had misunderstood the written guidance. I defined an index rebuild task according to the screenshot above in a SharePoint 2007 test environment, and used a Dynamic Management View (DMV) to validate the resultant fill factor setting. To be more specific, I queried the sys.indexes catalog view and - confirming my suspicions – the fill_factor column displayed a value of 30!

"Logical inversion failure"

I discussed my observation above with Neil Hodgkinson from Microsoft. Neil is a SharePoint 2007 and 2010 MCM and as well as knowing a shed load about SQL is a very helpful guy.

He describes the issue as "logical inversion failure" which is a lot more concise than my attempt to explain it. Rather than putting the recommended free space amount in the screenshots, Microsoft appear to have mistakenly put the inverse quantity: the recommended percentage that pages are filled.

Neil assured me that the Technet documentation will be updated in due course and I'd like to take the opportunity to say thanks for the prompt response.

He also made one observation that I hadn't considered: setting a server-wide fill factor may not be appropriate as non-content databases and particularly non-SharePoint databases (in the case of a shared instance) may not benefit from the change.

What's the damage?

I know a bunch of SQL people who have never even considered using the UI to create a maintenance plan as they prefer to use scripts for everything. Those people will most likely be unaffected by the typos in the two screenshots above.

I also know a lot of "accidental" SharePoint DBAs (I would consider myself to be one) who like to use the SSMS GUI to create maintenance plans due to the shallow learning curve. There is a fair chance that those people will be affected by the typos shown above, in which case I would consider it important that the fill factor setting is rectified.

The good news is that as far as I am aware this is relatively straightforward to resolve by changing the "free space per page percentage" to either 30% (SharePoint 2007) or 20% (SharePoint 2010) and performing an index rebuild. Although this is a very expensive operation, it could well be a one-off task assuming that you have the correct fill factor set. My advice would be to pick a sensible time during off-peak hours where your users won't be too cheesed off if your SQL server CPUs(s) happen to hit 100% usage for a while (obviously this will depend on your specific configuration but you get the idea).

Note that although scheduling an index reorganisation task can often be a suitable alternative to rebuilding them (it's cheaper), it doesn't change the fill factor assuming you are using the SMSS UI. The fill factor option "applies only when an index is created, or rebuilt" according to MSDN.

Also note that if you are using Windows SharePoint Services 3.0 SP2 and later, the problem may not be as significant as you might think, see the next section…

Do I even need to schedule an index rebuild?

I couldn't finish this blog post without mentioning the proc_DefragmentIndices stored procedure that was introduced for WSS 3.0 in this KB and remains in SharePoint Foundation and Server 2010. From Windows SharePoint Services 3.0 SP2 and later, the stored procedure is executed as part of a timer job to reduce fragmentation for search, profile and content databases.

The stored procedure rebuilds indexes that are heavily fragmented in order to improve performance. This is great as it means that Microsoft recognised that the use of non-sequential GUIDs as primary keys leads to heavy index fragmentation.

In light of the purpose of this stored procedure, do you need to schedule regular index rebuilds via a maintenance plan? "Probably not" is the best answer I can come up with given my limited knowledge of SQL server and even more limited knowledge of your specific environment. Personally, I find that scheduling a weekly index reorganisation task and leaving index rebuilds to proc_DefragmentIndices keeps fragmentation reigned in.

One more thing…

If you take a close look at the proc_DefragmentIndices stored procedure mentioned above, you will notice that Microsoft rebuild indexes with a fill factor of 80 for both SharePoint 2007 and 2010 despite the guidance contained in the 2007 white paper.

My stance on this is that if a stored procedure is going to execute on a daily basis and potentially change my index fill factor to 80 for heavily fragmented indexes, I may as well set the fill-factor setting to 80 (rather than 70) for consistency. You might find that your results differ but in our environments I have found that 80% fill factor (20% free space per page) is appropriate for both products.

Summary

Setting an appropriate fill factor according to the MS guidance is definitely worthwhile as it ensures that the fill factor is correct from the off, reducing index fragmentation due to page splits.
A server-wide fill factor may not be appropriate, particularly if you are sharing your SQL instance (i.e. it isn't dedicated to SharePoint).
The Microsoft documentation needs to be updated to show the correct settings for an index rebuild.
An index rebuild maintenance task can be useful to correct fill factor as a one-off.
Otherwise, the provided stored procedure can be relied upon to set fill factor correctly for indexes that are heavily fragmented.
A maintenance plan is still important to automate index reorganisation and check the integrity of your databases (in addition to backups).

I'd be interested to read any thoughts or further insights you might have on this.

Ben

0 Comment(s)

January 16

A (very) basic network primer for the SharePoint Admin

by Benjamin.Athawes

on 16/01/2012 22:00

Category: Networking

Over the last week or so I have spent some time configuring some new networking gear for one of eShare's numerous internal SharePoint farms and I thought I'd share a few of the lessons I've learned along the way.

My task seemed pretty straightforward: implement a couple of load balanced firewalls along with 3 isolated network segments. This is quite a common scenario for us as we deal with a lot of security sensitive clients who aren't big fans of back end servers sharing a subnet with those that are public facing. Indeed, there are few reasons not to implement a segmented network such as this as it provides a form of defence in depth should your perimeter servers be compromised.

However, the exercise made me feel somewhat schoolboy and I wanted to document a few "aha" moments for the other SharePoint admins out there that might need to wear a "networking" hat:

If you are fortunate enough to be configuring a green field network, ensure that you give yourself enough private IPs to play with. Don't fall into the trap of using a default address that might not give you a large enough address range. For example, using 192.168.1.0 / 24 will give you only 254 host addresses. Using 10.1.0.0 /16 gives you 65,534 hosts per subnet! Use a subnet calculator to help.

In order to send packets between network segments, a router is required. Unified Threat Management (UTM) gateways are very common as they allow firewall policies (routes) to be defined for controlled access between zones (e.g. you may choose to allow AD authentication traffic between your perimeter and internal networks).

VLANs can be used to "partition" a switch into multiple network segments. This can be a viable alternative to using a switch per network segment which may not scale well. Although many switches allow assignment of an IPv4 address, this might be purely to allow management via a Web UI (a switch is primarily a layer 2 device).

If you are using a UTM for routing, the default gateway on all hosts should typically be an interface on the firewall.

A different network address should be used for each VLAN. e.g. VLAN 101 = 10.1.0.0 /16, VLAN 201 = 10.2.0.0 /16 etc.

A lot of modern networking equipment – including both switches and firewalls – won't save changes as you go along. If you forget to save your changes and turn off the device you may have to start again, so I suggest taking regular backups. You have been warned!

If a lot of the above is news to you, then it might be worth getting some assistance from your vendor to ensure that you implement a secure network in a cost effective manner. This is especially the case if you are providing an externally facing network, as opposed to simply tinkering about with an internal development environment.

That's all for now folks, I hope this proves useful!

0 Comment(s)

November 15

My SharePoint Saturday IT PRO Session: Dodge the Bullet (with video)

by Benjamin.Athawes

on 15/11/2011 21:00

Category: Top 10...; Architecture; Conferences

25/01/2012 update

After what has been far too long since SPSUK, I have finally uploaded the slides for this session. They are available on slideshare. Enjoy!

Last Saturday I had the great pleasure of attending SharePoint Saturday UK 2011, organised by Tony Pounder, Mark Macrae (both from Intelligent Decisioning) and Brett Lonsdale (from Lightning Tools). I think everyone who attended agreed that the day was a huge success – a friend of mine commented on how welcoming and friendly we are as a community which was great to hear (he isn't a SharePoint person, although he is coming around to the idea of learning itJ). Sometimes I take this fact for granted and it's good to be reminded of how tight-knit we are.

I think Todd Klindt deserves a big shout out, as he flew over from the US to deliver a thought provoking keynote and an awesome session on PowerShell (oh, and he's a nice guy). I also met Anders Rask who presented a compelling set of reasons to use Web Templates which – as someone who is a little wary of Visual Studio – I just about managed to graspJ. Paul Grimley rounded off my day with a comprehensive set of considerations for global SharePoint deployments – Paul's discussion around performance over the WAN was particularly interesting.

I was lucky enough to be offered the opportunity to speak on the day which – whilst a little nerve wracking – proved to be every bit as exciting as I had hoped.

The session title was "Dodge the bullet: 10 ways to avoid common SharePoint administration mistakes" and the Twitter hashtag was #SPSUK08.

Watch the session: my good friend Ash recorded the majority of the session on his phone.
Slides: will be posted at some point this week once I have tidied up the accompanying notes.

If you were in the session – and even if you weren't – please feel free to post any questions or feedback you might have.

I received several great queries from the audience, and while I tried to answer them as best I could during the 60 minute session, I may follow up on over the next few weeks:

Will our environment start creaking before we hit the prescribed Web app limits?
What should the SQL server recovery model be set to on a development environment?
Are host-named site collections equivalent to host headers at the IIS site level?

The questions above are paraphrased so if I'm off the mark please let me know.

Related posts/sites:

Web [Application] limits in SP2010 - keep it low!
Bitesize Video Overview: Host-named Site Collections in SharePoint 2010
Determining NUMA node boundaries for modern CPUs
Configuration-Only Backup and Restore in SharePoint 2010 (Sean McDonough's blog – it's more detailed and therefore useful than mine)
SPDocKit Web site (the SharePoint farm configuration backup tool I mention during the session)

0 Comment(s)

November 09

Determining NUMA node boundaries for modern CPUs

by Benjamin.Athawes

on 09/11/2011 00:01

Category: Hardware; Virtualisation

Last Wednesday I had the pleasure of presenting at the East Anglia SharePoint user group (SUGUK). The user group is organised by Randy Perkins and Peter Baddeley who are both very friendly, knowledgeable SharePoint guys. Whilst my session aimed to provide some general guidance on SharePoint administration (I'm presenting a similar deck at SharePoint Saturday), the subject of this blog is a topic covered during the evenings first session: "SharePoint 2010 Virtualisation", presented by John Timney (MVP). To be more specific, this post discusses NUMA node boundaries in the context of virtualising SharePoint and hopefully raises some questions around whether the MS documentation should perhaps be updated to include guidance for larger multi-core processors (i.e. more than 4 cores).

Disclaimer
I feel the need to add a disclaimer at this stage as I am by no means an expert when it comes to NUMA or hardware in general. I do think however that my findings should be shared as the guidance from Microsoft almost certainly has a real impact on hardware purchasing decisions at a time when virtualising SharePoint is an industry hot topic (as perhaps evidenced by the great user group turnout).
Use this guidance at your own risk - seek the advice of your hardware vendor.

What is NUMA, and why should I care?

Let's start with a definition from Wikipedia:

"Non-Uniform Memory Access (NUMA) is a computer memory design used in Multiprocessing, where the memory access time depends on the memory location relative to a processor. Under NUMA, a processor can access its own local memory faster than non-local memory, that is, memory local to another processor or memory shared between processors."

So we can glean a few basic facts from that definition. NUMA is relevant to multiple processors and means that memory can be accessed quicker if it's closer. This means that memory is commonly "partitioned" at the hardware level in order to provide each processor in a multi-CPU system with its own memory. The idea is to avoid an argument when processors attempt to access the same memory. This is a good thing and means that NUMA has the potential to be more scalable than a UMA (multiple sockets share the same bus) design – particularly when it comes to environments with a large number of logical cores.

Remote and local NUMA node access

A possible NUMA architecture highlighting local and remote access. Source: Frank Denneman

As you can see from the diagram above, NUMA could be considered a form of cluster computing in that ideally logical cores work together with local memory for improved performance.

Before we proceed, it's worth noting that there are two forms of NUMA: hardware and software. Software NUMA utilises virtual memory paging and is in most cases an order of magnitude times slower than hardware NUMA. Today, we are looking at the hardware flavour – that is, CPU architectures that have an integrated memory controller and implement a NUMA design.

The "why should I care" part comes when one realises that NUMA should have a direct impact on deciding:

How much memory to install in a server (an up-front decision) and,
How much memory to allocate to each VM (an on-going consideration), assuming you are planning to virtualise.

In fact, Microsoft has gone so far as to say that "During the testing, no change had a greater impact on performance than modifying the amount of RAM allocated to an individual Hyper-V image". That was enough to make me sit up and pay attention. If you are one for metrics, Microsoft estimate that performance drops by around 8% when a VM memory allocation is larger than the NUMA boundary. This means that you could end up in a situation where assigning more RAM to a VM reduces performance due to the guest session crossing one or more NUMA node boundaries.

The current Microsoft guidance

We've looked at the theory and hopefully it's clear that we need to determine our NUMA node boundaries when architecting a virtualised SharePoint solution. Microsoft provides the following guidance to help calculate this:

"In most cases you can determine your NUMA node boundaries by dividing the amount of physical RAM by the number of logical processors (cores). It is recommended that you read the following articles:

Let's take a look at the bold text above which represents the "rule of thumb" calculation that is most commonly referred to when discussing NUMA nodes. Michael Noel (very well known in the SharePoint space) uses this calculation in most of his virtualisation sessions, a good example being available here:

"A dual quad-core host (2 * 4 = 8 cores) with 64GB RAM on the host would mean NUMA boundary is 64/8 or 8GB. In this example, allocating more than 8GB to a single guest session would result in performance drops".

At first glance the [RAM/logical cores] calculation provided by Microsoft might seem compelling due to its simplicity. I would guess that the formula was tested and found to be a reliable means of determining NUMA node boundaries (or at least performance boundaries for virtual guest sessions) at the time of publication.

However, as you will see later I haven't found a shred of evidence to suggest that this guidance actually provides NUMA node boundaries for modern (read: more than 4 logical cores) processors. That's not to say that it's bad advice: in a "worst case" scenario (i.e. the guidance doesn't work for larger CPUs); the outcome would be that those that have followed it to the letter will be left with oversized servers (with room for growth). In a "best case" scenario I am completely off the mark with this post and everyone (including me) can rest assured that our servers are sized correctly. It's a win-win.

Applying the current NUMA node guidance in practice

As diligent SharePoint practitioners we always aim to apply the best practice guidance provided by Microsoft and the NUMA node recommendation should in theory be no exception. In order to provide an example we need to consider any related advice, such as Microsoft's guidance on processor load:

"The ratio of virtual processors to logical processors is one of the determining elements in measuring processor load. When the ratio of virtual processors to logical processors is not 1:1, the CPU is said to be oversubscribed, which has a negative effect on performance."

While we're discussing processor sizing, let's not forget that Microsoft list 4 cores as a minimum requirement for Web and Application servers. We now have two potentially conflicting guidelines:

For large NUMA boundaries we need to either install a large amount of physical memory (an acceptable if potentially expensive option) or keep the number of logical cores down.
To consolidate our servers we need to ensure that there are enough logical cores to allow for a good virtual: logical processor ratio.

Let's apply those guidelines to a relatively straightforward consolidation scenario in which we want to migrate two physical servers to one virtual host. Let's assume that each server has 16GB of RAM and a quad core processor at present. Allowing some overhead for the host server, I think we would be quite safe with 10 logical cores and say 36GB of RAM… except we can't buy 5-core processors. We will have to settle with two hex-core processors, giving a total of 12 logical cores.

So what would our NUMA boundary be in that scenario?

36GB / 12 cores = 3 GB RAM.

That doesn't sound right. If each guest session is allocated 16GB RAM we would be crossing 6 NUMA boundaries! From what we've gathered so far, performance would rival that of a snail race.

Let's instead flip the formula on its head and work out how much RAM we need to allocate to ensure that we don't cross a NUMA boundary. 16GB NUMA node * 12 CPU cores = 192 GB RAM. That doesn't sound right either given that we were simply trying to consolidate two VMs. Our options appear limited to buying a shed load of memory or reducing the amount of memory allocated to each guest session. The downsizing option would probably mean we need an additional server or two meaning we would be scaling "down and out". A larger number of "thin" servers can potentially perform better than a smaller number of "thick" servers so this isn't necessarily a bad idea (although your license fees will go up!J).

At this stage it seems that the frequently cited NUMA requirements are very restrictive and limit us to either oversizing servers or changing our planned topology. In light of what we know so far about NUMA and our brief discussion above I think the question that we are all asking ourselves is: does the NUMA boundary guidance still apply for modern CPUs?

A deeper dig

In an attempt to provide evidence to help answer our question I decided to do a little research around NUMA and took a peek under the hood using metrics obtained from appropriate tooling (we'll be using CoreInfo and Hyper-V PerfMon stats).

Given that NUMA is a memory design that is relevant to CPUs, I figured that a good place to start would be two big players in this space: AMD and Intel. Presumably if they are manufacturing chips that implement NUMA they provide some guidelines around performance. I grabbed the following resources straight "from the horse's mouth":

Performance Analysis Guide for Intel® Core™ i7 Processor and Intel® Xeon™ 5500 processor

NUMA aware heap memory manager

A supporting statement (although not authoritative in the same way that statements regarding CPUs from Intel or AMD are) from a MSFT employee reads as follows:

"Today the unit of a NUMA node is usually one processor or socket. Means in most of the cases there is a 1:1 relationship between a NUMA node and a socket/processor. Exception is AMDs current 12-core processor which represents 2 NUMA nodes due to the processor's internal architecture."

So far we have found evidence to suggest that in general, the CPU socket (not logical core) represents the NUMA node boundary in modern processors. To reinforce our findings, let's see what CoreInfo and PerfMon have to say on the matter.

For reference the server in this example is a HP DL 380 G7 with 64GB RAM and two hex core Xeon E5649s (which implement NUMA). The CPUs have hyper threading enabled. The OS is Windows Server Core Enterprise 2008 R2 SP1.

EDIT 15/11/2011: Thanks to Brian Lalancette for pointing out that NUMA nodes are also exposed within Windows Task Manager - see the screenshot below. This is probably the quickest way of determining how many nodes you have assuming the feature is accurate.

Hex core HP server with Intel E5649s: Task Manager

Hex core HP server with Intel E5649s: CoreInfo

Hex core HP server with Intel E5649s: PerfMon (ProcessorCount)

Hex core HP server with Intel E5649s: PerfMon (PageCount)

NUMA node page count

There are a few points of interest in the screenshots above:

CoreInfo tells us that cross-NUMA (remote) node access cost is approximately 1.2 relative to fastest (local) access.
Hyper threading means that 24 logical cores are displayed in both CoreInfo and PerfMon.
PerfMon indicates that 12 processors are associated with each NUMA node.
Only two NUMA nodes show in both CoreInfo and PerfMon.
Each NUMA node contains 8,388,608 4K pages or 32 GB RAM.

Which leads us to the following results:

The formula provided by Microsoft doesn't work in this case assuming CoreInfo and PerfMon are correct (the MS guidance would indicate there are 12 NUMA boundaries of approximately 5.3 GB each).
In this particular case, there is a 1:1 ratio between CPU sockets and NUMA nodes, meaning that there are 2 NUMA nodes of 32 GB each.

Ask the expert

With some initial analysis in hand (but without any supporting data around performance) I thought it worth sharing with an industry expert - Michael Noel. Michael was kind enough to respond very promptly with this insight:

"As it looks, the chip manufacturers themselves changed the NUMA allocation in some of these larger core processors. When we originally did this analysis, the common multi-core processors were dual core or at most quad core. On these chips, the hardware manufacturers divided the NUMA boundaries into cores, rather than sockets. But it appears that that configuration is not the same for the larger multi-core (6, 12, etc.) chips. That's actually a good thing; it means that we have more design flexibility, though I still would recommend larger memory sizes…

CoreInfo is likely the best tool for this as well, agreed on your approach."

Conclusions

Viewing this data on one physical server isn't exactly conclusive. I do think that it raises questions around whether or not Microsoft's prescriptive guidance is causing a little confusion when it comes to virtual host and guest sizing. Without additional data my suggestion at this stage would be to adjust the guidance to take more of an "it depends" stance rather than providing a magic number. Hopefully the vendors will release some performance stats related to NUMA and virtualisation for modern (larger) CPUs that will help guide future hardware purchasing decisions.

To be fair to MS, they do provide this pearl of wisdom: "Because memory configuration is hardware-specific, you need to test and optimize memory configuration for the hardware you use for Hyper-V." While that should technically let them off the hook, I for one would prefer that the rule of thumb be removed if it starts to become less relevant for modern hardware.

In short, don't assume that your NUMA boundaries are divided into cores – it very much depends on your specific CPU architecture. My advice would be to check using tools such as CoreInfo and performance monitor or ask your hardware vendor in advance.

7 Comment(s)

October 18

Bitesize Video Overview: Host-named Site Collections in SharePoint 2010

by Benjamin.Athawes

on 18/10/2011 19:30

Category: Architecture

Today I published a post on SP365 which attempts to explain host-named site collections to the uninitiated in the form of a bitesize video overview.

Host-named site collections are a key part of SharePoint 2010's multi tenant support and offer a huge amount of scalability. They provide support for multiple root named URLs (vanity URLs) and in many cases are a valid alternative to creating additional Web applications.

If you have any feedback or suggestions, feel free to add a comment.

2 Comment(s)

October 11

Buying SharePoint servers: a checklist for SMBs – part 1 of 2

by Benjamin.Athawes

on 11/10/2011 22:58

Category: Hardware

Today is a quick post based on some recent work we have been doing at eShare over the last few weeks. You will be pleased to know that we won't (for once) be discussing Web Applications. Instead, I thought I would document a high level checklist of the things to think about when purchasing a new SharePoint server, with a focus on hardware. Part 2 will include a little more detail for items that require explanation (such as storage).

You might think that the timing of this post is poor in light of all the excitement about cloud computing, and you may well be right. However, I personally think that on-premise will remain a viable option for the foreseeable future and I figured it would be useful to post it up for my own reference (if for no other reason).

This is aimed mainly at SMBs looking to make sure they have most of the bases covered prior to purchasing a server. It is by no means complete and is focussed on the items that smaller shops seem to care about (no mentions of "green computing" here then).

I have also tried to include some of the "gotchas" that we have experienced in the past. High on my list of pet hates is purchasing inadequate storage space, and in particular assigning an absurdly low amount of capacity to the server operating system. I've made this mistake in the past and it can lead to a huge amount of wasted admin effort.

A few points are repeated deliberately based on the fact that they apply to multiple bits of kit. For example, you can't very well determine a NUMA node boundary without knowing the number of logical CPU cores and quantity of RAM you are planning to purchase.

Note that this is not a performance or capacity planning guide as such, although some of the bullets will hopefully prompt you to refer to your own plans to ensure that you buy sufficient kit. If you don't have a plan, a starting point would be Performance and capacity management on Technet which is comprehensive to say the least.

The SharePoint server buyers checklist

Base / Chassis

How much space is it going to take up (i.e. 1U, 2U etc. for rack mounted kit)?
How many drives do you need (see storage)?

Processor(s)

Consider licensing implications for multiple CPU sockets (e.g. SQL server)
NUMA nodes
If virtualising, have you got enough cores for all of your VMs and the host OS?

RAM

Relatively cheap – much easier to buy now rather than upgrading later
NUMA nodes
Do you need any spare slots for future growth?

Availability

Think about resource density (don't put it all on one box)
One probably isn't enough. Consider multiple:
- NICs
- PSUs
- Disks (configure RAID)
Consider purchasing from multiple vendors to improve resilience (reduces chances of servers failing at a similar time)

Storage

Allow space for the host OS (the minimum requirements state 80GB)
Allow space for the host OS!
Partitioning vs. dedicated spindle for OS
Consider your storage architecture (DAS vs. SAN)
Calculate your IOPS (especially for SQL server) - determined mainly by no. disks and speed (RPM)
Plan your RAID configuration; some vendors require you to configure yourself for specialised setups
RAID controller card - seek vendor advice to ensure it supports your RAID setup
Think about where your backups are being stored
SSD in production – performance vs. risk
Do you have enough spindles (e.g. splitting search index role from query)?

PSUs (Power Supplies)

Buy two for redundancy
Do you need a UPS (your data centre may provide this – what happens when the lights go out)?

Networking

Buy enough NICs / ports - remember 2 load balanced ports on the same physical NIC != resiliency
Gigabit Ethernet at a minimum

Practicalities

Do you need a CD drive for installing the OS etc?
Does the vendor provide rails to mount the server in a rack?

The commercials

Is the vendor assembling it for you?
Buy a warranty (it will break eventually – really!)
Shop around and don't accept the retail price

Operating System license

Dictated by both hardware and software: e.g. CPU sockets, RAM, VMs on machine

<Your checklist here, add a comment!>

In the second part of this series I'll look at a few of the items above in a little more detail.

Ben

0 Comment(s)

August 16

I'm speaking at SharePoint Saturday UK

by Benjamin.Athawes

on 16/08/2011 18:25

Category: Presentations; Conferences

I am very pleased to say that my speaker submission for SharePoint Saturday UK 2011 has been approved.

The IT PRO session will be titled "Dodge the Bullet - 10 common SharePoint administration mistakes".

During the session I will be looking to cover some of the lessons learned from 3 years of experience in the SharePoint administration space, including:

Why you MUST document your SharePoint farm configuration
Correcting the default SQL server settings to improve performance
A look at the SP1 guidance around content database sizing

I am hoping to make this a discussion as opposed to a lecture - I still have a lot to learn after all!

If there is something in particular that you would like me to cover please add a comment to this post, or contact me on Twitter.

I look forward to seeing you at the event!

Ben

SPSUK Logo

6 Comment(s)

July 09

Content DB sizing support in SharePoint 2010 SP1: the 50,000 ft view

by Benjamin.Athawes

on 09/07/2011 17:00

Category: Architecture; SQL

11/07/2011 Update:

I have had some feedback around this article that made me realise that I inadvertently gave RBS a bit of a bashing. It's a useful technology that - used in the right scenarios - can significantly improve maintainability and scalability. The main point of the RBS section was to debunk the myth that RBS provides a means of bypassing the [current] data sizing boundaries that Microsoft provide. I've got nothing against it, and to prove it here is a Microsoft WhitePaper that puts RBS in a positive light and presents the benefits and operational characteristics of the feature that are beyond the scope of this article.

The recent post from the SharePoint Product Team blog on Data Storage Changes for SharePoint 2010 has caused quite a stir.

This is not least because in specific scenarios, Microsoft have confirmed that they will support customers with content databases that are between 200GB and 4TB in size, and in one scenario have removed the SharePoint supported limitation altogether (thus meaning that customers will be limited to the SQL Server boundaries).

I encourage you to read through the barrage of decent information that has already been published online, including:

Data Storage Changes for SharePoint 2010 from Microsoft – this is the post that everyone is talking about.
Announcing: New Larger Content Database Size Limits and RBS Clarifications from Paul Andrew.
SharePoint 2010 – Now in New Mega Size! From Joel Oleson.
The SharePoint 2010 4TB content database limit fine prints - just a warning! From Wictor Wilén

Given that the sizing limitations have been a recent hot topic on Twitter / Google + (especially in the context of RBS), I wanted to share my own thoughts on the new guidance.

Whilst the SharePoint Product Team blog includes details around using the SQL RBS FILESTREAM provider to enable iSCSI connected storage (and therefore lower cost NAS storage), the focus of this post is on the sizing recommendations.

Before I begin note that none of the information below is official – it is simply my own interpretation of the recent guidance.

A step back

Let's start by rewinding a couple of weeks. Way back then, there were some heated debates on Twitter and various forums surrounding the 200GB supported content database limitation. In particular, the main theme was whether or not customers could use RBS to get around this limit.

This is largely because prior to the SP1 guidance, we only had a 200GB supportability limit to work toward for collaboration scenarios which lead customers to question the scalability of SharePoint. It seemed logical to conclude that – given the 200GB limitation - storing BLOBs outside of the SQL DB would mean that only metadata stored within the DB itself would count towards that limit. This misconception was encouraged by a lack of guidance from Microsoft (the software boundaries document made no mention of RBS) and no concrete explanation around the sizing limits.

The options for customers were limited to:

Assuming that RBS provided a means of bypassing the 200GB limitation and scaling up or,
Assuming that the 200GB limitation included metadata and BLOBs, regardless of BLOB location and scaling out by creating new Content Databases.

In case anyone hasn't read the links above (you should), it turns out that assumption #2 was correct as far as the Microsoft supportability guidance is concerned.

I think it's worth mentioning at this stage that the advice back then was – and still is – to ensure that site collections remain below 100GB in order to allow for backup/restore operations. The one exception to this is where a site collection is the only one stored within a content DB, in which case the size of the site collection is limited to the size of the content DB and backup/restore operations must be taken at the SQL DB level.

OK, what about this 4TB business?

Fast forward to today and we have some new guidance from the SharePoint Product Group based on lessons learned over the last 12 months. Rather than regurgitate this information I refer you to the links at the top of this post, and have attempted to provide a summary of the sizing changes:

SP2010 SP1 - what's changed?
* "Highly Optimised" refers to the guidance contained within the SharePoint Server 2010 capacity management: Software boundaries and limits document. In particular, the Content Database Limits section lists specific requirements that need to be met.

There are a few points to note in the above table:

The original 200GB recommendation remains for the majority of scenarios.
The new supported scenarios only apply to optimised environments.
There are no changes in terms of site collections limits.
RBS doesn't change any of the sizing recommendations.

For detailed recommendations beyond the summary provided above please head over to the Product Team Blog.

Hang on; RBS makes no difference to content DB sizing?

Here is the official statement from Microsoft:

"The content database size includes both metadata and BLOBs regardless of where the BLOBs are located and use of RBS does not bypass or increase these limits."

I think the formula below helps simplify this statement:

Content database size == [Metadata + BLOBs], regardless of where the BLOBs are stored.

This is not new and was the case prior to the SP1 guidance.

Although this is a gross oversimplification (I deliberately miss off various GUIDs including those that identify the SPSite objects), let's look at a straightforward example to illustrate this:

RBS in SharePoint simplified

In this example, we have reached the supported "Content database size" limitation for a typical collaboration usage scenario. Without a highly optimised SQL Server environment and advanced disaster recovery story we will need to scale out by adding additional content databases and potentially moving site collections using Move-SPSite.

Limits apply at the abstracted level, i.e. SPContentDatabase and SPSite, so although in the above scenario the actual SQL Content DB is only 10GB in size (the metadata in this case makes up for 5% of SPContentDatabase), the "Content Database Size" as far as SharePoint is concerned is 200GB so we have reached the supported limit for environments that do not meet the SQL Server optimisation requirements.

10/07/2011 Update:

Wictor Wilén kindly reviewed this post and suggested I add an explanation as to why utilising RBS doesn't affect the supported limits. Fortunately, Chris Mullendores' RBS post covers this off nicely:

"Activating RBS so that your 100 2GB files can be pushed out of the database file is not going to change the fact that SharePoint is only processing 100 rows of data. Put simply, the effort required to process the relational, structured data that is used to browse and present the SharePoint UI (and related content) will NOT be significantly impacted/benefited by RBS. If you have large lists in SharePoint, you're still going to have large lists after RBS, and that is where SQL is spending most of its time... sorting through the rows of data necessary to present SharePoint content. It isn't until you click directly on the file link that RBS is really even doing anything.."

Chris makes several good points here, one being that 100 items still means that 100 rows needs to be processed, RBS or not.

So… what's the point of RBS?

We now know that RBS is not there to help us get around the content DB sizing limitations - those of you who have deployed RBS knew that already though, right? :-).

For those of us that haven't, and are considering RBS for our environments, let's walk through a few valid scenarios based on the Product Teams blog:

Taking advantage of commodity storage – this is the key scenario and the new support for iSCSI connected storage improves this story.
Document Archive scenarios – BLOBs are immutable so frequent writes can reduce performance.
Getting around the 10GB content DB limitation imposed by SQL Server 2008 R2 Express: FILESTREAM data doesn't count toward the limit.
Improving performance where BLOBs are, on average larger than 1MB (if average BLOB size is below 256KB they may as well be stored in-line; 256KB-1MB is a grey area and it depends on your environment).
[11/07/2011] Enhancing maintainability:

Improving performance during index rebuild maintenance tasks.
Reducing the effective size of the SharePoint content database and backup size.
Reduced transaction-log-write overheads, the benefit of which will depend on the frequency with which you truncate the transaction log during a backup.

Another benefit offered by RBS is the capability to perform shallow copies whilst using the Move-SPSite cmdlet. This can be a big time saver if you frequently migrate site collections between content databases, and was added in SP2010 Service Pack 1. In essence, structured data is moved between content databases whilst unstructured data (remote BLOBs) remain untouched.

10/07/2011 Update:

Wictor also highlighted the new content database item limit: 60 million items including documents and list items. We can use this number alongside the other content DB sizing limits to review the supported extremes in the context of RBS:

60 million items in a typical usage scenario (200 GB) would mean that each file would be on average 3.49KB. This is clearly not a realistic collaboration environment as documents are typically larger than this.
60 million items in a highly optimised environment (4 TB) would mean that each file would be on average 71KB. This is feasible but clearly not a scenario for RBS assuming average file size > 1MB is optimal.
60 million items in a highly optimised, primarily read only document repository environment (no size limit) may be feasible using RBS or otherwise, assuming you meet the aforementioned requirements.

The second scenario really underline the point that RBS is most useful for storing a small number of large (> 1MB) BLOBs, as opposed to a large number of small (< 256 KB) BLOBs. We should note that in the third, read only archive scenario that BLOBs are immutable which means that frequent updates will kill limit performance in a large scale (> 4TB) document repository, RBS or not.

For further reading on valid RBS scenarios I suggest checking out Chris Mullendores post about RBS (Chris is a PFE and MCM candidate).

Wrap up

The new supported limits are great news for customers with highly optimised SQL server environments and an advanced disaster recovery strategy.
For the general usage scenarios, 200GB is still king and environments can scale out by adding additional databases.
The content database sizing recommendations apply at the SharePoint Content Database (SPContentDatabase) level, not SQL DB level.
There are several scenarios in which RBS can be useful but bypassing the supported DB sizing limits is not one of them.

3. Deploying and Managing Applications (24 percent)

Manage Web Applications.

From the Learning Plan:

This objective may include but is not limited to: managing databases, Web Application settings, security, and policies

My revision notes (Web applications)

The "User Policy" option within Web Application management is equivalent to the "Policy for Web application" option in MOSS Central Administration. The "Permission Policy" option contains the definitions of the permission levels that can be selected within user policy (e.g. Full Read).
Web application policies are the only place in SharePoint where an object can be denied access - this policy cannot be overridden by site level changes to permission (such as those made by a site collection administrator). Policies are typically used for audit purposes - for example auditors could be granted full read with little administrative effort.
Inherited permissions are generally easier to manage than broken site permissions.
Permissions should generally be configured on a per-group basis, as opposed to granting individual permissions which can quickly become unmanageable.
Anonymous user access in SharePoint is determined by the "Limited access" permission level. This permission level is automatically assigned at the root Web if a user or group is given access to a sub site with broken (i.e. not inherited) permissions.
Anonymous access to application pages can be controlled by the ViewFormPagesLockDown feature.
The Databases category within Central Administration is very similar to that used for MOSS - one obvious exception is that a failover can be specified for high availability purposes. The database schema level can also be checked - this is important in SP2010 as databases can be upgraded separately from the farm binaries (another high availability feature as downtime is minimised).

Manage site collections.

From the Learning Plan:

This objective may include but is not limited to: managing site collection policies, features, caching, and auditing; configuring site collection security; configuring multi-tenancy; and configuring site collection quotas and locks

My revision notes (site collections)

Options for creating new site collections are identical to those in MOSS with the exception of not being forced to select a template upon creation - you can opt to select one later and delegate this job to the site collection administrator.
The BLOB cache is a disk-based caching mechanism that is enabled on a per Web application basis in the site web.config file. it is especially useful where content is largely static - e.g. a public facing Web site.
The output cache (OFF by default) can result in substantial gains in throughput, but uses additional Web server memory due to files being retained for longer. It is configured on a per site collection basis and is only available where the publishing feature is being used. It can also be configured using the Web application configuration (web.config) file, but will then apply to all cache profiles in all site collections.
Similarly, the object cache (ON by default) reduces the amount of traffic passed between WFE and SQL servers. Again, it can be configured at the Web application and site collection level, and requires the publishing feature to be enabled.
Site collection locks can be used to place a site collection in a read only or inaccessible state.
Site collection quotas can be configured to limit site storage, send warning emails upon reaching a certain threshold and limit sandboxed solution resource usage.
Multi-tenancy in SharePoint 2010 enables hosted service providers to partition client data using shared resources. Hosting is a "first class citizen" (according to Spencer Harbars).
Multi-tenancy provides a real alternative to hosting a Web application per client (which does not scale).
Grouping (and partitioning) tenants is enabled through the use of Site Subscriptions and an associated ID (GUID) which must be created via PowerShell or the OM.
Feature packs (sets) allow grouping of site and Web scope features - hosters could use this to group by license type, ISVs can use to package features.
Host-named site collections allow "vanity URLs" to be used by tenants, even for SSL enabled sites.
"Content deployment" is the authoring of content in one site collection that will be deployed to another site collection according to a schedule or on an as-need basis.

Deploy and manage SharePoint solutions.

From the Learning Plan:

This objective may include but is not limited to: deploying and managing SharePoint solution packages, managing sandbox solutions, and managing user solutions

My notes (solutions)

Solution resource quota information is available within the "Solutions" gallery underneath top level site settings.

5 Comment(s)

1 - 10

RSS Feed

Alert Me

Tales from a SharePoint farm

Tales from a SharePoint farm

Quick Launch

PowerShell (IT101-IT102)

SQL (IT103-IT104)

User Profile Service (IT105)

Delivering Business Applications (CS706)

Building a new Intranet (IW507-IW508)

Search (IT109-IT110)

Capacity planning and performance testing (IT112-IT113)

Exploring SharePoint Enterprise features (BUS314)

What's next? (BUS315)

Office 365 (IT116)

A step back

OK, what about this 4TB business?

Hang on; RBS makes no difference to content DB sizing?

So… what's the point of RBS?

Wrap up

Further reading:

3. Deploying and Managing Applications (24 percent)

Manage Web Applications.

Suggested reading (Web applications)

My revision notes (Web applications)

Manage site collections.

Suggested reading (site collections)

My revision notes (site collections)

Deploy and manage SharePoint solutions.

Suggested reading (solutions)

My notes (solutions)

Recent Posts

Blogroll

Recent Bookmarks

‭(Hidden)‬ Blog Tools