SQL 2005 Performance Issue

denmark_serrano77

Not sure if somebody already provided this: we have a KB article for this particular issue. Here's the link:

https://mbs.microsoft.com/knowledgebase ... wsignin1.0

Waldo

That's indeed the recompile function where sourcerer was talking about.
The link is new though ... thanks!

pdj

Great thanks. However; I don't see it clearly if this is considered an error that will be solved or just "by design".

ara3n

Hello MS has released a solution to this in this month knowledge base.

https://mbs.microsoft.com/partnersource/resources/support/selfsupport/hottopics/navision/kb_apr_07_nav.htm


This is the article
https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;935395

colin_leversuch-roberts

so does sp3 + sql 2005 sp2 resolve all the performance issues?
are you all using plan guides to do this?
thanks.

Marije_Brummel

This is not a real solution, but the next workaround we already knew about.

SQL server should just not use the cached plan. ](*,)

Waldo

Mark Brummel wrote:

This is not a real solution, but the next workaround we already knew about.

SQL server should just not use the cached plan. ](*,)

May be you can talk about this on Tech Ed this week?

Marije_Brummel

Planning to do just that. O:)

Marije_Brummel

Well, here is the first 'Official' answer. RECOMPILE.

I'll try to ask more people.

bbrown

Mark Brummel wrote:

Well, here is the first 'Official' answer. RECOMPILE.

I'll try to ask more people.

We have had the Microsoft Escalation Team working with our client. The one this orginal post was start for. Microsoft is insisting that the plan guides are the solution and that many customers have implemented them and are happy. We are not getting positive feedback from our customer.

Waldo

bbrown wrote:

Mark Brummel wrote:

Well, here is the first 'Official' answer. RECOMPILE.

I'll try to ask more people.

We have had the Microsoft Escalation Team working with our client. The one this orginal post was start for. Microsoft is insisting that the plan guides are the solution and that many customers have implemented them and are happy. We are not getting positive feedback from our customer.

I don't like to be "devil's advocate", but couldn't it be that the "level of expectation" is different for these customers? When is a customer "happy"?

I have a customer who is very happy with their performance, while their database is far less performant then another customer of ours, who is not really happy with the current performance.

Sales people have a very dangerous role there ... if they create some kind of expectation that the performance of the database "is going to be OK after the tuning job" ... you never know when the client is going to be happy about it.

Therefore you should just come with testresults. A "BEFORE" and "AFTER" kind or report with testresults to show how many percentage it has improved... .

colin_leversuch-roberts

Hmmm Microsoft are issuing plan guides to force recompiles, they hint there is a problem with sql server, I'm not convinced, as a DBA the concept of forced recompiles doesn't really ring true. My client says that they had performance problems and were advised to upgrade to sql 2005, this has appears to have had the opposite effect and are a little unsure of a resolution. Was Navision actually tested with sql 2005 as after all this product is soon to be replaced with SQL2008 so it's not as if it's new technology?
Just wondered what your collective views were on this and where you thought the issue may me, SQL or Navision?

btw .. I'm a SQL DBA new to Navision

Sorcerer

Waldo wrote:

I don't like to be "devil's advocate", but couldn't it be that the "level of expectation" is different for these customers? When is a customer "happy"?

for my part i would be happy if we would have the same performance as we had under sql 2000....

NavStudent

Based on this document, Nav on SQL 2005 has supperior performance than 2000.

https://mbs.microsoft.com/partnersource/documentation/benchmarks/msdynav_sql_benchmark_results.htm?printpage=false

David_Singleton

Sorcerer wrote:

Waldo wrote:

I don't like to be "devil's advocate", but couldn't it be that the "level of expectation" is different for these customers? When is a customer "happy"?

for my part i would be happy if we would have the same performance as we had under sql 2000....

At Convergence EMEA last year, They showed a set of charts, showing performance of Native vs SQL on various versions of NAV and SQL 7/2000/2005 All based on using identical hardware. This was used to show that on 4.00 sp2 with SQL 2005 for the first time SQL was benchmarking faster than Native. They were very adamant that SQL 2005 is much faster than 200 for SQL.

