Post reply

How Do I Use Sp_Configure?

  • December 9, 2011 at 2:14 am

    #249394

    i have to two production server to live it.

    please help me to configure it using sp_configure.

  • December 9, 2011 at 2:16 am

    #1419129

    what do you need to configure?

    first thing would be show advanced options to get the full list of configurables.

    sp_configure 'show advanced options',1

    go

    reconfigure

    go

  • December 9, 2011 at 2:20 am

    #1419132

    i have fresh new server ,

    what i can do it for better performance.

    the 1st server has

    24 Gb RAm

    and 2nd server has

    12 Gb RAM.

  • December 9, 2011 at 2:27 am

    #1419138

    what are the servers going to be doing

    as it will all depend on what you need to configure.

    are they just the SQL engine, or do they have SSAS/SSIS/SSRS as well installed on them

    are any other programs running on the boxes

    do you run any other windows console based applications on these boxes?

    general rule of thumb I follow is to leave 2 GB for the OS, so set the max memory to 2GB less than the servers RAM for dedicated SQL boxes, if they run other things like SSAS/SSRS/SSIS then it all depends on what is needed for them services and anything else that runs on the servers.

  • December 9, 2011 at 2:35 am

    #1419140

    we are using only database engine

  • December 9, 2011 at 2:38 am

    #1419145

    In that case I would set the memory available to SQL to 22GB for server1 and 10GB for server2, I wouldnt usually do this via sp_configure, yet the GUI

    right click the server in object explorer --> properties --> memory

    maximum server memory = 22528 for server1, 10240 for server2

    you can do it via sp_configure

    you will need to do

    sp_configure 'show advanced options',1

    go

    reconfigure

    go

    sp_configure 'max server memory (MB)', VALUE

    go

    reconfigure

    go

  • December 9, 2011 at 2:41 am

    #1419146

    thanks antony

  • December 9, 2011 at 3:59 am

    #1419180

    Presumably this is a 64-bit box? On 32-bit you would additionally need to give the Lock Pages In Memory privilege to the SQL Server service accounts and enable AWE, and put a ceiling on the memory (which you can do via sp_configure thus):

    exec sp_configure 'Max server memory (MB)', value

    go

    reconfigure with override

    go

    I would also put a perfmon data collection set together with the Memory:Available MBytes and all SQL Server:Memory counters to run for a week during normal production, to see what SQL Server is actually doing with the memory. Depending on the context of use, you would be able to tune it to the needs of the system i.e. 'Nana's Cake Shop' running an EPOS app with SQL Server Express backend will not have the same requirements as the London Stock Exchange.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • December 9, 2011 at 4:15 am

    #1419192

    sql server is running on 64 bit environment,

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply