SQL 2008 Increase memory but it will not use

I added more memory to my SQL server running on windows 2008 R2.    I changed the max memory setting but SQL refuses to use more than the original 4 GIG.    Is there another setting somewhere I may have missed?
LVL 2
Zoldy2000Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
The max memory is only for buffer space; SQL will always use some additional memory outside the buffer space.  Most people don't realize that; the setting name, "max memory" is a bit misleading.
0
 
dannygonzalez09Commented:
In the 32 bit design there is a memory limitation to 4 GB where as it is not the same case with 64 bit version.

Are you using 32-bit version of SQL?
0
 
Zoldy2000Author Commented:
I am running 64 bit version
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Scott PletcherSenior DBACommented:
Which edition of SQL Server?

Did you reboot after the memory was added?

Did you set min and max memory or just max?  What specific value did you set max to?
0
 
dannygonzalez09Commented:
try restarting the sql server instance, if possible
0
 
Zoldy2000Author Commented:
SQL standard edition.
Server was restarted
Set max only
0
 
Scott PletcherSenior DBACommented:
What specific value did you set max to?
0
 
Zoldy2000Author Commented:
12288000

is it possible it just does not need the extra I gave it?
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
The memory is in MB, so:
12288000 MB
=
12288.000 GB
which is unlimited for practical purposes.

I think it's still limited to 64GB, but definitely not only 4GB.

Can SQL "see" that RAM is there?

What does this return:

SELECT *
FROM sys.dm_os_sys_info
0
 
Zoldy2000Author Commented:
yes it appears it can see the memory.    the usage has gone up to 5 GIG today perhaps it just does not need the additional RAM.   Although I thought I read it would use as much as I give it.

that command returns 17179402240 for physical RAM
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
True, because SQL will only acquire memory when it needs it.
0
 
Zoldy2000Author Commented:
thanks just one last thing on this.   i am essentially trying to boost by performance is there something else I should be looking at.     For example when i am running a report in my application in can take about 3 minutes but is only using about 17% CPU on the SQL server.    Could this be boosted if it was configured to use more ... or is that even possible.
0
 
Scott PletcherSenior DBACommented:
Indexing is usually what is critical, particularly clustered indexes.  If you want to look at those, I can post a query to check index usage.

But, for a potential "quick-and-dirty" gain, you could also try raising the "cost threshold for parallelism" from the default of 5 to, say, 40 or 50.
0
 
Zoldy2000Author Commented:
I am willing to look and and try anything to improve this.     Which do you suggest I try first the quick gain you posted?
0
 
Scott PletcherSenior DBACommented:
Yeah, 5 is way too low for that threshold, although you're not CPU-bound anyway.

Then run these commands and post the results; a spreadsheet would probably be easiest, but I could read it out of a code box as well.

The first part -- missing indexes -- may take a while.  The second, existing index usage, will be very fast.



USE [your_db_name] --*BEFORE* running, change as needed.

SET DEADLOCK_PRIORITY LOW --probably irrelevent, but just in case

DECLARE @list_missing_indexes bit
DECLARE @table_name_pattern sysname

SET @list_missing_indexes = 1
SET @table_name_pattern = '%'

PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

--SELECT create_date FROM sys.databases WITH (NOLOCK) WHERE name = N'tempdb'

IF @list_missing_indexes = 1
BEGIN
    SELECT
        GETDATE() AS capture_date,
        DB_NAME(mid.database_id) AS Db_Name,
        OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
        mid.equality_columns, mid.inequality_columns, mid.included_columns,
        migs.*,
         mid.statement, mid.object_id, mid.index_handle
    FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)
    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON
        mig.index_handle = mid.index_handle
    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ON
        migs.group_handle = mig.index_group_handle
    --order by
        --DB_NAME, Table_Name, equality_columns
    WHERE
        1 = 1
        AND mid.database_id = DB_ID()
        AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern
    ORDER BY
        --avg_total_user_cost * (user_seeks + user_scans) DESC,
        Db_Name, Table_Name, equality_columns, inequality_columns
END --IF

-- list index usage stats (seeks, scans, etc.)
SELECT
    ius2.row_num, DB_NAME() AS db_name,
    i.name AS index_name,
    OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name,
    i.index_id, --ius.user_seeks + ius.user_scans AS total_reads,
    dps.row_count,
    SUBSTRING(key_cols, 3, 8000) AS key_cols, SUBSTRING(nonkey_cols, 3, 8000) AS nonkey_cols,
    ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates,
    ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update,
    fk.Reference_Count AS fk_ref_count,
    FILEGROUP_NAME(i.data_space_id) AS filegroup_name,
    ca1.sql_up_days AS days_in_use,
    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,
    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update
FROM sys.indexes i WITH (NOLOCK)
INNER JOIN sys.objects o WITH (NOLOCK) ON
    o.object_id = i.object_id
