Professional Web Applications Themes

Phantom locks? - Microsoft SQL / MS SQL Server

If sp_lock doesn't show you anything, you probably don't have a locking problem. Have you looked at sp_who2 or queried the sysprocesses table? If a process is waiting for anything, and there are lots of reasons for waits besides locking. it will show up in sysprocesses. Sysprocesses will also tell you how long a process has been waiting. Have you tried selecting from the table when it is 'slow'? If it is only INSERTs that is causing the problem, I might suggest you are having page splitting. Have you checked the fragmentation of the table? (DBCC SHOWCONTIG) Checking it when ...

  1. #1

    Default Re: Phantom locks?

    If sp_lock doesn't show you anything, you probably don't have a locking
    problem. Have you looked at sp_who2 or queried the sysprocesses table? If a
    process is waiting for anything, and there are lots of reasons for waits
    besides locking. it will show up in sysprocesses. Sysprocesses will also
    tell you how long a process has been waiting.

    Have you tried selecting from the table when it is 'slow'? If it is only
    INSERTs that is causing the problem, I might suggest you are having page
    splitting. Have you checked the fragmentation of the table? (DBCC
    SHOWCONTIG) Checking it when the table is performing well, and then when it
    is slow can give you very useful information. Let us know what you find.

    --
    HTH
    ----------------
    Kalen Delaney
    SQL Server MVP
    www.SolidQualityLearning.com


    "David" <com> wrote in message
    news:google.com... 


    Kalen Guest

  2. #2

    Default Re: Phantom locks?

    On 29 Jul 2003 10:31:07 -0700, com (David) wrote:
    .... 
    ....

    Is there any DRI with another table that has to be scanned?

    Does your accessing code open a dynamic cursor on the table?

    Just stabbing at possibilities here.

    J.

    JXStern Guest

  3. #3

    Default Re: Phantom locks?

    Fortunately/unfortunately, I will have to wait until the problem crops
    up again. I've been scanning your Inside SQL 7 book and your article
    in the current issue of SQL Server Magazine looked like it contained
    some very helpful things for troubleshooting this problem... The next
    time it happens, I will have a better idea of where to look!

    I did find that fragmentation was minimal compared to when the table
    was healthy. Selecting was not a problem the last time this hapened,
    though I seem to recall that it can be...the behavior was
    inconsistent. My guess is that the initial insert problem caused a
    multitude of other performance issues, but I can't be sure until it
    happens again.

    Thank you,

    David


    "Kalen Delaney" <com> wrote in message news:<phx.gbl>... [/ref]
    David Guest

Similar Threads

  1. Phantom Transparency (?!) CS2
    By FARBgerECHT@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 2
    Last Post: September 25th, 11:17 AM
  2. phantom records
    By TB in forum MySQL
    Replies: 2
    Last Post: March 29th, 10:26 PM
  3. phantom fonts
    By Diane_Scheaffer@adobeforums.com in forum Adobe Indesign Windows
    Replies: 5
    Last Post: August 13th, 05:42 PM
  4. USB mouse phantom devices
    By Jonathan in forum Debian
    Replies: 3
    Last Post: August 14th, 08:20 AM
  5. Phantom Downloads
    By Ed Wurster in forum Adobe Photoshop Elements
    Replies: 3
    Last Post: August 5th, 03:38 AM

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