September 30, 2009 at 8:47 pm
I have a database only server Windows server 2003 Enterprise SP2
SQL 2005 SP2
RAM 12gig
Our production system
1) after installing memory I added PAE to boot.ini and restarted
2) I set the lock pages in memory to on
3) I enables AWE with SQL query and restarted server
4) I changed the maximize Data throughput setting for network application to ON and took off throughput for file sharing
To configure memory in SQL
exec sp_configure 'max server mem', 6144
reconfigure
go
I get this message
"Configuration option 'max server memory (MB)' changed from 2147483647 to 6144. Run the RECONFIGURE statement to install"
I thought the reconfigure statement in the query was what I needed to do. What should I do, what resource can you direct me to. I want to restart the server as soon as I'm through.
All our other systems do not have nearly that amount of memory. So I have no way to test. I did a lot of reading and research but somehow missed whatever I should do next.
Did I leave any information out?
Thanks for early reply
Funny bad feeling in pit of stomach
September 30, 2009 at 11:14 pm
The message you got telling you to run RECONFIGURE is always output from sp_configure.
The next command was the RECONFIGURE so you should be OK to continue.
September 30, 2009 at 11:20 pm
Thank you very much. After more research, I found something that didn't say it that clearly, but at least it lowered my stress level a notch. Have a great day.
LP
October 1, 2009 at 1:08 am
Just curious...
"Configuration option 'max server memory (MB)' changed from 2147483647 to 6144. Run the RECONFIGURE statement to install"
If 6144 is your max memory what is your min server memory?
"Keep Trying"
October 1, 2009 at 1:33 am
Why, on a server with 12 GB of memory, do you have the max for SQL Server set at 6? Are there other things running on this server?
32 bit or 64 bit OS? 32 bit or 64 bit SQL?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2009 at 1:51 pm
6 gig to SQL out of 12?
32 bit OS Windows 2003 Enterprise. 32 bit SQL, 2005 Standard.
I didn't set a minimum, my research indicated that would be appropriate.
My explanation may sound defensive, but it is only in the sense that I am trying to work very cautiously. I'm working self taught with no test environment in a small shop and no one else here knows more. (I am studying). We have only vendor supplied software applications, we don't have an actual programmer (I am learning TSQL). The system I am changing is the most important production system we have.
One user performs certain queries (from within the application) that no one else does and when he runs it, it takes much much longer than any other query in our system. So one by one I am trying to slay the dragon. Vendor response: pick one of following: it's your equipment; it's your user; we don't know.
My manager said I should make memory changes incrementally. Before memory addition we had 4 gig. This is only a DB server, and only one database is really active. My thinking was I'll add some to SQL and let the rest be used other OS etc and see if it helps. My next step was to set to 8 gig SQL max.
I am reading a chapter and looking up information about perfmon. I know how to set it up so I can grab information before & during this user's query. I have some ideas on which elements to look at. Once I get results I'll have more data to research.
I have also studied indexes and that may be an issue which I'll research after other items.
1) do you have other recommendations or ideas
2) should I have posted this in Newbies forum instead of administering?
Thanks! I'm just so relieved I didn't mess up last night.
LP
October 1, 2009 at 2:15 pm
xyzt (10/1/2009)
One user performs certain queries (from within the application) that no one else does and when he runs it, it takes much much longer than any other query in our system. So one by one I am trying to slay the dragon. Vendor response: pick one of following: it's your equipment; it's your user; we don't know.
Neither. It's the query or the indexes most likely. Post the query in a new thread here and someone will help
See http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
My manager said I should make memory changes incrementally. Before memory addition we had 4 gig. This is only a DB server, and only one database is really active. My thinking was I'll add some to SQL and let the rest be used other OS etc and see if it helps. My next step was to set to 8 gig SQL max.
9-10 GB is probably fine. If you want to err on the side of caution, set the max memory to 9GB, leave the min alone. That gives the OS 3GB to itself which is a reasonable figure for total of 12 GB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2009 at 7:52 am
I would agree memory is probably not going to help that much. You will need to dig in further and look at the problem SQL statements and see if any additional indexes are needed. It might help to schedule an UpdateStats job as well even if you have AutoStats turned on. You might also look into rebuilding indexes if the table(s) in question have alot of inserts and deletes. It is amazing how much help rebuilding indexes can give certain SQL statements.
October 2, 2009 at 12:40 pm
Thanks for both replies.
I've set up a large maintenance window tonight after users close shop. I will be resetting memory to go to 9 as per suggestion.
I rebuild stats on a maintenance plan.
Tonight I will be rebuilding all indexes. I thought I had rebuild indexes on a maintenance plan as well. I set that up on our other SQL systems. Somehow this is disabled on this system and I hadn't noticed. 🙁 I checked it again after the reply. We have had lots of changes in DB recently.
NExt week when user with the issue is on line I will follow up with a perfmon to see if I can find which indexes are causing a problem, and then start more research to see what I can do. Once I have an index or such, I can contact vendor again and probably get a better response.
Again, thanks for responses.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply