SQL SERVER – Get the List of Object Dependencies – sp_depends and information_schema.routines and sys.dm_sql_referencing_entities

Recently, I read a question on my friend Ritesh Shah‘s SQL site regarding the following: sp_depends does not give appropriate results whereas information_schema.routines does give proper answer.

I have quite often seen that information_schema.routines gives proper dependency relationship where assp_depends returns an incorrect answer. However, as per book online sp_depends will be deprecated, and instead, sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities are recommended.

Let us quickly see where sp_depends fail and other solutions work fine.

Let us first create two scenarios.

Scenario 1: Normal Table Creation Order, where objects are created first and then used afterwords.

USE TempDB
GO
CREATE TABLE dbo.TestTable
( ID INT,
Name VARCHAR(100))
GO
-- dbo.First is not created yet
CREATE PROCEDURE dbo.Second
AS
EXEC
dbo.First
GO
CREATE PROCEDURE dbo.First
AS
SELECT
ID, Name
FROM TestTable
GO

Scenario 2: Objects are created afterwords and they are referenced first.

USE TempDB
GO
CREATE TABLE dbo.TestTable
( ID INT,
Name VARCHAR(100))
GO
CREATE PROCEDURE dbo.First
AS
SELECT
ID, Name
FROM TestTable
GO
-- dbo.First is already created
CREATE PROCEDURE dbo.Second
AS
EXEC
dbo.First
GO

Now let us run following three queries on both the scenarios.

-- Method 1: Using sp_depends
sp_depends 'dbo.First'
GO
-- Method 2: Using information_schema.routines
SELECT *
FROM information_schema.routines ISR
WHERE CHARINDEX('dbo.First', ISR.ROUTINE_DEFINITION) > 0
GO
-- Method 3: Using DMV sys.dm_sql_referencing_entities
SELECT referencing_schema_name, referencing_entity_name,
referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.First', 'OBJECT');
GO

Result from Scenario 1

Result from Scenario 2

It is clear that sp_depends does not give proper/correct results when the object creation order is different or following deferred name resolution.

I suggest the use of the third method, in which sys.dm_sql_referencing_entities is used.

Use the following script to get correct dependency:

SELECT referencing_schema_name, referencing_entity_name,
referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('YourObject', 'OBJECT');
GO

Let me know the type of scripts you use for finding Object Dependencies. I will post your script with due credit.

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

About these ads

SQL SERVER – Location of Resource Database in SQL Server Editions

While working on project of database backup and recovery, I found out that my client was not aware of the resource database at all. The Resource database is a read-only database that contains all the system objects that are included with SQL Server.

Location of this database is at different places in the different version of SQL Server.

In SQL Server 2008:

<drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\Binn\.
The Resource database cannot be moved.

ResourceDB location in SQL Server 2008
ResourceDB location in SQL Server 2008

In SQL Server 2005:

Same place where master database is located.
The Resource database have to move with master database.

You can run following commands to know resourcedb version and last updated time.

SELECT SERVERPROPERTY('ResourceVersion') ResourceVersion,
SERVERPROPERTY('ResourceLastUpdateDateTime') ResourceLastUpdateDateTime
GO

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

SQL SERVER – Recently Executed T-SQL Query

About a year ago, I wrote blog post about SQL SERVER – 2005 – Last Ran Query – Recently Ran Query.  Since, then I have received many question regarding how this is better than fn_get_sql() or DBCC INPUTBUFFER.

The Short Answer in is both of them will be deprecated.

Please refer to following update query to recently executed T-SQL query on database.

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

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

SQL SERVER – Get Query Plan Along with Query Text and Execution Count

Quite often, we need to know how many any particular objects have been executed on our server and what their execution plan is. I use the following handy script, which I use when I need to know the details regarding how many times any query has ran on my server along with its execution plan. You can add an additional WHERE condition if you want to learn about any specific object.

SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
--WHERE OBJECT_NAME(st.objectid,st.dbid) = 'YourObjectName'

In result set, you will find the last column QueryPlan with XML text in it. Click on it, and it will open a new tab displaying the graphical execution plan.

You can also save the newly opened tab in XML format with the extension .sqlplan. When this new file is opened in SQL Server Management Studio, it will automatically display the graphical representation of the XML Plan.

It is a quite simple script, but it works all the time.

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

SQL SERVER – Measure CPU Pressure – CPU Business

