396
C
HAPTER
29
My favorite DMVs, and why
SELECT
'These procedures have not been executed in the past '
+ RTRIM(uptime) + ' minutes (the last time SQL started)',
sqlserver_start_time
FROM
AB_Utility.dbo.AB_Uptime();
SELECT
[name] = AB_Utility.dbo.AB_GetThreePartName
(p.[object_id], DB_ID()),
p.create_date,
p.modify_date
FROM
sys.procedures AS p
LEFT OUTER JOIN
sys.dm_exec_procedure_stats AS ps
ON
p.[object_id] = ps.[object_id]
WHERE
ps.[object_id] IS NULL
ORDER BY
p.[Name];
END
GO
EXEC dbo.sp_MS_marksystemobject N'dbo.sp_AB_GetUnusedProcedures';
GO
USE [your_database];

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
397
Some interesting applications of my favorite DMVs

USE [your_database];
GO
IF OBJECT_ID('dbo.AB_MeasureIndexUsefulness', N'P') IS NOT NULL
DROP PROCEDURE dbo.AB_MeasureIndexUsefulness;
GO
CREATE PROCEDURE dbo.AB_MeasureIndexUsefulness
AS
BEGIN
SET NOCOUNT ON;
SELECT 'These indexes have collected statistics for the past '
+ RTRIM(uptime) + ' minutes (the last time SQL started)',
sqlserver_start_time
FROM
AB_Utility.dbo.AB_Uptime();

WITH calced AS
(
SELECT
[object_id],
index_id,
reads = user_seeks + user_scans + user_lookups,
writes = user_updates,
perc = CONVERT(DECIMAL(10,2), user_updates * 100.0 /
(user_seeks + user_scans + user_lookups + user_updates))
FROM
sys.dm_db_index_usage_stats

C
HAPTER
29
My favorite DMVs, and why
AND c.index_id = i.index_id
WHERE
c.writes >= c.reads;
END
GO
Note that because the read and write metrics are per operation, not per row, a
DML
operation that affects 100 rows will only count as one
user update
in this view.
Finding inefficient queries
The table-valued function in listing 8 will return the top n queries, ordered in
descending order by longest average CPU time, longest average elapsed time, highest
average reads, highest logical reads, highest writes, or highest number of executions.
Because this one query does not rely on database-specific catalog views, it can be cre-
ated in the utility database and called from anywhere (passing database name, num-
ber of rows, and ordering preference). You can also add a
WHERE
clause to restrict the
result

set
to objects matching a certain naming pattern or queries that executed at
least n times.
USE AB_Utility;
GO

Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
399
Some interesting applications of my favorite DMVs
avg_physical_reads
= qs.total_physical_reads / qs.execution_count,
avg_writes
= qs.total_logical_writes / qs.execution_count,
avg_elapsed_time_milliseconds
= qs.total_elapsed_time / (1000 * qs.execution_count)
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.[sql_handle]) AS est
CROSS JOIN
AB_Utility.dbo.AB_Uptime() AS u
WHERE
est.[dbid] = DB_ID(@database_name)
) x
ORDER BY CASE @order_by
WHEN 'cpu time' THEN avg_cpu_time_milliseconds
WHEN 'logical reads' THEN avg_logical_reads
WHEN 'physical reads' THEN avg_physical_reads
WHEN 'writes' THEN avg_writes
WHEN 'elapsed time' THEN avg_elapsed_time_milliseconds
WHEN 'executions' THEN execution_count
END DESC,
exec_object
);
GO

CREATE

INDEX

DDL
if you wanted to follow through with the
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
400
C
HAPTER
29
My favorite DMVs, and why
suggestion. To use the function, you pass in the database name and the number of
rows you want to return.
USE AB_Utility;
GO
IF OBJECT_ID(N'dbo.AB_GetMissingIndexes', N'IF') IS NOT NULL
DROP FUNCTION dbo.AB_GetMissingIndexes
GO
CREATE FUNCTION dbo.AB_GetMissingIndexes
(
@database_name SYSNAME,
@number_of_rows INT
)
RETURNS TABLE
AS
RETURN
(
SELECT TOP (@number_of_rows)

) x
CROSS JOIN AB_Utility.dbo.AB_Uptime()
ORDER BY relative_benefit DESC
);
Listing 9 Finding missing indexes
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
401
DMV categories in SQL Server
GO
SELECT *
FROM AB_Utility.dbo.AB_GetMissingIndexes
(
'Org00010001',
50
);
DMV categories in SQL Server
Table 1 lists the
DMV
categories in both
SQL
Server 2005 and
SQL
Server 2008. Table 2
lists the new
DMV
categories in
SQL
Server 2008.


Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
402
C
HAPTER
29
My favorite DMVs, and why
Summary
Gone are the days of running
DBCC
commands and system stored procedures over
and over again, and keeping links to Profiler and Performance Monitor on every
machine’s desktop, when trying to peek into the usage characteristics of our
SQL
Server instances. I hope I have provided a glimpse of how much power we have been
given through
DMV
s and
DMF
s, and that I have inspired you to use them more often
when observing usage or troubleshooting performance issues.
About the author
Aaron Bertrand is the Senior Data Architect at One to One
Interactive, a global marketing agency headquartered in Boston,
Massachusetts. At One to One, Aaron is responsible for database
design and application architecture. Due to his commitment to
the community, shown through blogging at http:
//www.sql-
blog.com, peer-to-peer support on forums and newsgroups, and
speaking at user group meetings and code camps, he has been

Server database?”
It’s been many years since I’ve used Access, but I still remember the reason for
their concern. Access would continually add rows to the end of the table. If some,
or even all, of the rows were deleted from the table, Access wouldn’t reuse the
space. It kept adding rows to the end of the table and never backfilled the holes.
Compacting the Access database file would get rid of the holes.
Understanding how SQL Server
automatically reuses table space
I’m not an expert in Access, and I’m certainly not knocking it. I haven’t even used
Access since v2.0 back in the 1990s. This behavior may have changed since then, or
perhaps I misremember, but suffice it to say that with
SQL
Server this is not an
issue. But don’t take my word for it. Let’s consider an example to prove the point.
To set up the example, let’s create a table with three columns and then populate
it with test data. The
T-SQL
code for doing this is shown in listing 1.
USE tempdb ;
GO
--create a test table
CREATE TABLE dbo.Test
(
col1 INT
,col2 CHAR(25)
,col3 VARCHAR(4000)
) ;
--create some test data
DECLARE @cnt INT ;
SET @cnt = 0 ;

INSERT
statement. The
INSERT
statement has
been placed inside a
WHILE
loop and is executed 1,000 times. Note that the last col-
umn in the table will vary in length from zero to a maximum of 4,000. Statistically, it
should average around 1,000 characters.
Let’s view the contents of the table to make sure we have what we think we have.
We can do this by running a
SELECT
statement to retrieve the data, as shown in
listing 2. The results of the query are shown in figure 1.
--view the table
SELECT
*
FROM
dbo.Test ;
To examine the amount of space the table consumes, we’ll use a Dynamic Manage-
ment View (
DMV
) called
sys.dm_db_index_physical_stats
.
DMV
s were first intro-
duced in
SQL
Server 2005 and have been continued in

(that
is, the allocation unit that stores the table rows in our example). The avg_page_
space_used_in_percent column reports the average percentage of space used in all
data pages in the
IN_ROW_DATA
allocation type. The record_count intuitively contains
the number of rows contained in the table.
--check the size of the table
SELECT
alloc_unit_type_desc
,page_count
,avg_page_space_used_in_percent
,record_count
FROM
sys.dm_db_index_physical_stats(
DB_ID()
,OBJECT_ID(N'dbo.Test')
,NULL
,NULL
,'Detailed') ;
Figure 2 displays the results of the
DMV
query. As you can see on my test system, 158
data pages are used to store the 1,000 rows and each data page is, on average, 82.1 per-
cent full.
To continue with the example, let’s delete half of the rows in our dbo.Test table and
see what happens to the space used by the table. The
T-SQL
script in listing 4 uses the
modulo operator, represented by the percent sign in

row

#2
,
test

row

#4
, and so on, for a total of 500 rows.
Now that half of the rows in the table have been deleted, let’s look at the space the
table consumes by running the
DMV
query from listing 3 again.
Figure 4 shows that the number of pages used to store the table’s data remains con-
stant but the percentage used of each page changes. It is cut in half, from 82.1 percent
to 42.1 percent. Also notice that the number of rows reported in the record_count
column has been cut in half, as expected.
So let’s add some new rows to the table to prove that
SQL
Server will automatically
reuse the newly freed space, thus filling in the holes, to go back to our Access compari-
son. Using the
T-SQL
script found in listing 5, we can quickly add 500 rows of data to
the dbo.Test table. This script is similar to the one first used to populate the table with
sample data. It inserts one row at a time in a
WHILE
loop. To help differentiate the new
rows from the existing rows, I change the insert statement so that the third column is

sys.dm_db_index_physical_stats

DMV
to see the space
used by the table. Figure 5 displays the result of the query.
Notice that the table is still using 158 data pages, but the average percent used for
each page has increased to 62.9 percent from the prior value of 42.1 percent. The
results confirm our expectations—the holes were indeed reused by
SQL
Server. No
additional pages were allocated; the free space available was used.
At this point it is important to understand that, although
SQL
Server can and will
automatically reuse space that was previously used by one or more rows, it will do so
only under very specific circumstances. This behavior is most readily demonstrated
through the use of a heap (a table without a clustered index) in which there is no pre-
defined order of the rows. The behavior can also be observed in clustered tables when
the newly inserted rows have key values that allow them to be inserted into the holes
left by prior deletions. You shouldn’t expect all newly inserted rows to fit nicely in the
holes of a clustered table. The point is that
SQL
Server will reuse space as appropriate.
To clean up after this example, let’s execute one final statement to drop the
dbo.Test table. Listing 6 displays the
DROP
statement.
--clean up
DROP TABLE dbo.Test ;
Recognizing when SQL Server does not reclaim space

,col3 VARCHAR(4000)
) ;
--create some test data
DECLARE @cnt INT ;
SET @cnt = 0 ;
WHILE @cnt < 1000
BEGIN
SELECT
@cnt = @cnt + 1 ;
INSERT
dbo.Test2 ( col1,col2,col3)
VALUES (
@cnt
,'test row # ' + CAST(@cnt AS VARCHAR(10))
,REPLICATE('A', 4000)
) ;
END
Figure 6 shows the results from the
SELECT
statement. This table has three columns of
data, an integer in the first column, a character string in the second column that can
contain up to 25 characters, and a variable length character string in the final column
that contains 4,000 characters.
Using the query shown in listing 3, we can see how much space our newly created
dbo.Test2 table is consuming. Figure 7 shows the results. The newly created table
takes up 500 data pages to store the 1,000 rows of data. Each data page is, on average,
99.9 percent full.
Now to set up our test scenario, let’s drop the third column, the one that consumes
the most space. Listing 8 contains the
ALTER

Looking at the results in figure 8, we can see that we get the same results as
before—500 data pages, storing 1,000 rows, each 99.9 percent full, and this after drop-
ping the column that consumed the most space.
Why is this? When a table is altered to drop a column,
SQL
Server does not remove the
column data from the data pages. Instead it updates the metadata in the system tables
so that when queried, it appears as if the column no longer exists. The data is still
present in the data pages, but it’s not returned as a part of a result set. Thus, the space
cannot be reused initially.
So let’s add some additional rows to the table and see what happens. The script
shown in listing 9 inserts 500 additional rows into the dbo.Test2 table. Notice that this
script inserts only two columns of data because we’ve dropped the third column, col3.
--insert additional rows
DECLARE @cnt INT ;
SET @cnt = 0 ;
WHILE @cnt < 500
BEGIN
SELECT
@cnt = @cnt + 1 ;
INSERT
dbo.Test2 ( col1,col2 )
VALUES (
@cnt
,'test row # ' + CAST(@cnt AS VARCHAR(10))
) ;
END
Listing 8 Dropping a
varchar
column in the dbo.Test table

cally reuse space once consumed by dropped columns.
Fortunately, this space is not lost forever; we can reclaim this space by issuing a
DBCC
command. The
DBCC

CLEANTABLE
command allows us to specify a database and
table, and it will free up any space once consumed by dropped variable length charac-
ter columns.
To reclaim the space in our dbo.Test2 table, run the
T-SQL
command found in
listing 10.
--reclaim the space from the table
DBCC CLEANTABLE('tempdb', 'dbo.Test2') ;
If it succeeds, you should receive a message similar to the following as an output mes-
sage in the query window.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
DBCC

CLEANTABLE
reclaims space from variable length columns that no longer exist as
part of the table definition. In this context a variable length column can be one of the
following data types:
varchar
,
nvarchar
,

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
411
Summary
Figure 10 shows the results. Notice that the number of data pages consumed by the
table did not decrease; however, the average space used as a percentage decreased
dramatically to 1.4 percent from 99.7 percent.
To prove that the space is truly available for reuse, let’s add another 3,000 rows to the
table by altering and running listing 9.
Now checking the used space by again running the
DMV
query in listing 3, we see
that the number of pages used to make the table did not increase. Notice, though,
that the average page space used as a percentage did increase to 4.2 percent from 1.4
percent. Figure 11 shows the results.
Summary
When rows are deleted from a table without a clustered index (an object known as a
heap),
SQL
Server can readily reuse the space that was once consumed by deleted
rows. The same may hold true for clustered tables, as long as the newly inserted rows
have the required key values that would allow them be placed in a hole created by a
deleted row.
There are certain circumstances, in which deleted space is not immediately released
for reuse; in particular when variable length character columns are dropped from a
table. By employing the
DBCC

CLEANTABLE
command we can reclaim the space once
consumed by the dropped columns and make better use of the disk resources at hand.