Autonumber field: number

Showing posts with label number. Show all posts

Tuesday, March 20, 2012

Backcolor property

Is there a simple way to make the backcolor of a table
object alternate? If there was a row number global I could
just use an expression.Try something like this for the background color on the row...
=iif(RowNumber(Nothing) Mod 2, "white", "gainsboro")
"John Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:1f44601c4579a$fa975ba0$a501280a@.phx.gbl...
> Is there a simple way to make the backcolor of a table
> object alternate? If there was a row number global I could
> just use an expression.sql

Sunday, March 11, 2012

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--

Friday, February 24, 2012

Avoiding deadlock

I have a stored procedure spUpdateClient, which takes as params a number of properties of a client application that wants to register its existence with the database. The sp just needs to add a new row or update an existing row with this data.

I tried to accomplish this with code somethign like this. (The table I'm updating is called Client, and its primary key is ClientId, which is a value passed into the sp from the client.)

IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

But the client apps call this every second or so, so soon enough I started getting primary key violations. It looks like one client would make two calls nearly at the same time, both would get a 0 value on the SELECT line, so both would try to insert a new row with the same ClientId. No good.

So then I added

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

at the top, and a COMMIT at the bottom. I thought the first one in would get to run the whole sp, and the next one in would have to wait for the first to be done.

Instead I'm now getting deadlock errors.

If I understand the docs right, that's because the exclusive lock is not placed on the Clients table until the INSERT happens, not at the SELECT. So when two calls to the sp happen at nearly the same time (call them A and B), A does the SELECT and that locks Clients so nobody else can update it. Then B does the SELECT, locking Clients so nobody else (including A) can update it. Now A needs to exclusively lock Clients to do its INSERT, but B still has that read lock on it, and they're deadlocked.

I could catch the deadlock in my client app after SQL Server kills one of the transactions, but it seems to me there should be some way to set a lock at the top of the sp that says "nobody else can enter this sp until I exit it". Any such thing?

Thanks.

Nate Hekman

You can change your code to the following:

BEGIN TRANSACTION
IF NOT EXISTS(SELECT * FROM Clients WITH(SERIALIZABLE, XLOCK)

WHERE ClientId=@.ClientId)
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

COMMIT

The XLOCK hint in the SELECT statement will ensure that if the rows exists you lock it exclusively so the update will work fine. And the SERIALIZABLE hint will ensure that if the row doesn't exist you lock the key range for the new row. This will prevent the deadlock from happening.

Another approach is to do the following:

BEGIN TRANSACTION
UPDATE Clients WITH(SERIALIZABLE)
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
IF @.@.ROWCOUNT = 0

BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

COMMIT

|||

This is a very good understanding of why you are getting a deadlock :) You can cause it to single thread by adding an LOCK hint to the existence check. Also, change from using a count to exists, it will be better as it just needs to see a single row, rather than counting them all. No need for serializable here now, but make sure ClientId is indexed right (and if it is the declare primary key that should be fine)

BEGIN TRANSACTION

IF EXISTS (SELECT 1
FROM Clients WITH (XLOCK)
WHERE ClientId=@.ClientId)
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END
ELSE
BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

COMMIT TRANSACTION

An alternative is to just ignore the deadlock as you know why it occurs, and since you are only keeping a single copy, it is just as good and will be fast enough...

Another alternative would be to switch to an insert only methodology and just pump rows into a table. It would give you counts of visits, actual times of visits, etc. You could also glean the same information as you have now with no locking problems at all.

--clientId, visitDate would be the likely UNIQUE constraint
--if they are > .003 seconds apart, which I don't know based on your needs

create table clientVisit
(
clientVisitId int identity primary key,
clientId guid,
visitDate datetime default (getdate()),
hostName varchar(?),
etc varchar(?)
)

then just insert... It will take more disk space, but it should be just as fast. You could then pull the data off periodically and get the same information, plus some.

|||

I didn't think that:

BEGIN TRANSACTION
IF NOT EXISTS(SELECT * FROM Clients WITH(SERIALIZABLE, UPDLOCK)
WHERE ClientId=@.ClientId)

Would suffice since a SHARED lock compatible with an UPDATE lock? In this case, the second could still read there to be no rows.

