SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Some performance improvements tips required at server level


Some performance improvements tips required at server level

Author
Message
sayedkhalid99
sayedkhalid99
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1110 Visits: 557
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.
Alan Burstein
Alan Burstein
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52935 Visits: 8908
1. Moving mdf,ldf,tempdb to seperate drives.

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.

-- Alan Burstein


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

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

GilaMonster
GilaMonster
SSC Guru
SSC Guru (886K reputation)SSC Guru (886K reputation)SSC Guru (886K reputation)SSC Guru (886K reputation)SSC Guru (886K reputation)SSC Guru (886K reputation)SSC Guru (886K reputation)SSC Guru (886K reputation)

Group: General Forum Members
Points: 886670 Visits: 48655
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
sayedkhalid99
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1110 Visits: 557
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search