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


Checking server settings


Checking server settings

Author
Message
richardmgreen1
richardmgreen1
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2452 Visits: 1039
Hi guys

Just after someone checking a few server settings for me to see if I've done anything dim (it's entirely possible).
We have 2 servers that seem to be running fine (apart from SSRS), I just wondered if I could get any better performance with "quick wins".
SSRS seems to run very slowly (and a lot slower than our older server with approximately half the resources.

Details of the servers are:-
Server 1
16GB RAM - 12GB available to SQL
8 CPUs (1 NUMA node) - Max degree of parallelism set to 4
I've set Cost Threshold for parallelism to 75
This server also has SSAS installed with some RAM ring-fenced for that.


Server 2
32GB RAM - 16GB available to SQL
8 CPUs (1 NUMA node) - Max degree of parallelism set to 4
I've set Cost Threshold for parallelism to 75
This server also has SSAS installed with some RAM ring-fenced for that.

Can anyone see anything ridiculously wrong with this lot?
I can post other settings if necessary.

Thanks in advance....
Thom A
Thom A
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23319 Visits: 9228
12GB isn't really a lot for a SQL Server. Especially if you have SSAS, SSIS and SSRS all on the same instance. YOu also say you have RAM ring fenced for SSAS, Is this in addition to the remaining 4GB? If not SSAS is really going to struggle as it needs to share those 4GB with SSRS, SSIS, the OS (probably going to be using at least 2GB), and anything else running on the server. Is the Server not expected to be doing much most of the time? Is slow calculation time acceptable?

Out of interest, what licence are you running, Standard, BI or Enterprise?


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
richardmgreen1
richardmgreen1
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2452 Visits: 1039
Hi Thom

On Server1 there is a total of 16GB.
I've set up SQL to be able to access 12GB of that (leaving 4GB for Windows as I understand it).
It's my understanding that SSAS will take a proportion of the 12GB for it's own use.

This server is using the Developer edition of SQL2012 (we're upgrading the SQL tomorrow to 2016 Developer).

::edit::
Not sure if it makes any difference but both of these servers as virtual machines so we can ask for more RAM if necessary.
Thom A
Thom A
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23319 Visits: 9228
richardmgreen1 - Monday, August 7, 2017 9:29 AM
It's my understanding that SSAS will take a proportion of the 12GB for it's own use.

Wrong. SSAS, SSRS, SSIS and SQL Server (Data Engine) are all separate services and use their own resources. Limiting the data engine will 12GB, on a 16GB server, will leave 4GB any other processes. That will include SSAS, SSRS, and SSIS.

As an example, on SQL Server Standard edition each service is limited to 64GB. This means that (potentially) SQL Server, SSAS, SSIS, and SSRS could each use 64GB of RAM at the same time, totalling 256GB. That's before you take into account OS resources.

On your server1, therefore, that 4GB will need to be shared between SSAS, SSRS, SSIS (if you're using it) and the OS. Considering that, like I said, the OS is probably using about 2GB, that leaves the other services with only 2GB to play with between the 3 of them. That, more than likely, won't play well.



Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
richardmgreen1
richardmgreen1
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2452 Visits: 1039
Thanks for that. Looks like I mis-read something then.

I'll request more RAM for the server(s) and do some re-configuring and see if we can get them running any better.
Arsh
Arsh
Old Hand
Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)

Group: General Forum Members
Points: 325 Visits: 182
SSIS,SSAS, SSRS work under OS services separate from the main SQL server service. Hence use separate memory area i.e from what left over (from Total - (SQL server memory + O.S ), assuming no other apps use that server).
I would also suggest to gather memory stats for these services at different times of the day to understand the memory requirements for these apps to run better .

Arshad
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