Archive

Archive for the ‘Management Views and Functions’ Category

Using SQL DMVs – sys.dm_db_task_space_usage

June 8, 2011 Leave a comment

sys.dm_db_task_space_usage contains details for allocation/de-allocation pages for activity tasks for database.

It returns below columns:

1. session_id – session id

2. request_id – request id within the session, i.e. execution batch within a session

3. database_id – will always be 2, id of tempdb

4. user_objects_alloc_page_count – number pages allocated for the request

5. user_objects_dealloc_page_count – number of pages de-allocated for the request.

for other columns returned, refer BOL…

 

sys.dm_db_task_space_usage returns only active tasks:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      session_id, request_id,

            user_objects_alloc_page_count,

            user_objects_dealloc_page_count

FROM        sys.dm_db_task_space_usage

Partial Result Set:

session_id request_id  user_objects_alloc_page_count user_objects_dealloc_page_count

———- ———– —————————– ——————————-

1          0           0                             0

2          0           0                             0

3          0           0                             0

4          0           0                             0

5          0           0                             0

6          0           0                             0

7          0           1                             0

8          0           0                             0

 

When a query is executed which requires storage in tempdb, this DMV will return the number of pages allocated for the request.

For example, below query will require tempdb storage:

— Session ID = 56

 

USE AdventureWorks2008R2

 

SELECT      A.*

INTO        #TempTable

FROM        HumanResources.Employee A

CROSS APPLY HumanResources.Employee B

CROSS APPLY HumanResources.Employee C

You can check sys.dm_db_task_space_usage while the above query is running to check the pages allocated to the request.

SELECT      session_id, request_id,

            user_objects_alloc_page_count,

            user_objects_dealloc_page_count

FROM        sys.dm_db_task_space_usage

WHERE       session_id = 56

Result Set:

session_id request_id  user_objects_alloc_page_count user_objects_dealloc_page_count

———- ———– —————————– ——————————-

56         0           8193                          0

 

(1 row(s) affected)

Once the request is completed this row will no longer be available.

To check pages allocated to a session you can use sys.dm_db_session_space_usage.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Using SQL DMVs – sys.dm_db_session_space_usage

June 7, 2011 1 comment

While you can use sys.dm_db_file_space_usage to get the free space available in tempdb. you can use sys.dm_db_session_space_usage to get the used space by each session.

sys.dm_db_session_space_usage contains following columns:

1. session_id – session_id

2. database_id – will always be 2, id of tempdb

3. user_objects_alloc_page_count – number of pages allocated/reserved by the session

4. user_objects_dealloc_page_count – number of pages that are de-allocated by user objects for the session

for other columns returned by sys.dm_db_session_space_usage, refer BOL…

 

sys.dm_db_session_space_usage returns each row per session as below:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      session_id, user_objects_alloc_page_count,

            user_objects_dealloc_page_count

FROM        sys.dm_db_session_space_usage

Partial ResultSet:

session_id user_objects_alloc_page_count user_objects_dealloc_page_count

———- —————————– ——————————-

1          0                             0

2          0                             0

3          0                             0

4          0                             0

5          0                             0

6          0                             0

7          0                             0

8          0                             0

Now, if I create a new object I should be able to see the same in the view:

USE AdventureWorks2008R2

 

SELECT      *

INTO        #TempTable

FROM        HumanResources.Employee

 

SELECT      @@spid

Result Set:

(290 row(s) affected)

 

——

52

 

(1 row(s) affected)

Now, if we check the DMV for session id = 52, we should be able to see the usage for that session:

SELECT      session_id, user_objects_alloc_page_count,

            user_objects_dealloc_page_count

FROM        sys.dm_db_session_space_usage

WHERE       session_id = 52

Result Set:

session_id user_objects_alloc_page_count user_objects_dealloc_page_count

———- —————————– ——————————-

52         99                            90

 

(1 row(s) affected)

 

 

 

(1 row(s) affected)

After dropping the #TempTable the counts will change as below:

session_id user_objects_alloc_page_count user_objects_dealloc_page_count

———- —————————– ——————————-

52         99                            99

 

(1 row(s) affected)

 

 

 

(1 row(s) affected)

You can see here that de-allocated page count changed from 90 to 99.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Using SQL DMVs – sys.dm_db_file_space_usage

June 6, 2011 2 comments

You can use the sys.dm_db_file_space_usage dynamic management view to monitor the spaced used by tempdb for storing user/internal objects and version store.

sys.dm_db_file_space_usage has following columns:

1. database_id – will always be 2, database id of tempdb.

2. unallocated_extent_page_count – total number of free extents in the database file

3. version_store_reserved_page_count – pages allocated for version store

4. user_object_reserved_page_count – pages allocated for user objects, these are the permanent object you create explicitly in tempdb, system tables, temporary tables, table variables…

5. internal_object_reserved_page_count – pages allocated for internal objects

for details about other columns returned refer BOL.

 

Let’s see it in action:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      *

FROM        sys.dm_db_file_space_usage

 

SELECT      *

INTO        #TempTable2

FROM        AdventureWorks2008R2.HumanResources.Employee

 

SELECT      *

FROM        sys.dm_db_file_space_usage

Result Set:

database_id file_id unallocated_extent_page_count version_store_reserved_page_count user_object_reserved_page_count internal_object_reserved_page_count mixed_extent_page_count

———– ——- —————————– ——————————— ——————————- ———————————– ———————–

2           1       134832                        0                                 64                              0                                   136

 

(1 row(s) affected)

 

(290 row(s) affected)

 

database_id file_id unallocated_extent_page_count version_store_reserved_page_count user_object_reserved_page_count internal_object_reserved_page_count mixed_extent_page_count

———– ——- —————————– ——————————— ——————————- ———————————– ———————–

2           1       134824                        0                                 72                              0                                   136

 

(1 row(s) affected)

The user_object_reserved_page_count changed to 72 from 64 after creating a temporary table.

And you can also see that the free extents available decreased from 134832 to 134824.

 

If you need to check currently available free space in tempdb, you can use below query:

SELECT      (unallocated_extent_page_count * 1.0) / 128

            AS [FreeSpcae (MB)]

FROM        sys.dm_db_file_space_usage

Result Set:

FreeSpcae (MB)

—————————————

1053.312500

 

(1 row(s) affected)

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Recently Executed Queries – Using DMVs/DMFs

May 6, 2011 Leave a comment

To find the queries that were executed recently using the sys.dm_exec_query_stats DMV.

 

SELECT sql_handle, creation_time, last_execution_time
FROM   sys.dm_exec_query_stats
WHERE  last_execution_time > '2011-05-06 06:45:00.000'

 

The above statement will return the performance statistics for cached query plans. sys.dm_exec_query_stats contains the sql handle of the statement which was executed. To retrieve the actual query text, you need to use a DMF – sys.dm_exec_sql_text as follows:

 

sys.dm_exec_sql_text(sql_handle)

 

This functions takes the sql/plan hadle as input and returns the sql text. CROSS APPLY can be utilized add query text to the output:

 

SELECT        SQLTEXT.text, STATS.last_execution_time
FROM          sys.dm_exec_query_stats STATS
CROSS APPLY   sys.dm_exec_sql_text(STATS.sql_handle) AS SQLTEXT
WHERE         STATS.last_execution_time > '2011-05-06 19:15:00.000'
ORDER BY      STATS.last_execution_time DESC

 

image

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe