SQL Server 2000 and SAP Business One

Please search for the whitepaper below.
"SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability"
You should find some of the answers there even though it is for SQL Server 2005.
Also, check out the resources at http://microsoft-sap.com.

DBCC MEMORYSTATUS

If your upgrading from 2000 , you may want to know what the equivalent of DBCC MEMUSAGE in 2005. Check DBCC MEMORYSTATUS , there are lots of details in SQL2005 BOL.

SQL Server 2000- IO bottleneck problems

I was working on a system the other morning that was experiencing severe performance problems. Further compounded by the launch that day of a new product! Ideally , I would do a whole bunch of wait analysis , to try and identify the problem. The set up was 2 hard disks with no RAID and extremely high volume.

The conclusion was: I/O issue.Sustaining that level of volume with 2 hard disks will lead to problems. You need a caching Raid controller and disk arrays.

In their particular case , they were seeing checkpoints. During a Checkpoint all the dirty pages in the buffer cache are written to disk at one time. Without a good Raid array and a fair amount of disk cache you will never get good performance.

The other feature causing a problem was not separating the log files into their own RAID 1 disk array- insuring their is nothing else on that drive other than transaction log files. Log writes are exclusively sequential writes and can reside on RAID 1 (sequential writes are 20 times faster than random writes for a single drive.) If possible, try to have only transaction log per mirrored pair otherwise the disks start to perform random IO.

If there are an excessive amount of writes, it's best to have a RAID 10 for data.Also, a battery backed writeback conntroller with all the memory set to write cache. It's extremely difficult to get excellent performance unless a proper disk subsystem is put into place.

How do I monitor performace?

I log the following information by default into a system admin database for future follow up and trending.
1. Waitstats captured once per hour
2. Number of spids broken down by status captured on 5 minute snapshots
3. Database sizes captured once per day
4. IOstalls captured once per day
5. All SQL Server perf counters captured continuously
6. SQLTrace, 5 minute snapshot captured once per hour (if the sysem can withstand a continuous trace then I run 24x7)

There is a multitude of additional stats that I grab for SQL Server 2005. The above is a list of the basics allowing me to analyze performance at any time while also accounting for all of the historical information if I need to.

SQL Server: Database Size

I was inspecting a database data file which was about 80mb in size. However, the transaction log file which appeared to be exceeding 1gb.

The client had been experimenting with different ways of decreasing the size of the log files in a bid to restore some hard disk free space.

As this was a production server, I made sure the transaction log was not on truncate only. If you select the simple recovery model you are essentially saying that you want to truncate the log on checkpoint; removing all committed transactions from the log. Take a look at the sp_dboption and you will see that trunc. log on ckpt. is an option. Also, notice that in your simple recovery model you have the sp_dboption set to truncate the log on checkpoint.

My preferred choice is to leave the database model to Full Recovery mode and schedule regular backups of the transaction log. Periodically I recommend doing a scheduled shrinkfile on the transaction log during off hours setting turning the autoshrink option off. This is more of a performance gain if anything; but you will have to analyse the performance gain against giving in a little on disk space.

Every scheduling situation is different, but the concepts above will keep you minimise problems when you actually need the transaction log.