InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 20:32:10 GMT20http://www.sqlservercentral.com/Forums/Topic870863-391-1.aspxPlease note: 3 year old threadWed, 06 Feb 2013 11:07:17 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic870863-391-1.aspxIs the "MS Dynamics Navision ERP" application running on the same box as SQL Server in prod?If so, I'd lower the fixed memory for SQL considerably.Even if not, I'd still lower it to at least 96G (98304MB); at 128G, Windows itself needs some RAM to manage the other RAM.The only non-memoty things that would normally ever slow it that much is:1) I/O issues2) tempdb issues.So I suggest verifying those first before looking at other things.Wed, 06 Feb 2013 10:16:20 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic870863-391-1.aspxYeah you should check the actual query plan.One more thing maybe you should check the amount of IO or scanning actually happened using Set statistics io on then execute your query or sp.Wed, 06 Feb 2013 01:47:27 GMTbobiehttp://www.sqlservercentral.com/Forums/Topic870863-391-1.aspx[quote][b]BaldingLoopMan (2/24/2010)[/b][hr]mem settings• If server memory is <= 3 GB, set min memory to 1 GB, and max memory to server less 512 MB.• If server memory is > 3 GB, settings depend on the server platform (32-bit or 64-bit), presence of /3 GB in the boot.ini and on usage of AWE.• For servers on 64-bit platform AWE is not required, and [b]min memory should be server memory less 2 GB, max memory – server memory less 1 GB[/b].• On 32-bit platform with AWE : min memory and max memory should be server memory less 1 GB; without AWE – if /3 GB switch is not used, max memory = 2 GB, with the switch it is 3 GB (with min memory 1 GB less than that).[/quote]If you can I would leave at least 3GB for the OS in a 64bit system. Things like replication/connections/third party items all take up pieces of that memory space. I've run into quite a few issues as regards paging when not leaving plenty of room.Wed, 24 Feb 2010 11:54:47 GMTNicholas Cainhttp://www.sqlservercentral.com/Forums/Topic870863-391-1.aspx1. Are both builds of SQL the same?2. Do the execution plans look the same?3. How are you comparing response times between the two systems?Wed, 24 Feb 2010 11:39:12 GMTTim Weintzhttp://www.sqlservercentral.com/Forums/Topic870863-391-1.aspxmem settings• If server memory is <= 3 GB, set min memory to 1 GB, and max memory to server less 512 MB.• If server memory is > 3 GB, settings depend on the server platform (32-bit or 64-bit), presence of /3 GB in the boot.ini and on usage of AWE.• For servers on 64-bit platform AWE is not required, and [b]min memory should be server memory less 2 GB, max memory – server memory less 1 GB[/b].• On 32-bit platform with AWE : min memory and max memory should be server memory less 1 GB; without AWE – if /3 GB switch is not used, max memory = 2 GB, with the switch it is 3 GB (with min memory 1 GB less than that).Wed, 24 Feb 2010 11:37:21 GMTBaldingLoopManhttp://www.sqlservercentral.com/Forums/Topic870863-391-1.aspxHmm... I think the number you're looking for will be based on the SLA / user experience you're bound to deliver. I also think that the exact number you're looking for is dynamic in nature as it will change over time as the database grows. Once you determine the level of performance you want to deliver---and can meet that given the hardware you have, allocate your resources such that you meet your goals. If you can exceed them, perhaps you can do so until the spare resources are needed by other databases seeking a home. Use the page life, query response, cache hit ratio and other metrics listed earlier along with execution plans to find out what you're working with.---FRWed, 24 Feb 2010 00:21:24 GMTFidel Rodriguezhttp://www.sqlservercentral.com/Forums/Topic870863-391-1.aspx[quote][b]Mh-397891 (2/23/2010)[/b][hr]FYI, Per this article: This says to set both Min and Max memory to the same value:http://www.sqlmag.com/Articles/ArticleID/37890/pg/2/2.html[/quote]The article is dated April 2003. (sql2000)The NONO is about 64-bit.Experiences with 64-bit have shown it is better to just have these values deffer. Tue, 23 Feb 2010 23:57:56 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic870863-391-1.aspxConsider looking at the following:page life expectancy - (I think 5 mins is the MS best practice) this will tell you how long data pages are living in memorybuffer cache hit ratio - (I think 99+% is the MS best practice) this will tell you how frequently SQL goes to memory for datapagefile reads - (lower the better) this will tell you if the system is using the page file and reading (memory issue)disk queue reads / writes - this will tell you if the box is having trouble fetching data from disks assuming it's not in the cacheTo me, memory is something that should be configured according to the duties of the box. If, say, the box is solely for SQL, I'd recommend using the majority (90%) for the database engine. Note: this will probably set off alarms (SCOM or other alert software) because it'll look like the box is slammed, but it really isn't. It can also result in crazy-high page life expectancies which isn't a bad thing, but can indicate that the database doesn't need that much memory and can possibly take on more work. I hope that kind of makes sense.---FRTue, 23 Feb 2010 15:51:31 GMTFidel Rodriguezhttp://www.sqlservercentral.com/Forums/Topic870863-391-1.aspxHave you compared the execution plans between the two environments to see if they are the same? How about any hits to the disk? How hard is your hardware working processing other queries in production?Tue, 23 Feb 2010 13:24:55 GMTNicholas Cainhttp://www.sqlservercentral.com/Forums/Topic870863-391-1.aspx[quote][b]ALZDBA (2/23/2010)[/b][hr]These is one big NONO with your configuration:[quote]Minimum server memory(in MB): 102400Maximum server memory(in MB): 102400[/quote][size="4"][b]Don't set the same value for min and max server memory ! It confuses sqlserver memory manager ![/b][/size]With 64-bit you don't need to enable AWE because it can directly access all of your ram (+2TB)[/quote]FYI, Per this article: This says to set both Min and Max memory to the same value:http://www.sqlmag.com/Articles/ArticleID/37890/pg/2/2.htmlTue, 23 Feb 2010 12:24:57 GMTMh-397891http://www.sqlservercentral.com/Forums/Topic870863-391-1.aspxWhat value shall I assign to the Minimum server memory?Is the Maximum server memory value okay?Thanks.Tue, 23 Feb 2010 12:00:44 GMTMh-397891http://www.sqlservercentral.com/Forums/Topic870863-391-1.aspxThese is one big NONO with your configuration:[quote]Minimum server memory(in MB): 102400Maximum server memory(in MB): 102400[/quote][size="4"][b]Don't set the same value for min and max server memory ! It confuses sqlserver memory manager ![/b][/size]With 64-bit you don't need to enable AWE because it can directly access all of your ram (+2TB)Tue, 23 Feb 2010 11:50:35 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic870863-391-1.aspxHere is the configuration of our Production server:Windows server 2008 R2 Enterprise4 processorsInstalled memory(RAM): 128GB64 bit OSBelow is the info on the Memory which I got from the sql server properties:Memory:Server memory options: Use AWE to allocate memory: Not checkedMinimum server memory(in MB): 102400Maximum server memory(in MB): 102400Given the above information, I am trying to see if there is anything related to the memory issue, where SQL server is not using the available memory. How do I find out, how much memory SQL Server is using?I am not sure how to resolve this issue, any help is greatly appreciated. Thanks.Tue, 23 Feb 2010 11:07:38 GMTMh-397891http://www.sqlservercentral.com/Forums/Topic870863-391-1.aspxThe application we have is MS Dynamics Navision ERP, the application data seeks are slow in production. Like I said it's fast in Test environment. We ran the trace for some, duration of the command execution is more than 300 ms in prod. But one thing I do'nt understand is our production environment is far better than test enviornment, the database has same structures and same indexes in both prod and test environments. Now I am trying to figure out how to dump all the configuration/switch information on the serversas they should be close to identical. Please let me know how I can get the configuration/switch information on the servers so that I can compare from both environments.Thanks.Tue, 23 Feb 2010 08:27:20 GMTMh-397891http://www.sqlservercentral.com/Forums/Topic870863-391-1.aspxTry SQL Server Profiler, and see what the apps are doing.Mon, 22 Feb 2010 19:46:49 GMTMarlonRibunalhttp://www.sqlservercentral.com/Forums/Topic870863-391-1.aspxThe version of sql server is 2008.I just restored a backup of a database from the development server to the production server. The database was performing very good in the development server all the seeks were very fast in development server. After I restored the same database onto production server, the data seeks were very slow and our production server has very good hardware much better than development box. When I try to read data from the application even from single table from production environment, it is very slow. I tried every thing like rebuilding indexes etc in the production box, but still nothing is helping. Trying to figure out how to trouble shoot this performance issue.Please let me know, how I can find out where the poblem is. Thanks.Mon, 22 Feb 2010 19:09:57 GMTMh-397891