Forum Replies Created

Viewing 15 posts - 5,251 through 5,265 (of 49,552 total)

  • RE: Row locks not escalating to table locks after 5000

    As I said, I was connected to the 2014 instance for all of these tests.

    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

    July 18, 2015 at 11:37 am

    #1813511

  • RE: AlwaysOn transaction log filling up

    Don't shrink the log. It's not going to help. All you're doing there is forcing the log to grow again.

    What are the settings of the log backup in the maint...

    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

    July 18, 2015 at 11:37 am

    #1813510

  • RE: Row locks not escalating to table locks after 5000

    Jacob Wilkins (7/18/2015)

    So, just to double check, when you run the second INSERT after the TRUNCATE (all within the same transaction) in 2012 SP2, do you see the 6200ish page...

    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

    July 18, 2015 at 10:50 am

    #1813507

  • RE: Row locks not escalating to table locks after 5000

    Jacob Wilkins (7/18/2015)

    1) Both INSERTs in a single transaction, separated by a TRUNCATE TABLE x:

    First INSERT escalates successfully, second INSERT triggers escalation but takes out page locks anyway. This...

    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

    July 18, 2015 at 10:49 am

    #1813506

  • RE: Query Performance

    Ah, so all 8 get called each time, in sequence, every time? That wasn't clear.

    I suspect you may be falling into this trap a little: http://www.sqlservercentral.com/articles/Performance+Tuning/115825/

    Are these queries of Stories...

    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

    July 18, 2015 at 10:33 am

    #1813503

  • RE: Row locks not escalating to table locks after 5000

    Hehe, I think (at least for 2012 2014 and the insert after truncate case), the answer here is that SQL's a hell of a lot sneakier than we thought.

    The second...

    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

    July 18, 2015 at 10:18 am

    #1813500

  • RE: Indexes with more writes than reads

    lmarkum (7/17/2015)

    I see the same issue occcuring with a lot of the primary keys as well. Would the concensus be the same there too?

    No. Primary keys are part of...

    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

    July 18, 2015 at 9:46 am

    #1813498

  • RE: Query Performance

    If you're indexing is correct, it shouldn't be scanning.

    Copying to a table variable each time each procedure is run and then filtering the table var won't reduce the number of...

    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

    July 18, 2015 at 9:25 am

    #1813497

  • RE: Row locks not escalating to table locks after 5000

    Jacob Wilkins (7/18/2015)

    I tested in 2008 R2 SP3, where the bug is fixed (see the link provided in my earlier post)

    I've seen a bug 'closed as fixed' still present in...

    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

    July 18, 2015 at 9:22 am

    #1813496

  • RE: Row locks not escalating to table locks after 5000

    jotate (7/18/2015)

    Results are 1 and then 6433 on my 2008 R2 SP3 instance.

    2 and 2 on 2012 2014 RTM, with two lock escalation events fired.

    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

    July 18, 2015 at 9:15 am

    #1813495

  • RE: Query Performance

    First question is why are you trying to reduce the number of procedures? In SQL, generalising code often ends up degrading performance. What are you specifically trying to achieve here?

    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

    July 18, 2015 at 8:50 am

    #1813490

  • RE: Row locks not escalating to table locks after 5000

    Jacob Wilkins (7/18/2015)

    I didn't say it was blocked; I said lock escalation failed, because it seems the engine can't escalate to X from Sch-M (because Sch-M is a more protective...

    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

    July 18, 2015 at 8:47 am

    #1813489

  • RE: Row locks not escalating to table locks after 5000

    Jacob Wilkins (7/17/2015)

    Since this is all within a transaction, that Sch-M lock is held for the duration of the transaction, and when lock escalation is triggered by the second insert,...

    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

    July 18, 2015 at 6:46 am

    #1813482

  • RE: ...and suddenly a stored procedure takes too much time

    nonlinearly (7/18/2015)

    It was the statistics. I issue the command EXEC sp_updatestats 'RESAMPLE'.

    Not necessarily. Updating the stats would have invalidated all plans, so if it was a bad plan, the stats...

    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

    July 18, 2015 at 6:39 am

    #1813481

  • RE: Does database server location impact performance

    Vaguely, yes. You really should take more direct metrics.

    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

    July 17, 2015 at 2:02 pm

    #1813429

Viewing 15 posts - 5,251 through 5,265 (of 49,552 total)