Archive

Posts Tagged ‘system views’

Using Catalog Views – sys.sql_expression_dependencies

13.06.2011 No comments

sys.sql_expression_dependencies catalog view contains a row for each dependent object which references another object in the database. For example, if a view uses a tables in it’s definition, then the view depends on the table.

For example, the view HumanResources.vEmployee in AdvendureWorks2008R2 database contains a reference to following tables:

FROM [HumanResources].[Employee] e

INNER JOIN [Person].[Person] p

INNER JOIN [Person].[BusinessEntityAddress] bea

INNER JOIN [Person].[Address] a

INNER JOIN [Person].[StateProvince] sp

INNER JOIN [Person].[CountryRegion] cr

LEFT OUTER JOIN [Person].[PersonPhone] pp

LEFT OUTER JOIN [Person].[PhoneNumberType] pnt

LEFT OUTER JOIN [Person].[EmailAddress] ea

 

These dependencies can be retrieved from sys.sql_expression_dependencies as below:

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

 

USE AdventureWorks2008R2

 

SELECT      referenced_schema_name, referenced_entity_name,

            COL_NAME(referenced_id, referenced_minor_id)

FROM        sys.sql_expression_dependencies

WHERE       referencing_id = OBJECT_ID(N'HumanResources.vEmployee')

Result Set:

referenced_schema_name     referenced_entity_name           

———————-     ———————-     ———————

Person                     Address                    NULL

Person                     BusinessEntityAddress      NULL

Person                     CountryRegion              NULL

Person                     EmailAddress               NULL

HumanResources             Employee                   NULL

Person                     Person                     NULL

Person                     PersonPhone                NULL

Person                     PhoneNumberType            NULL

Person                     StateProvince              NULL

 

(9 row(s) affected)

You can find more information on sys.sql_expression_dependencies on BOL.

Hope This Helps!

Vishal

Using SQL DMVs – sys.dm_db_task_space_usage

08.06.2011 No comments

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

Using SQL DMVs – sys.dm_db_session_space_usage

07.06.2011 No comments

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

Recently Executed Queries – Using DMVs/DMFs

06.05.2011 No comments

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

SQL Server has been running for …

04.05.2011 1 comment

Recently on one of our production servers, which is a two-node Active/Passive cluster.  A failover had occurred. I wanted to find the exact time when this happened. You can get this information from SQL Server Log.

Another quick way is to check the creation time of  “tempdb” database. This will be same as SQL Server Service start time since “tempdb” is recreated each time SQL Server starts.

You can check the creation time using:

SELECT create_date

FROM   sys.databases

WHERE  name = 'tempdb'

 

You can also format the output using:

 

SELECT (DATEDIFF(DAY, create_date, GETDATE()))

       AS [Days],

       ((DATEDIFF(MINUTE, create_date, GETDATE())/60)%24)

       AS [Hours],

       DATEDIFF(MINUTE, create_date, GETDATE())%60

       AS [Minutes]

FROM   sys.databases

WHERE  name = 'tempdb'

 

This returns below on my local system:

 

image

 

Hope This Helps!

Vishal