3G ram for SQL server: max

Showing posts with label max. Show all posts

Thursday, March 29, 2012

A few BLOBs per page

Does SQL server 2005 places a few VARBINARY(MAX) values on a single page if
the length of those values are let's say 2KB?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200512/1
Hi Alex
Varbinary(max) data will actually be placed in the data row itself if there
is room.
You can set the table property to store all large objects out of the row,
and then varbinary(max) is treated just like image.
Image columns from the same table CAN share space on the same pages for
greater storage space efficiency.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Alex via droptable.com" <no@.spam.pls> wrote in message
news:589565dfa15f3@.uwe...
> Does SQL server 2005 places a few VARBINARY(MAX) values on a single page
> if
> the length of those values are let's say 2KB?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200512/1
>
|||Thanks a lot for your response.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200512/1
sql

Tuesday, March 27, 2012

A few BLOBs per page

Does SQL server 2005 places a few VARBINARY(MAX) values on a single page if
the length of those values are let's say 2KB?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1Hi Alex
Varbinary(max) data will actually be placed in the data row itself if there
is room.
You can set the table property to store all large objects out of the row,
and then varbinary(max) is treated just like image.
Image columns from the same table CAN share space on the same pages for
greater storage space efficiency.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Alex via SQLMonster.com" <no@.spam.pls> wrote in message
news:589565dfa15f3@.uwe...
> Does SQL server 2005 places a few VARBINARY(MAX) values on a single page
> if
> the length of those values are let's say 2KB?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1
>|||Thanks a lot for your response.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1

A few BLOBs per page

Does SQL server 2005 places a few VARBINARY(MAX) values on a single page if
the length of those values are let's say 2KB?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200512/1Hi Alex
Varbinary(max) data will actually be placed in the data row itself if there
is room.
You can set the table property to store all large objects out of the row,
and then varbinary(max) is treated just like image.
Image columns from the same table CAN share space on the same pages for
greater storage space efficiency.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Alex via droptable.com" <no@.spam.pls> wrote in message
news:589565dfa15f3@.uwe...
> Does SQL server 2005 places a few VARBINARY(MAX) values on a single page
> if
> the length of those values are let's say 2KB?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200512/1
>|||Thanks a lot for your response.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200512/1

Monday, March 19, 2012

A basic question: Removing duplicate results from Max function

Hi,

Say I have a table Job with columns name, date, salary . I want to get
the name ,date and salary for the date when that person earned maximum
salary. I am using something like

SELECT X.name,X.date,X.salary
FROM job X
WHERE X.salary IN
(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);

The problem is ; if a person earns maximum salary on two dates, both of
the dates are printed. I just want to get any one of those two rows.
I tried

SELECT X.name,Min(X.date),X.salary
FROM job X
WHERE X.salary IN
(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);

but it gives error.
Can anybody please suggest a solution?

Regards,
Aamir(aamircheema@.gmail.com) writes:
> Say I have a table Job with columns name, date, salary . I want to get
> the name ,date and salary for the date when that person earned maximum
> salary. I am using something like
>
> SELECT X.name,X.date,X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>
> The problem is ; if a person earns maximum salary on two dates, both of
> the dates are printed. I just want to get any one of those two rows.
> I tried
> SELECT X.name,Min(X.date),X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
> but it gives error.

SELECT a.name, a.date, a.salary
FROM tbl a
JOIN (SELECT b.name, date = MAX(b.date)
FROM tbl b
JOIN (SELECT name, salary = MAX(salary)
FROM tbl
GROUP BY name) c ON c.name = b.name
AND c.salary = b.salary
GROUP BY a1.name) b ON a.name = b.name
AND a.date = b.date

This presumes that (name, date) is unique, and a person does not have
two salaries the same day.

The inner selects are derived tables - sort of virtual temp tables within
the query. A very powerful tool to write complex queries. A derived table
is independent of the outer query, and this why the alias b can be reused.
Note that they are not necessarily computed in whole - the optimizer often
recast computation order for a very very effceient query plan.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||aamircheema@.gmail.com wrote:
> Hi,
> Say I have a table Job with columns name, date, salary . I want to get
> the name ,date and salary for the date when that person earned maximum
> salary. I am using something like
>
> SELECT X.name,X.date,X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>
> The problem is ; if a person earns maximum salary on two dates, both of
> the dates are printed. I just want to get any one of those two rows.
> I tried
> SELECT X.name,Min(X.date),X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
> but it gives error.
> Can anybody please suggest a solution?
> Regards,
> Aamir

