troubleshooting Question

Query performance slow down after migrating to SQL Server 2008 R2.

Avatar of OKE-JP
OKE-JPFlag for Japan asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008
22 Comments4 Solutions2765 ViewsLast Modified:
Hi experts!

There is a new server that runs SQL Server 2008 R2.
I moved my user DB from the old server that runs SQL Server 2005 SP1.

This new server has high-spec hardwares, CPU, memory and disk system.
Every parts are  better than the old server's.
So, in most cases, the new server works quite fine in performance.

But the next  query became bad in performance.

-- ORIGINAL
SELECT
	sob.id
,	MAX(jnl.jnlID) AS jnlID
FROM
	sysobjects sob
LEFT JOIN
	MyJournalTbl jnl
ON
	jnl.object_ID = sob.id
WHERE
	sob.name = 'MyTableName'
AND	sob.xtype IN ('U','V')
GROUP BY
	sob.id
;

I am not sure why this query became bad in performance.
But I find the next query is not so bad.

-- TESTING
SELECT
	sob.id
,	jnl.jnlID
FROM
	sysobjects sob
OUTER APPLY
	(
	SELECT
		TOP (1)
		jnlID
	FROM
		MyJournalTbl
	WHERE
		object_ID = sob.id
	ORDER BY
		jnlID DESC
	) jnl
WHERE
	sob.name = 'MyTableName'
AND	sob.xtype IN ('U','V')
;

I can not understand what is go on with these queries.
Please let me know.

Thanks,
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 4 Answers and 22 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 22 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros