Autonumber field: How can I resolve concurrency problems in SQL Server 2005?

I'm having a concurrency problem in SQL Server 2005. There are a number of free seats on the bus that I sell tickets to. Before inserting a sold ticket I need to check whether there are any free seats left. My stored procedure does something like this:

CREATE PROCEDURE add_ticket -- parameters DECLARE free_seats int BEGIN TRANSACTION SELECT free_seats = COUNT(*) FROM tickets WHERE seat_is_not_taken IF free_seats <> 0 INSERT INTO tickets VALUES(...) -- some other statements END TRANSACTION

The problem is that two processes can read the amount of free tickets concurrently and both save a ticket, even if there are no free seats left. I need a way to block processes from reading the amount of free tickets while other processes running the add_ticket procedure have not yet inserted a new ticket. SET TRANSACTION ISOLATION LEVEL does not help in this situation, am I right?

You are correct; a higher isolation level would not help ensure that multiple readers did not read the same rows simultaneously. However, there are several ways you could make this work. For instance, you could assign each seat a unique identifier (meaning, a unique key – not necessarily a GUID) and create a table for seats that have already been taken. Put a UNIQUE constraint on the table and you will be guaranteed that no seat is inserted twice.

That said, I think a more interesting option might be to employ SQL Service Broker. You could set up a conversation for each bus, and store the conversation handles in a table that can be referenced by readers before doing the RECEIVE. That way, the readers can filter appropriately. Drop a message into the queue for each seat on the bus. The readers can then simply RECEIVE the messages as needed (in the process, reserving seats on the bus). Service Broker will ensure that no message is received twice, meaning that you will no longer have any concurrency problems.PAE allows the OS to use more than 4GB of memory. AWE allows the
application to use more than 4GB.
--
Andrew J. Kelly SQL MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
> guys,
> what is the difference between AWE and PAE?|||for SQL server, shd i enable PAE or AWE? Can both be enabled at the same time?
"Andrew J. Kelly" wrote:
> PAE allows the OS to use more than 4GB of memory. AWE allows the
> application to use more than 4GB.
> --
> Andrew J. Kelly SQL MVP
>
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
> > guys,
> > what is the difference between AWE and PAE?
>
>|||AWE is a SQL Server setting (sp_configure) and PAE is an operating system setting (BOOT.INI). If you
want SQL Server to utilize > 4GB memory, you need both settings.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com...
> for SQL server, shd i enable PAE or AWE? Can both be enabled at the same time?
> "Andrew J. Kelly" wrote:
>> PAE allows the OS to use more than 4GB of memory. AWE allows the
>> application to use more than 4GB.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "rupart" <rupart@.discussions.microsoft.com> wrote in message
>> news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
>> > guys,
>> > what is the difference between AWE and PAE?
>>|||in that case...in a server with 5G of ram
should i put the /AWE /PAE swith in the same line in the boot.ini?
For the OS, i can see under system mgmt that 5G is enabled. How abt for sql?
how do i check it has 5G? Also, is there any significant on it? The
performance shd be better i suppose
Thank you
"Tibor Karaszi" wrote:
> AWE is a SQL Server setting (sp_configure) and PAE is an operating system setting (BOOT.INI). If you
> want SQL Server to utilize > 4GB memory, you need both settings.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com...
> > for SQL server, shd i enable PAE or AWE? Can both be enabled at the same time?
> >
> > "Andrew J. Kelly" wrote:
> >
> >> PAE allows the OS to use more than 4GB of memory. AWE allows the
> >> application to use more than 4GB.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> >> news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
> >> > guys,
> >> > what is the difference between AWE and PAE?
> >>
> >>
> >>
>
>|||This is a multi-part message in MIME format.
--080301010603090003030909
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
boot.ini should have something like this:
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003,
Enterprise" /fastdetect /pae /3gb
(The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
provide a little more to the apps, i.e. SQL Server.)
To check the amount of physical RAM the OS is seeing you can just check
the Performance tab in task manager.
To turn on AWE memory for SQL Server you use the sp_configure stored
proc (in Query Analyzer for example):
exec sp_configure 'awe enabled', 1
reconfigure
go
Then you have to restart the SQL instance as the AWE setting only takes
affect on server startup. Also, when SQL Server is using AWE memory, it
cannot use dynamic memory management. It *will not swap pages out of
memory *if another app requests memory and the OS doesn't have enough to
satisfy the request (unlike the SQL dynamic memory manager). So you
should specify a "max server memory" amount with sp_configure. For
example, on your 5GB box, if you wanted to allocate 4GB to SQL and the
remaining 1GB to the OS & other apps, you would do this in QA:
exec sp_configure "max server memory", 5120
reconfigure
go
All this is documented in SQL BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3stg.asp
To see how much memory SQL Server is currently consuming you can open
the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff on
AWE & memory management.
HTH
--
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
rupart wrote:
>in that case...in a server with 5G of ram
>should i put the /AWE /PAE swith in the same line in the boot.ini?
>For the OS, i can see under system mgmt that 5G is enabled. How abt for sql?
>how do i check it has 5G? Also, is there any significant on it? The
>performance shd be better i suppose
>Thank you
>"Tibor Karaszi" wrote:
>
>>AWE is a SQL Server setting (sp_configure) and PAE is an operating system setting (BOOT.INI). If you
>>want SQL Server to utilize > 4GB memory, you need both settings.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"rupart" <rupart@.discussions.microsoft.com> wrote in message
>>news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com...
>>
>>for SQL server, shd i enable PAE or AWE? Can both be enabled at the same time?
>>"Andrew J. Kelly" wrote:
>>
>>PAE allows the OS to use more than 4GB of memory. AWE allows the
>>application to use more than 4GB.
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"rupart" <rupart@.discussions.microsoft.com> wrote in message
>>news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
>>
>>guys,
>>what is the difference between AWE and PAE?
>>
>>
>>
>>
--080301010603090003030909
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>boot.ini should have something like this: 
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003,
Enterprise" /fastdetect /pae /3gb 
 
