Databases Reference
In-Depth Information
insert t1 (val1, val2) values ('X','Y')
go
-- Repeat this insert statement as often as you want to cause splits
insert t1 (val1, val2) (select val1, val2 from t1)
go
After executing the script you'll be able to see just how
frequently page splits occur as a database begins to be pop-
ulated, especially with a table design that was chosen to exag-
gerate the frequency of page splits. An example of one of the
page split events captured is shown in Figure 13-15.
Counting the Number of Locks Acquired
per Object
The i nal example you'll implement uses the histogram target to aggregate the number of locks
acquired for objects in a user database as queries are executed. You might want to do this to help
i nd locking hot spots in a database, but we're using it here because it's an easy way to demonstrate
how the histogram target, and in this example the event i lters, work.
FIGURE 13-15
The
lock_acquired
event provides all the information needed for this example, so you don't need
to use any global actions or additional event i elds. Instead, because SQL Server itself has a lot of
background locking activity from system processes occurring, you'll use a i lter to exclude the lock-
ing activity you're not interested in. The coni guration for this is shown in Figure 13-16.
FIGURE 13-16
If you are wondering where the i lter values came from, the answer is a combination of known
requirements and trial and error. One of the benei ts of deploying an Extended Events session is that
it's easy to stop the session, reconi gure it, and restart it if it's capturing too many or too few events
(see the
object_id
i lter setting shown in the screenshot). The
database_id
i eld was populated by
executing the query
select
db_id('PSDemo')
.
With the i lter coni gured, next you add the histogram target (see Figure 13-17). This requires con-
i guring some properties but you can use values selected from drop-down lists. You can think of the
“Base buckets on” option as being like the columns in a
group
by
clause of a
select
statement.
Search WWH ::
Custom Search