CROSS APPLY (
    SELECT DATEDIFF(DAY, create_date, GETDATE()) AS sql_up_days FROM sys.databases WHERE name = 'tempdb'
) AS ca1
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(object_id, ic.column_id)
    FROM sys.index_columns ic
    WHERE
        ic.key_ordinal > 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        ic.key_ordinal
    FOR XML PATH('')
) AS key_cols (key_cols)
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(object_id, ic.column_id)
    FROM sys.index_columns ic
    WHERE
        ic.key_ordinal = 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        COL_NAME(object_id, ic.column_id)
    FOR XML PATH('')
) AS nonkey_cols (nonkey_cols)
LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
    dps.object_id = i.object_id AND
    dps.index_id = i.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius WITH (NOLOCK) ON
    ius.database_id = DB_ID() AND
    ius.object_id = i.object_id AND
    ius.index_id = i.index_id
LEFT OUTER JOIN (
    SELECT
        database_id, object_id, MAX(user_scans) AS user_scans,
        ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans
    FROM sys.dm_db_index_usage_stats WITH (NOLOCK)
    WHERE
        database_id = DB_ID()
        --AND index_id > 0
    GROUP BY
        database_id, object_id
) AS ius2 ON
    ius2.database_id = DB_ID() AND
    ius2.object_id = i.object_id
LEFT OUTER JOIN (
    SELECT
        referenced_object_id, COUNT(*) AS Reference_Count
    FROM sys.foreign_keys
    WHERE
        is_disabled = 0
    GROUP BY
        referenced_object_id
) AS fk ON
    fk.referenced_object_id = i.object_id
WHERE
    --EXISTS(SELECT 1 FROM sys.indexes i2 WHERE i2.object_id = i.object_id AND i2.index_id = 0) AND --$T
    i.object_id > 100 AND
    i.is_hypothetical = 0 AND
    i.type IN (0, 1, 2) AND
    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND
    (
     o.name LIKE @table_name_pattern AND
     o.name NOT LIKE 'dtprop%' AND
     o.name NOT LIKE 'filestream[_]' AND
     o.name NOT LIKE 'MSpeer%' AND
     o.name NOT LIKE 'MSpub%' AND
     --o.name NOT LIKE 'queue[_]%' AND
     o.name NOT LIKE 'sys%' AND
    )
    --AND OBJECT_NAME(i.object_id, DB_ID()) IN ('tbl1', 'tbl2', 'tbl3')
ORDER BY
    --row_count DESC,
    --ius.user_scans DESC,
    --ius2.row_num, --user_scans&|user_seeks
    -- list clustered index first, if any, then other index(es)
    db_name, table_name, CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END, index_name

PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)
0
 
Zoldy2000Author Commented:
sorry just to be sure I get this right I am literally just pasting this entire script and running after entering my db name at the top?
0
 
Scott PletcherSenior DBACommented:
Yep.  

The first query block above lists indexes that SQL thinks are "missing".

The second query block above shows current SQL indexes and the activity against them, since the SQL instance started.
0
 
Zoldy2000Author Commented:
sorry I am no SQL expert for sure.    So i am running them separate and for every database on my SQL server?
0
 
Scott PletcherSenior DBACommented:
Whichever db you deem most critical to performance.  I can review one ... if you want, you can review the others based on that :-) .
0
 
Zoldy2000Author Commented:
Okay run the first part and got results attached.     however the second one received an error.

Msg 137, Level 15, State 2, Line 84
Must declare the scalar variable "@table_name_pattern".
Book1.xlsx
0
 
Scott PletcherSenior DBACommented:
If you run them separately, you need to include the setup & variable declarations before the second query:


USE [your_db_name] --*BEFORE* running, change as needed.

SET DEADLOCK_PRIORITY LOW --probably irrelevent, but just in case

DECLARE @list_missing_indexes bit
DECLARE @table_name_pattern sysname

SET @list_missing_indexes = 1
SET @table_name_pattern = '%'

PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)



Also, make sure you don't add a "GO" between the queries, because that will end the batch, causing the variables to be discarded.
0
 
Zoldy2000Author Commented:
sorry for the troubles.   run together or declaring separate as above I get this error.    Pasted the section it is having an issue with

Msg 102, Level 15, State 1, Line 100
Incorrect syntax near ')'.

    (
     o.name LIKE @table_name_pattern AND
     o.name NOT LIKE 'dtprop%' AND
     o.name NOT LIKE 'filestream[_]' AND
     o.name NOT LIKE 'MSpeer%' AND
     o.name NOT LIKE 'MSpub%' AND
     --o.name NOT LIKE 'queue[_]%' AND
     o.name NOT LIKE 'sys%' AND
    )
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Sorry, I created a version of my internal code to share externally and accidentally left a syntax error in it.


USE [AATELC]

