SQL SERVER – SSMS: Disk Usage Report

Let us start with humor! 

I think we the series on various reports, we come to a logical point. We covered all the reports at server level. This means the reports we saw were targeted towards activities that are related to instance level operations. These are mostly like how a doctor diagnoses a patient. At this point I am reminded of a dialog which I read somewhere:

Patient: Doc, It hurts when I touch my head.

Doc: Ok, go on. What else have you experienced?

Patient: It hurts even when I touch my eye, it hurts when I touch my arms, it even hurts when I touch my feet, etc.

Doc: Hmmm

Patient: I feel it hurts when I touch anywhere in my body.

Doc: Ahh now I get it. You need a plaster to your finger John.

Sometimes the server level gives an indicator to what is happening in the system, but we need to get to the root cause for a specific database. So, this is the first blog in series where we would start discussing about database level reports. To launch database level reports, expand selected server in Object Explorer, expand the Databases folder, and then right-click any database for which we want to look at reports. From the menu, select Reports, then Standard Reports, and then any of database level reports.

In this blog, we would talk about four “disk” reports because they are similar:

  1. Disk Usage
  2. Disk Usage by Top Tables
  3. Disk Usage by Table
  4. Disk Usage by Partition

Disk Usage

This report shows multiple information about the database. Let us discuss them one by one.  We have divided the output into 5 different sections.

Section 1 shows the high level summary of the database. It shows the space used by database files (mdf and ldf). Under the hood, the report uses, various DMVs and DBCC Commands, it is using sys.data_spaces and DBCC SHOWFILESTATS.

Section 2 and 3 are pie charts. One for data file allocation and another for the transaction log file. Pie chart for “Data Files Space Usage (%)” shows space consumed data, indexes, allocated to the SQL Server database, and unallocated space which is allocated to the SQL Server database but not yet filled with anything. “Transaction Log Space Usage (%)” used DBCC SQLPERF (LOGSPACE) and shows how much empty space we have in the physical transaction log file.

Section 4 shows the data from Default Trace and looks at Event IDs 92, 93, 94, 95 which are for “Data File Auto Grow”, “Log File Auto Grow”, “Data File Auto Shrink” and “Log File Auto Shrink” respectively. Here is an expanded view for that section.

If default trace is not enabled, then this section would be replaced by the message “Trace Log is disabled” as highlighted below.

Section 5 of the report uses DBCC SHOWFILESTATS to get information. Here is the enhanced version of that section. This shows the physical layout of the file.

In case you have In-Memory Objects in the database (from SQL Server 2014), then report would show information about those as well. Here is the screenshot taken for a different database, which has In-Memory table. I have highlighted new things which are only shown for in-memory database.

The new sections which are highlighted above are using sys.dm_db_xtp_checkpoint_files, sys.database_files and sys.data_spaces. The new type for in-memory OLTP is ‘FX’ in sys.data_space.

The next set of reports is targeted to get information about a table and its storage. These reports can answer questions like:

  1. Which is the biggest table in the database?
  2. How many rows we have in table?
  3. Is there any table which has a lot of reserved space but its unused?
  4. Which partition of the table is having more data?

Disk Usage by Top Tables

This report provides detailed data on the utilization of disk space by top 1000 tables within the Database. The report does not provide data for memory optimized tables.

Disk Usage by Table

This report is same as earlier report with few difference.

  1. First Report shows only 1000 rows
  2. First Report does order by values in DMV sys.dm_db_partition_stats whereas second one does it based on name of the table.

Both of the reports have interactive sort facility. We can click on any column header and change the sorting order of data.

Disk Usage by Partition

This report shows the distribution of the data in table based on partition in the table.

This is so similar to previous output with the partition details now. Here is the query taken from profiler.

