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
>