Professional Web Applications Themes

RowLock - Microsoft SQL / MS SQL Server

Hey guys, I've been having performance problems and I'm going to through Microsoft's Kb articles but I was wondering if what I'm seeing is a "Tell-tell" sign of something obvious. I've noticing that many of the queries that "time-out" seem to run to completion when I add the rowlock hint to the select like ... SELECT DISTINCT CDR.EXTENSION FROM CDR WITH (ROWLOCK) WHERE LastEditTimeStamp IS NULL and PostTimeStamp is NULL AND PID IS NULL Does this point to anything obvious to anyone? -- Any and all contributions are greatly appreciated ... Regards TJ...

  1. #1

    Default RowLock

    Hey guys, I've been having performance problems and I'm going to through
    Microsoft's Kb articles but I was wondering if what I'm seeing is a
    "Tell-tell" sign of something obvious.

    I've noticing that many of the queries that "time-out" seem to run to
    completion when I add the rowlock hint to the select like ...

    SELECT DISTINCT CDR.EXTENSION FROM CDR WITH (ROWLOCK)
    WHERE LastEditTimeStamp IS NULL and PostTimeStamp is NULL AND PID IS NULL

    Does this point to anything obvious to anyone?
    --
    Any and all contributions are greatly appreciated ...
    Regards TJ


    TJ Guest

  2. #2

    Default Re: RowLock

    Thanks Andrew, I think I'm goint to pull the little hair I have left -- out.
    The thing that so interesting is that when I run the stored procedure from
    QA(from the hosting server or my workstation) it run within 1 sec but when
    it's executed from ASP it times-out?

    This is a good one ...

    "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
    news:e7f5chKRDHA.2096TK2MSFTNGP12.phx.gbl...
    > I suspect this is due to the fact the WHERE clause is highly unlikely to
    use
    > an index and will most likely scan the table. If that is the case a table
    > or page level lock is more efficient. IS NULL is not always usable in an
    > index seek especially where there are usually many rows that are null.
    >
    > --
    >
    > Andrew J. Kelly
    > SQL Server MVP
    >
    >
    > "TJ" <nospamnowhere.com> wrote in message
    > news:e7jNLwJRDHA.2224TK2MSFTNGP12.phx.gbl...
    > > Hey guys, I've been having performance problems and I'm going to through
    > > Microsoft's Kb articles but I was wondering if what I'm seeing is a
    > > "Tell-tell" sign of something obvious.
    > >
    > > I've noticing that many of the queries that "time-out" seem to run to
    > > completion when I add the rowlock hint to the select like ...
    > >
    > > SELECT DISTINCT CDR.EXTENSION FROM CDR WITH (ROWLOCK)
    > > WHERE LastEditTimeStamp IS NULL and PostTimeStamp is NULL AND PID IS
    NULL
    > >
    > > Does this point to anything obvious to anyone?
    > > --
    > > Any and all contributions are greatly appreciated ...
    > > Regards TJ
    > >
    > >
    >
    >

    TJ Guest

  3. #3

    Default Re: RowLock

    I bet that the connection for ASP is set up as Serializable instead of Read
    committed. You should be able to check that out with profiler to see what
    is going on.

    --

    Andrew J. Kelly
    SQL Server MVP


    "TJ" <nospamnowhere.com> wrote in message
    news:%23hUppuMRDHA.2144TK2MSFTNGP11.phx.gbl...
    > Thanks Andrew, I think I'm goint to pull the little hair I have left --
    out.
    > The thing that so interesting is that when I run the stored procedure from
    > QA(from the hosting server or my workstation) it run within 1 sec but when
    > it's executed from ASP it times-out?
    >
    > This is a good one ...
    >
    > "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
    > news:e7f5chKRDHA.2096TK2MSFTNGP12.phx.gbl...
    > > I suspect this is due to the fact the WHERE clause is highly unlikely to
    > use
    > > an index and will most likely scan the table. If that is the case a
    table
    > > or page level lock is more efficient. IS NULL is not always usable in
    an
    > > index seek especially where there are usually many rows that are null.
    > >
    > > --
    > >
    > > Andrew J. Kelly
    > > SQL Server MVP
    > >
    > >
    > > "TJ" <nospamnowhere.com> wrote in message
    > > news:e7jNLwJRDHA.2224TK2MSFTNGP12.phx.gbl...
    > > > Hey guys, I've been having performance problems and I'm going to
    through
    > > > Microsoft's Kb articles but I was wondering if what I'm seeing is a
    > > > "Tell-tell" sign of something obvious.
    > > >
    > > > I've noticing that many of the queries that "time-out" seem to run to
    > > > completion when I add the rowlock hint to the select like ...
    > > >
    > > > SELECT DISTINCT CDR.EXTENSION FROM CDR WITH (ROWLOCK)
    > > > WHERE LastEditTimeStamp IS NULL and PostTimeStamp is NULL AND PID IS
    > NULL
    > > >
    > > > Does this point to anything obvious to anyone?
    > > > --
    > > > Any and all contributions are greatly appreciated ...
    > > > Regards TJ
    > > >
    > > >
    > >
    > >
    >
    >

    Andrew J. Kelly Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139