I am busy performing performance tests of SQL2005 just before we go live with it. So far it behaves quite well but sometimes with big lists it freezes a bit.
Anyway, thought I would share something what is bugging me:
My system: 3.7A database, 4.02 Executables. SQL2005 version 9.0.2196.
I chose to time the re-index of the Contact Search tables recording start and stop time - 190,000 contacts to re-index. Both my SQL2000 & SQL2005 box have pretty much the same architechture.
The problem: SQL2000 takes around 30minutes to run, SQL2005 at least 2 hours. [:(]
I ran a trace and found that the sql command sp_execute (converts Navision code to TSQL commands) seems to be the reason why take longer in SQL2005 also sp_cursorexecute.
Has anyone experienced the same issues with SQL2005?
For testing purposes I made the re-index report stop after 1,000 records
Tested on SQL2000
Nav Version 4.03.23305: ± 54sec
Nav Version 4.02.23099: ± 54sec
Tested on SQL2005
Nav Version 4.03.23305: ± 1min 25 sec
Nav Version 4.02.23099: ± 1min 25 sec
So, in this case, the difference between version does not make a difference. Clearly the SQL2005 is slower. I am not so worried about re-indexing the contact search, its just the fact that it is slower that is concerning me.
Well, I never experienced a decrease in performance with re-indexing (SQL Server site DBCC etc.) on 2005, actually quite the opposite is the case.
You have to be aware, that 2005 supports ONLINE reindexing which reduces the locking issues, but also takes much longer - maybe that’s the case?
In general: Some transactions are indeed slower in SQL Server 2005 than in 2000. As NAV is sending parameterized RPC calls this leads to a SQL behaviour called “parameter sniffing” - I spare the details - where 2005 has obviously a higher tendency to perform Index Scans instead of Seeks. To improve this, you have to fiddle with the indexes, especially the clustered one …
I am using standard Navision report 5170 Generate Search Index, which basically inserts into Search Word & Search Word Detail tables with a little logic around delimiters etc. It also runs an Automation BlurrySearch.GenerateKey (‘Navision Contact Search 1.0’.BlurrySearch). I plan to use Automatic search-index creation so this wont be a problem, was just a bit more worried if there were other areas with NAV that have the same behaviour.
With “ONLINE reindexing”, I am not sure, perhaps this is the case. We are using one file group.
“Index Scans instead of Seeks”, is this not a bad thing? I understand the need for playing with the indexes and clustered index but I expected that the same database on 2000 to perform the same on 2005. So what we are really saying is that moving to 2005 will require individual attention to any tables that appear slower and without proper load testing I will only discover these once Live.
Jörg, I am passing your comments to our SQL dba, so hopefully we can look further into it.
OK, obviously I misunderstood “re-indexing” in this case - I guess the NAV feature has nothing to do with real SQL site index-operations. But yes, “Scanning” is indeed a bad thing, as it increases the transaction-duration and the risk of blocking problems.
We also expected, that an optimized 2000 DB should run as good on 2005, but actually we encountered some really poor performing transactions, which were OK before. Our investigations have shown, that 2005 did not pick the same indexes as 2000, resulting in Index Scans (ans some of them were a real pain). So we were forced to tune the thing again …
I use Form 150020, with Client Monitor and Code Coverage to make your indexing decisions (oh, and then Query Analyzer in SQL). hmmmmmph, having to re-tune is time consuming, but other than that there is no reason for me not to convert to SQL2005. I wonder how many other installations have to go through this?
I think just about all of them. At least if they are a decent size at least. Have you seen Hynek’s sql perform tool set, it seems to help minimize a lot of this work.
From what I can see though, most SQL based applications need to be tunned, its just that with the simplicity of Navision C/SIDE database, we have been protected for so long from these complications. So to us it seems like jumping though a lot of hoops, even though most other applications have required this level of tuning always.
Its still quite amazing to think that we are still using a database (C/SIDE I mean) that is virtually unchanged since they introduced commitcache in 1993. 14 years without a major upgrade or enhancement is quite a long time.
Yes it really is rock solid, can you think of any software that is in constant daily use that has not had a significant upgrade for 14 years.
I still think the Navision server ranks as one of the best bits of software ever written. And to think that in a few more years it will be squashed. [:(] [U]
Just a quick update. I have received another hotfix for SQL2005 after logging my problem with Microsoft (KB 930775). It looks like I will have to use SQL Profiler to identify slow queries and then SQL2005 Plan Guide to force the queries to recompile. I.e. problem may exist with cached plans.
I will update again once I have had an opportunity to test. [sn]