August 18, 2009 at 12:15 am
hi
i have a desktop level sql server machine ( intel core duo 2.33 ghz and ram 2 gb)
it sounds BAD but we have 4 instances on this , we are using this machine for development.
all the 4 instances comtains data of about 30-35 gb.
we have mirroring between instance A and B.
i created instance C couple of days ago, and setted up the mirroring between instance C and D.
now this machine has become very slow, it taking longer time to visit it remotely. and opneing sql tools is also a PAIN.
Even we cannot traverse from its local folder to another folder .
i neeed to imporve its performance
can any one tell me how can i figure out the , culprit areas ? or any scripts which can provide me related statistics.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 18, 2009 at 6:57 am
The answer really is buy a dev server but hey ho!
If you go into Task Manager how much memory are your instances reserving? You will likely find that doing anything is causing Paging which hits the IO performance and in turn grinds everything to a halt!
Steve.
August 19, 2009 at 5:48 am
I would suggest using perfmon to see what is worst affected. It is most probably your drives as mirroring takes up alot of IO. Please let me know what you find. What kind of disk conf do you have for the server.
How many?
does mirror run on seperate hd's?
can you move transaction log to seperate hd?
can you move Tempdb to seperate HD?
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
August 19, 2009 at 6:00 am
this system has very bad/low configurartion of .
Only 160gb space in one drive ( all the 4 instances are using same drive)
no transaction log is being taken
mirroring is also on the same disk (drive)
around 120 gb space has been used by all 4 instances
BUT
i thing which i did today is , i fixed the memory usag for all instances (512 mb ) each
now i feel very small improvement in case of CPU usage ( i m not sure whther fixing mamory can impact on CPU usage or not , as i have experience these things first time )
i cannot do enhancement in Hardware , so please tell me steps/approach so that i can tune the server time to time
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 19, 2009 at 6:23 am
The problem is that if it is memory the SQL will now use all memory on the machine as soon as you run any processes that is memory intensive 512MB is very low for 1 instance.
Disk IO will create massive locks as all 4 instances is contending for time to write and read. Only other thing you can look at is removing all services from that server that is not needed. Also move all other apps that is running there to other PC.
Other than that you will have to upgrade to get more performance. The cheapest I would think is adding more drive/s.
In configurations like this disk IO waits/CPU waits will become so high that other waits start to appear.
Just for intrest you can try to give the mirror instances even less mem.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
August 19, 2009 at 6:39 am
I had the same problem a year ago :crazy: before I told everyone that the system will eventually fail in the next few days ... that didn't do the trick, but eventually we upgraded ... 😀
-The first thing to do is to increase the Paging file, in Task manager you will see that the ussage of this machine's resources is high in every aspect starting from RAM and CPU ussage to paging file resources.
Increasing paging file will bring some fresh air to the system but that won't be a complete tune up.
-Second you've said that you have split your RAM memory to the four SQL instances, not the brightest thing to do since there is not enough memory for the OS. I suggest to leave 512 or at least 256 MB for the OS. And then split the rest to the four instances, maybe a slight boost in memory to the instances that are mirrored (A,C).
-Third, scheduled restart on the sql instances is also a boosting tune up, although it is not a good practice. (I've tried it works fine in the next 1 hour or so, still better than nothing).
- The real solution is hardver boosting, mainly a RAM intervention. Been there done that, worked fine ... but still the best solution is a new server machine 😀
August 19, 2009 at 6:45 am
Thanks for immediate response
there are no extra app or services running.
i will assign more memory to pronicipal server then mirrored
but just tell me fixing memory can decrease the CPU usage ? or i means to say that
is fixing memory directly related to CPU usage ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 19, 2009 at 7:49 am
By default SQL Server reserves some resources (RAM) for each query, and increased number of queries drains the instance, which on the other hand has also limited resources. The queries that take long time to finish (great connection timeout) hold the resources all the time.
?????Memory adjustment can increase the CPU if additional RAM is added.?????
Increasing adjustments in memory or page file increases threading performance in a way that the CPU has bigger queue and less work. Before memory adjustment the queue is smaller and the processor is forced to finish more work in a single thread, since there will be threads that cannot be saved in RAM (paging file is hard disk space that OS uses like a RAM) or served by the processor that leads to dropping threads. Since you like the queries to return a result, connection timeout is set greater and the processor continues to work overwhelmed, and that leads to increased CPU ussage.
// my bad, writing mistake
"Memory adjustment can increase the CPU if additional RAM is added." should be
"Memory adjustment can decrease the CPU usage if additional RAM is added."
August 19, 2009 at 10:14 pm
I am sorry buddy , but i couldnt get you explaination ( as i am new in adminstration field)
can u please explain whether fixing memory increase the CPU usage (increase the spikes) ) or not ?
(please send me link of related article , if u have )
and one more question since I have allocated all the RAM to 4 instances ( 512 MB each * 4 = 2 Gb )
so how would OS will get RAM for its resources ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 19, 2009 at 10:21 pm
sorry buddy , i couldnt get ur explaination 🙁
just explain me one thing as u know i have fixed the RAM to all 4 instances , will it peak(increase ) the CPU usage ? ( i am new in sql adminstration)
( please send a link of related article )
one more question , as i have assigned equal RAM to instances ( 512 * 4 = 2 GB )
how would OS get RAM/memory of its resources ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 20, 2009 at 4:28 am
Hello again
What we suggested was reducing the memory for the mirror instances to 256mb each and see how that works. SQL will try to use all memory you allocate if a memory intensive script runs.
SQL use what it needs up to the limit you set.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
August 20, 2009 at 4:59 am
Yes , i tried by setting memory to 256mb on one of least used instance but
as when checked the sql jobs on that instance, i found that some of the jobs got failed
showing " INSUFFICIENT MEMORY OR LOW DISK SPACE"
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 20, 2009 at 5:00 am
Setting FIXED MEMORY to instance will increase the CPU usage or decrease it ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 20, 2009 at 6:33 am
Excesive wait times on the memory could increase wait times on the cpu. cpu usage should not increase but the waittimes would.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
August 20, 2009 at 2:20 pm
I think the reality of your situation is that you can't fit 10 pounds of crap in a 5 pound bucket.:w00t:
Running 4 instances on desktop grade hardware with only 2GB of memory is like loading a pickup truck like a dump truck and wondering why it won't move very fast.
Increasing instance memory from 256MB to 512MB might sound good since you are doubling the amount, but even 512MB for a SQL instance is in my opinion not nearly enough. And then to have the CPU manage 3 more instances with limited memory is enough to choke a horse. Now add in the overhead associated with mirroring and everything gets caught in the memory bottleneck.
I know you have said that you can't upgrade to a server, but my friend 'It is what it is'! Playing with memory settings and paging file size cannot possibly make this machine perform to a satisfactory level.
That's my 2 cents worth. . . . . .
"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply