extent locks

hi
Im getting exclusive locks on my table and ext is the resource locked......
can anyone explain me..why do we get ext locks on tables.
regardjust means SQL server has identified a group of 8 pages(extent) for update insert(most likely upadte and insert), create or drop destined for an exclusive lock. More efficient than locking an individual page of a contiguous set of 8 pages|||They are most likely Intent locks and essentially help to prevent things
like deadlocks.
--
Andrew J. Kelly SQL MVP
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:24647640-DE56-4A51-BAE5-74088CD31DE1@.microsoft.com...
> hi,
> Im getting exclusive locks on my table and ext is the resource
locked.......
> can anyone explain me..why do we get ext locks on tables..
> regards
>

extent locks

hi,
Im getting exclusive locks on my table and ext is the resource locked......
.
can anyone explain me..why do we get ext locks on tables..
regardsjust means SQL server has identified a group of 8 pages(extent) for update i
nsert(most likely upadte and insert), create or drop destined for an exclusi
ve lock. More efficient than locking an individual page of a contiguous set
of 8 pages.|||They are most likely Intent locks and essentially help to prevent things
like deadlocks.
Andrew J. Kelly SQL MVP
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:24647640-DE56-4A51-BAE5-74088CD31DE1@.microsoft.com...
> hi,
> Im getting exclusive locks on my table and ext is the resource
locked.......
> can anyone explain me..why do we get ext locks on tables..
> regards
>

Ext. SPs

I need to be able to create my own MSSQ UDF to return the current system
date, offset by a configurable number of minutes, depending on a value in a
table. The reason I am trying to do this is, I want to be able to, for
testing purposes, fake the system into thinking that time has elapsed.
Changing the time on the computer is not an option.

I would like to call the UDF MyGetDate and to replace all code occurrence of
getdate() in the database with this call. This includes column default value
constraints and stored procedures.

The problem is that MSSQL does not allow the function GETDATE with a UDF. I
thought to try an fake it out by having the UDF call a SP, which in turn
called GETDATE. When I did this, I got the error 'Only functions and
extended stored procedures can be executed from within a function.'

I guess I can go down the road to try and learn how to write an extended
stored procedure to return the current time, but I imagine that there is a
learning curve here.

I realize that all COLUMN default CONSTRAINT with GETDATE could be handled
by create ADD AND UPDATE TRIGGERS that populate thisIt's a kludge but you can create a UDF that reads a value from a table.
Then schedule a Sql Agent Job to run every minute, updating that
table's value.|||Chad (chad.dokmanovich@.unisys.com) writes:
> I need to be able to create my own MSSQ UDF to return the current system
> date, offset by a configurable number of minutes, depending on a value
> in a table. The reason I am trying to do this is, I want to be able to,
> for testing purposes, fake the system into thinking that time has
> elapsed. Changing the time on the computer is not an option.
> I would like to call the UDF MyGetDate and to replace all code
> occurrence of getdate() in the database with this call. This includes
> column default value constraints and stored procedures.
> The problem is that MSSQL does not allow the function GETDATE with a
> UDF. I thought to try an fake it out by having the UDF call a SP, which
> in turn called GETDATE. When I did this, I got the error 'Only functions
> and extended stored procedures can be executed from within a function.'
> I guess I can go down the road to try and learn how to write an extended
> stored procedure to return the current time, but I imagine that there is a
> learning curve here.

Using a UDF in all sorts of constraints, could have performance issues,
and if that UDF calls an extended procedure that does not make things
better.

You could save the show with:

CREATE FUNCTION kalle(@.d datetime) RETURNS datetime AS
BEGIN
RETURN dateadd(DAY, 12, @.d)
END
go
select dbo.kalle(getdate())

Yes, that will be somewhat bulkier, but it should get the job one.

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

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

Ext Locks to tempdb db

Running sp_lock to a spid that runs a sproc and seems to have a lot of 'X'
EXT type of lock in the tempdb database . The sproc does not seem to be
doing anything with temp tables or table variables,etc.. Theres some
aggregate functions only in a simple select statement
Any idea why the EXT type of lock ? Thanks
Aggregations often use tempdb and the nolock would have no bearing on that
aspect of it.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OTWI8WZ3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Running sp_lock to a spid that runs a sproc and seems to have a lot of 'X'
> EXT type of lock in the tempdb database . The sproc does not seem to be
> doing anything with temp tables or table variables,etc.. Theres some
> aggregate functions only in a simple select statement
> Any idea why the EXT type of lock ? Thanks
>
|||In addition to Andrew's answer:
These tempdb EXT locks are used internally by SQL Server to allocate or
deallocate pages and extents. The pages and extents are used as temporary
storage to process the query.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eNAu0Wa3EHA.924@.TK2MSFTNGP14.phx.gbl...
> Aggregations often use tempdb and the nolock would have no bearing on that
> aspect of it.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OTWI8WZ3EHA.3416@.TK2MSFTNGP09.phx.gbl...
>
|||Hi Hassan,
I highly recommend reviewing this article
FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/default...b;en-us;328551
In my opinion, every server should implement the following suggestions
on how to reduce tempdb contention.
Increase the Number of Tempdb Data Files with Equal Sizing
If the data file size of tempdb is 5 GB, and the Log file size is 5 GB,
the recommendation is to increase the single datafile to 10 (each of 500
MB to maintain equal sizing), and leave the log file as is. Having the
different data files on separate disks would be good; however, this is
not required and they can co-exist on the same disk.
The optimal number of tempdb data files depends on the degree of
contention seen in tempdb. As a starting point, you can configure the
tempdb to be at least equal to the number of processors assigned for SQL
Server. For higher end systems (for example, 16 or 32 proc), the
starting number could be 10. If the contention is not reduced, you may
have to increase the number of data files more.
The equal sizing of data files is critical because the proportional fill
algorithm is based on the size of the files. If data files are created
with unequal sizes, the proportional fill algorithm tries to use the
largest file more for GAM allocations instead of spreading the
allocations between all the files, thereby defeating the purpose of
creating multiple data files.
The auto-grow of tempdb data files can also interfere with the
proportional fill algorithm. Therefore, it may be a good idea to turn
off the auto-grow feature for the tempdb data files. If the auto-grow
option is turned off, you must make sure to create the data files so
that they are large enough to prevent the server from experiencing a
lack of disk space with tempdb.
How Increasing the Number of Tempdb Data Files with Equal Sizing Reduces
Contention
Here is a list of how increasing the number of tempdb data files with
equal sizing reduces contention:
With one data file for the tempdb, you only have one GAM page, and one
SGAM page for each 4 GB of space.
Increasing the number of data files with the same sizes for tempdb
effectively creates one or more GAM and SGAM pages for each data file.
The allocation algorithm for GAM gives out one extent at a time (eight
contiguous pages) from the number of files in a round robin fashion
while honoring the proportional fill. Therefore, if you have 10 equal
sized files, the first allocation is from File1, the second from File2,
the third from File3, and so on.
The resource contention of the PFS page is reduced because eight pages
are marked as FULL at a time because GAM is allocating the pages.
Yih-Yoon Lee
Hassan wrote:
> Running sp_lock to a spid that runs a sproc and seems to have a lot of 'X'
> EXT type of lock in the tempdb database . The sproc does not seem to be
> doing anything with temp tables or table variables,etc.. Theres some
> aggregate functions only in a simple select statement
> Any idea why the EXT type of lock ? Thanks
>

