November 5, 2012 at 10:46 pm
GilaMonster (11/5/2012)
sanket kokane (11/5/2012)
What I was mean to say is consider enabling /PAE as your last option.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.
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 . 🙂
-----------------------------------------------------------------------------
संकेत कोकणे
November 6, 2012 at 12:09 am
GilaMonster (11/5/2012)
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.
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?
November 6, 2012 at 12:12 am
sanket kokane (11/5/2012)
GilaMonster (11/5/2012)
sanket kokane (11/5/2012)
What I was mean to say is consider enabling /PAE as your last option.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.
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 . 🙂
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.
November 6, 2012 at 12:39 am
in your this case you don't need /PAE option ..as you are using 64 bit machine
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.
Can you please explain,why this is not recommended ? :crazy:
-----------------------------------------------------------------------------
संकेत कोकणे
November 6, 2012 at 12:48 am
sanket kokane (11/6/2012)
in your this case you don't need /PAE option ..as you are using 64 bit machineI 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.
Can you please explain,why this is not recommended ? :crazy:
Not sure. The programming guide did not explain why. So its still a gray area 🙂
November 6, 2012 at 12:52 am
rmudway (11/6/2012)
sanket kokane (11/6/2012)
in your this case you don't need /PAE option ..as you are using 64 bit machineI 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.
Can you please explain,why this is not recommended ? :crazy:
Not sure. The programming guide did not explain why. So its still a gray area 🙂
Sound more like an issue with the application then with the SQL Server flag.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 6, 2012 at 12:59 am
Koen Verbeeck (11/6/2012)
rmudway (11/6/2012)
sanket kokane (11/6/2012)
in your this case you don't need /PAE option ..as you are using 64 bit machineI 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.
Can you please explain,why this is not recommended ? :crazy:
Not sure. The programming guide did not explain why. So its still a gray area 🙂
Sound more like an issue with the application then with the SQL Server flag.
True, actually its out of scope of my question as i not need that switch anyway.
Did anyone see my question earlier?
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?
November 6, 2012 at 1:06 am
rmudway (11/6/2012)
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?
Yes. Gail already replied to that.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 6, 2012 at 1:13 am
Koen Verbeeck (11/6/2012)
rmudway (11/6/2012)
Yes. Gail already replied to that.
Actually, im still hoping for a response on this, as Gail kindly answered the question before :pinch:
November 6, 2012 at 8:59 am
sanket kokane (11/5/2012)
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.
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.
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
November 6, 2012 at 9:01 am
rmudway (11/6/2012)
GilaMonster (11/5/2012)
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.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).
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.
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
November 6, 2012 at 7:36 pm
GilaMonster (11/6/2012)
rmudway (11/6/2012)
GilaMonster (11/5/2012)
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.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).
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.
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:
SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);
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).
November 6, 2012 at 11:50 pm
rmudway (11/6/2012)
GilaMonster (11/6/2012)
rmudway (11/6/2012)
GilaMonster (11/5/2012)
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.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).
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.
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:
SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);
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).
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply