We always talk about Informix and Oracle on this forum. What about Microsoft SQL? What are your favorite tips to get this database to run as fast as possible for Baan?

(Just felt like starting a new topic)


-- Jim

This is a very interesting topic.

We are using Windows NT and MSSQL7 - Entreprise Edition, for 60 users. The performance is better than with standard edition.

The problem is that Baan said in 1999 that a new driver is being developed for MSSQL Server. I mean, a level 2 driver. It has-t come out, yet. Has Baan Company forgetten about these people that are using MSSQL? A lot of space in the database is wasted because of hash columns....

I think that Oracle, for example, is more flexible regarding storage. In ora_storage you can specify the tablespace, the INIT, NEXT, etc. Using MSSQL I only managed to spcify a different filegroup for a test company. I would also like something like INIT or NEXT.... Does anybody have any idea ? Please suggest !

I think that Baan does not pay enough attention to those who use MSSQL since all Baan scripts are made to work faster with Oracle.... It is not fair, I think...

Looking forward to your postings,

Are we talking MS SQL (Microsoft SQL) or mSQL here?

Microsoft SQL (per the edited title Pat)

I saw the same thing after your last post about this so I changed the title on the thread.

Doh, I was wondering whether I actually posted that message or not! Sorry Jim, didn't realize you deleted the post :)

I must say that I am quite unpleasantly surprised that nobody has anything to say about all these problems.... I know that Oracle is nice to tune and to administer, but there are some good tools for Microsoft SQL Server, too. I do believe that anybody can "play" at home with a MSSQL 7.0 installation and Baan. Moreover, the Entreprise Edition works fine, has a good performance.
I think that Baan has the best installer for MSSQL, not for Oracle or Informix....

I hope I will hear more from you,

I agree that Oracle has the most flexibility, but then I think that the administration of the options can become the reason for slow performance. I believe that there are ways to optimize MSSQL. I am trying to find out the time frame for MSSQL L2 drivers.

Most of the tuning I see done with MSSQL has to do with disk/IO tuning as well as NT/Win2000 memory tuning. I have found some points of optimization with the FETCH optimization so I am going to do some tests to see how much of a difference these settings really make.

But, there doesn't seem to be that much to tune from my perspective (which is tough for me to say as a UNIX bigot)

-- Jim

Hi,

It is a very interesting news the one about level 2 driver. Is this one new? In what porting Set is it included? I could not find anything about this in the Release Notes of the Porting Set. Is this a BETA version?

It would be very nice if you can find some time and share with us the tricks you use for memory tuning and disk I/O tuning. For example, we have this problem on NT 4.0 SP6a EE + BaanIVc4 + MSSQL7.0 SP2 EE, PS6.1c.06.02:

Let's say the server is rebooted. Baan is started. Memory usage - 80 MB of 1 GB RAM. Then 50 users log in Baan, they do something, etc. The memory usage goes to 1.8 GB, so it needs swapping space. At the end of the day, all of them get out. No jobs running. Then the memory usage goes to 1.3 GB, instead of falling.....

This behaviour has not been seen while using Oracle.... We have set MSSQL to start with a fixed amount of memory, 400 MB, instead of allocating it dinamically. This helped a little. But not so much, though.... Do you know how can this behaviour be "fixed" ?

I would appreciate any idea.

Hi Stefan,

We are using BaanERP 5.0c on the same platform you are using. But in stead of you, we installed 4GB of memory. On our system a baan-client uses ±40MB.
so:
50 baanusers: 50*40MB = 2GB.

You will always have the problem of swapping, because your server needed memory and the sql server needs hughe memory.

The only thing to do is install more memory.
;)

Hi fans,

Out of total table fields, we might effectively use 50% of them
(BaaN c4: Item master 50 fields out of 140+ fields). This results in overall db size growing very fast in SQL.

But BaaN stores white spaces for all the empty fields in SQL. Bcos BaaN is using CHAR data type.

Any idea why BaaN does not use VARCHAR for the fields data type or any new porting set is available ....?!

Tks

Vish

Hi,

I have always used the tunings recommended in the upgrade ducument from MSSQL 6.5 to 7.0

Here they are if you guys don't have them:
In query analyzer
EXECUTE sp_configure "show advanced options",1
reconfigure with override
EXECUTE sp_configure "max degrree of parallelism",1
If this is a dedicated server (as it should be)
EXECUTE sp_configure "priority boost",1
EXECUTE sp_configure "lightweight pooling",1

Also a global Windows environment variable can be set
USERWEIGHTING with value 2.0

Also in $BSE\lib\defaults\de_resource
dbsinit:01
lock_retry:0
bdb_max_session_schedule:50


Another option these days is to upgrade MSSQL 7.0 ot 2000. The Baan impact is minor and MSSQL 2000 is better than 7.0

From the MS SQL Server database driver externals and internals manual.

Since Baan IV SQL expects ANSI compliant string comparisons the MSQL CHAR data type is used (instead of VARCHAR). The driver uses the MSQL CHAR data type in cases where the ANSI-compliant standard beharviour is expected for character data (for example, the Baan IV string type). This MSQL data type is used because a BAAN IV string data type has characteristics that conform to the ANSI specification for character data. Operations like comparison and concatenation can be done in a predefined, standard fashion with predicatble results when the CHAR data type is used.