Deadlock graphs in SQL Server 2005

In one of the previous posts : http://decipherinfosys.wordpress.com/2007/01/21/interpreting-deadlock-trace-output-in-sql-server/

We had talked about the deadlock analysis and had mentioned the usage of Deadlock graphs and saving that output as XML for easily storing and transferring that data from the field to the office for easier analysis.  Moreover, having a graphical representation helps the analysis.  So, let’s use the same example that we did in that earlier blog post (refer the scripts for the two sessions from that blog).  Before starting the execution of the two sessions, let’s set up our profiler trace.  Here are the events and the columns that you need to chose in profiler:

In addition to this, you can set other filters, chose other columns and events as per your requirements.  The SQL Statements, RPC events need to be checked.  You can go to the “Events Extraction Settings” tab and select to save the deadlock XML events separately as well à this creates a *.xdl extension file which can then be sent for further analysis in case your application is deployed in the field and your support staff is at a remote location.

 

So, now if we run our testcase, we will get one of the sessions to run into the deadlock error.  This is the error that you will get:

120513

Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

51

-1

Once you have received the error, stop the profiler and you can see from the image below, the deadlock graph which you can choose to store as an XML file.  The next two images after that show the xdl file opened up in Management Studio for further analysis and the XML file output.  This is a pretty neat feature in SQL Server 2005 and saves a ton of time to help make troubleshooting much more easier.

 

 

Enhancements in Windows Firewall

As promised, here is the first of many entries regarding new and improved Windows features found in Microsoft Vista and Longhorn.

 

Both Windows Vista and Longhorn operating systems come bundled with the latest edition of Windows Firewall. Many of you are familiar with this firewall from Windows XP SP2 and Windows Server 2003 SP1, and the overall look and feel of the new version is quite similar. However, there are a number of enhancements that Microsoft has included to ensure not only better security of the operating system, but also to improve the ease with which the firewall can be managed.

Comprehensive Traffic Filtering

The new Firewall supports comprehensive filtering for both incoming and outgoing traffic, and has the ability to drop not only block traffic that does not correspond to any allowed access rule, but also blocks unsolicited traffic – even if that traffic has been earmarked as allowed or expected. This capability is extremely beneficial since it will help prevent the spread of viruses and worms that spread through unsolicited traffic. The default settings for the Firewall are to block all incoming traffic unless it is solicited or matches an Inbound access rule, and allow all outgoing traffic unless it matches an Outbound access rule.

Integrated Firewall and IPSec Configuration 

In Windows XP SP2 and Windows Server 2003 SP1, Windows Firewall and IPSec settings were configured separately. Since both of these security features can allow or block incoming traffic, it was far too easy to configure overlapping, or even worse contradictory, access rules. The new version of Windows Firewall does away with this by enabling the configuration of both Firewall and IPSec settings through the same GUI and command line commands.

 

In addition, for inbound and outbound traffic that must have IPSec protection, administrators can specify a list of computer, group, or user accounts that are authorized to initiate such communications.

Multiple Ways to Configure Rules 

The new Windows Firewall allows for rules to be configured any number of ways including but not limited to:

• Rules for source and destination IP addresses can now be configured for both inbound and outbound traffic. You can also specify certain predefined addresses for destination addresses, including DNS, DHCP, and WINS servers, as well as default gateways.
• In addition to creating rules based on TCP and UDP ports, you can now specify other types of traffic that do not use these protocols.
• Rules can be configured for both source and destination TCP and UDP ports
• In the past you could only configure port-based rules by specifying a single TCP or UDP port. The new version of the Firewall still allows single port configuration, but goes a step further by allowing users to also specify a range of ports or all ports.
• Rules can now be configured for certain interfaces, including LAN, wireless, or remote access.

New MMC Snap-In – In the past, Windows Firewall configuration has been limited to a section within Control Panel and some Group Policy settings that have to be accessed via the Group Policy snap-in. The new version can still be administered via Control Panel and Group Policies, but Microsoft has added a separate snap-in called Windows Firewall with Advanced Security. The snap-in has a nice yet simple GUI layout that allows administrators to configure such items as Inbound and Outbound Rules as well as the ability to monitor existing Firewall configurations.