SQL Server Performance Forum – Threads Archive

New install – Recommendations please

[?] After several performace issues with the old machine, I am given a new machine and I need to install the sql2k and the database there. Need your advice. machine: 4 – 2.66xeon processors;2gb ram; win2k advanced server sp5 (not sure).
C Drive: 35gb; mirrored (1+0); OS files; sql binaries;
master,msdb & tempdb located here.
E Drive: 35gb; not mirrored. DB size: 1.2GB; growth around 10mb daily; Page file: 3072mb to 4096mb in C drive; nothing in E drive. (should I keep some in E as well?) Can I keep the .TRN & .BAK files in C drive; and keep the data files of the application db in E drive? Trying to balance out the reads & writes.
Transaction backup: every 15 minutes (around 7 to 8mb). High volume order placement system.
Full db backup: Daily once. Your comments/suggestions greatly appreciated. Thanks

Hi there, The e: drive not being mirrored makes it not so good for databases and logs, unless you are happy to go back to the last good backup if the drive fails… The ideal would be to have separate drives for: OS & SQL binaries
tempdb
logs
datafiles
backups A busy database likes different drives. If you can afford RAID 1 for all of them (RAID10 better but your database is not really large enough to warrant RAID10) tempdb and datafiles can be kept together as can backups and os/sql if you are short on drives. All our servers have 3 RAID sets. RAID 1 for OS/SQL, RAID 1 for log, RAID5 for data, Backups are put on a network drive, just in case the whole box fails With the size box that you mention, I would have been surprised if you had any performance issues… unless the application could do with some tuning…? The RAM and CPU seem more than sufficient Cheers
Twan PS note that you wont need advanced server unless you need enterprise edition SQL2K or clustering…

Thanks Twan.
I cannot really change the configuration; as the machines are ordered and came here.
C has miror; and i put the sql binaries. Thought of putting datafiles in E. In my previous machine,(not raided) I have NT batch scripts running to copy .TRN and .BAK to another server in the network. Performance is our only issue. The current sever is win2k;sql2k sp2;1.4ghz dual processor with 2gb ram. One logical disk dividked into C & E drives. raided.
Around 70 users; and mostly 90% above cpu utilization; queue length high; dedicated sql machine; sqlserver is the only process. transactions/sec high. Regular reindexing; So, now C is raided with windows binaries on it; E is not raided;
Pagefile is defined only in C drive.

If the configuration cannot be changed then better to add-up memory to get optimum speed compared to the previous installation, and make sure to test and patch up the Win2K & SQL Server with required hotfixes/SPs. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

This really sounds like an application performance problem. Check that the correct indexes are used, profile to get any RPC:completed and Batch:Completed with duration greater than 100ms. In a database of your size I would not expect to see any statements taking that long 90-95% of all performance problem come down to application design, database design or indexing If all you have is two drives then I’d put the database files on C: and the logs on e:. The reason being that with 2GB of RAM your not going to touch the database files very often for reading (since it will be cached) and writing is done by a background process) The log will benefit from having its own drive, as the head will move sequentially along the disk. It sounds as if this is the only database on the server…? Beware though that RAID0 means a failure will cause loss of data The page file on a database system should see next to no usage, if it does then you’re either running more thant SQL Server on the box (in which case you’ll want to set the maximum amount of memory for SQL Server to use) or you don’t have enough memory for SQL to work with Cheers
Twan

Twan,
Can you give me your email, I can send some queries, which cpu is greater than 100.
I have a lot of them repeatedly hitting my server. tried playing around with indexes. regards,
bill

Hi Bill, I see you’ve already found my email address <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Fire away and I’ll have a look. I may not be able to magically fix things as I won’t know the table structure, indexes or data Cheers Twan

Actually with 3 drives, I’d almost consider setting up a RAID5 set. You can still have multiple logical drives if you like, and you will still have 70GB of storage, but all of your data is protected by RAID… As you can see I’m very nervous about running a database server with any RAID0 Cheers
Twan

Hi Bill, Have had a quick look through the profiler log. The first thing is that as you mentioned the values are coming through as N’xyz’ whereas the data is not unicode. This will cause inefficiencies since SQL Server will convert each value in the column to unicode (hence no index seeks) Your JAVA guys/girls aren’t quite right when saying that SQLServer adds the N before their constant values. It is the JDBC driver that does this. They need to find a way to avoid this… Possibilities are: - don’t pass actual values, instead use bind variables of the correct type (there are security reasons for this too… look up SQL Injection) - instruct the JDBC driver to send ASCII rather than unicode strings. e.g. jnetdirect has a method setASCIIStringParameters to force the correct datatype to be used The JDBC standard has setASCIIStream rather than using setString - get them to use "convert( varchar(200), N’xyz’ )" where the varchar(200) is whatever the column declaration is (or a suitable maximum varchar length) - or change the columns to nchar/nvarchar, but a waste of diskspace and some performance if you are never adding unicode values Also check for the following indexes: - Orderline( orederLineId ) - PaymentHistory( orderNumber, paymentId ) - AppExclProduct( customerTypeCode, orderTypeCode, excludedProductCode ) - AppExclProduct( customerTypeCode, orderTypeCode, excludedProdTypeCode ) - OrderHistory( customerCode, status, startDate ) — possibly change order of last two columns) - OrderHistory( customerCode, submitDate ) - OrderHistory( orderNumber ) - genealogyHistory( period ) - OrderInfo( orderNumber ) check out: the select groupCode statement the update for customer, since it includes customerCode in the update set, although it is not being changed. SQL won’t realise this and arrange to change the nonclustered index I’d also consider ensuring that you have a clustered index in each of your main tables… e.g. customer( customerCode ) This will avoid having to update the non-clustered index when you update a row, and SQLServer is forced to move the row to another slot or another page The first one to tackle is the mismatch of values passed as unicode with columns that are not unicode Cheers Twan PS feel free to send me another profile output after you’ve made some of the changes above (especially the unicode one <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> )

Thanks Twan.
I have been thinking the same way. Why should a straight select take 140 cpu.
I am going to check the JRUN server for the parameters. Any JDBC gurus know how to make it in ASCII format.
The query is coming from java frontend like this.
select * from Customer where customercode = N’12345678′ whereas, we need to come like:
select * from Customer where customercode = ‘12345678’ — without the N any help greatly appreciated. thanks.

First of all A BIG THANK YOU to Twan. Twan, You are right. It was a JDBC setting, which retured queries in utf-8/unicode format, and we changed it to return in ascii format. (I had to convice them really hard; they are all managers; highly rigid; you know what i mean <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> ). Now there are no N’….’ s. Last Night, we made the changes to production, and today it is smooth with the same load, with cpu rarely goes to 10% (ten %) usually above 80% manytimes touches 100%. It is a big relief for me, and for the management. Because of this cpu problem, they took it out of the cluster and put it in individual machines, and then they hired me. Now i am recommending them to put it back in the cluster; and telling them no need to buy more powerful machines (may be i shouln’t; let them spend and help the economy to rebound) <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> I have posted my problem in several forums, and I recently found about this forum, which is very useful/technical, to meet talented people. Thanks much!! Thanks Twan !! I will keep visiting this forum just to watch your posts. regards, Bill

Glad to be able to help Bill! Cheers
Twan

Very good post. Think this should end up on a MSSQL-JDBC tips page on this site or something. /Argyle

]]>