Unanswered: Performance, Performance, how to increase performance?
This is actually a continuation to the first thread I posted about linked servers.
Initially I thought that by putting 2 servers together, performance would increase as in the concept of 2 brains are better than 1.
But this was not the case as mentioned by NigelRivett. Link servers actually slow down the performance of the link host server as data has to be pass to it to do a join.
I previously been meddling with various ways to improve the performance of a MS SQL server database transaction performance, be it a select, update, insert or any other thing.
First of course is to improve it via the “soft” method, that is by:
1.Redesign better indexing.
2.Rewrite you statements so that less items are selected.
3.Redesign you database structure to include summary tables
and archive tables.
4.Include “with (nolock)” so that tables are not lock for a particular
query to finish.
Then there is the “hard” method, that is by:
1.Increasing RAM so that the MS SQL can use fast memory for its
2.Increasing processor speed.
Obviously link servers is out of the option now.
The “soft” method can only do so much to a certain extend.
Having some issues regarding the “hard” issues. I was previously using Windows 2000 server to host a “standard” edition MS SQL. And I’ve been monitoring RAM and Processor usage on the PC. It seems to be using a lot of Processing power.
So the obvious solution is to add in another processor for the job. Then a “consultant” advice that it has nothing to do with processor power. What I should do, is to buy the “enterprise” edition of MS SQL.
Is this the only way to solve it? Personally I don’t think changing editions would solve performance issues.
What other options is there besides those I’ve already mentioned, to increase performance to a MS SQL database ?
>> So the obvious solution is to add in another processor for the job. Then a “consultant” advice that it has nothing to do with processor power. What I should do, is to buy the “enterprise” edition of MS SQL.
And did he give anything to back up this statement?
Usually poor performance is due to bad design/implementation.
Adding hardware will improve matters but you will still end up with problems - especially as data volumes and usage increase.
You probably need to redesign things.
High processor usage may indicate a lot of procedural processing.
Do you have a lot of cursors? If so recode to get rid of them.
Make all access via stored procedures so the server doesen't have to keep creating query plans and checking permissions and also so that you can check optimisation easily.
Using nolock is dangerous as it can cause you to work on invalid data.
Ah yes, seperating database to different physical hard disks is also 1 of the ways.
The concept to this, as I've been told, is that each "read/write" head of the hard disk will have less task to do if it is concentrated to "read/writing" only 1 individual database.
There is also the concept of putting your data files in 1 hard disk, and your transaction logs in another, so the "read/write" of each task is reduce. You may aslo put your backup in another hard disk.
Another thing you can do is about your RAID configuration. But I forgot which is better and the reason behind.
I heard that only MS SQL Enterprise Edition offers clustered servers. At first I thought this was linked servers concept. But is not. Can clustered MS SQL servers help ? And how does 1 actually do this ? There's also something about load balancing components.
Do some reading about raid configurations, clustering, failover ...
Look at your backupo strategy as this will be impacted.
What availability do you expect.
Think about what you expect from the system and what you are willing to do to obtain that.
First just look at creating and maintaining indexes - this can be a quick win.
Then look at how the system is designed and architected. Then look at the implementation.
It is no use throwing hardware at a fundamentally flawed system but if you do memory usually has the most affect.
You will need to also find out what is causing problems - profiler can be useful for this if you don't have in-built logging.
Also check that nothing else is running on the system (make sure you have sp3).