InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 20:50:00 GMT20http://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspx[quote][b]rmudway (11/6/2012)[/b][hr][quote][b]GilaMonster (11/6/2012)[/b][hr][quote][b]rmudway (11/6/2012)[/b][hr][quote][b]GilaMonster (11/5/2012)[/b][hr]Doesn't sound right. If the historical data isn't queried, it won;t be in memory, doesn't matter if in one DB or two.[/quote]Well... i dont need the historical data too be that fast, so it can reside whereever SQL puts it. But i do need the Database holding Realtime data to be as fast as possible. And the point of their idea was to keep Database2 (Realtime only) in RAM and keep it small enough to fit in there easily, and as it will be very active it will stay in their the whole time. So I figure i could move all the old realtime data into the Historical Database (Database1).[/quote]Honestly, don't bother. SQL does not keep entire DBs in memory, it keeps active and frequntly used pages in memory. Hence the real time data that;s used a lot will likely be in memory, the historical data not so much. Splitting into 2 DBs would not change this, but would require a lot of work.[/quote]Ok, then i think it is already doing this. As the Query i received on my first post showed me how much of the Database was cached in memory:[code="sql"]SELECT DB_NAME(database_id) AS [Database Name],COUNT(*) * 8/1024.0 AS [Cached Size (MB)]FROM sys.dm_os_buffer_descriptorsWHERE database_id > 4 -- system databasesAND database_id <> 32767 -- ResourceDBGROUP BY DB_NAME(database_id)ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);[/code]So are there no advantages to even having 2 databases? The person that gave me the advice was quite reputable, so im trying to find the worth in what he said. I wish i could speak with him again.So what i am hearing here is that, it would be little or no difference to the performance/speed it is running at currently (as one whole Database).[/quote]There are some advantages, for example if you store them on different filegroups on different hard disks. You can install the recent data on a faster (and more expensive) hard disk (or solid state disk), and the old data that you hardly use on a cheaper, slower disk. Or you could have two different back-up maintenance plans for those two databases. But in regards to caching in RAM, there's no advantage as Gail already said.Tue, 06 Nov 2012 23:50:07 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspx[quote][b]GilaMonster (11/6/2012)[/b][hr][quote][b]rmudway (11/6/2012)[/b][hr][quote][b]GilaMonster (11/5/2012)[/b][hr]Doesn't sound right. If the historical data isn't queried, it won;t be in memory, doesn't matter if in one DB or two.[/quote]Well... i dont need the historical data too be that fast, so it can reside whereever SQL puts it. But i do need the Database holding Realtime data to be as fast as possible. And the point of their idea was to keep Database2 (Realtime only) in RAM and keep it small enough to fit in there easily, and as it will be very active it will stay in their the whole time. So I figure i could move all the old realtime data into the Historical Database (Database1).[/quote]Honestly, don't bother. SQL does not keep entire DBs in memory, it keeps active and frequntly used pages in memory. Hence the real time data that;s used a lot will likely be in memory, the historical data not so much. Splitting into 2 DBs would not change this, but would require a lot of work.[/quote]Ok, then i think it is already doing this. As the Query i received on my first post showed me how much of the Database was cached in memory:[code="sql"]SELECT DB_NAME(database_id) AS [Database Name],COUNT(*) * 8/1024.0 AS [Cached Size (MB)]FROM sys.dm_os_buffer_descriptorsWHERE database_id > 4 -- system databasesAND database_id <> 32767 -- ResourceDBGROUP BY DB_NAME(database_id)ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);[/code]So are there no advantages to even having 2 databases? The person that gave me the advice was quite reputable, so im trying to find the worth in what he said. I wish i could speak with him again.So what i am hearing here is that, it would be little or no difference to the performance/speed it is running at currently (as one whole Database).Tue, 06 Nov 2012 19:36:18 GMTrmudwayhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspx[quote][b]rmudway (11/6/2012)[/b][hr][quote][b]GilaMonster (11/5/2012)[/b][hr]Doesn't sound right. If the historical data isn't queried, it won;t be in memory, doesn't matter if in one DB or two.[/quote]Well... i dont need the historical data too be that fast, so it can reside whereever SQL puts it. But i do need the Database holding Realtime data to be as fast as possible. And the point of their idea was to keep Database2 (Realtime only) in RAM and keep it small enough to fit in there easily, and as it will be very active it will stay in their the whole time. So I figure i could move all the old realtime data into the Historical Database (Database1).[/quote]Honestly, don't bother. SQL does not keep entire DBs in memory, it keeps active and frequntly used pages in memory. Hence the real time data that;s used a lot will likely be in memory, the historical data not so much. Splitting into 2 DBs would not change this, but would require a lot of work.Tue, 06 Nov 2012 09:01:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspx[quote][b]sanket kokane (11/5/2012)[/b][hr]I had done this once with one of my server last year,when we try to restart server (yes you need to restart your server after enabling the /PAE or /3GB option) ,it was not getting up.[/quote]Then you had something else wrong at the root of that, /PAE alone will not cause a server to fail to restart. I'd say check the logs, but it was long enough ago they're probably gone by now.Tue, 06 Nov 2012 08:59:05 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspx[quote][b]Koen Verbeeck (11/6/2012)[/b][hr][quote][b]rmudway (11/6/2012)[/b][hr]Did anyone see my question earlier?[quote]Yes. Gail already replied to that.[/quote]Actually, im still hoping for a response on this, as Gail kindly answered the question before :pinch:Tue, 06 Nov 2012 01:13:26 GMTrmudwayhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspx[quote][b]rmudway (11/6/2012)[/b][hr]Did anyone see my question earlier?[quote]Well... i dont need the historical data too be that fast, so it can reside whereever SQL puts it. But i do need the Database holding Realtime data to be as fast as possible. And the point of their idea was to keep Database2 (Realtime only) in RAM and keep it small enough to fit in there easily, and as it will be very active it will stay in their the whole time. So I figure i could move all the old realtime data into the Historical Database (Database1).I like the idea, but not sure about the pros/cons? [/quote]Yes. Gail already replied to that.Tue, 06 Nov 2012 01:06:18 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspx[quote][b]Koen Verbeeck (11/6/2012)[/b][hr][quote][b]rmudway (11/6/2012)[/b][hr][quote][b]sanket kokane (11/6/2012)[/b][hr]in your this case you don't need /PAE option ..as you are using 64 bit machine[quote][b]I hear you on that one and not like that option/switch very much.There are some niche applications that i have programmed with that do not recommend using this option. For me its a gray area.[/quote]Can you please explain,why this is not recommended ? :crazy:[/quote]Not sure. The programming guide did not explain why. So its still a gray area :-)[/quote]Sound more like an issue with the application then with the SQL Server flag.[/quote]True, actually its out of scope of my question as i not need that switch anyway.Did anyone see my question earlier?[quote]Well... i dont need the historical data too be that fast, so it can reside whereever SQL puts it. But i do need the Database holding Realtime data to be as fast as possible. And the point of their idea was to keep Database2 (Realtime only) in RAM and keep it small enough to fit in there easily, and as it will be very active it will stay in their the whole time. So I figure i could move all the old realtime data into the Historical Database (Database1).I like the idea, but not sure about the pros/cons? [/quote]Tue, 06 Nov 2012 00:59:27 GMTrmudwayhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspx[quote][b]rmudway (11/6/2012)[/b][hr][quote][b]sanket kokane (11/6/2012)[/b][hr]in your this case you don't need /PAE option ..as you are using 64 bit machine[quote][b]I hear you on that one and not like that option/switch very much.There are some niche applications that i have programmed with that do not recommend using this option. For me its a gray area.[/quote]Can you please explain,why this is not recommended ? :crazy:[/quote]Not sure. The programming guide did not explain why. So its still a gray area :-)[/quote]Sound more like an issue with the application then with the SQL Server flag.Tue, 06 Nov 2012 00:52:09 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspx[quote][b]sanket kokane (11/6/2012)[/b][hr]in your this case you don't need /PAE option ..as you are using 64 bit machine[quote][b]I hear you on that one and not like that option/switch very much.There are some niche applications that i have programmed with that do not recommend using this option. For me its a gray area.[/quote]Can you please explain,why this is not recommended ? :crazy:[/quote]Not sure. The programming guide did not explain why. So its still a gray area :-)Tue, 06 Nov 2012 00:48:26 GMTrmudwayhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspxin your this case you don't need /PAE option ..as you are using 64 bit machine[quote][b]I hear you on that one and not like that option/switch very much.There are some niche applications that i have programmed with that do not recommend using this option. For me its a gray area.[/quote]Can you please explain,why this is not recommended ? :crazy:Tue, 06 Nov 2012 00:39:23 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspx[quote][b]sanket kokane (11/5/2012)[/b][hr][quote][b]GilaMonster (11/5/2012)[/b][hr][quote][b]sanket kokane (11/5/2012)[/b][hr]What I was mean to say is consider enabling [b]/PAE[/b] as your last option.[/quote]If running SQL on 32-bit OS with more than 4GB of memory, /PAE is a requirement if that memory it to be used by SQL.[/quote]Yes, and that to be done carefully with the help of your well experienced system administrator.I had done this once with one of my server last year,when we try to restart server (yes you need to restart your server after enabling the /PAE or /3GB option) ,it was not getting up.One of my system administrator friend saves my life by restarting it with safe mode and then removing /PAE option, server got up . :-)[/quote]I hear you on that one and not like that option/switch very much.There are some niche applications that i have programmed with that do not recommend using this option. For me its a gray area.Tue, 06 Nov 2012 00:12:47 GMTrmudwayhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspx[quote][b]GilaMonster (11/5/2012)[/b][hr]Doesn't sound right. If the historical data isn't queried, it won;t be in memory, doesn't matter if in one DB or two.[/quote]Well... i dont need the historical data too be that fast, so it can reside whereever SQL puts it. But i do need the Database holding Realtime data to be as fast as possible. And the point of their idea was to keep Database2 (Realtime only) in RAM and keep it small enough to fit in there easily, and as it will be very active it will stay in their the whole time. So I figure i could move all the old realtime data into the Historical Database (Database1).I like the idea, but not sure about the pros/cons?Tue, 06 Nov 2012 00:09:24 GMTrmudwayhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspx[quote][b]GilaMonster (11/5/2012)[/b][hr][quote][b]sanket kokane (11/5/2012)[/b][hr]What I was mean to say is consider enabling [b]/PAE[/b] as your last option.[/quote]If running SQL on 32-bit OS with more than 4GB of memory, /PAE is a requirement if that memory it to be used by SQL.[/quote]Yes, and that to be done carefully with the help of your well experienced system administrator.I had done this once with one of my server last year,when we try to restart server (yes you need to restart your server after enabling the /PAE or /3GB option) ,it was not getting up.One of my system administrator friend saves my life by restarting it with safe mode and then removing /PAE option, server got up . :-)Mon, 05 Nov 2012 22:46:36 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspx[quote][b]sanket kokane (11/5/2012)[/b][hr]What I was mean to say is consider enabling [b]/PAE[/b] as your last option.[/quote]If running SQL on 32-bit OS with more than 4GB of memory, /PAE is a requirement if that memory it to be used by SQL.Mon, 05 Nov 2012 22:34:19 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspx[quote][b]Koen Verbeeck (11/5/2012)[/b][hr][quote][b]sanket kokane (11/5/2012)[/b][hr]Also consider this as your very last options.To speed up your DB check,first check if there any scope for Query Tuning.[/quote]I wouldn't consider this the last option. Of course, query tuning is important, I won't deny that.In the session "Building the fastest SQL Servers", Brent Ozar - which we can consider a performance guru - had this piece of advice for OLTP systems: "add enough RAM so that your DB fits into memory".Original video can be watched here:[url]http://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI328[/url][/quote]What I was mean to say is consider enabling [b]/PAE[/b] as your last option.and first check if there any scope for Query Tuning.Of course I have bad experience with /PAE .Mon, 05 Nov 2012 22:13:49 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspxDoesn't sound right. If the historical data isn't queried, it won;t be in memory, doesn't matter if in one DB or two.Mon, 05 Nov 2012 22:04:48 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspxI also had some advice from someone who said that for me to get over a performance issue with my Database, i could use a 2nd Database which would have Realtime data only. Leaving the historical information in the 1st Database which could grow.He went on to say that this 2nd Database would remain small and SQL would likely keep this in RAM, as the activity would be constant.Are there be any issues that i should be thinking about, if i go for this approach? Such as delay between accessing databases, configuration, etc?Thanks.Mon, 05 Nov 2012 21:11:11 GMTrmudwayhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspx[quote][b]Koen Verbeeck (11/5/2012)[/b][hr][quote][b]sanket kokane (11/5/2012)[/b][hr]Also consider this as your very last options.To speed up your DB check,first check if there any scope for Query Tuning.[/quote]I wouldn't consider this the last option. Of course, query tuning is important, I won't deny that.In the session "Building the fastest SQL Servers", Brent Ozar - which we can consider a performance guru - had this piece of advice for OLTP systems: "add enough RAM so that your DB fits into memory".Original video can be watched here:[url]http://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI328[/url][/quote]Hi Koen,Thanks for the link, i will go and check this out.Apart from performance tuning i think the Server may be suffering from not being setup properly such as the hardware. It is all on a single hard disk including the OS.Mon, 05 Nov 2012 20:59:44 GMTrmudwayhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspxPerformance tuning should be done first, not last. Well performing queries are generally more efficient in memory usage.Database being entirely in memory doesn't automatically mean the queries will be fast.Mon, 05 Nov 2012 20:31:09 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspxHi sanket,The Machine is 64-bit, so i have not done this setting.The OS is Win 2008 R2 Ent and my Database is running on SQL 2008 R2 STD.Thanks.Mon, 05 Nov 2012 20:01:50 GMTrmudwayhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspx[quote][b]rmudway (11/5/2012)[/b][hr]Hi sanket!!Thanks for your response, that was a nice little piece of info. I found that my test machine had the whole DB in RAM, but the Main SQL Server only had half of its Database in RAM.What can i do to speed up the Database if it is already in Mem, its holding realtime info and i see that it can get 100Msg/sec, any suggestions?I have inherited this database and need to try and get it to a good place :-DThanks.Rich.[/quote]if you have the OS setup to use all 8 gig of ram (either 64bit os or something else) Sql Server will read pages into memory from disk as it needs them. SQL Server will only flush the cache if the server comes under memory pressure and needs to reclaim some from sql server. otherwise the information just sits in the buffer cache. along with how much of your database is in memory also look at the page life expectancy. In our production environment we have around 100 gigs of data and 32 gig of ram on our server, however we only use a small portion as there is allot of historical logging tables that are not accessed very often. because of that our "Active" portion of our database fist very comfortably in memory and our page life expectancy sits at several hours. (an overnight maintenance on indexes uses all of that ram and then our page life expectancy just rises through out the day.)all that is to say that just because the entire DB is not in memory does not mean something is wrong. If SQL Server never needs half the data (or has not needed half the data since the last time the buffer cache was flushed) its perfectly normal behavior to only have half the DB in memory. As long as you have the memory there so that if SQL Server needs more memory its there and ready so its only a read from disk and not a flush of memory then a read from disk your good to go.as far as what to do once it all is in memory that would be performance tuning which is an entirely deep and broad subject.Mon, 05 Nov 2012 12:12:27 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspx[quote][b]sanket kokane (11/5/2012)[/b][hr]Also consider this as your very last options.To speed up your DB check,first check if there any scope for Query Tuning.[/quote]I wouldn't consider this the last option. Of course, query tuning is important, I won't deny that.In the session "Building the fastest SQL Servers", Brent Ozar - which we can consider a performance guru - had this piece of advice for OLTP systems: "add enough RAM so that your DB fits into memory".Original video can be watched here:[url]http://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI328[/url]Mon, 05 Nov 2012 06:58:32 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspxAlso consider this as your very last options.To speed up your DB check,first check if there any scope for Query Tuning.Mon, 05 Nov 2012 06:44:13 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspxIs your production machine 32- bit ?if yes ,you need to add /PAE option in your boot.ini file .http://support.microsoft.com/kb/283037[b]But I will recommend before doing this discuss with your system admin .Because making change in boot.ini file is risk .[/b]Mon, 05 Nov 2012 06:29:28 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspxHi sanket!!Thanks for your response, that was a nice little piece of info. I found that my test machine had the whole DB in RAM, but the Main SQL Server only had half of its Database in RAM.What can i do to speed up the Database if it is already in Mem, its holding realtime info and i see that it can get 100Msg/sec, any suggestions?I have inherited this database and need to try and get it to a good place :-DThanks.Rich.Mon, 05 Nov 2012 06:20:36 GMTrmudwayhttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspxSorry,this is with proper code format :-)[code="other"]SELECT DB_NAME(database_id) AS [Database Name],COUNT(*) * 8/1024.0 AS [Cached Size (MB)]FROM sys.dm_os_buffer_descriptorsWHERE database_id > 4 -- system databasesAND database_id <> 32767 -- ResourceDBGROUP BY DB_NAME(database_id)ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);[/code]Mon, 05 Nov 2012 06:06:42 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspxthis might help you[sup]SELECT DB_NAME(database_id) AS [Database Name],COUNT(*) * 8/1024.0 AS [Cached Size (MB)]FROM sys.dm_os_buffer_descriptorsWHERE database_id > 4 -- system databasesAND database_id <> 32767 -- ResourceDBGROUP BY DB_NAME(database_id)ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);[/sup]Query by Glenn Berry Mon, 05 Nov 2012 05:53:25 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1381027-391-1.aspxHi All,I have looked around and not found enough to answer my questions. And under some pressure to review this topic. So im wondering if someone could help me understand a few blanks...What i found out is that it seems that i have no control over putting my Database into RAM, and that SQL decides this on its own. My Database is 4 GBs and i have 8GB of RAM. As the Database is holding realtime data thus having a lot of activity, could I assume that SQL has already promoted the Database into RAM already?If so, how do i know if its already in RAM?Thanks,Rich.Mon, 05 Nov 2012 05:43:50 GMTrmudway