SELECT
row_number() OVER (ORDER BY a1.used_page_count DESC, a1.index_id) AS row_number
,      (dense_rank() OVER (ORDER BY a5.name, a2.name))%2 AS l1
,      a1.OBJECT_ID
,      a5.name AS [schema]
,       a2.name
,       a1.index_id
,       a3.name AS index_name
,       a3.type_desc
,       a1.partition_number
,       a1.used_page_count * 8 AS total_used_pages
,       a1.reserved_page_count * 8 AS total_reserved_pages
,       a1.row_count
FROM sys.dm_db_partition_stats a1
INNER JOIN sys.all_objects a2  ON ( a1.OBJECT_ID = a2.OBJECT_ID) AND a1.OBJECT_ID NOT IN (SELECT OBJECT_ID FROM sys.tables WHERE is_memory_optimized = 1)
INNER JOIN sys.schemas a5 ON (a5.schema_id = a2.schema_id)
LEFT OUTER JOIN  sys.indexes a3  ON ( (a1.OBJECT_ID = a3.OBJECT_ID) AND (a1.index_id = a3.index_id) )
WHERE (SELECT MAX(DISTINCT partition_number)
FROM sys.dm_db_partition_stats a4
WHERE (a4.OBJECT_ID = a1.OBJECT_ID)) >= 1
AND a2.TYPE <> N'S'
AND  a2.TYPE <> N'IT'
ORDER BY a5.name ASC, a2.name ASC, a1.index_id, a1.used_page_count DESC, a1.partition_number

Using all of the above reports, you should be able to get the usage of database files and also space used by tables.

I think this is too much disk information for a single blog and I hope you have used them in the past to get data. Do let me know if you found anything interesting using these reports in your environments.

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

About these ads

SQL SERVER – SSMS: Top Object and Batch Execution Statistics Reports

The month of June till mid of July has been the fever of sports. First, it was Wimbledon Tennis and then the Soccer fever was all over. There is a huge number of fan followers and it is great to see the level at which people sometimes worship these sports. Being an Indian, I cannot forget to mention the India tour of England later part of July. Following these sports and as the events unfold to the finals, there are a number of ways the statisticians can slice and dice the numbers. Cue from soccer I can surely say there is a team performance against another team and then there is individual member fairs against a particular opponent. Such statistics give us a fair idea to how a team in the past or in the recent past has fared against each other, head-to-head stats during World cup and during other neutral venue games.

All these statistics are just pointers. In reality, they don’t reflect the calibre of the current team because the individuals who performed in each of these games are totally different (Typical example being the Brazil Vs Germany semi-final match in FIFA 2014). So at times these numbers are misleading. It is worth investigating and get the next level information.

Similar to these statistics, SQL Server Management studio is also equipped with a number of reports like a) Object Execution Statistics report and b) Batch Execution Statistics reports. As discussed in the example, the team scorecard is like the Batch Execution statistics and individual stats is like Object Level statistics. The analogy can be taken only this far, trust me there is no correlation between SQL Server functioning and playing sports – It is like I think about diet all the time except while I am eating.

Performance – Batch Execution Statistics

Let us view the first report which can be invoked from Server Node -> Reports -> Standard Reports -> Performance – Batch Execution Statistics.

Most of the values that are displayed in this report come from the DMVs sys.dm_exec_query_stats and sys.dm_exec_sql_text(sql_handle).

This report contains 3 distinctive sections as outline below.

 

Section 1: This is a graphical bar graph representation of Average CPU Time, Average Logical reads and Average Logical Writes for individual batches. The Batch numbers are indicative and the details of individual batch is available in section 3 (detailed below).

Section 2: This represents a Pie chart of all the batches by Total CPU Time (%) and Total Logical IO (%) by batches. This graphical representation tells us which batch consumed the highest CPU and IO since the server started, provided plan is available in the cache.

Section 3: This is the section where we can find the SQL statements associated with each of the batch Numbers. This also gives us the details of Average CPU / Average Logical Reads and Average Logical Writes in the system for the given batch with object details. Expanding the rows, I will also get the # Executions and # Plans Generated for each of the queries.

Performance – Object Execution Statistics

The second report worth a look is Object Execution statistics. This is a similar report as the previous but turned on its head by SQL Server Objects.

The report has 3 areas to look as above. Section 1 gives the Average CPU, Average IO bar charts for specific objects. The section 2 is a graphical representation of Total CPU by objects and Total Logical IO by objects.

The final section details the various objects in detail with the Avg. CPU, IO and other details which are self-explanatory.

At a high-level both the reports are based on queries on two DMVs (sys.dm_exec_query_stats and sys.dm_exec_sql_text) and it builds values based on calculations using columns in them:

SELECT *
FROM    sys.dm_exec_query_stats s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE   s2.objectid IS NOT NULL AND DB_NAME(s2.dbid) IS NOT NULL
ORDER BY  s1.sql_handle;

This is one of the simplest form of reports and in future blogs we will look at more complex reports.

