Feeds:
Posts
Comments

Archive for the ‘SQL DMV’ Category

SQL SERVER – sys.dm_xe_map_values – Reasons for Statement Recompilation

Posted in PostADay, SQL, SQL Authority, SQL DMV, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on May 5, 2013 | 1 Comment »

Sometime I feel I know a lot about SQL Server and very next moment, I realize that honestly I do not know much about this product. Earlier today, I had similar moments. I was playing with few DMVs and suddenly I ended up on the DMV sys.dm_xe_map_values. There are only four columns and one of the columns is a GUID.

The reason I ended up on this DMV was because I was asked a question what are the different reasons any statement can be recompiled. I knew few of the reasons why would any statement recompile but I was not aware of all the reasons for any statement recompilation. After doing some search on the internet and my older archived I figured out the reasons for Statement Recompilation using sails. dm_xe_map_values.

If you have collected trace using either SQL Server Profiler or an Extended Events session, you can capture SP:Recompile event in your trace. When you look at the event it displays the reason for recompiling the statement. However, when you run following statement in SQL Server Management Studio, you can find all the possible reasons for statement recompilation.

SELECT dxmv.name,
dxmv.map_key,
dxmv.map_value
FROM sys.dm_xe_map_values AS dxmv
WHERE dxmv.name = N'statement_recompile_cause'
ORDER BY dxmv.map_key;

When I ran this query, I got following 14 different reasons for statement recompilation. I personally was not aware of all the 14 and it was interesting learning for me.

map_key map_value
1 Schema changed
2 Statistics changed
3 Deferred compile
4 Set option change
5 Temp table changed
6 Remote rowset changed
7 For browse permissions changed
8 Query notification environment changed
9 PartitionView changed
10 Cursor options changed
11 Option (recompile) requested
12 Parameterized plan flushed
13 Test plan linearization
14 Plan affecting database version changed

It will be interesting to build a test case for each of the compilation reason, as it will give a great learning experience.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

Read Full Post »

SQL SERVER – Identify Last User Access of Table using T-SQL Script

Posted in PostADay, SQL, SQL Authority, SQL DMV, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on March 21, 2013 | 2 Comments »

During the TechEd India 2013 presentations I received a question how to identify when any table is accessed by any of the user. It seems people would like to know if the table was used in any part of query by any user. The best possible solution is to create database audit task and watch the database table access. However, sometime we all want shortcut even thought it is not accurate. Here is how you can use DMV to do so. However, please note that this DMV will get reset when database services or servers are restart. Let me know if you think I should modify this DMV and have some better alternatives.

SELECT DB_NAME(ius.[database_id]) AS [Database],
OBJECT_NAME(ius.[object_id]) AS [TableName],
MAX(ius.[last_user_lookup]) AS [last_user_lookup],
MAX(ius.[last_user_scan]) AS [last_user_scan],
MAX(ius.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS ius
WHERE ius.[database_id] = DB_ID()
AND ius.[object_id] = OBJECT_ID('YourTableName')
GROUP BY ius.[database_id], ius.[object_id];

Remember to change your database context to your current database as well make sure that you insert your table name in the object_id condition.

DMV sys.dm_db_index_usage_stats has columns related to last user lookup, last user scan and last user seek. Any table which is accessed will either go for seek or scan. We can watch these columns and figure out when the table was used last. Which ever value among the last_user_lookup, last_user_scan and last_user_seek is latest is the last user access of the table.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Read Full Post »

SQL SERVER – Find Referenced or Referencing Object in SQL Server using sys.sql_expression_dependencies

Posted in PostADay, SQL, SQL Authority, SQL DMV, SQL Query, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology on December 2, 2012 | 6 Comments »

A very common question which I often receive are:

How do I find all the tables used in a particular stored procedure?

How do I know which stored procedures are using a particular table?

Both are valid question but before we see the answer of this question – let us understand two small concepts – Referenced and Referencing.

Here is the sample stored procedure.

CREATE PROCEDURE mySP
AS
SELECT *
FROM Sales.Customer
GO

Reference: The table Sales.Customer is the reference object as it is being referenced in the stored procedure mySP.

Referencing: The stored procedure mySP is the referencing object as it is referencing Sales.Customer table.

http://www.pinaldave.com/bimg/references.jpg

Now we know what is referencing and referenced object. Let us run following queries. I am using AdventureWorks2012 as a sample database. If you do not have SQL Server 2012 here is the way to get SQL Server 2012 AdventureWorks database.

Find Referecing Objects of a particular object

Here we are finding all the objects which are using table Customer in their object definitions (regardless of the schema).

USE AdventureWorks
GO
SELECT
referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID),
referencing_object_name = o.name,
referencing_object_type_desc = o.type_desc,
referenced_schema_name,
referenced_object_name = referenced_entity_name,
referenced_object_type_desc = o1.type_desc,
referenced_server_name, referenced_database_name
--,sed.* -- Uncomment for all the columns
FROM
sys.sql_expression_dependencies sed
INNER JOIN
sys.objects o ON sed.referencing_id = o.[object_id]
LEFT OUTER JOIN
sys.objects o1 ON sed.referenced_id = o1.[object_id]
WHERE
referenced_entity_name = 'Customer'

