Saturday, November 17, 2012

Installing 32-bit SQL Server on 64-bit Windows (x64)

Looking at the hardware and software requirements for SQL Server 2008, 2008 R2 and 2012 from Microsoft site, we should be able to install 32-bit version of SQL Server onto 64-bit (x64) Windows operating system.

If you have installation media or ISO image that contains both 32-bit and 64-bit version of SQL Server 2008, 2008 R2 or 2012, here’s how you can install the 32-bit SQL Server on 64-bit Windows (x64). When you insert the installation media, or mount the ISO image, in some cases you might need to double click on the setup.exe, you will be presented with the SQL Server Installation Center. Click on the Options link on the left.

image

You should then be able to select to install the 32-bit (x86) of the SQL Server.

image

You can kick off the 32-bit installation process, by running the following command:

setup.exe /action="install" /x86=true

As a matter of fact, you can use the X86=true in the command line or answer file if you want to have unattended installation of SQL Server 2008, 2008 R2 or 2012.

Once it has been installed, when you check the SQL Server version using:

SELECT @@VERSION

For SQL Server 2012 installation, you should get something like the following:

Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)Feb 10 2012 19:13:17Copyright (c) Microsoft CorporationEnterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64)

Noticed that it is WOW64 (Windows 32-bit on Windows 64-bit).

Saturday, November 10, 2012

Deadlock Graph XML from Extended Events in SQL Server 2008

We were dealing with some deadlock problems in our database. However, unfortunately we did not have trace flag 1204 and/or trace flag 1222 turned on. Fortunately there is a way to retroactively retrieved deadlock information starting with SQL Server 2008 using Extended Events.

Someone forwarded an article written by Jonathan Kehayias (blog | twitter), an expert in Extended Events, regarding this. The article can be found here. It is a great article. In the article, Jonathan showed us the way to retrieved the deadlock graph from Extended Events.

Jonathan also mentioned in the article that there is a bug in the output of deadlock graph from the Extended Events, in which it is not a valid XML. There is a problem with missing end tag for the <victim-list> node. He then provided a work-around script in which he utilizes the replace function on the deadlock information text from the Extended Events. Then after that we could cast the text as XML.

I went ahead and try that script. When I tried it, I got the following error message:

Msg 9436, Level 16, State 1, Line 1
XML parsing: line 5, character 17, end tag does not match starting

Upon further review, it looks like the script works on deadlocks that have exactly 1 <victimProcess> node. However, if there is empty <victimProcess> node or multiple <victimProcess> nodes, the script would not work. In the case of multiple <victimProcess> nodes, for some reasons, the Extended Events only closed the last <victimProcess> node, but all the other ones are missing the end tag. Thus, I made a small tweak to Jonathan’s script to handle empty <victimProcess> node or multiple <victimProcess> nodes. Here’s the script:

SELECT    CAST(REPLACE(

            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),
                        '<victimProcess', '</victimProcess><victimProcess'),
                    '<victim-list>', '<deadlock><victim-list><victimProcess>'),
                '<process-list>', '</victim-list><process-list>'),
            '<victim-list/>', '<deadlock><victim-list>'),
        '<victimProcess>' + CHAR(10) + SPACE(2) + '</victimProcess>', '') AS XML) AS DeadLockGraph
FROM
(SELECT CAST(target_data as XML) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'