I truly hope that these reports can give DBAs and developers a hint about what is the possible performance tuning area. As a closing point I must emphasize that all above reports pick up data from the plan cache. If a particular query has consumed a lot of resources earlier, but plan is not available in the cache, none of the above reports would show that bad query.

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

SQL SERVER – SSMS: Top Queries by CPU and IO

Let me start this blog post with a personal story.

Personal Story – Dad and I

My fascination for computers started way back when I was about to get into highschool. My father once took me to his office (was for some family day if I remember correctly) and it was fun to watch the PC. Interestingly enough, it was the only color PC in that office it seems – for those days it was a green font’s print CRT monitor. I am not sure how many even had a chance to work on those PC’s. It is a lost era for this generation.

Even from that time the most discussed part of these computers have been its processors – if I remember correctly, it was the 32-bit processors (pre-Pentium era) and the Hard-disks with the (3 ½ inch Floppy drives). It was an era where having few MB’s of data was a big deal.

Fast forward to today, all these stories seem like a great recreation for my daughters bedtime stories. Moore’s Law has proved itself for more than 4 decades and still amuses us. The processors these days on our watch / handheld devices are more powerful (by a factor of 1000x at least) than what we used to work on a PC 15-20 years back. The days are surely changing so should we! I am not sure what the innovations and technology would be when my daughter grows up.

Back to SQL Server

In today’s context, the advancements in technology have not stopped us from troubleshooting these parameters even today. Whenever I get involved in performance tuning exercise, one of the first questions I ask is – “What is the CPU utilization?”, “How is Memory consumption?”, “How is Disk activity and Disk queue length?” and “How is the network doing?”

So for today’s blog post, we will concentrate on 4 different reports:

  1. Top Queries by Average CPU Time
  2. Top Queries by Total CPU Time
  3. Top Queries by Average IO Time
  4. Top Queries by Total IO Time

These are the standard reports from the Server node. Go to Server Node -> Right Click -> Reports -> Standard Reports and you will find these in SQL Server Management Studio.

Top Queries by Average CPU Time

When it comes to CPU, for me Perfmon is still the primary tool for tracking down fundamental CPU usage and I feel it should remain so for you.  However, from time to time we need to track down which process is using a physical CPU. When DBA’s ask me why SQL Server using all the CPU, I ask them the first question – are you sure SQL Server is the process utilizing CPU on server.

Just for the records, I typically read and used the kernel debugger to determine what process is using which exact CPU. For a primer take a look at how to use XPerf tool on MSDN Blogs.

Though these tools are powerful, there is a considerable learning curve for a novice DBA. So let me come back to favorite tool of choice – SQL Server Management Studio. Let us start to look at the Top Queries by Average CPU Time report. The output has two sections: a) Graph Section and b) Details Section.

The first Graph section is a color palette sorted in descending order the Top queries that are consuming Average CPU Time and Total CPU Time. For me the next section has more interesting details to analyze. Before I jump there – there is an important note on the top of the report that is worth a look. It reads as:

Note: This report identifies the queries currently residing in the plan cache that have consumed the most total CPU time over the course of all their executions.  This data is aggregated over the lifetime of the plan in the cache and is available only for plans currently in the cache.

This means that the values will get reset if SQL Server has been restarted because the cache gets flushed and emptied. In your active transactional system this will never be the case and I am sure you will know where to start your tuning exercise when SQL Server is utilizing higher CPUs.

As I mentioned, the second section is of higher interesting values to look at. As you can see, the Top 10 CPU consuming queries are listed and we can start investigating what is the resource utilization of each of these queries and how we can tune them. The label of the colors (1, 2, 3 etc.) are “Query No.” column in the table. It is quite possible that the order in the second graph may not be same as first graph.

In the above picture we can see that the Top most expensive query is utilizing close to 1.774 seconds to execute and on an average of 2 executions it is taking close to 0.887 seconds for each execution. The query under question is also highlighted under the Query Text.

Top Queries by Total CPU Time

Since the output is almost similar to what we discussed before. The queries are sorted by Top CPU time now. That is the only difference. The rough DMV’s used for this report would be:

SELECT TOP(10)
creation_time
,       last_execution_time
,       (total_worker_time+0.0)/1000 AS total_worker_time
,       (total_worker_time+0.0)/(execution_count*1000) AS [AvgCPUTime]
,       execution_count
FROM sys.dm_exec_query_stats  qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE total_worker_time > 0
ORDER BY total_worker_time DESC

