I have upgraded a NAvision 3.70 native database to SQL 2005 sp2. I’m using nav 4.0 sp3 with 3.70 objets.
it works more or less properly. the database is 60 gb and there are 42 current users. sometimes they get the blocked message but this is not the worse thing.
If you try to see a customer stadistics it takes about 5-10 seconds, and it’s quite slow.
the customer wants to use ODBC. I have configured it, an I can create a simple SQL select (or msquery): select no_ where no_ =‘0303030’. but if i try to set a range >= ‘0303030’ and <= ‘0303039’, it only takes 5 seconds the server to crash. then we have to reboot manually (pressing the switch off button)
I have set the tunning performance counters,and only three are wrong: pages, avg disk read queue length and read length. they are 0,4 of average, but sometimes it rises until 15-40. so it’s suppose to be a raid problem.
The server is configurated with:
2 xeon biprocessor 2.0
4 gb RAM
6 hd 147 gb, configured in 2 array (first array OS and log 27+120 RAID 1+0; second array: DB 293 RAID 1+0 )
windows 2003 standard edition + sql 2005 standard edition.
Could anyone suggest anything???
I’m quite desperated because the customer is nervous.
Well, NAV & SQL Performance is an issue which is actually permanently discussed on this site! If you search these forums for “SQL Performance” you’ll get tons of useful advices about how to tune your system [I]
I have already done the search, and also I bought your book [;)], but I don’t know if it’s possible looking the hardware configuration find somethink wrong at a glance.
hardware has not been installed for us, and I’m trying to find something in that way.
Well, when t about your hardware, there is somethng to “complain” about: if you just have 2 drives you could never fulfill the minimum requirements to run a NAV database efficiently! With your db size, the disk-subsystem should look like this (minimum!):
C:\ RAID1 OS, Page File, SQL Server (master, model, msdb)
D:\ RAID1 tempdb
E:\ RAID1 NAV db (mdf/ldf)
F:\ RAID10 NAV db (ldf)
Even though some db locations could be discussed, it is a MUST that the Transaction Log of the NAV db is isolated on it’s dedicated drive …
If you’re running a 32bit you have to assure that the RAM is properly configured (/3GB /PAE AWE) …
… and then it’s up to configuration, index structures, SIFT structures, C/AL Code, etc. …
Well, ideally I would go for RAID10 (or 01) for both drives. But if “spindles” are rare, then indeed I prefer to have RAID10 for the Transaction Log (ldf):
All transactions depend on the physical I/O on the TLog - SQL Server will not perform any changes without writing ahead in the TLog nor physically commit data. Therefore I consider it crucial to have the TLog on the best performing drives available; that’s why I would recommend the striping (0) here in addition to the mirroring (1).
The data-files (mdf/ndf) also have a high I/O (random-access), but actually asynchronously when writing data (after checkpoints) to disk. For this delayed physical access I do not consider it mandatory to have striping.
But of course, it always depends: If you have a very high number of concurrent transactions in a large database, causing a high “dynamic” in the cache and high pressure on the disks, then also RAID10 gets crucial for the data-files. And it also makes a difference if you’re using SATA, SCSI or SAS - with fast controllers (and spindles 15k) striping might be pointless anyway …
Finally: it depends. It’s just important to have the TLog as fast as can …
I am not disputing the need to put a big emphasis on the speed of the disk subsystem that stores your transaction log, and I agree that in case you need more than 2 disks for the transaction log, then indeed you put it on RAID10. It’s just that most databases don’t need more than one pair of mirrored disks for the transaction log, and in that case there simply is no striping, and by definition you can’t have RAID 10.
I know this because I have a number of customers (with database sizes of up to 100GB) who moved to a big RAID10 array for their TL, and it made no difference at all. I have also seen that it DID make a difference, so it is not easy to make this generalization. In most cases though, NAV databases do not need to have more than one RAID1 pair for the TL.
In your initial post you told Manolo to put the MDF on RAID1, and that I thought was a typo. I didn’t think you gave the wrong advice, I thought you forgot to type the 0. Most databases have significant file sizes for the data files, and you will want to put those on multiple disks, and in that case you ARE going to want to stripe as well as mirror. Just like the transaction logs, it is best practice to also maximize the speed of the disk subsystem that stores the data files. The more read/write disk heads are handling asynchronous I/O requests, the faster your system will be, and it makes sense to stripe AND mirror the data files.
If you don’t stripe across multiple mirrored disks then you need to define multiple files for the data, and then you go into advanced file management, something that most companies that run on NAV don’t have access to. It is much easier to have the disk subsystem set up as RAID 10 and only have a limited number of drives to manage, then to split files up and put them across multiple drives.
This is all a matter of whether you have the skill (or access to someone who does) to examine the disk subsystem properly and make adjustments accordingly. As a general rule though I do not agree to recommend multiple disk pairs in a RAID10 for TL and just RAID1 for data files. Without knowing the details of this specific situation, that is oversimplifying the issue.
As you could see from this discussion there are different experiences and opinions about configuring an optimized disk-subsystem.
As I said: It depends …
There are plenty of things to regard; e.g. adding Backup- and Maintenance-Strategy to the list. For example, a complete index-rebuild could expand the TLog size up to DB-Size! Hence, if your DB drives are larger (due to “striping”) and the DB using more space than the disk-size of the TLog (non-striped), the TLog file could “hit the wall” causing some trouble …
Again: It depends …
Hence, it is crucial to investigate and discuss your specific requirements and available options!