SQL SERVER – How to See Active SQL Server Connections For Database

Another question received via email -

“How do I I know which user is connected to my database with how many connection?”

Here is the script which will give us answer to the question.

SELECT DB_NAME(dbid) AS DBName,
COUNT(dbid) AS NumberOfConnections,
loginame
FROM    sys.sysprocesses
GROUP BY dbid, loginame
ORDER BY DB_NAME(dbid)

Here is the resultset:

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

About these ads

SQL SERVER – Finding Last Backup Time for All Database – Last Full, Differential and Log Backup – Optimized

Four years ago, I wrote a blog post SQL SERVER – Finding Last Backup Time for All Database. It has been a very popular script. SQL Expert Sravani has posted a fantastic script which also displays last full, differential and log backup for the database – I have blogged about it here SQL SERVER – Finding Last Backup Time for All Database – Last Full, Differential and Log Backup.

Well, in technology, there is always space for improvement and we should always be ready to learn and progress. SQL Expert TheSQLGuru came up with further optimized script which also lists all the details which earlier blog post listed, and the script is very quick to execute. Here is the script.

SELECT d.name AS 'DATABASE_Name',
MAX(CASE WHEN bu.TYPE = 'D' THEN bu.LastBackupDate END) AS 'Full DB Backup Status',
MAX(CASE WHEN bu.TYPE = 'I' THEN bu.LastBackupDate END) AS 'Differential DB Backup Status',
MAX(CASE WHEN bu.TYPE = 'L' THEN bu.LastBackupDate END) AS 'Transaction DB Backup Status',
CASE d.recovery_model WHEN 1 THEN 'Full' WHEN 2 THEN 'Bulk Logged' WHEN 3 THEN 'Simple' END RecoveryModel
FROM MASTER.sys.databases d
LEFT OUTER JOIN (SELECT database_name, TYPE, MAX(backup_start_date) AS LastBackupDate
FROM msdb.dbo.backupset
GROUP BY database_name, TYPE) AS bu ON d.name = bu.database_name
GROUP BY d.Name, d.recovery_model

Thanks TheSQLGuru for excellent script, you win USD 20 Amazon Gift Card or INR 1000 Flipkart Gift Voucher. I have already sent you emails with details.

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

SQL SERVER – sys.dm_xe_map_values – Reasons for Statement Recompilation

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.

Click to Download Scripts

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

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

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.

Click to Download Scripts

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

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

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)

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

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.

Click to Download Scripts

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

 

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

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)