Before I sign off on CPU utilization, I have always relied on this report for CPU workloads. But as a primer there are obvious candidates that I always look out to when CPU is high like:

  1. Query Execution and Parallelism
  2. Compiles and recompiles on the server
  3. Any tracing if enabled in the system – includes running of Activity Monitor somewhere or Profiler (we can use sys.traces to check traces which are configured to run)
  4. If any anti-virus is running in the system.
  5. Invalid drivers for SAN, BIOS or other components

These are some great starting points to eliminate before going into tuning queries for a CPU deprived system.

Performance – Top Queries by Average IO

As described for the CPU output, the output is almost the same here too with two sections. The difference is, it has been sorted by Average IO utilization.

The meat of information is available in section 2 as usual.

This section apart from CPU values, has also the values of Logical Reads, Logical Writes. As you can see, Total Logical IO = Logical Reads + Logical Writes

The query to get this information would be:

SELECT TOP 10
creation_time
,       last_execution_time
,       total_logical_reads AS [LogicalReads]
,       total_logical_writes AS [LogicalWrites]
,       execution_count
,       total_logical_reads+total_logical_writes AS [AggIO]
,       (total_logical_reads+total_logical_writes)/(execution_count+0.0) AS [AvgIO]
,      st.TEXT
,       DB_NAME(st.dbid) AS database_name
,       st.objectid AS OBJECT_ID
FROM sys.dm_exec_query_stats  qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE total_logical_reads+total_logical_writes > 0
AND sql_handle IS NOT NULL
ORDER BY [AggIO] DESC

It is important to understand that IO for a query is a SUM of both Logical IO and Physical IO. Irrespective of where the data resides the IO is calculated as a Union of these two values.

Performance – Top Queries by Total IO

This output is identical to the previous output with the only difference of it being sorted by the Total IO parameter. The sections and output are exactly same as above reports.

As I sign off this post, wanted to know if anyone has used any of these reports or do you rely on using DMVs and other tools to troubleshoot CPU and/or IO related problems inside SQL Server? Do share your experience.

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

SQL SERVER – SSMS: Top Transaction Reports

Let us start with a personal story.

Personal Story – My Daughter and I

My day has its own part of long meetings and my daughter tries to pull a string here and there to get my attention almost every other day. And recently I was made to watch one of her school assignment on drawing. As a parent, the beauty is in the eye of the beholder – so I can never complain what my daughter drew for me. Since this was some sort of school competition, I wanted to see what the remark of the teacher was – it mentioned “Very Good”. Intrigued by this comment, I started to quiz my daughter to who got the Best painting marks and what did they draw? I am sure the parent inside me was taking a sneak peek into how my daughter performed as compared to others in the class. She was quick to respond with a few names of her friends and said they also drew best. The honesty moved me because as a child we are unbiased and true to our very self. This moved me and I thought it is a great time to take my daughter to the park and spend some quality time as father-daughter.

Getting Back to SQL Server

Returning from park, this incident was on top of my mind and I thought being a class topper or ahead of the crowd is an intrinsic quality we all try to follow as human beings. It is so strange that today’s post is all about the “Top” nature. In life, it is great to be a Top performer but in SQL Server parlance it is not a great thing to be on these Top reports. You are surely going to become a subject for tuning next. This surely is a great boon for Administrators though.

This blog will call out 3 different report from the Server Node -> Reports -> Standard Reports

  1. Top Transactions by Age
  2. Top Transactions by Locks Count
  3. Top Transactions by Blocked Transactions Count

Since all these reports were from the Top category about the transaction based on various factors, I thought to have them all covered in one post.

Top Transactions by Age

This is one of the simplest of reports which shows based on when the query was submitted to the server and how long some of the transactions have been waiting in the instance. This is a good starting point to know which Session ID, Database is holding the # of lock for how long. These for all practical purposes is a very good starting point in my opinion to start looking at long running transactions.

Some of the DMV’s that work behind the scenes to generate the report are:

  • sys.dm_tran_active_transactions – Has information of transactions in a given instance.
  • sys.dm_tran_session_transactions – Has information of transactions for a given session.
  • sys.dm_tran_database_transactions – Gives transactions at a database level.
  • sys.dm_exec_sessions – Has information about the active sessions currently on the server.
  • sys.dm_exec_requests – Information of each request currently running on the server.

From this report, the DBA can take a decision to what process is causing these locks? And why they are held for such a long time.