In SQL Server 2005:

WITH j AS
(SELECT name, date, salary,
RANK() OVER (PARTITION BY name ORDER BY salary DESC, date DESC) rnk
FROM job)
SELECT name, date, salary
FROM j
WHERE rnk = 1 ;

That assumes the combination of (name,salary,date) is unique. If it
isn't then just add other columns to the ORDER BY specification to make
a key.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||On 10 Jun 2006 07:48:38 -0700, aamircheema@.gmail.com wrote:

(snip)
>I tried
>SELECT X.name,Min(X.date),X.salary
>FROM job X
>WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>but it gives error.
>Can anybody please suggest a solution?

Hi Aamir,

Here's a third suggestion:

SELECT X.name,Min(X.date),X.salary
FROM job X
WHERE X.salary IN
(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name)
GROUP BY X.name, X.salary;

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP|||
Thanks Everybody. That helped a lot

95th percentile calculation

Hi *,

For our needs, we have to integrate on a Visual Studio project (a Report Server Project, which gives a report dealing with AVG, MAX counters' values from a SQL DB), a calculation of a '95th percentile' average for a counter (in place of a 'normal' average).
(In another words, we have to automatically substract the 5th 'highest values' of the total of the value, and so only make the average on the remaining 95th of the value).
And this treatment should appear as a function, that we could integrate on the Report.

Does this function already exist ?

Does anyone have any idea to help me to find how to do ?

Tks a lot to everybody.

HI,snakeoli :

Can you elaborate more about your requirment? Do you need something like top 5 percent?

|||

Hi,

No, in fact, we have to calculate the average, in the case for several ranges of values.
But to have 'relevant' results, for each sorted ranges of values, we have to exclude 5% of the highest values before calculating the average.
This maneer is called '95th percentile calculation'.

(for ex : in this range of 20 values : 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
the 95th percentile give : 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19 !!
=> exclusion of the 5thhighest values of the list (5% of '20' gives '1' ==> 1 value)
And so, calculation of the average only on 19 values !)

The goal is, in some case, to supress values that can influence the result of a average.

Imagine that in this example, we have '1250' in the place of '20' !

So the average of the 20 values gives a result not relevant !
(1+ 2+ 3+ 4 + ...+ 18 + 19 + 1250) / 20

And so, finaly, that what we need to create, throught Visual Studio :
a function (similar as 'AVG()') that can calculate automaticaly this !
(for a sorted range of value, a average excluding the 5th highest values !)

I whisper sincerely that I was clearly in my explanation.

Thanks a lot for your help.

|||

Hi,snakeoli:

I got to know what you want to do in this scenario. However AVG() can not deal with such comlicated calculation for you. I just recommend you to do this in the sql statement. It will be much reasonable.

Sunday, March 11, 2012

8 GB of RAM

I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
Standard.
I have the switches /PAE /3GB in boot.ini
SQL max server memory is = 6656 MB
but with those settings I cannot get SQLServer to get more than 2.7 GB
of RAM
If I use the AWE switch, then SQLServer can only use 128 MB max
What's wrong?
PaulOn Dec 4, 3:42 am, pd...@.clic.net wrote:
> I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
> Standard.
> I have the switches /PAE /3GB in boot.ini
> SQL max server memory is = 6656 MB
> but with those settings I cannot get SQLServer to get more than 2.7 GB
> of RAM
> If I use the AWE switch, then SQLServer can only use 128 MB max
> What's wrong?
> Paul
Is it SQL Server 2000 or SQL Server 2005?
If its SQL Server 2000, then AWE is not supported
If its SQL Server 2005, then check the AWE 'Run Value' in
sp_configure... if its 0, then you might want to recycle the SQL
Server service to enable the AWE
In any case i dont believe you need to have /3GB switch in boot.ini.|||Hey, of course you can use AWE on SQL Server 2000 (and also on SQL Server
2005).
You will need to run
sp_configure 'awe enabled', 1
and grant the Lock Page in Memory permission to your service account. For
more details see
How to configure SQL Server to use more than 2 GB of physical memory
http://support.microsoft.com/kb/274750
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"dineshbabu.munugala@.gmail.com" wrote:
> On Dec 4, 3:42 am, pd...@.clic.net wrote:
> > I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
> > Standard.
> > I have the switches /PAE /3GB in boot.ini
> >
> > SQL max server memory is = 6656 MB
> >
> > but with those settings I cannot get SQLServer to get more than 2.7 GB
> > of RAM
> >
> > If I use the AWE switch, then SQLServer can only use 128 MB max
> >
> > What's wrong?
> >
> > Paul
> Is it SQL Server 2000 or SQL Server 2005?
> If its SQL Server 2000, then AWE is not supported
> If its SQL Server 2005, then check the AWE 'Run Value' in
> sp_configure... if its 0, then you might want to recycle the SQL
> Server service to enable the AWE
> In any case i dont believe you need to have /3GB switch in boot.ini.
>|||Ben
If you are on 64-bit , you do not need AWE anymore. If the OP has SQL Server
2000 along with SP4 , make sure that he has a last hotfix especially if he
are going to enable AWE
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:2ADA35D9-C4FA-4852-B20A-3E08F240C626@.microsoft.com...
> Hey, of course you can use AWE on SQL Server 2000 (and also on SQL Server
> 2005).
> You will need to run
> sp_configure 'awe enabled', 1
> and grant the Lock Page in Memory permission to your service account. For
> more details see
> How to configure SQL Server to use more than 2 GB of physical memory
> http://support.microsoft.com/kb/274750
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "dineshbabu.munugala@.gmail.com" wrote:
>> On Dec 4, 3:42 am, pd...@.clic.net wrote:
>> > I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
>> > Standard.
>> > I have the switches /PAE /3GB in boot.ini
>> >
>> > SQL max server memory is = 6656 MB
>> >
>> > but with those settings I cannot get SQLServer to get more than 2.7 GB
>> > of RAM
>> >
>> > If I use the AWE switch, then SQLServer can only use 128 MB max
>> >
>> > What's wrong?
>> >
>> > Paul
>> Is it SQL Server 2000 or SQL Server 2005?
>> If its SQL Server 2000, then AWE is not supported
>> If its SQL Server 2005, then check the AWE 'Run Value' in
>> sp_configure... if its 0, then you might want to recycle the SQL
>> Server service to enable the AWE
>> In any case i dont believe you need to have /3GB switch in boot.ini.|||On 3 d=E9c, 18:04, dineshbabu.munug...@.gmail.com wrote:
> On Dec 4, 3:42 am, pd...@.clic.net wrote:
> > I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
> > Standard.
> > I have the switches /PAE /3GB in boot.ini
> > SQL max server memory is =3D 6656 MB
> > but with those settings I cannot get SQLServer to get more than 2.7 GB
> > of RAM
> > If I use the AWE switch, then SQLServer can only use 128 MB max
> > What's wrong?
> > Paul
> Is it SQL Server 2000 or SQL Server 2005?
> If its SQL Server 2000, then AWE is not supported
> If its SQL Server 2005, then check the AWE 'Run Value' in
> sp_configure... if its 0, then you might want to recycle the SQL
> Server service to enable the AWE
> In any case i dont believe you need to have /3GB switch in boot.ini.
SQL2005 Standard.
Running in 32 bits system
As soon as I use AWE, I'm stuck with 128 MB of RAM for SQL server...
(i.e. I run dbcc checkDB so that it starts using rapidly memory and I
see SQLServer.exe using not more than 128 MB of RAM in Task Manager).
Paul|||Hi
Do you have SP4 with hotfix?
Install hotfix from(http://support.microsoft.com/kb/899761)
Do you see in PerfMon that SQL Server allocate 6.6GB RAM?
<pdube@.clic.net> wrote in message
news:e4a9b19a-1b95-4df7-b6d9-e8c374bfb9c0@.a39g2000pre.googlegroups.com...
On 3 déc, 18:04, dineshbabu.munug...@.gmail.com wrote:
> On Dec 4, 3:42 am, pd...@.clic.net wrote:
> > I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
> > Standard.
> > I have the switches /PAE /3GB in boot.ini
> > SQL max server memory is = 6656 MB
> > but with those settings I cannot get SQLServer to get more than 2.7 GB
> > of RAM
> > If I use the AWE switch, then SQLServer can only use 128 MB max
> > What's wrong?
> > Paul
> Is it SQL Server 2000 or SQL Server 2005?
> If its SQL Server 2000, then AWE is not supported
> If its SQL Server 2005, then check the AWE 'Run Value' in
> sp_configure... if its 0, then you might want to recycle the SQL
> Server service to enable the AWE
> In any case i dont believe you need to have /3GB switch in boot.ini.
SQL2005 Standard.
Running in 32 bits system
As soon as I use AWE, I'm stuck with 128 MB of RAM for SQL server...
(i.e. I run dbcc checkDB so that it starts using rapidly memory and I
see SQLServer.exe using not more than 128 MB of RAM in Task Manager).
Paul|||On 4 d=E9c, 10:45, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> Do you have SP4 with hotfix?
> Install hotfix from(http://support.microsoft.com/kb/899761)
> Do you see in PerfMon that SQL Server allocate 6.6GB RAM?
> <pd...@.clic.net> wrote in message
> news:e4a9b19a-1b95-4df7-b6d9-e8c374bfb9c0@.a39g2000pre.googlegroups.com...
> On 3 d=E9c, 18:04, dineshbabu.munug...@.gmail.com wrote:
>
>
> > On Dec 4, 3:42 am, pd...@.clic.net wrote:
> > > I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
> > > Standard.
> > > I have the switches /PAE /3GB in boot.ini
> > > SQL max server memory is =3D 6656 MB
> > > but with those settings I cannot get SQLServer to get more than 2.7 GB=
> > > of RAM
> > > If I use the AWE switch, then SQLServer can only use 128 MB max
> > > What's wrong?
> > > Paul
> > Is it SQL Server 2000 or SQL Server 2005?
> > If its SQL Server 2000, then AWE is not supported
> > If its SQL Server 2005, then check the AWE 'Run Value' in
> > sp_configure... if its 0, then you might want to recycle the SQL
> > Server service to enable the AWE
> > In any case i dont believe you need to have /3GB switch in boot.ini.
> SQL2005 Standard.
> Running in 32 bits system
> As soon as I use AWE, I'm stuck with 128 MB of RAM for SQL server...
> (i.e. I run dbcc checkDB so that it starts using rapidly memory and I
> see SQLServer.exe using not more than 128 MB of RAM in Task Manager).
> Paul- Masquer le texte des messages pr=E9c=E9dents -
> - Afficher le texte des messages pr=E9c=E9dents -
SQL 2005 (9.00.3161.00)
The hotfix is for SQL 2000...
In PerfMon, I see Commited Bytes around 3.2 GB.
In Task Manager I still see SQLServer.exe using 2.7 GB. I guess then
that's just a problem related with Task Manager not showing the
correct amount of RAM used...?
Paul|||On 4 d=E9c, 14:08, pd...@.clic.net wrote:
> On 4 d=E9c, 10:45, "Uri Dimant" <u...@.iscar.co.il> wrote:
>
>
> > Hi
> > Do you have SP4 with hotfix?
> > Install hotfix from(http://support.microsoft.com/kb/899761)
> > Do you see in PerfMon that SQL Server allocate 6.6GB RAM?
> > <pd...@.clic.net> wrote in message
> >news:e4a9b19a-1b95-4df7-b6d9-e8c374bfb9c0@.a39g2000pre.googlegroups.com...=
> > On 3 d=E9c, 18:04, dineshbabu.munug...@.gmail.com wrote:
> > > On Dec 4, 3:42 am, pd...@.clic.net wrote:
> > > > I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server=
> > > > Standard.
> > > > I have the switches /PAE /3GB in boot.ini
> > > > SQL max server memory is =3D 6656 MB
> > > > but with those settings I cannot get SQLServer to get more than 2.7 =GB
> > > > of RAM
> > > > If I use the AWE switch, then SQLServer can only use 128 MB max
> > > > What's wrong?
> > > > Paul
> > > Is it SQL Server 2000 or SQL Server 2005?
> > > If its SQL Server 2000, then AWE is not supported
> > > If its SQL Server 2005, then check the AWE 'Run Value' in
> > > sp_configure... if its 0, then you might want to recycle the SQL
> > > Server service to enable the AWE
> > > In any case i dont believe you need to have /3GB switch in boot.ini.
> > SQL2005 Standard.
> > Running in 32 bits system
> > As soon as I use AWE, I'm stuck with 128 MB of RAM for SQL server...
> > (i.e. I run dbcc checkDB so that it starts using rapidly memory and I
> > see SQLServer.exe using not more than 128 MB of RAM in Task Manager).
> > Paul- Masquer le texte des messages pr=E9c=E9dents -
> > - Afficher le texte des messages pr=E9c=E9dents -
> SQL 2005 (9.00.3161.00)
> The hotfix is for SQL 2000...
> In PerfMon, I see Commited Bytes around 3.2 GB.
> In Task Manager I still see SQLServer.exe using 2.7 GB. I guess then
> that's just a problem related with Task Manager not showing the
> correct amount of RAM used...?
> Paul- Masquer le texte des messages pr=E9c=E9dents -
> - Afficher le texte des messages pr=E9c=E9dents -
When I say that I see 128 MB of RAM in SQLServer.exe when using AWE I
mean that's what I see in Task Manager. Am I correct looking at this
value?
Should I check PerfMon instead? Is the memory shown in TaskManager
only kernel memory and would not show all memory used by SQL?
Paul|||SP4 hotfix is not needed here as this is SQL Server 2005.
Try this to see how much memory SQL Server is using. Performance Monitor,
add SQLServer:Memory Manager - Total Server Memory (KB).
Have you granted the Lock Page in Memory permission to your service account?
See your Error log. Must show 'Address Windowing Extensions enabled' or some
AWE error message.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Uri Dimant" wrote:
> Hi
> Do you have SP4 with hotfix?
> Install hotfix from(http://support.microsoft.com/kb/899761)
> Do you see in PerfMon that SQL Server allocate 6.6GB RAM?
>
>
> <pdube@.clic.net> wrote in message
> news:e4a9b19a-1b95-4df7-b6d9-e8c374bfb9c0@.a39g2000pre.googlegroups.com...
> On 3 dÃ©c, 18:04, dineshbabu.munug...@.gmail.com wrote:
> > On Dec 4, 3:42 am, pd...@.clic.net wrote:
> >
> > > I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
> > > Standard.
> > > I have the switches /PAE /3GB in boot.ini
> >
> > > SQL max server memory is = 6656 MB
> >
> > > but with those settings I cannot get SQLServer to get more than 2.7 GB
> > > of RAM
> >
> > > If I use the AWE switch, then SQLServer can only use 128 MB max
> >
> > > What's wrong?
> >
> > > Paul
> >
> > Is it SQL Server 2000 or SQL Server 2005?
> > If its SQL Server 2000, then AWE is not supported
> > If its SQL Server 2005, then check the AWE 'Run Value' in
> > sp_configure... if its 0, then you might want to recycle the SQL
> > Server service to enable the AWE
> >
> > In any case i dont believe you need to have /3GB switch in boot.ini.
> SQL2005 Standard.
> Running in 32 bits system
> As soon as I use AWE, I'm stuck with 128 MB of RAM for SQL server...
> (i.e. I run dbcc checkDB so that it starts using rapidly memory and I
> see SQLServer.exe using not more than 128 MB of RAM in Task Manager).
> Paul
>
>|||Sorry. I missed that you are on SQL Server 2005.
In 32 -bit you do need AWE and as Ben pointed out gran permission Lock
Page in Memory permission to your service account.
To see if SQL server uses 6GB open PerfMonitor , do not use Task Manager
Visit also http://blogs.msdn.com/slavao/
<pdube@.clic.net> wrote in message
news:f9c9559b-d836-4b7f-92d4-bfc8834aab35@.s36g2000prg.googlegroups.com...
On 4 déc, 14:08, pd...@.clic.net wrote:
> On 4 déc, 10:45, "Uri Dimant" <u...@.iscar.co.il> wrote:
>
>
> > Hi
> > Do you have SP4 with hotfix?
> > Install hotfix from(http://support.microsoft.com/kb/899761)
> > Do you see in PerfMon that SQL Server allocate 6.6GB RAM?
> > <pd...@.clic.net> wrote in message
> >news:e4a9b19a-1b95-4df7-b6d9-e8c374bfb9c0@.a39g2000pre.googlegroups.com...
> > On 3 déc, 18:04, dineshbabu.munug...@.gmail.com wrote:
> > > On Dec 4, 3:42 am, pd...@.clic.net wrote:
> > > > I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
> > > > Standard.
> > > > I have the switches /PAE /3GB in boot.ini
> > > > SQL max server memory is = 6656 MB
> > > > but with those settings I cannot get SQLServer to get more than 2.7
> > > > GB
> > > > of RAM
> > > > If I use the AWE switch, then SQLServer can only use 128 MB max
> > > > What's wrong?
> > > > Paul
> > > Is it SQL Server 2000 or SQL Server 2005?
> > > If its SQL Server 2000, then AWE is not supported
> > > If its SQL Server 2005, then check the AWE 'Run Value' in
> > > sp_configure... if its 0, then you might want to recycle the SQL
> > > Server service to enable the AWE
> > > In any case i dont believe you need to have /3GB switch in boot.ini.
> > SQL2005 Standard.
> > Running in 32 bits system
> > As soon as I use AWE, I'm stuck with 128 MB of RAM for SQL server...
> > (i.e. I run dbcc checkDB so that it starts using rapidly memory and I
> > see SQLServer.exe using not more than 128 MB of RAM in Task Manager).
> > Paul- Masquer le texte des messages précédents -
> > - Afficher le texte des messages précédents -
> SQL 2005 (9.00.3161.00)
> The hotfix is for SQL 2000...
> In PerfMon, I see Commited Bytes around 3.2 GB.
> In Task Manager I still see SQLServer.exe using 2.7 GB. I guess then
> that's just a problem related with Task Manager not showing the
> correct amount of RAM used...?
> Paul- Masquer le texte des messages précédents -
> - Afficher le texte des messages précédents -
When I say that I see 128 MB of RAM in SQLServer.exe when using AWE I
mean that's what I see in Task Manager. Am I correct looking at this
value?
Should I check PerfMon instead? Is the memory shown in TaskManager
only kernel memory and would not show all memory used by SQL?
Paul

8 GB of RAM

Friday, February 24, 2012

64 bit max memory setting for SQL 2005

We have 32GB of RAM on x64 bit Windows 2003 SP2 and SQL 2005 SP2 64 bit as
well.
This box only serves for SQL Server application
So I have been receiving some mixed recommendations about max and min memory
settings
They are
1) Just leave it as default which is 0 for min and 2147483647 for max
2) Put a default of around 15 or 20GB for min and 2147483647 for max i.e. do
not set a max setting but set a min setting so that when SQL Server starts
up, it has enough memory committed at the beginning.
3) Leave the default of 0 for min but set max to say 28GB i.e. have a max
setting
Please let me know whats the best way to go about this.
ThanksWhat is best for you may not be best for others but in general there is no
need to set a min setting if SQL Server is the only app on the server. But I
always set a MAX to ensure the OS and any other temporary processes such as
Term Services, Notepad, WinZip etc. that usually get run have enough all the
time. With 32GB I would set the MAX at 28 or 29 and see how that works out.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.hotmail.com> wrote in message
news:Op6o550PIHA.4712@.TK2MSFTNGP04.phx.gbl...
> We have 32GB of RAM on x64 bit Windows 2003 SP2 and SQL 2005 SP2 64 bit as
> well.
> This box only serves for SQL Server application
> So I have been receiving some mixed recommendations about max and min
> memory settings
> They are
> 1) Just leave it as default which is 0 for min and 2147483647 for max
> 2) Put a default of around 15 or 20GB for min and 2147483647 for max i.e.
> do not set a max setting but set a min setting so that when SQL Server
> starts up, it has enough memory committed at the beginning.
> 3) Leave the default of 0 for min but set max to say 28GB i.e. have a max
> setting
> Please let me know whats the best way to go about this.
> Thanks
>|||Andrew,
What about leaving it to the default of 2147483647 ?
Would that cause SQL to use all 32 GB if it needs to ?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23V2%23TM1PIHA.3676@.TK2MSFTNGP06.phx.gbl...
> What is best for you may not be best for others but in general there is no
> need to set a min setting if SQL Server is the only app on the server. But
> I always set a MAX to ensure the OS and any other temporary processes such
> as Term Services, Notepad, WinZip etc. that usually get run have enough
> all the time. With 32GB I would set the MAX at 28 or 29 and see how that
> works out.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:Op6o550PIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> We have 32GB of RAM on x64 bit Windows 2003 SP2 and SQL 2005 SP2 64 bit
>> as well.
>> This box only serves for SQL Server application
>> So I have been receiving some mixed recommendations about max and min
>> memory settings
>> They are
>> 1) Just leave it as default which is 0 for min and 2147483647 for max
>> 2) Put a default of around 15 or 20GB for min and 2147483647 for max i.e.
>> do not set a max setting but set a min setting so that when SQL Server
>> starts up, it has enough memory committed at the beginning.
>> 3) Leave the default of 0 for min but set max to say 28GB i.e. have a max
>> setting
>> Please let me know whats the best way to go about this.
>> Thanks
>>
>|||64 bit has less overhead than 32 bit when dealing with large amounts of
memory but the OS still needs x amount of memory to operate properly. Even
though the memory in 64 bit can be dynamic sql server can still get to a
point where it may want to fight with the OS for every last bit of memory.
If you have Lock Pages in Memory set (which you should) it can be slow to
respond. There is no need to put it into that situation.
http://blogs.msdn.com/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.hotmail.com> wrote in message
news:OAsXbW1PIHA.4752@.TK2MSFTNGP05.phx.gbl...
> Andrew,
> What about leaving it to the default of 2147483647 ?
> Would that cause SQL to use all 32 GB if it needs to ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23V2%23TM1PIHA.3676@.TK2MSFTNGP06.phx.gbl...
>> What is best for you may not be best for others but in general there is
>> no need to set a min setting if SQL Server is the only app on the server.
>> But I always set a MAX to ensure the OS and any other temporary processes
>> such as Term Services, Notepad, WinZip etc. that usually get run have
>> enough all the time. With 32GB I would set the MAX at 28 or 29 and see
>> how that works out.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:Op6o550PIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> We have 32GB of RAM on x64 bit Windows 2003 SP2 and SQL 2005 SP2 64 bit
>> as well.
>> This box only serves for SQL Server application
>> So I have been receiving some mixed recommendations about max and min
>> memory settings
>> They are
>> 1) Just leave it as default which is 0 for min and 2147483647 for max
>> 2) Put a default of around 15 or 20GB for min and 2147483647 for max
>> i.e. do not set a max setting but set a min setting so that when SQL
>> Server starts up, it has enough memory committed at the beginning.
>> 3) Leave the default of 0 for min but set max to say 28GB i.e. have a
>> max setting
>> Please let me know whats the best way to go about this.
>> Thanks
>>
>|||I suggest you to read the following article, it'll make your mind clear
about min\max server memory options.
http://msdn2.microsoft.com/en-us/library/ms180797.aspx
--
Ekrem Önsoy
"Hassan" <hassan@.hotmail.com> wrote in message
news:Op6o550PIHA.4712@.TK2MSFTNGP04.phx.gbl...
> We have 32GB of RAM on x64 bit Windows 2003 SP2 and SQL 2005 SP2 64 bit as
> well.
> This box only serves for SQL Server application
> So I have been receiving some mixed recommendations about max and min
> memory settings
> They are
> 1) Just leave it as default which is 0 for min and 2147483647 for max
> 2) Put a default of around 15 or 20GB for min and 2147483647 for max i.e.
> do not set a max setting but set a min setting so that when SQL Server
> starts up, it has enough memory committed at the beginning.
> 3) Leave the default of 0 for min but set max to say 28GB i.e. have a max
> setting
> Please let me know whats the best way to go about this.
> Thanks
>

64 bit max memory setting for SQL 2005

64 bit and max server memory

Do we still need to put a cap to the max server memory settings for SQL on a
64 bit machine or just leave it to the default ?The guideline on setting Max Server Memory doesn't change whether it's 32-bit
or 64-bit.
Linchi
"Hassan" wrote:
> Do we still need to put a cap to the max server memory settings for SQL on a
> 64 bit machine or just leave it to the default ?
>
>

Thursday, March 29, 2012

A few BLOBs per page

Tuesday, March 27, 2012

A few BLOBs per page

A few BLOBs per page

Monday, March 19, 2012

A basic question: Removing duplicate results from Max function

95th percentile calculation

Sunday, March 11, 2012

8 GB of RAM

8 GB of RAM

8 GB of RAM

Friday, February 24, 2012

64 bit max memory setting for SQL 2005

64 bit max memory setting for SQL 2005

64 bit max memory setting for SQL 2005

64 bit and max server memory

3G ram for SQL server

Blog Archive

About Me