Some performance improvements tips required at server level

Question

Post reply

Some performance improvements tips required at server level

sayedkhalid99

Ten Centuries

Points: 1394
More actions
April 17, 2016 at 7:43 pm

#313306

My db is currently around 650mb but is is increasing with around 500 concurrent users doing transactions and reporting at the same server. Please let me know whether the following changes can improve the performance or not.
1. Moving mdf,ldf,tempdb to seperate drives.
2. Create 2 server one for transactions and one for reporting ,replicating data to second server through always on or replication mechanism.
3- changing the current raid 5 to raid 10 in first server or both.
What other changes i need to bring to improve performance , becouse some of the query is performing very slowin the server . can i expect performance gain by doing the above tasks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

[Quote]1. Moving mdf,ldf,tempdb to seperate drives.[/quote]

Yes, you want to do that anyhow but yes, do that.

2. Create 2 server one for transactions and one for reporting ,replicating data to second server through always on or replication mechanism.

Yes, this will help tremendously. As a general rule it's best to separate your OLTP and reporting. Plus, with a reporting server you can index the heck out of it and do a lot of other things that you don't want to do on your OLTP system.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

sayedkhalid99 (4/17/2016)
What other changes i need to bring to improve performance ,

The usual process of identify the slow queries, tune them (indexes and/or queries). Repeat until performance is acceptable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sayedkhalid99 Ten Centuries Points: 1394 More actions · Answer 3

thanks Alan Burstein,Gail Shaw for you feedback , my point was the same query will perform differently when i execute that in local pc and in server , i will move for separating the server due to load of users on server hard drives,processor,memory and heavy report generations crystal report and etc.. and see how it works.