Ext Locks to tempdb db

Running sp_lock to a spid that runs a sproc and seems to have a lot of 'X'
EXT type of lock in the tempdb database . The sproc does not seem to be
doing anything with temp tables or table variables,etc.. Theres some
aggregate functions only in a simple select statement
Any idea why the EXT type of lock ? ThanksAggregations often use tempdb and the nolock would have no bearing on that
aspect of it.
--
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OTWI8WZ3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Running sp_lock to a spid that runs a sproc and seems to have a lot of 'X'
> EXT type of lock in the tempdb database . The sproc does not seem to be
> doing anything with temp tables or table variables,etc.. Theres some
> aggregate functions only in a simple select statement
> Any idea why the EXT type of lock ? Thanks
>|||In addition to Andrew's answer:
These tempdb EXT locks are used internally by SQL Server to allocate or
deallocate pages and extents. The pages and extents are used as temporary
storage to process the query.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eNAu0Wa3EHA.924@.TK2MSFTNGP14.phx.gbl...
> Aggregations often use tempdb and the nolock would have no bearing on that
> aspect of it.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OTWI8WZ3EHA.3416@.TK2MSFTNGP09.phx.gbl...
>> Running sp_lock to a spid that runs a sproc and seems to have a lot of
>> 'X'
>> EXT type of lock in the tempdb database . The sproc does not seem to be
>> doing anything with temp tables or table variables,etc.. Theres some
>> aggregate functions only in a simple select statement
>> Any idea why the EXT type of lock ? Thanks
>>
>|||Hi Hassan,
I highly recommend reviewing this article
FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
In my opinion, every server should implement the following suggestions
on how to reduce tempdb contention.
Increase the Number of Tempdb Data Files with Equal Sizing
If the data file size of tempdb is 5 GB, and the Log file size is 5 GB,
the recommendation is to increase the single datafile to 10 (each of 500
MB to maintain equal sizing), and leave the log file as is. Having the
different data files on separate disks would be good; however, this is
not required and they can co-exist on the same disk.
The optimal number of tempdb data files depends on the degree of
contention seen in tempdb. As a starting point, you can configure the
tempdb to be at least equal to the number of processors assigned for SQL
Server. For higher end systems (for example, 16 or 32 proc), the
starting number could be 10. If the contention is not reduced, you may
have to increase the number of data files more.
The equal sizing of data files is critical because the proportional fill
algorithm is based on the size of the files. If data files are created
with unequal sizes, the proportional fill algorithm tries to use the
largest file more for GAM allocations instead of spreading the
allocations between all the files, thereby defeating the purpose of
creating multiple data files.
The auto-grow of tempdb data files can also interfere with the
proportional fill algorithm. Therefore, it may be a good idea to turn
off the auto-grow feature for the tempdb data files. If the auto-grow
option is turned off, you must make sure to create the data files so
that they are large enough to prevent the server from experiencing a
lack of disk space with tempdb.
How Increasing the Number of Tempdb Data Files with Equal Sizing Reduces
Contention
Here is a list of how increasing the number of tempdb data files with
equal sizing reduces contention:
? With one data file for the tempdb, you only have one GAM page, and one
SGAM page for each 4 GB of space.
? Increasing the number of data files with the same sizes for tempdb
effectively creates one or more GAM and SGAM pages for each data file.
? The allocation algorithm for GAM gives out one extent at a time (eight
contiguous pages) from the number of files in a round robin fashion
while honoring the proportional fill. Therefore, if you have 10 equal
sized files, the first allocation is from File1, the second from File2,
the third from File3, and so on.
? The resource contention of the PFS page is reduced because eight pages
are marked as FULL at a time because GAM is allocating the pages.
Yih-Yoon Lee
Hassan wrote:
> Running sp_lock to a spid that runs a sproc and seems to have a lot of 'X'
> EXT type of lock in the tempdb database . The sproc does not seem to be
> doing anything with temp tables or table variables,etc.. Theres some
> aggregate functions only in a simple select statement
> Any idea why the EXT type of lock ? Thanks
>