(The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
provide a little more to the apps, i.e. SQL Server.) 
 
To check the amount of physical RAM the OS is seeing you can just check
the Performance tab in task manager. 
 
To turn on AWE memory for SQL Server you use the sp_configure stored
proc (in Query Analyzer for example): 
</tt>
<blockquote><tt>exec sp_configure 'awe enabled', 1</tt> 
<tt>reconfigure</tt> 
<tt>go 
</tt></blockquote>
<tt>Then you have to restart the SQL instance as the AWE setting only
takes affect on server startup.Â Also, when SQL Server is using AWE
memory, it cannot use dynamic memory management.Â It will not swap
pages out of memory if another app requests memory and the OS
doesn't have enough to satisfy the request (unlike the SQL dynamic
memory manager).Â So you should specify a "max server memory" amount
with sp_configure.Â For example, on your 5GB box, if you wanted to
allocate 4GB to SQL and the remaining 1GB to the OS & other apps,
you would do this in QA: 
</tt>
<blockquote><tt>exec sp_configure "max server memory", 5120</tt> 
<tt>reconfigure</tt> 
<tt>go</tt> 
</blockquote>
<tt>All this is documented in SQL BOL: 
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3stg..asp</a> ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3stg.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3stg..asp</a> 
 
To see how much memory SQL Server is currently consuming you can open
the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
Manager | Total Server Memory (KB).Â SQL BOL has a lot of good stuff on
AWE & memory management. 
 
HTH 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
 mallesons stephen jaques 
