"There are definitely times that I think the people that build the system stored procedures and other code we can see don't really work with SQL Server. It seems that best practices aren't followed and things aren't even well coded."

Agree with this comment but please realize that many of the system stored procedures are identical to those from in the 1993 Sybase version and probably are from earlier versions. A lot has changed in the area of RDBMSs in the last 15 years.