http://www.pinaldave.com/bimg/references1.jpg

The above query will return all the objects which are referencing the table Customer.

Find Referenced Objects of a particular object

Here we are finding all the objects which are used in the view table vIndividualCustomer.

USE AdventureWorks
GO
SELECT
referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID),
referencing_object_name = o.name,
referencing_object_type_desc = o.type_desc,
referenced_schema_name,
referenced_object_name = referenced_entity_name,
referenced_object_type_desc = o1.type_desc,
referenced_server_name, referenced_database_name
--,sed.* -- Uncomment for all the columns
FROM
sys.sql_expression_dependencies sed
INNER JOIN
sys.objects o ON sed.referencing_id = o.[object_id]
LEFT OUTER JOIN
sys.objects o1 ON sed.referenced_id = o1.[object_id]
WHERE
o.name = 'vIndividualCustomer'

http://www.pinaldave.com/bimg/references2.jpg

The above query will return all the objects which are referencing the table Customer.

I am just glad to write above query. There are more to write to this subject. In future blog post I will write more in depth about other DMV which also aids in finding referenced data.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Read Full Post »

SQL SERVER – 2012 – List All The Column With Specific Data Types in Database

Posted in PostADay, SQL, SQL Authority, SQL DMV, SQL Query, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology on October 13, 2012 | 8 Comments »

5 years ago I wrote script SQL SERVER – 2005 – List All The Column With Specific Data Types, when I read it again, it is very much relevant and I liked it. This is one of the script which every developer would like to keep it handy. I have upgraded the script bit more. I have included few additional information which I believe I should have added from the beginning. It is difficult to visualize the final script when we are writing it first time. I use every script which I write on this blog, the matter of the fact, I write only those scripts here which I was using at that time. It is quite possible that as time passes by my needs are changing and I change my script. Here is the updated script of this subject. If there are any user data types, it will list the same as well.

SELECT s.name AS 'schema', ts.name AS TableName,
c.name AS column_name, c.column_id,
SCHEMA_NAME(t.schema_id) AS DatatypeSchema,
t.name AS Datatypename
,t.is_user_defined, t.is_assembly_type
,c.is_nullable, c.max_length, c.PRECISION,
c.scale
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id
INNER JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.schemas s ON s.schema_id = ts.schema_id
ORDER BY s.name, ts.name, c.column_id

I would be very interested to see your script which lists all the columns of the database with data types. If I am missing something in my script, I will modify it based on your comment. This way this page will be a good bookmark for the future for all of us.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Read Full Post »

SQL SERVER – DMV sys.dm_exec_describe_first_result_set_for_object – Describes the First Result Metadata for the Module

Posted in PostADay, SQL, SQL Authority, SQL DMV, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on April 7, 2012 | 4 Comments »

Here is another interesting follow up blog post of SQL SERVER – sp_describe_first_result_set New System Stored Procedure in SQL Server 2012. While I was writing earlier blog post I had come across DMV sys.dm_exec_describe_first_result_set_for_object as well. I found that SQL Server 2012 is providing all this quick and new features which quite often we miss  to learn it and when in future someone demonstrates the same to us, we express our surprise on the subject.

DMV sys.dm_exec_describe_first_result_set_for_object returns result set which describes the columns used in the stored procedure. Here is the quick example. Let us first create stored procedure.

USE [AdventureWorks]
GO
ALTER PROCEDURE [dbo].[CompSP]
AS
SELECT [DepartmentID] id
,[Name] n
,[GroupName] gn
FROM [HumanResources].[Department]
GO

Now let us run following two DMV which gives us meta data description of the stored procedure passed as a parameter.

Option1: Pass second parameter @include_browse_information as a 0.

SELECT *
FROM sys.dm_exec_describe_first_result_set_for_object (
OBJECT_ID('[dbo].[CompSP]'),0) AS Table1
GO

Option2: Pass second parameter @include_browse_information as a 1.

SELECT *
FROM sys.dm_exec_describe_first_result_set_for_object (
OBJECT_ID('[dbo].[CompSP]'),1) AS Table1
GO

Here is the result of Option1 and Option2.

If you see the result, there is absolutely no difference between the results. Both of the resultset are returning column names which are aliased in the stored procedure. Let us scroll on the right side and you will notice that there is clear difference in some columns.

You will see in second resultset source_database, Source_schema as well few other columns are reporting original table instead of NULL values. When @include_browse_information result is set to 1 it will provide the columns details of the underlying table. I have just discovered this DMV, I have yet to use it in production code and find out where exactly I will use this DMV. Do you have any idea? Does any thing comes up to your mind where this DMV can be helpful.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Read Full Post »

Older Posts »