blog: <a href="http://links.10026.com/?link=/">http://sqlnerd.blogspot.com">
http://sqlnerd.blogspot.com</a> 
</div>
 
 
rupart wrote:
<blockquote cite="mid0C68F97D-764B-4C1A-926D-3926F39CC830@.microsoft.com"
type="cite">
<pre wrap="">in that case...in a server with 5G of ram
should i put the /AWE /PAE swith in the same line in the boot.ini?
For the OS, i can see under system mgmt that 5G is enabled. How abt for sql?
how do i check it has 5G? Also, is there any significant on it? The
performance shd be better i suppose
Thank you
"Tibor Karaszi" wrote:
</pre>
<blockquote type="cite">
<pre wrap="">AWE is a SQL Server setting (sp_configure) and PAE is an operating system setting (BOOT.INI). If you
want SQL Server to utilize > 4GB memory, you need both settings.
--
Tibor Karaszi, SQL Server MVP
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.karaszi.com/sqlserver/default.asp</a>">http://www.karaszi.com/sqlserver/default.asp">http://www.karaszi.com/sqlserver/default.asp</a>
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.solidqualitylearning.com/</a>">http://www.solidqualitylearning.com/">http://www.solidqualitylearning.com/</a>
"rupart" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:rupart@.discussions.microsoft.com"><rupart@.discussions.microsoft.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com">news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com</a>...
</pre>
<blockquote type="cite">
<pre wrap="">for SQL server, shd i enable PAE or AWE? Can both be enabled at the same time?
"Andrew J. Kelly" wrote:
</pre>
<blockquote type="cite">
<pre wrap="">PAE allows the OS to use more than 4GB of memory. AWE allows the
application to use more than 4GB.
--
Andrew J. Kelly SQL MVP
"rupart" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:rupart@.discussions.microsoft.com"><rupart@.discussions.microsoft.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com">news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com</a>...
</pre>
<blockquote type="cite">
<pre wrap="">guys,
what is the difference between AWE and PAE?
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</blockquote>
</body>
</html>
--080301010603090003030909--|||does /3g means the system will allocate 3g for system and the rest for
sql(that is after enabling thru AWE, rite?)?
yeah, good link...thank you
"Mike Hodgson" wrote:
> boot.ini should have something like this:
> multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003,
> Enterprise" /fastdetect /pae /3gb
> (The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
> provide a little more to the apps, i.e. SQL Server.)
> To check the amount of physical RAM the OS is seeing you can just check
> the Performance tab in task manager.
> To turn on AWE memory for SQL Server you use the sp_configure stored
> proc (in Query Analyzer for example):
> exec sp_configure 'awe enabled', 1
> reconfigure
> go
> Then you have to restart the SQL instance as the AWE setting only takes
> affect on server startup. Also, when SQL Server is using AWE memory, it
> cannot use dynamic memory management. It *will not swap pages out of
> memory *if another app requests memory and the OS doesn't have enough to
> satisfy the request (unlike the SQL dynamic memory manager). So you
> should specify a "max server memory" amount with sp_configure. For
> example, on your 5GB box, if you wanted to allocate 4GB to SQL and the
> remaining 1GB to the OS & other apps, you would do this in QA:
> exec sp_configure "max server memory", 5120
> reconfigure
> go
> All this is documented in SQL BOL:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3stg.asp
> To see how much memory SQL Server is currently consuming you can open
> the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
> Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff on
> AWE & memory management.
> HTH
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> rupart wrote:
> >in that case...in a server with 5G of ram
> >should i put the /AWE /PAE swith in the same line in the boot.ini?
> >For the OS, i can see under system mgmt that 5G is enabled. How abt for sql?
> >how do i check it has 5G? Also, is there any significant on it? The
> >performance shd be better i suppose
> >Thank you
> >
> >"Tibor Karaszi" wrote:
> >
> >
> >
> >>AWE is a SQL Server setting (sp_configure) and PAE is an operating system setting (BOOT.INI). If you
> >>want SQL Server to utilize > 4GB memory, you need both settings.
> >>
> >>--
> >>Tibor Karaszi, SQL Server MVP
> >>http://www.karaszi.com/sqlserver/default.asp
> >>http://www.solidqualitylearning.com/
> >>
> >>
> >>"rupart" <rupart@.discussions.microsoft.com> wrote in message
> >>news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com...
> >>
> >>
> >>for SQL server, shd i enable PAE or AWE? Can both be enabled at the same time?
> >>
> >>"Andrew J. Kelly" wrote:
> >>
> >>
> >>
> >>PAE allows the OS to use more than 4GB of memory. AWE allows the
> >>application to use more than 4GB.
> >>
> >>--
> >>Andrew J. Kelly SQL MVP
> >>
> >>
> >>"rupart" <rupart@.discussions.microsoft.com> wrote in message
> >>news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
> >>
> >>
> >>guys,
> >>what is the difference between AWE and PAE?
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
>|||The other way around. 3 GB for the application and 1 GB for the system.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:4944B057-1DC7-4638-BB88-9040FDCC9B9F@.microsoft.com...
> does /3g means the system will allocate 3g for system and the rest for
> sql(that is after enabling thru AWE, rite?)?
> yeah, good link...thank you
> "Mike Hodgson" wrote:
>> boot.ini should have something like this:
>> multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003,
>> Enterprise" /fastdetect /pae /3gb
>> (The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
>> provide a little more to the apps, i.e. SQL Server.)
>> To check the amount of physical RAM the OS is seeing you can just check
>> the Performance tab in task manager.
>> To turn on AWE memory for SQL Server you use the sp_configure stored
>> proc (in Query Analyzer for example):
>> exec sp_configure 'awe enabled', 1
>> reconfigure
>> go
>> Then you have to restart the SQL instance as the AWE setting only takes
>> affect on server startup. Also, when SQL Server is using AWE memory, it
>> cannot use dynamic memory management. It *will not swap pages out of
>> memory *if another app requests memory and the OS doesn't have enough to
>> satisfy the request (unlike the SQL dynamic memory manager). So you
>> should specify a "max server memory" amount with sp_configure. For
>> example, on your 5GB box, if you wanted to allocate 4GB to SQL and the
>> remaining 1GB to the OS & other apps, you would do this in QA:
>> exec sp_configure "max server memory", 5120
>> reconfigure
>> go
>> All this is documented in SQL BOL:
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3stg.asp
>> To see how much memory SQL Server is currently consuming you can open
>> the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
>> Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff on
>> AWE & memory management.
>> HTH
>> --
>> *mike hodgson*
>> / mallesons stephen jaques/
>> blog: http://sqlnerd.blogspot.com
>>
>> rupart wrote:
>> >in that case...in a server with 5G of ram
>> >should i put the /AWE /PAE swith in the same line in the boot.ini?
>> >For the OS, i can see under system mgmt that 5G is enabled. How abt for sql?
>> >how do i check it has 5G? Also, is there any significant on it? The
>> >performance shd be better i suppose
>> >Thank you
>> >
>> >"Tibor Karaszi" wrote:
>> >
>> >
>> >
>> >>AWE is a SQL Server setting (sp_configure) and PAE is an operating system setting (BOOT.INI).
>> >>If you
>> >>want SQL Server to utilize > 4GB memory, you need both settings.
>> >>
>> >>--
>> >>Tibor Karaszi, SQL Server MVP
>> >>http://www.karaszi.com/sqlserver/default.asp
>> >>http://www.solidqualitylearning.com/
>> >>
>> >>
>> >>"rupart" <rupart@.discussions.microsoft.com> wrote in message
>> >>news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com...
>> >>
>> >>
>> >>for SQL server, shd i enable PAE or AWE? Can both be enabled at the same time?
>> >>
>> >>"Andrew J. Kelly" wrote:
>> >>
>> >>
>> >>
>> >>PAE allows the OS to use more than 4GB of memory. AWE allows the
>> >>application to use more than 4GB.
>> >>
>> >>--
>> >>Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >>"rupart" <rupart@.discussions.microsoft.com> wrote in message
>> >>news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
>> >>
>> >>
>> >>guys,
>> >>what is the difference between AWE and PAE?
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>|||This is a multi-part message in MIME format.
--010506000107000909000008
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Oops - slight typo in my "max server memory" statement. To set a max
server memory of 4GB you would run:
exec sp_configure "max server memory", 4096
reconfigure
go
The 5120 figure I included in my previous post would try to set it at
5GB (not 4GB).
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Mike Hodgson wrote:
> boot.ini should have something like this:
> multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003,
> Enterprise" /fastdetect /pae /3gb
> (The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
> provide a little more to the apps, i.e. SQL Server.)
> To check the amount of physical RAM the OS is seeing you can just
> check the Performance tab in task manager.
> To turn on AWE memory for SQL Server you use the sp_configure stored
> proc (in Query Analyzer for example):
> exec sp_configure 'awe enabled', 1
> reconfigure
> go
> Then you have to restart the SQL instance as the AWE setting only
> takes affect on server startup. Also, when SQL Server is using AWE
> memory, it cannot use dynamic memory management. It *will not swap
> pages out of memory *if another app requests memory and the OS doesn't
> have enough to satisfy the request (unlike the SQL dynamic memory
> manager). So you should specify a "max server memory" amount with
> sp_configure. For example, on your 5GB box, if you wanted to allocate
> 4GB to SQL and the remaining 1GB to the OS & other apps, you would do
> this in QA:
> exec sp_configure "max server memory", 5120
> reconfigure
> go
> All this is documented in SQL BOL:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3stg..asp
> To see how much memory SQL Server is currently consuming you can open
> the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
> Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff
> on AWE & memory management.
> HTH
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> rupart wrote:
>>in that case...in a server with 5G of ram
>>should i put the /AWE /PAE swith in the same line in the boot.ini?
>>For the OS, i can see under system mgmt that 5G is enabled. How abt for sql?
>>how do i check it has 5G? Also, is there any significant on it? The
>>performance shd be better i suppose
>>Thank you
>>"Tibor Karaszi" wrote:
>>
>>AWE is a SQL Server setting (sp_configure) and PAE is an operating system setting (BOOT.INI). If you
>>want SQL Server to utilize > 4GB memory, you need both settings.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"rupart" <rupart@.discussions.microsoft.com> wrote in message
>>news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com...
>>
>>for SQL server, shd i enable PAE or AWE? Can both be enabled at the same time?
>>"Andrew J. Kelly" wrote:
>>
>>PAE allows the OS to use more than 4GB of memory. AWE allows the
>>application to use more than 4GB.
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"rupart" <rupart@.discussions.microsoft.com> wrote in message
>>news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
>>
>>guys,
>>what is the difference between AWE and PAE?
>>
>>
>>
--010506000107000909000008
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Oops - slight typo in my "max server memory" statement.Â To set a
max server memory of 4GB you would run: 
</tt>
<blockquote><tt>exec sp_configure "max server memory", 4096</tt> 
<tt>reconfigure</tt> 
<tt>go</tt> 
</blockquote>
<tt>The 5120 figure I included in my previous post would try to set it
at 5GB (not 4GB). 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Mike Hodgson wrote:
<blockquote cite="midOSFjFxFfFHA.3944@.tk2msftngp13.phx.gbl" type="cite">
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<tt>boot.ini should have something like this: 
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003,
Enterprise" /fastdetect /pae /3gb 
 
