I was making similar choice recently for my new offroad bikepacking / adventure / mellow trail hardtail build and did some research, also I've tried a new SID Ultimate on a rent XC boke. To make a recap:
New SID Ultimate
Pro:
plenty stiff enough with 35mm stanchions, very light, XC race oriented firm and fast, can be locked out to a fully-rigid state, great base tune out of the box, very pedal-friendly
Cons:
29" only, 100mm and 120mm only, 44mm offset only, not as adjustable as pike (rebound and lockout only), can be too firm and quick, rides higher in travel and ramps fast - not so good for rough trail abuse
Pike Ultimate
Pro:
120mm-160mm, 42mm and 51mm offset, can be adjusted for different use (LSC, 3-position lockout, rebound), much plusher with bigger oil/air chambers
Cons:
No 100mm, heavier (over 300g more), not as firm when fully locked out, not as pedal-friendly as SID, requires more effort to setup
To sum things up:
New SID is better fork if your trails are mild or more XC oriented, you ride plenty of paved surfaces, you want maximum pedal efficiency, you don't need more than 120mm travel, you often reach for lockout on your current fork, you don't want to mess with settings, you want a lighter fork and lighter bike overall. Short offset works better on modern frames designed for such forks (can be a slight con for someone).
Pike Ultimate large range of travels and adjustability makes this fork a better investment if you going to use it for everything from XC to trail riding and even bikeparks. For example, if you will decide to change frame to one that is more trail-oriented ro designed for longer travel you won't need to buy a new fork. If you're not a hardcore pedalhead or weight weenie, Pike makes a much better value fork over SID.