Top Transactions by Lock Count

I would say, this is in continuation to the previous report. In the previous report, I was able to find out the number of locks for a given Session ID and database. But the specifics on the type of locks were not clear.

This report is all about expanding that part of the uncertainty. This report shows the type of locks are held by a specific session. In our report below we can see the session ID 52 and 53 are holding Object, Page and Key locks respectively. While 52 has an Exclusive Lock already taken, 53 has an Update fired on the dbo.test table.

I am sure on a highly transactional production server this will surely be a busy report to view. Do let me know how many nodes you see on your servers.

The transaction state can be one of the following:

  • Uninitialized
  • Initialized
  • Active
  • Prepared
  • Committed
  • Rolled Back
  • Commiting

These values can be got from sys.dm_tran_database_transactions DMV. While the Active Transaction State can take the below values as defined in the sys.dm_tran_active_transactions   DMV:

  • Invalid
  • Initialized
  • Active
  • Ended
  • Commit Started
  • Prepared
  • Committed
  • Rolling Back
  • Rolled Back

 

Top Transactions by Blocked Transactions Count

The last report under the Top category is the Blocked transaction Count report. This is almost similar to the report we say in our previous post of Blocking Transactions Report. Since we have explained the same out there already, I will refrain from getting into the details here.

 

These reports can help in finding cause of below few errors:

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. (Msg 1204, Level 19, State 4)

Action: Find the session_id which is taking more locks and tune them.

Lock request time out period exceeded.(Msg 1222, Level 16, State 45)

Action: Find the session_id which is holding locks and see why they have a long running transaction.

I am curious to know how many of you have every used the Top reports mentioned here in your environments to debug something? Do let me know how you used them effectively so that we can learn from each other.

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

SQL SERVER – SSMS: Transaction Log Shipping Status Report

History has its own way to define now civilizations thrived. Most of the cities flourished in the river side and transporting lumber was one of the key activity. Most of the cities like Seattle and many others have this boom and bust life. The idea here was to cut the timber upstream and use the natural flow of rivers to transport to factories downstream using the river. These are classic and wonderful examples of how we typically work with Log-Shipping in SQL Server too. This blog is about Log Shipping Status report.

Ensuring the availability of databases, meeting SLA and performance tuning are some of the top priorities for today’s database administrators (DBAs). One of the important work of DBA is to monitor the database servers and make sure the application is working fine. The monitoring might involve automatic alerts, running scripts or looking at some dashboard. Even for high availability solutions, we need some kind of monitoring mechanism. One of the traditional high availability solution is Log Shipping.

As the name suggests, Log-shipping is based on transaction log backups getting shipped from one server to one or more servers on the other side. For understanding this you need to know basics of transaction log backups. First, log backups can be taken from the database which is in full or bulk logged recovery model. In the simple recovery model, transaction log backups are not allowed because every checkpoint flushes the transaction log file. In other two recovery models log backup would do flush. Another basics of log shipping is that all log backups form a chain. T1, T2 and T3 must be restored in sequence. Missing any one the file would cause an error message during restore. In log shipping, backup, copy and restore is done automatically. The SQL Agent service does that for us. Since we can ship to multiple servers, backup location is shared so that other servers can get a copy of that file to perform the restore. Source server in technical terms is called as the primary server. Rest all servers which are at receiving end are called as a secondary server. You would also hear monitor server, which is responsible to check the health of copy, backup and restore job. If the jobs are not running properly, then secondary would be behind primary server and would defeat the purpose of high availability. Based in the threshold defined, monitor server can raise alerts so that corrective action can be taken.

This is the last report in the list under server node. Based on the name of the report, you might have already guessed that it can be used to “see” the status of log shipping status.

The important note about this report is that the data shown in the column would be dependent on the server where we launch the report. Here is the report, when launched from Primary Server.

If we notice, information about backup section is populated. This is because the report doesn’t make a remote connection to check secondary server status. If the report is launched from a Secondary Server the output would be as below:

The information about copy and restore related information is populated automatically because those are available on secondary server.

If we configure monitor server in log-shipping (which I have not done) and launch report there, we can see information about all three steps (i.e. backup, copy and restore)

The good part about the report is that it shows the alarming pair in red color. To demonstrate, I have configured log shipping for two databases, and for one, I have disabled the backup, copy and restore jobs so that alerts are raised and we can see the impact on report.

You may wonder how this information is fetched. This has the simplest possible query behind the scene.