(The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
provide a little more to the apps, i.e. SQL Server.) 
 
To check the amount of physical RAM the OS is seeing you can just check
the Performance tab in task manager. 
 
To turn on AWE memory for SQL Server you use the sp_configure stored
proc (in Query Analyzer for example): 
</tt>
<blockquote><tt>exec sp_configure 'awe enabled', 1</tt> 
<tt>reconfigure</tt> 
<tt>go 
</tt></blockquote>
<tt>Then you have to restart the SQL instance as the AWE setting only
takes affect on server startup.Â Also, when SQL Server is using AWE
memory, it cannot use dynamic memory management.Â It will not swap
pages out of memory if another app requests memory and the OS
doesn't have enough to satisfy the request (unlike the SQL dynamic
memory manager).Â So you should specify a "max server memory" amount
with sp_configure.Â For example, on your 5GB box, if you wanted to
allocate 4GB to SQL and the remaining 1GB to the OS & other apps,
you would do this in QA: 
</tt>
<blockquote><tt>exec sp_configure "max server memory", 5120</tt> 
<tt>reconfigure</tt> 
<tt>go</tt> 
</blockquote>
<tt>All this is documented in SQL BOL: 
<a class="moz-txt-link-freetext"
href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3stg..asp</a> ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3stg.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3stg..asp</a> 
 
