Solved

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

Posted on 2012-12-25
22
2,054 Views
Last Modified: 2013-01-02
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
;

Open in new window


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')
;

Open in new window


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

Thanks,
0
Comment
Question by:OKE-JP
  • 12
  • 6
  • 2
  • +2
22 Comments
 
LVL 19

Assisted Solution

by:Melih SARICA
Melih SARICA earned 50 total points
Comment Utility
Create statistics or reorgize Table indexes ..

And check ur database Compatibility elvel. if not set it to SQL2008(100)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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.
0
 

Author Comment

by:OKE-JP
Comment Utility
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
)
;

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
;

Open in new window

0
 

Author Comment

by:OKE-JP
Comment Utility
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,
0
 

Author Comment

by:OKE-JP
Comment Utility
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,
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
can you please show the explain plan?
what "performance" problem are we speaking about?
0
 
LVL 19

Expert Comment

by:Melih SARICA
Comment Utility
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

Open in new window

0
 

Author Comment

by:OKE-JP
Comment Utility
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
0
 

Author Comment

by:OKE-JP
Comment Utility
Thank you non_zero.

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

Thanks,
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
0
 

Author Comment

by:OKE-JP
Comment Utility
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?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Assisted Solution

by:RRobinho
RRobinho earned 50 total points
Comment Utility
If any query taking more time than normal its a performance issue of your sql server
u need to monitor your SQL server ..may be it is fragmented that's why some query taking more time you can perform several operation

*De fragmentation of your database
*Reorganize your table indexes
*Partitioning of the table if a table is too large

These operation will improve your sever performance
0
 

Author Comment

by:OKE-JP
Comment Utility
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,
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 50 total points
Comment Utility
Try putting the limiting criteria in the JOIN rather than the WHERE, since by default SQL processes JOINs first.

Also, you should stop using "sysobjects" and starting using "sys.objects", but we need to make that a separate change so we know what caused any performance changes.



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
0
 

Author Comment

by:OKE-JP
Comment Utility
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;

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

Author Comment

by:OKE-JP
Comment Utility
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)
;

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,
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
can you recreate the index with "descending" order for the relevant column?
0
 

Author Comment

by:OKE-JP
Comment Utility
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....
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 350 total points
Comment Utility
you may want to try some alternative syntax for the "max" part:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html

select object_ID, jnlID 
 FROM (
SELECT
	jnl.object_ID
,	jnl.jnlID 
, ROW_NUMBER() OVER ( PARTITION BY jnl.object_ID ORDER BY jnl.jnlID DESC) rn
FROM
	MyJournalTbl jnl
WHERE
	jnl.object_ID = 333960266 --[ name='MyTableName' AND xtype IN ('U','V')]
) sq
WHERE sq.rn = 1
                                            

Open in new window

0
 

Author Comment

by:OKE-JP
Comment Utility
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
;

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,
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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 ...
0
 

Author Comment

by:OKE-JP
Comment Utility
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,
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now