SQL Server Performance Forum – Threads Archive
SQL Server Performance
Hi All, I have migrated all my sql databases to a single powerful box(4GB Ram/4 Processor).The performance is very slow across wan and lan.Can some one help me on how to fine tune the SQL Database and also the backup jobs keeps faling.I rerun the jobs manually.The SQL holds around 15 Databases. I have close to 300 + users accessing the database in 3 shifts. SQL Server 2000 Standard Edition. Many thanks for your help in advance. Aravind.SVRBusiness Analyst
Hi ya, probably the best thing to do is use SQLProfiler to record all RPC:Complete and SQL:BatchComplete with a duration > 100ms, then look to optimise each of those statements How big are your databases, and why are the jobs failing? Twan
Hi Twan, We do not why the jobs are failing.Moreover the log files grow in abnormal size.Using the backup log databasename with truncate_only does not work though the query says command performed sucessfully.So we use dbcc shrinkfile command daily after taking a backup manually. Database size is around 40 GB and it varies for different markets.When a full backup is run the logs files are supposed to be truncated automatically but they are not happening so.We have been having this problem for almost 2 weeks.Your inputs would be greatly appreciated. If you require more information let me know. Aravind Aravind.SVR
Business Analyst
have the databases been set up for replication? Something is pinning a transaction in the log… how well behaved are the applications? no chance that they may be leaving uncommitted transactions? what are your database settings for recovery mode, autogrow, etc? you’re on sql2000 SP3? did anything happen about 2 weeks ago? no application upgrade or anything like that? Cheers
Twan
Hi Twan, I am on SQl Server 2000 SP3.Autogrow is automatic(Default like we do for other databases-Autogrow by 10%).Two weeks we updated the patch to SP3A and restarted the Server.We never had any problem when the databases was splitted between different servers.The problem started when we merged all the databases to one server.There is no replication enabled. What I am currently doing is moving the database to another server and checking the performance. If you require more information let me know. Aravind
Aravind.SVR
Business Analyst
Hi ya, probably best to run SQLProfiler to see what is going on… Cheers
Twan
Hi Twan, I think the problem is with SP.I have a SP to find out Potential Duplicate Records and this writes to the temp database.The LDF file grows abnormal in size in the TEST PC.Can i send you the stoed procedure and hope u can help me. Aravind
Aravind.SVR
Business Analyst
Hi ya, feel free to post it here if you like… If it is huge, then I’d suggest using SQLProfiler to find the statement causing most of the delay and then posting that statement along with index and size information for each of the tables involved in the query Cheers
Twan
]]>