Many a time, DBAs face the following question: can the CPU handle the current transaction? I have seen many DBAs getting confused on whether they should get a CPU with higher clock speed or more number of CPUs when it is time to upgrade system. As we all know, the CPU (or the Central Processing Unit) is the core of any system. The CPU is responsible for not only SQL Server operations but also all the OS (Operating System) tasks related to the CPU on the server.

It is quite possible that although we are running very few operations on our SQL Server, we still do not obtain the expected results. This is when the SQL Server CPU has to be checked. Again, please note that I am not suggesting that the CPU is the root cause of poor performance in this case. There are ample chances that the Memory, Input/Output or Tempdb can be responsible for performance degradation. However, to find out the “real” culprit, we must run a test; here, we intend to start running tests on the CPU.

Let us understand the simple meaning of CPU pressure. CPU pressure is a state wherein the CPU is fully occupied with currently assigned tasks and there are more tasks in the queue that have not yet started.

We can run several different types of queries to test the CPU. However, my personal favorite is the following one. The resultset will contain as many rows as those being used by the CPU SQL Server.

SELECT
scheduler_id,
cpu_id,
current_tasks_count,
runnable_tasks_count,
current_workers_count,
active_workers_count,
work_queue_count,
pending_disk_io_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255;

Above query will give result something like following.

Before we go ahead, let us familiarize ourselves with the terms. First of all, we should understand what a “scheduler” refers to in the SQL Server. Each SQL Server instance behaves and acts like any OS internally. It has schedules and also it synchronizes concurrent tasks by itself without going to OS. Each instance can handle many concurrent requests. However, there is a point at which the instance cannot accept any more requests. SQL Servers have dynamic management view (DMV), which in fact keeps a log of all the scheduler-related tasks and also it provides a good idea about the CPU pressure.

current_tasks_count is the number of counts of the currently running task. However, we should give utmost consideration to runnable_tasks_count. If this number is higher, it indicates that a large number of queries, which are assigned to the scheduler for processing, are waiting for its turn to run. This gives a clear indication of the CPU pressure. Additionally, count pending_disk_io_count displays the tasks that are yet to be processed in the scheduler. For better processing, this count is expected not to be very high.

When we say that the numbers are high or low, it does not make any sense unless we compare it to a standard or any other known count. Therefore, here, these numbers are compared to the worker counts (current_worker_count). If current_worker_count is 24 and there are 1000 tasks in queue, then this is not a good scenario. Thus, you can always look at the numbers and make your own judgement here.

Well, in this article, I have done a lot of talking with very little script. Please do let me know your opinions regarding whether this was simple enough to convey the message regarding CPU pressure.

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

SQL SERVER – Get Last Running Query Based on SPID

We often need to find the last running query or based on SPID need to know which query was executed. SPID is returns sessions ID of the current user process. The acronym SPID comes from the name of its earlier version, Server Process ID.

To know which sessions are running currently, run the following command:

SELECT @@SPID
GO

In our case, we got SPID 57, which means the session that is running this command has ID of 57.

Now, let us open another session and run the same command. Here we get different IDs for different sessions.

In our case, we got SPID 61. Please note here that this ID may or may not be sequential.

In session with SPID 61, we will run any query. In session with SPID 57, we will see which query was run in session with SPID 61.

Let us run a simple SELECT statement in session with SPID 61 and in session with SPID 57 run the following command.

DBCC INPUTBUFFER(61)
GO

Now, here in DBCC command we have passed the SPID of previous session; we will see the text below. The following image illustrates that we get the latest run query in our input buffer.


There are several ways to find out what is the latest run query from system table sys.sysprocesses.

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 61
SELECT TEXT
FROM
sys.dm_exec_sql_text(@sqltext)
GO

The following image portrays that we get the latest run query in our input buffer.


Please note that in any session there may be multiple running queries, but the buffer only saves the last query and that is what we will be able to retrieve.

There is one more way to achieve the same thing – using function fn_get_sql

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 61
SELECT TEXT
FROM
::fn_get_sql(@sqltext)
GO


All the three methods are same but I always prefer method 2 where I have used sys.sysprocesses.

Today, we have explored a very simple topic. Let me know if you find it useful.

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

SQL SERVER – Performance Counters from System Views – By Kevin Mckenna