SET DEADLOCK_PRIORITY LOW --probably irrelevent, but just in case

DECLARE @list_missing_indexes bit
DECLARE @table_name_pattern sysname

SET @list_missing_indexes = 1
SET @table_name_pattern = '%'

PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

--SELECT create_date FROM sys.databases WITH (NOLOCK) WHERE name = N'tempdb'

IF @list_missing_indexes = 1
BEGIN
    SELECT
        GETDATE() AS capture_date,
        DB_NAME(mid.database_id) AS Db_Name,
        OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
        mid.equality_columns, mid.inequality_columns, mid.included_columns,
        migs.*,
         mid.statement, mid.object_id, mid.index_handle
    FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)
    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON
        mig.index_handle = mid.index_handle
    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ON
        migs.group_handle = mig.index_group_handle
    --order by
        --DB_NAME, Table_Name, equality_columns
    WHERE
        1 = 1
        AND mid.database_id = DB_ID()
        AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern
    ORDER BY
        --avg_total_user_cost * (user_seeks + user_scans) DESC,
        Db_Name, Table_Name, equality_columns, inequality_columns
END --IF

-- list index usage stats (seeks, scans, etc.)
SELECT
    ius2.row_num, DB_NAME() AS db_name,
    i.name AS index_name,
    OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name,
    i.index_id, --ius.user_seeks + ius.user_scans AS total_reads,
    dps.row_count,
    SUBSTRING(key_cols, 3, 8000) AS key_cols, SUBSTRING(nonkey_cols, 3, 8000) AS nonkey_cols,
    ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates,
    ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update,
    fk.Reference_Count AS fk_ref_count,
    FILEGROUP_NAME(i.data_space_id) AS filegroup_name,
    ca1.sql_up_days AS days_in_use,
    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,
    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update
FROM sys.indexes i WITH (NOLOCK)
INNER JOIN sys.objects o WITH (NOLOCK) ON
    o.object_id = i.object_id
CROSS APPLY (
    SELECT DATEDIFF(DAY, create_date, GETDATE()) AS sql_up_days FROM sys.databases WHERE name = 'tempdb'
) AS ca1
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(object_id, ic.column_id)
    FROM sys.index_columns ic
    WHERE
        ic.key_ordinal > 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        ic.key_ordinal
    FOR XML PATH('')
) AS key_cols (key_cols)
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(object_id, ic.column_id)
    FROM sys.index_columns ic
    WHERE
        ic.key_ordinal = 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        COL_NAME(object_id, ic.column_id)
    FOR XML PATH('')
) AS nonkey_cols (nonkey_cols)
LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
    dps.object_id = i.object_id AND
    dps.index_id = i.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius WITH (NOLOCK) ON
    ius.database_id = DB_ID() AND
    ius.object_id = i.object_id AND
    ius.index_id = i.index_id
LEFT OUTER JOIN (
    SELECT
        database_id, object_id, MAX(user_scans) AS user_scans,
        ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans
    FROM sys.dm_db_index_usage_stats WITH (NOLOCK)
    WHERE
        database_id = DB_ID()
        --AND index_id > 0
    GROUP BY
        database_id, object_id
) AS ius2 ON
    ius2.database_id = DB_ID() AND
    ius2.object_id = i.object_id
LEFT OUTER JOIN (
    SELECT
        referenced_object_id, COUNT(*) AS Reference_Count
    FROM sys.foreign_keys
    WHERE
        is_disabled = 0
    GROUP BY
        referenced_object_id
) AS fk ON
    fk.referenced_object_id = i.object_id
WHERE
    --EXISTS(SELECT 1 FROM sys.indexes i2 WHERE i2.object_id = i.object_id AND i2.index_id = 0) AND --$T
    i.object_id > 100 AND
    i.is_hypothetical = 0 AND
    i.type IN (0, 1, 2) AND
    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND
    (
     o.name LIKE @table_name_pattern AND
     o.name NOT LIKE 'dtprop%' AND
     o.name NOT LIKE 'filestream[_]' AND
     o.name NOT LIKE 'MSpeer%' AND
     o.name NOT LIKE 'MSpub%' AND
     --o.name NOT LIKE 'queue[_]%' AND
     o.name NOT LIKE 'sys%'
     )
    --AND OBJECT_NAME(i.object_id, DB_ID()) IN ('tbl1', 'tbl2', 'tbl3')
ORDER BY
    --row_count DESC,
    --ius.user_scans DESC,
    --ius2.row_num, --user_scans&|user_seeks
    -- list clustered index first, if any, then other index(es)
    db_name, table_name, CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END, index_name

PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)
0
 
Zoldy2000Author Commented:
no problem i really appreciate the help.     output is attached
Book1.xlsx
Book2.xlsx
0
 
