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

FYI:
The scheme of the MyJournalTbl is following:

CREATE TABLE MyJournalTbl(
	insertDatetime datetime NOT NULL,
	jnlID int IDENTITY(1,1) NOT NULL,
	object_ID int NOT NULL,
	pkValue int NOT NULL,
	eventType tinyint NOT NULL,
 CONSTRAINT PK_MyJournalTbl PRIMARY KEY CLUSTERED 
(
	jnlID ASC
)
)
;

CREATE NONCLUSTERED INDEX IDX_MyJournalTbl_max ON dbo.MyJournalTbl 
(
	object_ID ASC,
	jnlID ASC
)
;

Select all Open in new window

And records distribution is following:

object_ID      Sub_count
333960266      183556324       (<- MyTableName)
1834541669      24033980
1994698404      15715071
112263705      8646143
2135014687      4885342
1442820202      3633581
682537565      2484612
1314103722      1934803
1946698233      1323102
1863013718      1007312
2023014288      754868
586537223      607888
1778105375      403084
282536140      370696
2063346415      328428
283864078      274308
363864363      193296
1898489842      96034
1706697378      12200
1781841660      477

SELECT
	object_ID
,	COUNT(*) AS Sub_count
FROM
	MyJournalTbl
GROUP BY
	object_ID
ORDER BY
	COUNT(*) DESC
;

Select all Open in new window

Thank you non_zero.

I move the DB by detach-and-attach scenario.
And I changed the database compatibility level to 100.
After that I recreate statistics and reorgize table indexes.

Thanks,

Thank your angelIII.

The old server is shut down and it is not easy to re-boot.
So, I would like to solve this problem with only new server.

I tried the OPTION (MAXDOP1) but I could not fix it.

Thanks,

Thank you angelIII.

Before, the ORIGINAL query finished in a blink.(I think under 100 millisecond)
But now it needs 40 seconds.

STATISTICS PROFILE shows the attachment file.

Thanks,
SET-STATISTICS-PROFILE-ON.txt

Thank you non_zero.

You query, about sysobjects table, works very quickly.
Always finish within 0msec or 13msec.

Thanks,

Thank you angelIII.

I tried to drop and recreate IDX_MyJournalTbl_max index.
But there are not changed in performance and execution plans.

So, I thought I should use TOP expression and OUTER APPLY operator instead of to use LEFT JOIN, GROUP BY clause and MAX function.

If I can always expect to get the good performance, I will use TOP expression and OUTER APPLY operator.
But it might seem a little troublesome to use the TOP expression and OUTER APPLY operator.
I think LEFT JOIN, GROUP BY clause and MAX function are easy to use and understand for average coders.

What do you think about it?

Thank you RRobinho.

I guess I can see your suggestions.

But, in this case, the index that is the cause of the bad performance is just recreated.
Now, the index's  fragmentation is 0%.

I can not understand why the original query (LEFT JOIN, GROUP BY clause and MAX function) became so bad performance.
And I am not sure that I have to rewrite the query to the new one (TOP expression and OUTER APPLY operator).

To think about to simplify the coding, I prefer to use the original query to the other one.

Can you please give me advice?

Thanks,

Thank  you ScottPletcher,

I tried your query.

SET SHOWPLAN_ALL ON;
GO
-- 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
;
GO
-- EVALUATION --
SELECT
	sob.id
,	MAX(jnl.jnlID) AS jnlID
FROM
	sysobjects sob
LEFT JOIN
	MyJournalTbl jnl
ON
	jnl.object_ID = sob.id
AND	sob.name = 'MyTableName'
AND	sob.xtype IN ('U','V')
GROUP BY
	sob.id
;
GO
-- 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')
;

GO
SET SHOWPLAN_ALL OFF;

Select all Open in new window

Please see the result.
comp3.xls

TotalSubtreeCost of these queries are follows:

ORIGINAL = 35.26755
EVALUATION = 705.2021
TESTING = 0.00986072

Sorry, I could not understand what you let me know.
Please give me advice again.

Thanks,

I found the key point of this problem.
So, please let me explain the essence of my question again.

I have a SQL query like below:

SELECT
	jnl.object_ID
,	MAX(jnl.jnlID) AS jnlID
FROM
	MyJournalTbl jnl
WHERE
	jnl.object_ID = 333960266 --[ name='MyTableName' AND xtype IN ('U','V')]
GROUP BY
	jnl.object_ID
OPTION (MAXDOP 1)
;

Select all Open in new window

It works good performance on SQL Server 2005 SP1(90).
Bt it works very bad performance on SQL Server 2008 R2(100).

I compared the execution plans of both.

These plans are quite different to each other.
Obviously,  the plan of SQL Server 2005 is better than the plan of SQL Server 2008.

This is the detail of the plans.
Comp-2005-2008.xls

If these reached the opposite result, I was not so confused.
Because, it means the version up of the SQL Server give me a opportunity to improve my queries automatically.

Of course, I know there are many nice new features of SQL Server 2008.
But this result looks like the version up is taking a change for the worse.

Why the newer version, SQL Server 2008 works this way?
What can I do for this unwelcome behavior of SQL Server 2008?
Should I change methodology when I write a query?

Somebody, please give me a help!

Regards,

Thank you very much angelIII!

I tried to recreate the index with descending.
But unfortunately, the result was not changed.

And I tried to change the MAX function to MIN function as a trial.
This result was also some execution plan.

I have no idea what to do....

Thank you angelIII !

Your query ,ROW_NUMBER() OVER ( PARTITION BY ... WHERE sq.rn = 1 , works fine not only SQL Server 2005 but also SQL Server 2008.
And your article was very helpful for me. Thank you again.

I can deeply understand the meaning of this rn column.
The technique, ROW_NUMBER() OVER ( PARTITION BY ... WHERE sq.rn = 1, is my favorite when to reduce/limit data respect to something priority ranking.
I often use and love it.

Actually, your query is logically equivalent to the following:

SELECT
	jnl.object_ID
,	MAX(jnl.jnlID) AS jnlID
FROM
	MyJournalTbl jnl
WHERE
	jnl.object_ID = 333960266 --[ name='MyTableName' AND xtype IN ('U','V')]
GROUP BY
	jnl.object_ID
;

Select all Open in new window

Although I would like to use MAX ... GROUP BY ... in this case.
Because, MAX ... GROUP BY ... is much easier to write and understand for everyone even non-DB-pro.

So,  it is not easy to imagine the Microsoft changed the specification such a way for me.
Is this common sense for DBA of SQL Server 2008?

Anyway, thank you very much angelIII and all experts for your assistances.


Best regards,

Thank you very much angelIII.
I really appreciate your advises and your outstanding knowledge.

Please do not feel bad about the rigorous evaluation about the grade.
Although your all comments were quite helpful, I feel disappointed about this bad execution plan of SQL Server 2008.
Please understand the "average" is directed toward the poor execution plan.

I still never give up to figure out this problem.
I will continue my investigation in the future.
Because, I believe it will be very beneficial to everyone involved.

Anything you could do for me would be very much appreciated.

Thanks and best regards,