I just love social media and all the new concepts of Web 2.0. There are bloggers who are overwhelmed by the new concepts of technology and are not able to keep pace with it. But I like taking such challenges. Twitter has acquired tremendous popularity nowadays and just like everybody else I am also fond of this latest vogue. You can follow me at Twitter here. Through twitter I am getting to meet people like me and it’s a great experience interacting with them. I met SQL and .NET expert Kevin Mckenna on twitter itself.

Kevin is originally from Liverpool, England, and moved to the US in December 2005. He is a DBA for a large warehousing and logistics company based in the US. He has been developing software with SQL backends for approximately 6-7 years, and has been administrating SQL and Oracle databases for the past 4 years.

He is married and has 2 children.  He is a huge football (soccer for the Americans) fan, and when he is not working, he likes spending his time either reading or playing on twitter.

He has recently sent me a very interesting note on Performance Counters from System Views. I really enjoyed reading it and with his permission I am publishing the content here. Let me have your opinion on this.

Many of you are not aware of this fact that access to performance information is readily available in SQL Server and that too without querying performance counters using a custom application or via perfmon. Till now, this fact has remained undisclosed but through this post I would like to explain you can easily access SQL Server performance counter information.

Without putting much effort you will come across the system view sys.dm_os_performance_counters. As the name suggests, this provides you easy access to the SQL Server performance counter information that is passed on to perfmon, but you can get at it via tsql.

You can start with a few simple steps:

SELECT* FROM sys.dm_os_performance_counters

This returns 701 rows of data on the test machine, with information from Buffer Manager, Buffer Partition, Buffer Node, General Statistics, Locks, and so forth.

I’ll try to make you all understand by making it a bit more simple. Here, we will concentrate on Buffer Manager.

Please note that if you have an instance name, the object name will be MSSQLlt;instance name>: instead of SQLServer:

The next step is

SELECT* FROM sys.dm_os_performance_counters WHERE OBJECT_NAME = 'SQLServer:Buffer Manager'

The following screen will be displayed.

XX

As you all can notice, what is displayed above via tsql is similar to what is available via perfmon

Let’s take a look at the columns returned:

field name data type Description
object_name nchar(128) Category to which this counter belongs
counter_name nchar(128) Name of the counter
instance_name nchar(128) Name of the specific instance of the counter. Often contains the database name
cntr_value bigint Current value of the counter
cntr_type int Type of counter as defined by the Windows performance architecture

Now, if you all are wondering what can be done with this information then let me tell you that you have a few things options. We can create a job that will sample a particular value on a timed basis and add it to a table, and view an average value from it. Another option is to just take a glance at it for peace of mind!

One of the most useful things we could derive from this however is trend information.  For example, let’s chart the number of Active Transactions during the work day.

We will start with creating a table, as given below.

CREATE TABLE ActiveTrans_sqlGenus (
obj_name NCHAR(128),
counter_name NCHAR(128),
instance_name NCHAR(128),
cntr_value bigint,
cntr_type INT,
datestamp datetime
)

We can then fill up this table on a timed basis:

INSERT INTO ActiveTrans_sqlGenus
SELECT
OBJECT_NAME, counter_name, instance_name, cntr_value,
cntr_type, GETDATE()
FROM
sys.dm_os_performance_counters
WHERE
OBJECT_NAME = 'SQLServer:Databases'
ANDcounter_name = 'Active Transactions'
ANDinstance_name = 'sqlGenus'

Here, let’s assume that we are inserting into this table on a consistent basis. We can then issue the following command:

SELECT* FROM ActiveTrans_sqlGenus
WHERE datestamp BETWEEN '06/02/2009 09:00' AND '06/02/2009 17:00'

After that, we can retrieve all the entries between 9am and 5pm, our fictitious work day.

We could manipulate this data even further, averaging out the values, pulling the MAX and MIN and so on.

Obviously, this is a very simple example, and certainly not the best way to store this information over a period of time. However, with some digging into just what you need, you can provide access to your SQL Server performance in a remarkably simple manner.

The biggest disappointment in obtaining this information from SQL Server’s system views is that it only provides SQL Server information, so we do not receive information like Processor, Network and Disk information.  These values still need to be pulled via Performance Counters.  But despite this negative aspect, this view will provide ample usable information to keep your database in an excellent shape.

You can find more information on this system view from MSDN:

http://msdn.microsoft.com/en-us/library/ms187743(SQL.90).aspx

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