But is this what we are seeing in the real world?

Waldo

David Singleton wrote:

At Convergence EMEA last year, They showed a set of charts, showing performance of Native vs SQL on various versions of NAV and SQL 7/2000/2005 All based on using identical hardware. This was used to show that on 4.00 sp2 with SQL 2005 for the first time SQL was benchmarking faster than Native. They were very adamant that SQL 2005 is much faster than 200 for SQL.

But is this what we are seeing in the real world?

Depends on the hardware.
If you have a 64 bit server with 8 CPU's, tons of RAM, a decent HD configuration ... then I suppose Native is slower then SQL, because it only uses a fraction of that hardware... .

What I mean is that it is easy to "misinterprete" the charts... or to create charts that make no sense.

You should compare hardware that both SQL and Native can use (1 CPU, 1Gb RAM). And then, I can't imagine native is slower than SQL.

David_Singleton

Waldo wrote:

...
What I mean is that it is easy to "misinterprete" the charts... or to create charts that make no sense.

You should compare hardware that both SQL and Native can use (1 CPU, 1Gb RAM). And then, I can't imagine native is slower than SQL.

exactly.

bbrown

In general we are seeing very good performance on SQL 2005 (after the typical index & code tweaking). This client is a 100 GB database with 150 users.

They are in a test mode on SQL 2005. (Live is native). This performance issue is isolated to drilldown on large ledger tables. Along with moving around within the associated list forms. With this client the performance is poor enough that they have delayed the golive on SQL.

NavStudent

David Singleton wrote:

Waldo wrote:

...
What I mean is that it is easy to "misinterprete" the charts... or to create charts that make no sense.

You should compare hardware that both SQL and Native can use (1 CPU, 1Gb RAM). And then, I can't imagine native is slower than SQL.

exactly.

Why are you bringing Native server into this discussion. :?: I provided the link that compares 2K to 2K5. ](*,) ](*,)

David_Singleton

Posting a link does not help when the majority of users can not open it.

I was not replying to your thread, I did not even read it.

colin_leversuch-roberts

As I'm not a partner I'm unable to read any of these links too - something I don't find too helpful.
Can I ask about database size ? Anyone using a terabyte or approaching/exceeding that size?

Marije_Brummel

Yes, there are a couple of Terabyte databases out there and it can work, but the trick is to keep your working data as small as possible.

Archiving is your friend. This actualy starts after the first 100GB. You might want to consider keeping tables like sales shipments and invoices as small as possible.

A great solution can be to build a datawarehouse and use this for management reporting, because that is what keeping old data is all about isn't it? It is not the dataentry people who want to have the old figures, they want a fast system.

For me, without archiving NAV ends at about 100GB or maybe sooner. After that you need to be really creative.

There is a great business opportunity for someone because as partners are getting bigger, customers are getting bigger. (Or the other way around).

Heltborg

Have the same issue at one of my customers.

Upgrading from sql 2000 32bit -> 2005 64bit

Had major performance degrade on several tables. Contacted MS and did the whole recompile thing etc.

Even had MS ppl onsite to help figuring the problem because it persisted even through Recompile

Turned out that the problem is the parameterized queries where SQL 2005 is using the wrong indexes.

one of the trouble queries look like this

SELECT * FROM "NoName"."dbo"."NoName$G_L Entry" WITH (READUNCOMMITTED)
WHERE (("G_L Account No_"=@P1
OR "G_L Account No_"=@P2))
AND (("Selskabskode"=@P3))
AND (("Posting Date">=@P4
AND "Posting Date"<=@P5))
AND "G_L Account No_">@P6
ORDER BY "G_L Account No_",
"Business Unit Code",
"Afdelingskode",
"Projektkode",
"Selskabskode",
"Posting Date",
"Entry No_"
OPTION (RECOMPILE, FAST 5)

