IMO the problem is that your primary key is CLUSTERING !

This also has an impact to _all_ NCI !

Drop and recreate your Primary key but make it non-clustering.

Create a clustering...

DECLARE @Date_Time_Requested  DATETIME

DECLARE @Count_TimeOut_Records INT

DECLARE @MailMessage  VARCHAR(8000)

DECLARE @NewLine   CHAR(2)

DECLARE @Oldest_Date  DATETIME

DECLARE @EmailAddress  VARCHAR(50)

DECLARE @MailSubject                VARCHAR(150)

---Initialisation

SELECT @Oldest_Date = getdate()

SELECT @Count_TimeOut_Records = 1

  Set @EmailAddress = 'yourmailaddress@yourprovider.whatever'

  SELECT @NewLine = char(13) + char(10)

  SELECT @MailSubject = 'TEST  !  (...

@query = 'select * from AIS.dbo.vwOrdersShippedNotInvoiced'

this may "sound" redicule, but that's how it always works overhere

I suppose each node hosts 2 active instances of sqlserver.

On each node the active instanceses will compete for the ram (6Gb < 2*4Gb) and cpu (4speed cpu < 2*4slow ? ) ....

btw "...The databases migrated from four standalone servers with quad processors and 4 to 6GB of memory...."

could it be that .... 4 * 4slow + 4*(4 to...

may I add ....

DBCC dbreindex all your tables and indexed views and indexes.

dbcc updateusage(yourdb) with count_rows

sp_updatestats all your db

and SP4 has changes regarding implicit datatypeconversion ! This may have a...

http://blogs.msdn.com/lcris/archive/2005/06/10/428178.aspx

explains a bunch of it !!

select

replace( cast ( serverproperty('ProductVersion')