December 2006 - Posts

Ever wondered how to trace trigger execution? It’s actually quite simple and you can get a lot of information – statements executed, reads for the statement, writes and even row counts; it just makes development and testing so much simpler (and quicker).

 

Anyway, here’s an example:-

 

create database TestTriggerTrace

go

use TestTriggerTrace

go

create table mytest (

    id  int not null,

    somedata int null

    )

go

 

alter trigger test_trigger on mytest

    for insert

as

begin

    if @@rowcount = 0

        return

 

    if exists ( select *

                from inserted

                where id % 2 = 1 )

        return

 

    update mytest  

        set somedata = id % 2

    where id in ( select id

                  from inserted )

 

end

go

 

--  Determine the object id for the trigger

print object_id( 'test_trigger' )

go

 

--  Determine the database id

print db_id()

go

 

You need the object id of the trigger and the database id in order to trace those specific events otherwise you’ll get so much information to wade through – which could be your next step if you are tracking down a problem.

 

Now kick off SQL Profiler and a new trace; you need only trace the SP:StmtCompleted event because that’s where trigger execution appears. Select the columns that most interest you; in my example I’ve chosen the usual stuff – CPU, Reads, Writes, RowCounts, LineNumber and TextData which will contain the statement within the trigger being executed; with all that information and trace event you can see which statements are being executed and which aren’t.

 

Let’s try a test, run this…

 

insert mytest ( id ) values ( 1 )

 

Because the id is 1 and we are checking 1 % 2 which returns 1 then the trigger just returns – hence not seeing the rest of the trigger.

 

insert mytest ( id ) values ( 2 )

 

Executing that you see a different execution path through the trigger.

 

 

The end – hopefully you’ll find it useful; there is so much more that SQL Profiler has to offer you could spend a day (or more) on it!

 

How do we go about calculating paths between two connecting nodes; say we have a table with two columns – FromNode and ToNode; our query has a starting node and a destination node and we need to calculate all possible paths between the start and destination and return them to the user. This comes from a question I answered on the Microsoft NNTP news groups.

 

Example

 

**Note, the script to build the table of nodes and data is attached at the bottom of this blog entry.

 

From                 To

100035      100046     

100035      100327     

100327      100046     

etc…

 

The output needs to look like this, basically the relational node structure pairs flattened into an enumerated list (probably a CSV or XML in the real world).

 

p0          p1          p2          p3          p4          p5          edges

----------- ----------- ----------- ----------- ----------- ----------- -----------

100035      100046      -1          -1          -1          -1          1

100035      100327      100046      -1          -1          -1          2

100035      100334      100046      -1          -1          -1          2

100035      100351      100046      -1          -1          -1          2

100035      100036      100046      -1          -1          -1          2

100035      100065      100046      -1          -1          -1          2

100035      100179      100046      -1          -1          -1          2

100035      100182      100046      -1          -1          -1          2

100035      100076      100046      -1          -1          -1          2

100035      100078      100046      -1          -1          -1          2

100035      100094      100046      -1          -1          -1          2

100035      100125      100046      -1          -1          -1          2

100035      100150      100046      -1          -1          -1          2

100035      100158      100046      -1          -1          -1          2

100035      100267      100046      -1          -1          -1          2

100035      100372      100046      -1          -1          -1          2

100035      100036      100150      100046      -1          -1          3

100035      100036      100158      100046      -1          -1          3

100035      100036      100125      100046      -1          -1          3

 

 

So, how do we go about doing this in SQL Server? SQL Server 2005 introduced a new operator on the FROM clause – the APPLY operator; this allows us to take a row from the left table joining and passing a value into a table value function, the table value function runs and returns a recordset for each row passed in; you have CROSS and OUTER functionality, the CROSS is a bit confusing – to me its more like an INNER JOIN in that no rows are produced if the table valued function doesn’t return anything whereas the OUTER you always get a row from the table on the left hand side of the join.

 

What does that mean in English? Lets look at some examples.

 

Take our table valued function that really does the biz for us…

 

CREATE FUNCTION tvfn_get_nodes_for(

    @FromNode as int,

    @StartAtNode int,

    @StopAtNode int )

RETURNS TABLE

AS

    RETURN(

        SELECT ToNode

        FROM Paths

        WHERE FromNode = @FromNode

          AND FromNode <> @StopAtNode

          AND ToNode   <> @StartAtNode

        )

GO

 

When we run our table valued function in isolation we get 873 rows…

 

SELECT *

FROM tvfn_get_nodes_for( 100035, 100035, 100046 )

 

So, that’s the nodes that 100035 is connected to, we need to do it recursively for each path through the tree, we can use the OUTER APPLY functionality to do that, so take the following example.

 

declare @nodes table (

    start_node int not null

)

insert @nodes values( 100036 )

insert @nodes values( 100041 )

insert @nodes values( 100042 )

insert @nodes values( 100043 )

insert @nodes values( 100044 )

 

SELECT n.start_node, count(*)

FROM @nodes n

    OUTER APPLY tvfn_get_nodes_for( n.start_node, 100035, 100046 )

GROUP BY n.start_node

