SQL performance finding.

Dear all SQL expertise,

After a perfmon finding, I see the following:

1) Page Faults/sec on average: 4496, Page/sec:1 on average, is it high? how to solve it ?
2) Data volume has larger Disk Transfers/sec than log volume, how to solve it?
3) SQL Compilations/sec at most 188, average 1. Is it normal?
4) SQL Re-Compilations/sec at most 5, average 0. Is it normal?
5)Memory Manager\Total Server Memory (KB): 736156 (736MB)
6) Memory Manager\Target Server Memory (KB): 45056000 (45GB)
7) Memory Manager\Optimizer Memory (KB): 1040 (1GB)
8) \Memory\Available Bytes: average 559336662938
9) \Memory\Committed Bytes: average: 13698367211
10) Page writes/sec is much larger than Page reads/sec
11) SQL Compilations/sec at most 188, average 1.
12) SQL Re-Compilations/sec at most 5, average 0.

Waht is the difference avaialbe bytes and committed bytes means ?

Any problem you all can see ?

Anything to do with the Optimizer Memory ?

right now it seems that target server memory is much bigger than total server memory, are we out of memory ?

DBA100.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AmmarRCommented:
hi marrowyung

we just had a long discussion on the same subject, check the link and if you still need assistance let me know..

Regards

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27508912.html
0
marrowyungSenior Technical architecture (Data)Author Commented:
AmmarR:

I see topic that if target server memory is much bigger than total server memory, then we are out of memory, is that right? so we need to increase the memory to the same value as the target server memory ?

DBA100.
0
Mark WillsTopic AdvisorCommented:
Nope...

Total Server Memory is how much is being used (well, in reality there are a few other memory objects outside of it, but for now, lets simply consider the engine) and Target Server Memory is how much SQL reckons it would ideally love to use. So the numbers you have above is fine in terms of "actually" using (ie Total Server Memory) and "ideally" available (ie Target).

You have Total Server Memory well under Target so there shouldnt be memory pressure based on those measures alone.

As for committed bytes v available, same type of thing - except now we are talking about the entire virtual memory space (physical + pages) and committed should always be less and less that total because it is the amount of space reserved in the page files and available is how much is currently available for processes. I am not so sure I would be monitoring as much...

But the page faults do look *interesting* and maybe a bit high - would be better (for us) to see the Page File / % Usage and/or % Usage Peak this should be less than about 70% otherwise it shows that disk is being used rather than memory (which will happen to an extent - just dont want it to get too high).

Page Writes per sec being larger than page reads per sec might be fine - dont really know your environment (or the values) so cannot really comment.

Can you describe the machine and environment and what your SQL Server is doing (type of databases - OLTP/DW/Reporting/etc, sizes, number of connections, number of sql instances etc).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marrowyungSenior Technical architecture (Data)Author Commented:
mark_wills:

Thanks for that, this is very clear.

But one thing, the "actually" using (ie Total Server Memory) and "ideally" available (ie Target). should be something like SQL is try to get target memory, but we don't have it, so it will slow down, that's is the reason this counter give result like this.

Any ratio make you say "Total Server Memory well under Target " ? I just see target memory is much larger than total server memory. I am not  as expertist as you, please coach on this.

"committed should always be less and less that total because it is the amount of space reserved in the page files and available is how much is currently available for processes. I am not so sure I would be monitoring as much... "

you mean committed should be less than Available Bytes or Total Server Memory? so we should monitor that much as it is not necessary ?


"Can you describe the machine and environment and what your SQL Server is doing (type of databases - OLTP/DW/Reporting/etc, sizes, number of connections, number of sql instances etc).
 "
Right now this DB is read operation in major,  size is about 32GB>, number of SQL instance is 1, number connection, I have to use the perfmon to check it. Checking the user connection counter? that's what you mean, right?

page faults means the need to load data from disk to memory again, so this also tells our server is runnning out of memory ?

DBA100.






0
marrowyungSenior Technical architecture (Data)Author Commented:
What is the reasonable value of Page Faults/sec? Memory Manager\Optimizer Memory (KB):  ?SQL Compilations/sec ? SQL Re-Compilations/sec ? page write/sec and page read/sec?
0
marrowyungSenior Technical architecture (Data)Author Commented:
hi all,

What counter you will usually use ? which make a lot of sense already.

I am thinking about I am using too much counter which mess me up.

Also if we have an exercise that changing the allocation unit of the disk SQL data/log is using from 4k to 64k (we are using 4k at production and staging SQL server), what coutner should we use and what range it is said to be improved if you were me to check the perfomrance before and after the change ?

logical disk write/sec and logical disk read/sec can be a good example. Anything else ?

DBA100.
0
marrowyungSenior Technical architecture (Data)Author Commented:
mark_wills:

I have some connection question about from Application to SQL server, please also join and share your idea.:


http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27514147.html#a37366053.

And recently, more and more incident about the growth of tempDB file size, but we can't failover it to other nodes or restart it. HOw cna we free up the tempdB size ?

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27507183.html
0
Mark WillsTopic AdvisorCommented:
Hi marrowyung,

I really do need to apologise for not returning sooner, I have been caught out with other activities (like Beta Testing for EE v 10).

I will most certainly look at the other questions later tonight (my time "down under").
0
marrowyungSenior Technical architecture (Data)Author Commented:
mark_wills:

That's ok, you are one of the member of EE ?

I now I can't create any more ticket and please help to answer as much question as possible.

LIke this :
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27524642.html?cid=239#a37460595

I have a big task to do in this few days too.

will give score.

DBA100.
0
Mark WillsTopic AdvisorCommented:
Not an employee, just a regular member who tries to be an Expert rather than Asker. But I do volunteer to do a few different things in EE.

You said "I can't create any more ticket", that is probably that "open questions" thingy kicking into action...

Maybe I can share an observation with you (please take it in good spirit, trying to be helpful and friendly)

I have noticed in your questions, you tend to ask a seemingly specific question and then it morphs into needing to raise either a lot of new questions, or, additional questions within the thread.

Maybe one approach for you is to raise the overall problem you are trying to find and then detail some of the specific questions you have. I know you might think that is what is happening, but reading through your threads, there seems to be so many more questions. It does sometimes come acroos as a process of discovery.

For example...

In this question you have generated a whole pile of statistics, so the immediate thought is you know what some of that stuff means. But then if we count the question marks in the original post, there are 8 questions being raised. Then after each expert post, there is a fairly quick response raising a few more questions.

So, maybe the missing part in some regards is the underlying "why are you asking these questions" which might well be "I have a concern about memory usage because the system seems to get very slow after a while" then tell us your configuration and then we can help steer you toward which metrics would make most sense to look at.

It can sometimes be a challenge if the experts need to answer some concerns about performance and at the same time, educate about other aspects of performance metrics and what they all mean - it can distract from being able to answer the original question.

Then again, sometimes it is just a hard slog and one answer does lead to another question or three, but need to try to stay on top of them all :)

Hope that helps a little bit ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.