EXEC sp_help_log_shipping_monitor

As per Books online – “Returns a result set containing status and other information for registered primary and secondary databases on a primary, secondary, or monitor server.”

If you see anything in red color, you need to start investigation further to find the cause of delay. What is the most common cause you have observed, which causes delay in log shipping? Networking, Disk slowness or something else? Please comment and let me know.

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

SQL SERVER – Finding Object Dependencies in SSMS – SQL in Sixty Seconds #071

While we are doing development, we create and drop objects. We build new things and we need to understand the relationships between database objects when we are doing various activities in SQL Server. Well, it is indeed very hard to know all, the relationship between various objects in SQL Server. However, with the help of SQL Server 2014 Management Studio, you can for sure do the same task very easily.

You have to go the object of which you want to see properties of and right click over it.

Now click over the option “View Dependencies”. It will bring up a screen listing various dependencies.

I hope this is clear enough. If not, I strongly suggest you watch following quick video where I explain this concept in extremely simple words.

Action Item

Here are the blog posts I have previously written on SSMS. You can read it over here:

You can subscribe to my YouTube Channel for frequent updates.

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

SQL SERVER – SSMS: Activity – All Blocking Transactions

Working out of India has its own challenges and I enjoy here despite these challenges thrown at me. One of the biggest advantage I have working with Pluralsight is, I can still get my job done by working-from-home occasionally. And this is one of the perks I wish most of the companies give their employees. You might be thinking why I am doing this, well the obvious answer to this question relies on the fact how the previous day went. If it rained heavily, which is does in Bengaluru in July, then the chances are that roads would have a build-up of traffic the next day morning. Taking traffic away from your life is never so easy, but with technology improvements like Maps on the phone, I still manage to get an alternate route to reach my destination. This is what makes life interesting and the exploration into new places always fun.

I just wish SQL Server had some way of achieving the same. Blocking and Locking are fundamental to keeping databases in sync and consistent. This blog is all about Blocking Transactions report from the instance level.

To access the report, get to Server node -> Reports -> Standard Reports -> Activity – All Blocked Transactions.

From this node, if there are no apparent blocking happening in the system at the point this report was run, we will be presented with a “Blank” output as shown below.

The ideal situation for us to be in this state, even for a transitional system, but this will never be the case in reality. For a highly transactional systems which try to modify / insert data in same table, SQL Server will respect the order in which the request came and will not allow incompatible locks to exist at the same time. So this behaviour creates a queue automatically and this is what we call as Blocking.

This brings us to the next output, where we are having multiple transactions running. To show some data in report from my non-production-workload system, I have simulated a blocking scenario using two statements. In such a scenario you can see there are two regions to look at: the Session ID of 52, 53 and 54. From the hierarchy, we know that 52 is blocking both 53 and 54. We can also know there are 2 “#Directly Blocked Transactions” in the system currently from the top row for SPID 52. If there are additional transactions trying to insert or delete, then this will show the complete chain of tractions currently blocked.

We also get to see the type of statement that is waiting in this blocking scenario. In the diagram below we see the two statements involved are – INSERT and DELETE.

Various DMVs which have been used to get this information are sys.dm_tran_locks, sys.dm_tran_active_transactions, sys.dm_tran_session_transactions, sys.dm_tran_database_transactions and sys.dm_exec_requests. Along with above, report also uses DMF sys.dm_exec_sql_text to convert the SQL handle to more meaningful text.
If that was not enough then we can also head to the Activity Monitor and expand the Processes tab to get similar information. It is evident that the head of blocking is 52 whereas 53 and 54 are waiting on 52. It is completely up to us to decide what we need to do. We can Kill process 52 and the other transactions will go through.

As a small note, the Task States can give us vital information of what is happening in the system. Some of the states are worth mentioning:

Sleeping This shows the SPID is waiting for a command or nothing is currently executing.
Running SPID is currently running.
Suspended SPID is waiting for locks or a latch.
Rollback Connection is in rollback state of a transaction.

You can use the state information to take an informed decision of killing a process if required.

At this moment, yet another blog post that is worth a mention is Blocked Process Threshold post. This option makes sure there is a profiler event raised when a request is blocked beyond a predefined period of time. So do take a look at that too if you are interested in that behaviour.

The reports series is catching up and the learnings are multi-fold for me personally. Subsequent posts I will get into the other reports and give you my learnings.

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