(I did overlook that you need to increase the isolation level just in case READ_COMMITTED_SNAPSHOT is enabled. And I didn't realize you could put lock hints on UPDATE statements :)

Thanks!

|||You are right. This should be XLOCK instead.|||

Wow, thanks for the excellent replies everyone! I'm very new at T-SQL so all this locking stuff is a lot to mull over. But you've given me several good approaches that I think will work just great.

Thanks again.

Nate

|||

IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

|||

This problem's interesting and I'm sure lots of people have encountered before.

There are some things I still don't understand.
If we set the isolation level as Serializable and use XLOCK for the SELECT as follows:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

IF (SELECT COUNT(ClientId) FROM Clients (WITH XLOCK) WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

COMMIT TRANSACTION

As Nate Hekman first wrote:
If I understand the docs right, that's because the exclusive lock is not placed on the Clients table until the INSERT happens, not at the SELECT. So when two calls to the sp happen at nearly the same time (call them A and B), A does the SELECT and that locks Clients so nobody else can update it. Then B does the SELECT, locking Clients so nobody else (including A) can update it. Now A needs to exclusively lock Clients to do its INSERT, but B still has that read lock on it, and they're deadlocked.

Question 1>
If the row doesn't exist, does the process go like this:
process1 executes SELECT ... WITH XLOCK and holds an exclusive lock on Clients.
Because isolation level Serializable is used, other processes can't insert new rows or update rows within the defined range. Also, because an exclusive lock is held, other processes can't even read until process1 finishes?

Question 2>
How does it solve the deadlock problem?

|||

Question 1:
When a transaction holds Exclusive locks on a range of rows, other transaction cannot read those rows, unless the second transaction's isolation level is not READ UNCOMMITTED. As Nate uses the same sp for inserts, yes, the select command blocks all readers.

Question2:
Deadlocks do not occur because a transaction that executes its select command is guaranteed to be granted all the locks it will needs further, that is, it cannot be blocked, so a deadlock can never occur. This is a sort of pre-declaration of locks, or pessimistic locking. When transaction A executes its select command, the other ones (say trans. B)cannot read. If transaction A takes a long time to commit, B might be eventually aborted. But this will not happen-the insert transaction is a short one, and it will need milliseconds to commit, after that transaction B(or other waiting transaction) will be granted the lock. In this scenario, aborts are very unlikely to occur unless the workload is high.

Avoiding deadlock

I tried to accomplish this with code somethign like this. (The table I'm updating is called Client, and its primary key is ClientId, which is a value passed into the sp from the client.)

IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

So then I added

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

at the top, and a COMMIT at the bottom. I thought the first one in would get to run the whole sp, and the next one in would have to wait for the first to be done.

Instead I'm now getting deadlock errors.

Thanks.

Nate Hekman

You can change your code to the following:

BEGIN TRANSACTION
IF NOT EXISTS(SELECT * FROM Clients WITH(SERIALIZABLE, XLOCK)

WHERE ClientId=@.ClientId)
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

COMMIT

Another approach is to do the following:

BEGIN TRANSACTION
UPDATE Clients WITH(SERIALIZABLE)
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
IF @.@.ROWCOUNT = 0

BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

COMMIT

|||

BEGIN TRANSACTION

COMMIT TRANSACTION

An alternative is to just ignore the deadlock as you know why it occurs, and since you are only keeping a single copy, it is just as good and will be fast enough...

--clientId, visitDate would be the likely UNIQUE constraint
--if they are > .003 seconds apart, which I don't know based on your needs

create table clientVisit
(
clientVisitId int identity primary key,
clientId guid,
visitDate datetime default (getdate()),
hostName varchar(?),
etc varchar(?)
)

then just insert... It will take more disk space, but it should be just as fast. You could then pull the data off periodically and get the same information, plus some.

|||

I didn't think that:

BEGIN TRANSACTION
IF NOT EXISTS(SELECT * FROM Clients WITH(SERIALIZABLE, UPDLOCK)
WHERE ClientId=@.ClientId)

Would suffice since a SHARED lock compatible with an UPDATE lock? In this case, the second could still read there to be no rows.

(I did overlook that you need to increase the isolation level just in case READ_COMMITTED_SNAPSHOT is enabled. And I didn't realize you could put lock hints on UPDATE statements :)

Thanks!

|||You are right. This should be XLOCK instead.|||

Wow, thanks for the excellent replies everyone! I'm very new at T-SQL so all this locking stuff is a lot to mull over. But you've given me several good approaches that I think will work just great.

Thanks again.

Nate

|||

IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

|||

This problem's interesting and I'm sure lots of people have encountered before.

There are some things I still don't understand.
If we set the isolation level as Serializable and use XLOCK for the SELECT as follows:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

COMMIT TRANSACTION

Question 2>
How does it solve the deadlock problem?

|||

Avoiding compilation

Using small stored procs or sp_executesql dramatically reduces the number of
recompiles and increases the reuse of execution plans. This is evident from
both the usecount in syscacheobjects, perfmon, and profiler. However I'm at
a loss to determine what causes a compilation. Under rare circumstances the
usecount for Compiled Plan does not increase as statements are run. Seems
to correspond to when there is no execution plan. It would seem to me that
compilation is a resource intensive task that if possible (data and schema
are not changing) should be held to a minimum.

How does one encourage the reuse of compile plans?
Is this the same as minimizing compilation?

Looks like some of this behavior is changing in SQL 2005...

Thanks,
DannyI am not privy to all the internals, there are better guys out there for
that .. but my $.02

When enough has changed in the data and table statistics where SQL Server
thinks it could get a better query plan it will do a recompile.

I know you can force a recompile by adding a parm to the proc create
statement, but don't know how to have it NOT recompile, or if you'd really
want to (I'd like to continue to use this crappy query plan please)..

Some thing that almost guarantees a recompile is the creation of #Temp
Tables in the proc.
Substitute a table variable to get around that.

"Danny" <istdrs@.flash.net> wrote in message
news:59Rrd.1559$nE7.982@.newssvr17.news.prodigy.com ...
> Using small stored procs or sp_executesql dramatically reduces the number
> of recompiles and increases the reuse of execution plans. This is evident
> from both the usecount in syscacheobjects, perfmon, and profiler. However
> I'm at a loss to determine what causes a compilation. Under rare
> circumstances the usecount for Compiled Plan does not increase as
> statements are run. Seems to correspond to when there is no execution
> plan. It would seem to me that compilation is a resource intensive task
> that if possible (data and schema are not changing) should be held to a
> minimum.
> How does one encourage the reuse of compile plans?
> Is this the same as minimizing compilation?
> Looks like some of this behavior is changing in SQL 2005...
> Thanks,
> Danny|||David,

In general on a DSS, recompiles I can avoid. Although I've always heard
that using table variables instead or temp tables in procs reduces the
chance of recompile. But in simple testing of temp tables in procs (see
sample code below), I'm not seeing any indication of a recompile either in
syscacheobjects, perfmon, or profiler.

Any know if this is true and why?

Danny

use northwind
go

Create proc TestRecompile (@.X int)
As
set nocount on

-- create temp table
create table #testtable (col1 int not null)
insert into #testtable values (@.X)

--do something else
select * from northwind.dbo.[order details] o
join #testtable t on o.orderid = t.col1

dbcc FREEPROCCACHE

exec TestRecompile 10248

select bucketid, cacheobjtype, objid, usecounts from
master..syscacheobjects where objtype = 'Proc' and sql = 'TestRecompile'
-- recompile on first run

exec TestRecompile 10255
-- Perfmon shows compilation
-- usecounts increases to 2 for Compiled Plan no corresponding Executable
plan

select bucketid, cacheobjtype, objid, usecounts from
master..syscacheobjects where objtype = 'Proc' and sql = 'TestRecompile'

--drop proc TestRecompile

"David Rawheiser" <rawhide58@.hotmail.com> wrote in message
news:xsRrd.1020236$Gx4.172215@.bgtnsc04-news.ops.worldnet.att.net...
>I am not privy to all the internals, there are better guys out there for
>that .. but my $.02
> When enough has changed in the data and table statistics where SQL Server
> thinks it could get a better query plan it will do a recompile.
> I know you can force a recompile by adding a parm to the proc create
> statement, but don't know how to have it NOT recompile, or if you'd really
> want to (I'd like to continue to use this crappy query plan please)..
> Some thing that almost guarantees a recompile is the creation of #Temp
> Tables in the proc.
> Substitute a table variable to get around that.
> "Danny" <istdrs@.flash.net> wrote in message
> news:59Rrd.1559$nE7.982@.newssvr17.news.prodigy.com ...
>> Using small stored procs or sp_executesql dramatically reduces the number
>> of recompiles and increases the reuse of execution plans. This is
>> evident from both the usecount in syscacheobjects, perfmon, and profiler.
>> However I'm at a loss to determine what causes a compilation. Under rare
>> circumstances the usecount for Compiled Plan does not increase as
>> statements are run. Seems to correspond to when there is no execution
>> plan. It would seem to me that compilation is a resource intensive task
>> that if possible (data and schema are not changing) should be held to a
>> minimum.
>>
>> How does one encourage the reuse of compile plans?
>> Is this the same as minimizing compilation?
>>
>> Looks like some of this behavior is changing in SQL 2005...
>>
>> Thanks,
>> Danny
>>|||Danny (istdrs@.flash.net) writes:
> In general on a DSS, recompiles I can avoid. Although I've always heard
> that using table variables instead or temp tables in procs reduces the
> chance of recompile. But in simple testing of temp tables in procs (see
> sample code below), I'm not seeing any indication of a recompile either in
> syscacheobjects, perfmon, or profiler.
> Any know if this is true and why?

It's not that simple that if you have a temp table you get a recompile.
But if you create a temp table, fill it with quite some data, you are
likely to see a recompile in the next operation. Also, if you create a
temp table in the middle of a stored procedure, the bets for a reompile
are good.

Note that sometimes recompilations are bad, and sometimes they are heaven-
sent, all depending on the nature of the stored procedure.

Rather than discussing the topic in detail myself, I refer you to this white
paper: http://www.microsoft.com/technet/pr...005/recomp.mspx.
While it is written for SQL 2005, it gives plenty of details that applies
to SQL 2000 as well. The biggest difference between the two, is that
SQL2005 adds statement recompilation which is not in SQL 2000.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Thursday, February 16, 2012

average of selected members?

I got a measure caled [number of persons]

Now i want another calculated meassure which shows the average number of persons within the selected time period.

My timedimension is :

[Date].[Period].[Month]

Let's say i have

jan - 1000, feb - 1200, mar - 1100, apr - 1000

So if i select "feb" and "mar" in the cube, my average should show 1150

if i select jan,feb,apr i want my average to show 1066,67

is this possible ?

Assuming that you're using AS 2005, something like:

Avg(Existing [Date].[Period].[Month].Members, [Measures].[number of persons])

|||

Ahh yes... This will work if the selection of members is specified in the WHERE statement of the MDX query. However, it will not work if a sub-select/SUBCUBE is used.

See http://www.sqljunkies.com/WebLog/reckless/archive/2006/03/08/18601.aspx for an elaboration on the issue.

Monday, February 13, 2012

Average column in a matrix

Hi,
I am trying to add an average column to a matrix in report services. I keep
receiving errors about aggregate amounts.
Example
Amount Number of Units Average
10.00 2
?
15.00 3
?
Total: 25.00 5
?You may want to try to explicitly cast the datatype like this:
=Avg(CDbl(Fields!Amount.Value))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Susan" <Susan@.discussions.microsoft.com> wrote in message
news:6EABA805-17D6-4365-A1E1-474E60775B47@.microsoft.com...
> Hi,
> I am trying to add an average column to a matrix in report services. I
keep
> receiving errors about aggregate amounts.
> Example
> Amount Number of Units Average
> 10.00 2
> ?
> 15.00 3
> ?
> Total: 25.00 5
> ?

Available MSSQL Servers during registration ...

Hi all,
Observation: When I use the "Register SQL Server Wizard" I noticed that
there are a number of SQL DB Servers that are not populating the "Available
servers" listbox. Note: this also occurs when I programatically generate a
list of database servers using the ODBC32 API's.
Question: How can I get this list to accurately reflect the databases on my
network? Are there settings on the database server that neet to be changed?
For example, my local development workstation has three databases on it --
the "(local)" instance, and two additional instances ("\DEV" and "\TEST").
The "(local)" instance shows up, but I am not seeing the two other instances
"<COMPUTERNAME>\DEV" and "<COMPUTERNAME>\TEST".
Thoughts?
Thanks!
WadeHi
You may have hide server checked in the SQL Servers TCP/IP setting.
John
"Wade" wrote:
> Hi all,
> Observation: When I use the "Register SQL Server Wizard" I noticed that
> there are a number of SQL DB Servers that are not populating the "Available
> servers" listbox. Note: this also occurs when I programatically generate a
> list of database servers using the ODBC32 API's.
> Question: How can I get this list to accurately reflect the databases on my
> network? Are there settings on the database server that neet to be changed?
> For example, my local development workstation has three databases on it --
> the "(local)" instance, and two additional instances ("\DEV" and "\TEST").
> The "(local)" instance shows up, but I am not seeing the two other instances
> "<COMPUTERNAME>\DEV" and "<COMPUTERNAME>\TEST".
> Thoughts?
> Thanks!
> Wade
>
>|||Hi John,
Thanks for the suggestion. However, I just checked, and all of these DB
servers have TCP/IP and Named Pipes enabled, and the "Hide server" checkbox
has not been checked.
Any other ideas?
Thanks,
Wade
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:EBECB381-89A7-456A-9016-921FDA02413B@.microsoft.com...
> Hi
> You may have hide server checked in the SQL Servers TCP/IP setting.
> John
> "Wade" wrote:
>> Hi all,
>> Observation: When I use the "Register SQL Server Wizard" I noticed that
>> there are a number of SQL DB Servers that are not populating the
>> "Available
>> servers" listbox. Note: this also occurs when I programatically generate
>> a
>> list of database servers using the ODBC32 API's.
>> Question: How can I get this list to accurately reflect the databases on
>> my
>> network? Are there settings on the database server that neet to be
>> changed?
>> For example, my local development workstation has three databases on
>> it --
>> the "(local)" instance, and two additional instances ("\DEV" and
>> "\TEST").
>> The "(local)" instance shows up, but I am not seeing the two other
>> instances
>> "<COMPUTERNAME>\DEV" and "<COMPUTERNAME>\TEST".
>> Thoughts?
>> Thanks!
>> Wade
>>|||The browsing implementation isn't guaranteed to find all servers, quite simply. Type the name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:%23Dl9gR0pFHA.3064@.TK2MSFTNGP15.phx.gbl...
> Hi John,
> Thanks for the suggestion. However, I just checked, and all of these DB servers have TCP/IP and
> Named Pipes enabled, and the "Hide server" checkbox has not been checked.
> Any other ideas?
> Thanks,
> Wade
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:EBECB381-89A7-456A-9016-921FDA02413B@.microsoft.com...
>> Hi
>> You may have hide server checked in the SQL Servers TCP/IP setting.
>> John
>> "Wade" wrote:
>> Hi all,
>> Observation: When I use the "Register SQL Server Wizard" I noticed that
>> there are a number of SQL DB Servers that are not populating the "Available
>> servers" listbox. Note: this also occurs when I programatically generate a
>> list of database servers using the ODBC32 API's.
>> Question: How can I get this list to accurately reflect the databases on my
>> network? Are there settings on the database server that neet to be changed?
>> For example, my local development workstation has three databases on it --
>> the "(local)" instance, and two additional instances ("\DEV" and "\TEST").
>> The "(local)" instance shows up, but I am not seeing the two other instances
>> "<COMPUTERNAME>\DEV" and "<COMPUTERNAME>\TEST".
>> Thoughts?
>> Thanks!
>> Wade
>>
>|||Thanks for the info Tibor. Just curious -- is this a definitive answer, or
just your experience? Do you know of any KB article (or anything else) that
discusses this?
Thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eFcIbl0pFHA.3572@.TK2MSFTNGP09.phx.gbl...
> The browsing implementation isn't guaranteed to find all servers, quite
> simply. Type the name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:%23Dl9gR0pFHA.3064@.TK2MSFTNGP15.phx.gbl...
>> Hi John,
>> Thanks for the suggestion. However, I just checked, and all of these DB
>> servers have TCP/IP and Named Pipes enabled, and the "Hide server"
>> checkbox has not been checked.
>> Any other ideas?
>> Thanks,
>> Wade
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:EBECB381-89A7-456A-9016-921FDA02413B@.microsoft.com...
>> Hi
>> You may have hide server checked in the SQL Servers TCP/IP setting.
>> John
>> "Wade" wrote:
>> Hi all,
>> Observation: When I use the "Register SQL Server Wizard" I noticed that
>> there are a number of SQL DB Servers that are not populating the
>> "Available
>> servers" listbox. Note: this also occurs when I programatically
>> generate a
>> list of database servers using the ODBC32 API's.
>> Question: How can I get this list to accurately reflect the databases
>> on my
>> network? Are there settings on the database server that neet to be
>> changed?
>> For example, my local development workstation has three databases on
>> it --
>> the "(local)" instance, and two additional instances ("\DEV" and
>> "\TEST").
>> The "(local)" instance shows up, but I am not seeing the two other
>> instances
>> "<COMPUTERNAME>\DEV" and "<COMPUTERNAME>\TEST".
>> Thoughts?
>> Thanks!
>> Wade
>>
>>
>|||I think there might be a KN article, should be quick to find out, with a few good keyword to search
for. Also, check www.sqldev.net. There are some examples on calling this browser functionality from
code and there might also be some text on the shortcomings. Below is some text I saved from GertD,
from a previous posting. Come to thing of it, searching the ng archives can be a good idea as well:
There is no way to do return a guaranteed list of install SQL Servers, not
even using the old LAN Manager NetServersEnum looking for SV_TYPE_SQLSERVER
ListAvailableServers using the ODBC function SQLBrowseConnect. This function
performs a discovery broadcast on the network to discover installed SQL
Servers, the SQL Server have to respond on the discovery broadcast, hence
that you will find that not all servers will show up. Some conditions:
1: Only running SQL Servers will show up, however there is a detail (mostly
15 min.) after which the broadcast can be responded by the master browser
services on the network, incase the SQL Server just stopped.
2: On 7.0 the default protocol is Named Pipes which is NetBIOS based, in
2000 it is TCP/IP sockets, hence the broadcast in 7.0 was a NetBIOS
broadcast, which one does not have guaranteed delivery, two most routers are
configured not to pass on NetBIOS broadcast so you only will get subset of
the list of servers, mainly the one in you subnet. For sure the broadcast
does not go beyond or between NT domains. In 2000 this has been changed to a
TCP UDP broadcast.
3: In 7.0, Win9x versions do not respond on the NetBIOS broadcast, hence do
not show up in the list, in 2000 this has been changed to TCP UDP which is
understood by Win9x
4: The response has to be back to the originator within a certain timeout,
otherwise the server named is not included in the list.
Bottom-line, this is never going to return a list of SQL Server that you can
rely on. In 2000 the ability to register a SQL Server in the Active
Directory has been added, this would be a better solution.
That is not what SQL-DMO uses in SQL Server 7.0 and later. This was the case
in 6.5 and previous releases, when only NT was supported.
In 7.0 and later SQL-DMO uses the ODBC function SQLBrowseConnect to retrieve
a list of servers.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message news:Op5cma1pFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Thanks for the info Tibor. Just curious -- is this a definitive answer, or just your experience?
> Do you know of any KB article (or anything else) that discusses this?
> Thanks!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eFcIbl0pFHA.3572@.TK2MSFTNGP09.phx.gbl...
>> The browsing implementation isn't guaranteed to find all servers, quite simply. Type the name.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:%23Dl9gR0pFHA.3064@.TK2MSFTNGP15.phx.gbl...
>> Hi John,
>> Thanks for the suggestion. However, I just checked, and all of these DB servers have TCP/IP and
>> Named Pipes enabled, and the "Hide server" checkbox has not been checked.
>> Any other ideas?
>> Thanks,
>> Wade
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:EBECB381-89A7-456A-9016-921FDA02413B@.microsoft.com...
>> Hi
>> You may have hide server checked in the SQL Servers TCP/IP setting.
>> John
>> "Wade" wrote:
>> Hi all,
>> Observation: When I use the "Register SQL Server Wizard" I noticed that
>> there are a number of SQL DB Servers that are not populating the "Available
>> servers" listbox. Note: this also occurs when I programatically generate a
>> list of database servers using the ODBC32 API's.
>> Question: How can I get this list to accurately reflect the databases on my
>> network? Are there settings on the database server that neet to be changed?
>> For example, my local development workstation has three databases on it --
>> the "(local)" instance, and two additional instances ("\DEV" and "\TEST").
>> The "(local)" instance shows up, but I am not seeing the two other instances
>> "<COMPUTERNAME>\DEV" and "<COMPUTERNAME>\TEST".
>> Thoughts?
>> Thanks!
>> Wade
>>
>>
>>
>|||Thanks, Tibor.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%239l9QZ7pFHA.620@.TK2MSFTNGP15.phx.gbl...
>I think there might be a KN article, should be quick to find out, with a
>few good keyword to search for. Also, check www.sqldev.net. There are some
>examples on calling this browser functionality from code and there might
>also be some text on the shortcomings. Below is some text I saved from
>GertD, from a previous posting. Come to thing of it, searching the ng
>archives can be a good idea as well:
> There is no way to do return a guaranteed list of install SQL Servers, not
> even using the old LAN Manager NetServersEnum looking for
> SV_TYPE_SQLSERVER
> ListAvailableServers using the ODBC function SQLBrowseConnect. This
> function
> performs a discovery broadcast on the network to discover installed SQL
> Servers, the SQL Server have to respond on the discovery broadcast, hence
> that you will find that not all servers will show up. Some conditions:
> 1: Only running SQL Servers will show up, however there is a detail
> (mostly
> 15 min.) after which the broadcast can be responded by the master browser
> services on the network, incase the SQL Server just stopped.
> 2: On 7.0 the default protocol is Named Pipes which is NetBIOS based, in
> 2000 it is TCP/IP sockets, hence the broadcast in 7.0 was a NetBIOS
> broadcast, which one does not have guaranteed delivery, two most routers
> are
> configured not to pass on NetBIOS broadcast so you only will get subset of
> the list of servers, mainly the one in you subnet. For sure the broadcast
> does not go beyond or between NT domains. In 2000 this has been changed to
> a
> TCP UDP broadcast.
> 3: In 7.0, Win9x versions do not respond on the NetBIOS broadcast, hence
> do
> not show up in the list, in 2000 this has been changed to TCP UDP which is
> understood by Win9x
> 4: The response has to be back to the originator within a certain timeout,
> otherwise the server named is not included in the list.
> Bottom-line, this is never going to return a list of SQL Server that you
> can
> rely on. In 2000 the ability to register a SQL Server in the Active
> Directory has been added, this would be a better solution.
>
> That is not what SQL-DMO uses in SQL Server 7.0 and later. This was the
> case
> in 6.5 and previous releases, when only NT was supported.
> In 7.0 and later SQL-DMO uses the ODBC function SQLBrowseConnect to
> retrieve
> a list of servers.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:Op5cma1pFHA.2076@.TK2MSFTNGP14.phx.gbl...
>> Thanks for the info Tibor. Just curious -- is this a definitive answer,
>> or just your experience? Do you know of any KB article (or anything else)
>> that discusses this?
>> Thanks!
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:eFcIbl0pFHA.3572@.TK2MSFTNGP09.phx.gbl...
>> The browsing implementation isn't guaranteed to find all servers, quite
>> simply. Type the name.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:%23Dl9gR0pFHA.3064@.TK2MSFTNGP15.phx.gbl...
>> Hi John,
>> Thanks for the suggestion. However, I just checked, and all of these
>> DB servers have TCP/IP and Named Pipes enabled, and the "Hide server"
>> checkbox has not been checked.
>> Any other ideas?
>> Thanks,
>> Wade
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:EBECB381-89A7-456A-9016-921FDA02413B@.microsoft.com...
>> Hi
>> You may have hide server checked in the SQL Servers TCP/IP setting.
>> John
>> "Wade" wrote:
>> Hi all,
>> Observation: When I use the "Register SQL Server Wizard" I noticed
>> that
>> there are a number of SQL DB Servers that are not populating the
>> "Available
>> servers" listbox. Note: this also occurs when I programatically
>> generate a
>> list of database servers using the ODBC32 API's.
>> Question: How can I get this list to accurately reflect the databases
>> on my
>> network? Are there settings on the database server that neet to be
>> changed?
>> For example, my local development workstation has three databases on
>> it --
>> the "(local)" instance, and two additional instances ("\DEV" and
>> "\TEST").
>> The "(local)" instance shows up, but I am not seeing the two other
>> instances
>> "<COMPUTERNAME>\DEV" and "<COMPUTERNAME>\TEST".
>> Thoughts?
>> Thanks!
>> Wade
>>
>>
>>
>>
>

Tuesday, March 20, 2012

Backcolor property

Sunday, March 11, 2012

How can I resolve concurrency problems in SQL Server 2005?

Friday, February 24, 2012

Avoiding deadlock

Avoiding deadlock

Avoiding compilation

Thursday, February 16, 2012

average of selected members?

Monday, February 13, 2012

Average column in a matrix

Available MSSQL Servers during registration ...

Available MSSQL Servers during registration ...

Available MSSQL Servers during registration ...

Autonumber field

Blog Archive

About Me