Running this takes approx 22 secs on the system because the query is using a wrong index, changing WITH (READUNCOMMITTED) to WITH(READUNCOMMITTED, INDEX ([$5)) lowers the query to 4 secs.

MS is looking into this problem now and hopefully it is fixed later summer

And yes we have not yet upgraded to 2005 64bit due to this

One more thing, Before we added the recompile option the query took 30-45 mins so.... that is optimizing for sure

Waldo

Heltborg wrote:

Running this takes approx 22 secs on the system because the query is using a wrong index, changing WITH (READUNCOMMITTED) to WITH(READUNCOMMITTED, INDEX ([$5)) lowers the query to 4 secs.

You do this using NAV? :-k Or did you build the SQL yourself in a query window?

Heltborg

The query is build by NAV. got it through profiler and tested it through Query Analyzer. Added the Index hint manually, After watching the different query plans

NAV cannot supply the Index hint option

We tried it aswell on our sql 2000 system where it uses the correct index without the hint/recompile

colin_leversuch-roberts

you can add the index hint through a plan guide.
There is also a problem using the FAST option, especially with an order by. 4 sp3 allows the removal of the fast option in queries, we have a sp2 fix which does the same thing and is proving useful.
It's really a navision issue rather than a sql server issue in as much as many of the queries are so constructed that the parameterisation doesn't work with the cached plans. The select * doesn't help and neither does the navision process of appending the pk columns to secondary indexes, however, my system doesn't seem to match others , terabyte database + nearly 1k users. We're working with microsoft and are currently applying plan guides and moving to sp3 ( this had an issue for us as it fixes a bug introduced in sp1 and sp2 ) which involves rebuilding all sift tables, this we couldn't do as it would take around 3 days ( estimated )
so:- use plan guides to resolve poor queries, drop the fast option which causes problems esp with an order by, go to sql indexes - and provide more useful indexes - been disappointed with sql2005 performance although we're not fully 64bit yet.

pdj

Heltborg wrote:

Running this takes approx 22 secs on the system because the query is using a wrong index, changing WITH (READUNCOMMITTED) to WITH(READUNCOMMITTED, INDEX ([$5)) lowers the query to 4 secs.
...
One more thing, Before we added the recompile option the query took 30-45 mins so.... that is optimizing for sure

Thanks a lot for you report. (And Colin's too) It is great with these reports, because that is not quite the kind of information we get officially from MS...

However; if it is possible I would like to know how long time the query needs on SQL2000 which doesn't need to recompile. Is that easy for you to try or do you already have it tested?

mav

SO in our business plan we have to purchase 2005 and upgrade from 2000SQL for our Navision Server.

From what I am reading there is a real problem with this...

Do we think there will be a fix by Oct nov?

We have about 68 users and a 80GB DB.

I think a major issue with performance is the client side ....due to older PC's ........

Any suggestions please let me know.

Mike

Sandra

Now I have to bud in also - we were one of the first ones to introduce the plan guides and at first everything seemed wonderful - but in the meantime - though things have improved, a lot of statements for which we have plan guides are hanging again. We are speaking of 30 - 40 min wait for the General Ledger Entries I am now going to look into an update to SP3 because this together with the plan guides seems to work a lot better, but still, our first euphoria about the plan guides has quickly died off.

colin_leversuch-roberts

There's a lot of different factors at play with navision and sql server 2005. I'm told that sp3 release 6 will address the FAST problems which are fixed with plan guides. We have about 500 plan guides but it's not easy to see what else is poor - basically too many different things. I followed a post from a colleague and am talking to microsoft about this - here are the posts should you want to follow them:-

http://sqlblogcasts.com/blogs/sqldbatip ... l2005.aspx

http://sqlblogcasts.com/blogs/grumpyold ... store.aspx

I think that essentially we have established we need to change indexes from Navision to sql and we need to create efficient indexes. This is a time consuming process however, and needs careful testing - initial tests on plan guides I did showed that the guide was required becuase there was not a suitable index for the query - however this was only a brief look.