Forum Replies Created

Viewing 15 posts - 25,756 through 25,770 (of 49,552 total)

  • RE: Why does this query cause index scan when the index is covering?

    SELECT COUNT(*)

    FROM Table1 WHERE Col1 = 0

    vs

    SELECT COUNT(*)

    FROM Table1 WHERE Col1 = Table2.ID

    Query 1:

    Please count how many people there are in the telephone directory with a surname 'Van Westhuyzen'

    Query 2:

    Please...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    June 3, 2011 at 2:04 pm

    #1333924

  • RE: Why does this query cause index scan when the index is covering?

    SELECT COUNT(*)

    FROM Table1 WHERE Col1 = Table2.ID

    That can't be seeked. There's no single value to be looked up. It could do one seek for each value of Table2.ID (that's what...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    June 3, 2011 at 1:59 pm

    #1333918

  • RE: (Nolock) created blocking?

    GSquared (6/3/2011)

    GilaMonster (6/3/2011)

    GSquared (6/3/2011)

    The original post asserted no pending DDL. Worth double-checking, of course.

    The Sch-S is the only lock a select with nolock takes, that's only blocked by an Sch-M....

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    June 3, 2011 at 1:57 pm

    #1333917

  • RE: (Nolock) created blocking?

    GSquared (6/3/2011)

    The original post asserted no pending DDL. Worth double-checking, of course.

    The Sch-S is the only lock a select with nolock takes, that's only blocked by an Sch-M. Need to...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    June 3, 2011 at 1:49 pm

    #1333908

  • RE: (Nolock) created blocking?

    clare.xia (6/3/2011)

    Thank you for the reply! I see the blocking from Activity Monitor, it says, the select session is 'Blocking' and the 'Insert/update/detelet' session is blocked by the session Id...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    June 3, 2011 at 1:27 pm

    #1333899

  • RE: Are the posted questions getting worse?

    SQLRNNR (6/3/2011)

    What would you say to finding MVPs who are plagiarizing content to either gain MVP or to retain MVP?

    I wouldn't say a thing. I'd just report them to Global...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    June 3, 2011 at 1:22 pm

    #1333898

  • RE: Are the posted questions getting worse?

    SQLRNNR (6/3/2011)

    GilaMonster (6/3/2011)

    Garadin (6/3/2011)

    I saw the big bowl of alphabet soup get added onto Gus' signature and figured this would be the place to figure out what led up to...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    June 3, 2011 at 1:19 pm

    #1333896

  • RE: tempdb full because of row versioning

    Are there any transactions concurrently reading from tableA?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    June 3, 2011 at 1:18 pm

    #1333893

  • RE: tempdb full because of row versioning

    Yes. No.

    The pseudo-tables are materialised from the version store in SQL 2005+

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    June 3, 2011 at 10:20 am

    #1333791

  • RE: Are the posted questions getting worse?

    Garadin (6/3/2011)

    Oh, and you forgot MCP! :hehe:

    Yes. Intentionally.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    June 3, 2011 at 9:23 am

    #1333761

  • RE: Are the posted questions getting worse?

    Stefan Krzywicki (6/3/2011)

    GilaMonster (6/3/2011)

    Garadin (6/3/2011)

    I saw the big bowl of alphabet soup get added onto Gus' signature and figured this would be the place to figure out what led up...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    June 3, 2011 at 8:49 am

    #1333743

  • RE: tempdb full because of row versioning

    That's not going to be fixed with an isolation level change. As I mentioned earlier...

    GilaMonster (5/31/2011)

    Triggers use tempdb to materialise the inserted and deleted tables. From the message (dbo.deleted) that's...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    June 3, 2011 at 8:36 am

    #1333726

  • RE: Are the posted questions getting worse?

    Garadin (6/3/2011)

    I saw the big bowl of alphabet soup get added onto Gus' signature and figured this would be the place to figure out what led up to it. ...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    June 3, 2011 at 8:30 am

    #1333719

  • RE: What is LOP_BEGIN_XACT

    SQL Server != open source.

    Portions are undocumented because they're not guaranteed to remain the same. Documented features have to be formally deprecated before they can be removed, formally noted as...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    June 3, 2011 at 4:24 am

    #1333571

  • RE: tempdb full because of row versioning

    Sure. Use SET TRANSACTION ISOLATION LEVEL to set whatever isolation you want.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    June 3, 2011 at 4:23 am

    #1333570

Viewing 15 posts - 25,756 through 25,770 (of 49,552 total)