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

please compare the execution plans of the queries on the 2 servers.

usually, either one server is using parallel query execution, actually slowing down performance for small queries, or the stats need to be recomputed on the tables/indexes.

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 allOpen 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 allOpen 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,

can you please show the explain plan?
what "performance" problem are we speaking about?

can u check this query ...

How is the performance ?

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

Select allOpen in new window

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,

visibly the issue is here:
183556324      1                  |--Index Seek(OBJECT:([MyDBName].[dbo].[MyJournalTbl].[IDX_MyJournalTbl_max] AS [jnl]), SEEK:([jnl].[object_ID]=[MyDBName].[sys].[sysschobjs].[id]) ORDERED FORWARD)      1      15      14      Index Seek      Index Seek      OBJECT:([MyDBName].[dbo].[MyJournalTbl].[IDX_MyJournalTbl_max] AS [jnl]), SEEK:([jnl].[object_ID]=[MyDBName].[sys].[sysschobjs].[id]) ORDERED FORWARD      [jnl].[jnlID]      1.317166E+07      14.71868      14.48898      11      29.20766      [jnl].[jnlID]      NULL      PLAN_ROW      0      1

many rows returned, and takes indeed most of the time.

so, I would try to drop an recreate this index: IDX_MyJournalTbl_max

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 allOpen 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 allOpen 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,

can you recreate the index with "descending" order for the relevant column?

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 allOpen 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,

glad I could help, though I'm surprised of the "average" grade ...
if it's MS that changes specs, it's not the experts ...
and yes, I have seen cases where queries change behavior due to a changed query explain plan due to a changed engine ...

and I totally agree about the readability comment.
though usually, as one often needs more columns from the "max" row, I do prefer my technique, with some comments it's fine even for non-dba ...

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,