Zoldy2000Author Commented:
just curious what happened I posted my results and have not heard back from you in a while?
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Nice results!  I rarely see stats this good.  You have, or at least must have had, a really good DBA and/or table key designers.

I'm sticking to only what's clear cut to me, since the usage reflects only one day's worth of activity.  I prefer to have at least 93 days (a quarter of a year).  And keep in mind that I don't the cardinality/frequency of keys, so I can't adjust my conclusions based on that.

I haven't gone into detailed mechanics on how best to change a clustered key on a table: I assume you know that (or can find it :-) ).


APOBL:
    remove indexes: "APOBL_KEY_0" and "KEY_5"
    change clustered index to be on: IDVEND

VMINHD:    
    change clustered index to be on: NMDOCID
    create a nonclustered index on: NMNOTE (existing clusted index)

VMPH: --depends somewhat on cardinality of WDDOCTYPE.  [Even more valuable if this table is often joined to VMPREQ]
    remove index: "KEY_1"
    change clustered index to be on: WDDOCTYPE

VMPREQ: --[Even more valuable if this table is often joined to VMPH]
    remove indexes: "KEY_1", "KEY_2", "KEY_3", "KEY_4", "KEY_5" and "KEY_6"
    change clustered index to be on: WDDOCTYPE
    create a nonclustered index on: NMPREQ (existing clustered index)
0
 
Zoldy2000Author Commented:
I have to admit you completly lost me on that reply.    The query you posted is it safe?    Will it improve performance?     Based on what rou said it sounds like it's already close to optima?   Yet some reports can run very long which started this whole thing for me.
0
 
Zoldy2000Author Commented:
sorry I know this case got off track this is run on another small database that I built ...    It is small right now but growing perhaps this is where some of my issues are coming from since the main one I posted looks good?

See attached there is only one file because the output was blank on the other one.
Book1.xlsx
0
 
Scott PletcherSenior DBACommented:
The row counts on the tables in that database are so low they won't affect performance at all.
0
 
Zoldy2000Author Commented:
Okay thanks..    by the way i checked memory usage today and it is now using more than my max memory settings.    the exact opposite of what I originally posted.

What does your script do that you posted by the way?
0
 
Scott PletcherSenior DBACommented:
It's not a script.  It's just a description of immediate tuning you can do on the tables.
0
 
Zoldy2000Author Commented:
okay I guess its beyond me then...  I have no idea what to do with that.
0
 
Zoldy2000Author Commented:
Ill go back to my original issue with memory then close the case and award points.   Any idea why SQL would go above my max memory setting?
0
 
Zoldy2000Author Commented:
Nevermind I think I see my mistake I represented in KB I think.

I want 12 GIG

my current setting is 12288000

I think it should be 12288 correct?
0
 
Scott PletcherSenior DBACommented:
Hopefully you'll get answers you appreciate more on the next q.
0
 
Anthony PerkinsCommented:
Zoldy2000,

Please read this:
What grade should I award?
0
 
Zoldy2000Author Commented:
okay i read it and i think I graded exactly as I should.    I never did resolve anything.   But the information was useful none the less.
0
 
Scott PletcherSenior DBACommented:
Perhaps I phrased that wrong.

Hopefully next time someone else can give you better answers than I was able to.
0
 
Anthony PerkinsCommented:
I have posted a message in CS for a Moderator to review.
0
 
Zoldy2000Author Commented:
I don't understand are you complaining about my grading?
0
 
Anthony PerkinsCommented:
Not at all.  I am asking for an EE Moderator to review the grade given based on the EE Guidelines and your original question:   Is there another setting somewhere I may have missed? which in my view was more than adequately answered by Scott to be awarded just a "Good" grade.

So perhaps we can let an EE Moderator decide?
0
 
Zoldy2000Author Commented:
You don't have an issue but your letting a moderator decide?       Hmmmmm  I am confused but whatever.
0
 
Anthony PerkinsCommented:
I am confused but whatever.
That is why I stated we need to let an EE Moderator decide.
0
 
Zoldy2000Author Commented:
I don't agree with this at all.    I have been a member for 10 plus years.     At the end of the day you changing the grade does not cost me anything but I should be able to grade the question as I see fit.    It is my question and my account which I pay for year after year.
0
 
Scott PletcherSenior DBACommented:
I have no big problem if you really want to grade a 'B'.

May I point out, though, that I also reviewed all your existing indexes and provided very specific and detailed index tuning suggestions.  Frankly, even many DBAs aren't capable of doing that, and any consultant would charge you a lot more for that (and probably do it less well :-) ).

I'm sorry you didn't have time to use Books Online to get the specific syntax to implement the suggestions, but they are still represent expert-only advice.
0
 
Zoldy2000Author Commented:
The grade has been changed.    I guess your answer was too expert for me  :) .. so I gave you a B.

Thanks
0
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.

All Courses

From novice to tech pro — start learning today.