We are driving the table valued function from the @nodes table variable, the table valued function will execute 5 times, once for 100036, then 100041 etc… The output is shown below…

 

start_node  rows

----------- -----------

100036      738

100041      5

100042      25

100043      20

100044      24

 

(5 row(s) affected)

 

Cool or what! The biggest use I have for this CROSS APPLY stuff is for the dynamic management views to get at what SQL Server is doing – a lot of them have been implemented as table valued functions.

 

So that’s the guts of it, a real query that gives 5 node hops is shown below, increasing the number of hops is simple – you just add more OUTER APPLY’s and the other bits.

 

DECLARE @FromNode int,

        @ToNode   int

SET @FromNode = 100035

SET @ToNode   = 100046

 

SELECT *,

       edges = 5 - ( CASE WHEN p2 = -1 THEN 4 WHEN p3 = -1 THEN 3 WHEN p4 = -1 THEN 2 WHEN p5 = -1 THEN 1 ELSE 0 END )

FROM (

    SELECT p0 = p1.FromNode,

           p1 = COALESCE( p1.ToNode, -1 ),

           p2 = COALESCE( p2.ToNode, -1 ),

           p3 = COALESCE( p3.ToNode, -1 ),

           p4 = COALESCE( p4.ToNode, -1 ),

           p5 = COALESCE( p5.ToNode, -1 )

    FROM paths AS p1

        OUTER APPLY tvfn_get_nodes_for( p1.ToNode, @FromNode, @ToNode ) AS p2

        OUTER APPLY tvfn_get_nodes_for( p2.ToNode, @FromNode, @ToNode ) AS p3

        OUTER APPLY tvfn_get_nodes_for( p3.ToNode, @FromNode, @ToNode ) AS p4

        OUTER APPLY tvfn_get_nodes_for( p4.ToNode, @FromNode, @ToNode ) AS p5

    WHERE p1.FromNode = @FromNode

    ) AS d

WHERE ( p1 = @ToNode OR p2 = @ToNode OR p3 = @ToNode OR p4 = @ToNode OR p5 = @ToNode )

  AND ( p1 NOT IN ( p2, p3, p4, p5 )

     OR p1 = -1 )

  AND ( p2 NOT IN ( p1, p3, p4, p5 )

     OR p2 = -1 )

  AND ( p3 NOT IN ( p1, p2, p4, p5 )

     OR p3 = -1 )

  AND ( p4 NOT IN ( p1, p2, p3, p5 )

     OR p4 = -1 )

  AND ( p5 NOT IN ( p1, p2, p3, p4 )

     OR p5 = -1 )

ORDER BY edges

 

The usual recommendations apply, functions are usually resource intensive but in this situation I can’t honestly think of a better way of doing it without resorting to more linear approaches; of course – if the data was stored x.x.x.x. in the first place we wouldn’t have this problem.

It took me a little while to google for the answer so I'm blogging it so I don't need to spend the time doing that again :)

If you have installed a 64 bit OS, for instance I have two machines, both running Windows 2003 Server x64; that means when you run the default PERFMON you are actually running the 64bit version of the application which can only load 64bit counter dlls; so - any counters for 32bit applications will be missing.

So, guess what - on one of my machines I'm running 64 bit SQL Server 2005 but on the other I'm just running the 32 bit but on a 64 bit OS so it runs on Wow - good old test environment.... So, the counters don't appear!

Resolution, you load the 32bit version of PERFMON to be able to read the 32 bit SQL Server counters, that is easily done by running this: mmc /32 perfmon.msc.

Thanks to Wei Lu for that answer on the public forums. What ever did we do without search engines?

Martin Bell, SQL Server MVP is kick starting the Scottish User Group again, if you are in Scotland and want the user group to work please show Martin your support and attend and contribute; we really want a Scottish group to be successful.

We will be running a competition for the best idea for subject to be covered by a session of the User Group the winner will get a registration for Visual Studio 2005 (to be claimed by 31/12/2006).

We also have a number of other prizes on to be offered during the evening.

Registration is at 6pm, evening will commence at 6:30pm and finish 9:30pm.

6:30pm - 6.45pm
Round Table Discussion
Update on what''s been going on and is going on in the SQL Server space. Bring your SQL problems and ask the audience, bounce ideas - anything related to SQL Server.

6.45pm - 8.00pm
"Identifying Performance Problems with SQL 2005 Profiler"
Martin Bell, SQL Server MVP

In this session Martin will demonstrate how to use SQL Profiler to find poorly performing SQL.

8.00 - 8.15pm
Break

8.15pm - 9.30pm
SQL 2005 - XML
Martin Bell, SQL Server MVP

This talk will show you how to use the new XML datatype in SQL 2005, changes to the FOR XML statement, XML data manipulation and using XQuery.

Registration
============
Registration for the event is mandatory, so if you want to come or even thinking about coming then please register.

http://sqlserverfaq.com?eid=89


Location
========
Microsoft Edinburgh
127 George Street
Edinburgh EH2 4JN

Directions
==========
Directions can be found here: http://www.microsoft.com/uk/about/map-edinburgh.mspx


Many thanks,
Tony.

Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
ph. +44(0)1582 346161 | mb. +44(0)796 816 0362