SQL Server Performance Forum – Threads Archive
How to Improve this Server
Hi All, I’m a sysadmin and one Of the ERP System guys set this SQL 2000 Std Server in following ways and we having performace issues. With their reviews, they found that the Intergration (eFrontOffice to eBackOffice locking the users and take 10-15 mints locks.)cause the issues.
Now we want to seperate this eFrontoffice and eBackoffice on to 2 Servers. I got the recomendation from JO for this New server. But is that possible to improve this Server to make this System better befor we spend $$$ for new Server? I got ML570 G2 Quad Processor X 2.Ghz Server with following
1. 4GB RAM
2. 1 x Smart Array 6400 Contoller
3. 3 x RAID 5 Arrays
4. Array Config as follows Array A: Logical drive1 68GB ( c: OS & d: page File)
Array B: Logical drive2 135GB ( E: SQL Data & Tempdb )
Array C: Logical drive3 280GB ( G: Logs & Backup) 5. DB Sizes DB1: 45GB
DB2: 35GB
TempDB: 13GB
DB4-DB10 : between 1MB- 10GB 6. And also Noticed tempdb Data and Logs are same name like this E:MSQLDATA empdb empdb.mdf
G:MSQLLOGS empdb.mdf
check the DB properties too??? is this work like abobe? lol 7. We doing snapshot data replication to our web server and repldata in C: Drive too
Before proceeding further I suggest to take help of thishttp://www.sql-server-performance.com/sql_server_performance_audit.asp article to get complete understanding of the system. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
HI, Thanks, Can someone give me your thoughts about Q 6? 6. And also Noticed tempdb Data and Logs are same name like this E:MSQLDATA empdb empdb.mdf
G:MSQLLOGS empdb.mdf
check the DB properties too??? is this work like above? I got the answer from the developer like this for the 13 GB Size of this tempdb. "The database would be set to dynamically GROW – however, it will not automatically SHRINK (Microsoft advises against automatic shrinking for online databases). So, theoretically, the tempdb will always be as big as it has needed to grow at any given point in time. It would be possible to manually shrink ‘tempdb#%92 to reduce the size"
Even though the naming convention is suggested to have .MDF for data and .LDF for log, while managing you could rename the .LDF file as .MDF too. TEMPDB will not shrink which is a default behaviour, also when you attempt to restart the SQL services then it will come back to default size. Look at these link to take care in this [email protected]:
http://www.sqlteam.com/article/what-to-do-when-tempdb-is-full
http://sqlserver2000.databases.aspf…nd-how-can-i-prevent-this-from-happening.html http://www.sql-server-performance.com/tempdb.asp – to fine tune tempdb. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
HI SKJ, Thanks for your help.I had a look on all the links. but still got few questions so please tell me 1. When i check the DB size, this tell me. do we need to restart this server ?
database_name database_size unallocated space
—————————————————————————————-
tempdb 13987.31 MB 13440.35 MB
reserved data index_size unused
—————— —————— —————— ——————
32728 KB 13552 KB 9816 KB 9360 KB 2. Do i really need to change this log file name to ldf or is that easy to rename or safe?
1. no need to restart the server unless you need to, you can run SP_SPACEUSED UPDATEUSAGE=’TRUE’ against TEMPDB to get optimum values. If you do not have any disk space issues then leave the settings as is. 2. I suggest if you do not have any problem at the moment leave it as it as, as the files are notused with a hard-coded name any way. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
HI, Sorry, may be i didn’t ask you properley. We got few issues with our system DB locks and freezing etc..etc. I have noticed that most of the issues releate to Sales Orders and can see the tempdb using lots of these tabels. And also what i understand this tempdb is delete and re create new one each time the sql start? The Size of the DB is 13.4 GB but used space is 54MB got 13 GB free. And i got HDD space too
Hi, Just restart my server last night and found tempdb size is 40MB and this morning 429MB
So do we need to restart this server weekly or is there any way to maintain this tempdb?
You haven’t looked at the links above andhttp://www.sql-server-performance.com/tempdb.asp talks about how to tune and keepup the tempdb size too, and that ASPFAQ article too in this case. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>