To see how much memory SQL Server is currently consuming you can open
the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
Manager | Total Server Memory (KB).Â SQL BOL has a lot of good stuff on
AWE & memory management. 
 
HTH 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
 mallesons
stephen
 jaques 
blog: <a href="http://links.10026.com/?link=/">http://sqlnerd.blogspot.com">
http://sqlnerd.blogspot.com</a> 
</div>
 
 
rupart wrote:
<blockquote
cite="mid0C68F97D-764B-4C1A-926D-3926F39CC830@.microsoft.com"
type="cite">
<pre wrap="">in that case...in a server with 5G of ram
should i put the /AWE /PAE swith in the same line in the boot.ini?
For the OS, i can see under system mgmt that 5G is enabled. How abt for sql?
how do i check it has 5G? Also, is there any significant on it? The
performance shd be better i suppose
Thank you
"Tibor Karaszi" wrote:
</pre>
<blockquote type="cite">
<pre wrap="">AWE is a SQL Server setting (sp_configure) and PAE is an operating system setting (BOOT.INI). If you
want SQL Server to utilize > 4GB memory, you need both settings.
--
Tibor Karaszi, SQL Server MVP
<a class="moz-txt-link-freetext"
href="http://links.10026.com/?link=http://www.karaszi.com/sqlserver/default.asp</a>">http://www.karaszi.com/sqlserver/default.asp">http://www.karaszi.com/sqlserver/default.asp</a>
<a class="moz-txt-link-freetext"
href="http://links.10026.com/?link=http://www.solidqualitylearning.com/</a>">http://www.solidqualitylearning.com/">http://www.solidqualitylearning.com/</a>
"rupart" <a class="moz-txt-link-rfc2396E"
href="http://links.10026.com/?link=mailto:rupart@.discussions.microsoft.com"><rupart@.discussions.microsoft.com></a> wrote in message
<a class="moz-txt-link-freetext"
href="http://links.10026.com/?link=news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com">news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com</a>...
</pre>
<blockquote type="cite">
<pre wrap="">for SQL server, shd i enable PAE or AWE? Can both be enabled at the same time?
"Andrew J. Kelly" wrote:
</pre>
<blockquote type="cite">
<pre wrap="">PAE allows the OS to use more than 4GB of memory. AWE allows the
application to use more than 4GB.
--
Andrew J. Kelly SQL MVP
"rupart" <a class="moz-txt-link-rfc2396E"
href="http://links.10026.com/?link=mailto:rupart@.discussions.microsoft.com"><rupart@.discussions.microsoft.com></a> wrote in message
<a class="moz-txt-link-freetext"
href="http://links.10026.com/?link=news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com">news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com</a>...
</pre>
<blockquote type="cite">
<pre wrap="">guys,
what is the difference between AWE and PAE?
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</blockquote>
</blockquote>
</body>
</html>
--010506000107000909000008--

Sunday, March 11, 2012

How can I resolve concurrency problems in SQL Server 2005?

No comments:

Post a Comment

Autonumber field

Blog Archive

About Me