Forum Replies Created

Viewing 15 posts - 16,996 through 17,010 (of 49,552 total)

  • RE: Optimizing Stored Procedures that utilize only local variables (and lots of them)

    mtassin (8/22/2012)

    I do recall back with SQL 7/2000 that IN was very expensive. Figures that at some point MS would make it better. I recall watching IN get...

    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

    August 22, 2012 at 12:45 pm

    #1528857

  • RE: Optimizing Stored Procedures that utilize only local variables (and lots of them)

    Evil Kraig F (8/22/2012)

    where dbo.ClientEnrollment.Void is null and dbo.ClientEnrollment. Enrollment_Date between

    '''+convert(char(10),@FromDate,101)+''' and '''+convert(char(10),@ThruDate,101)+''''

    Nope, this is injectable.

    Depends. Very very hard to inject anything with 10 characters to play with...

    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

    August 22, 2012 at 12:18 pm

    #1528838

  • RE: Optimizing Stored Procedures that utilize only local variables (and lots of them)

    mtassin (8/22/2012)

    The above could instead be constructed as a JOIN then, which generally performs better than using the IN operator.

    No it doesn't.

    http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/

    General conclusion: In is a very slight bit...

    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

    August 22, 2012 at 12:08 pm

    #1528832

  • RE: Any info on Deadlock detection algorithms?

    Yes, that could be the cause.

    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

    August 22, 2012 at 10:53 am

    #1528785

  • RE: Single sql column defined as 2 separate indexes-unique and non unique?

    John Mitchell-245523 (8/22/2012)

    GilaMonster (8/22/2012)

    The thing is, they're not quite the same indexes. The unique index (for reasons of index architecture) is essentially an index on the key column, include the...

    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

    August 22, 2012 at 10:51 am

    #1528783

  • RE: high reads

    BaldingLoopMan (8/22/2012)

    I thought perimeter sniffing only applied to local variables or input params getting defaulted.

    No, not at all. Parameter sniffing problems occur when SQL reuses a plan compiled for...

    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

    August 22, 2012 at 10:49 am

    #1528780

  • RE: Confirm: new index on specific filegroup spreads over data files

    Indianrock (8/22/2012)

    Thanks. I think my example was poor, but was trying to create a scenario where sql would attempt to put too much data in the file that had more...

    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

    August 22, 2012 at 10:46 am

    #1528779

  • RE: Single sql column defined as 2 separate indexes-unique and non unique?

    I would tend to agree with you.

    The thing is, they're not quite the same indexes. The unique index (for reasons of index architecture) is essentially an index on the...

    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

    August 22, 2012 at 9:21 am

    #1528725

  • RE: Confirm: new index on specific filegroup spreads over data files

    As I said, it's not the space on the drive that affects proportional fill, it is the space free in the data file.

    Autogrow's irrelevant in your case, there's 5GB free...

    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

    August 22, 2012 at 9:19 am

    #1528721

  • RE: high reads

    Gut feel from what you've said is a parameter sniffing problem. Seen that many, many times. Need the actual execution plan of a slow execution to tell for sure (so...

    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

    August 22, 2012 at 8:32 am

    #1528677

  • RE: DELETE operation immediately in SUSPENDED mode

    Yes, it's deleting. The PageIOLatch indicates that the waits are due to fetching the pages off disk.

    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

    August 22, 2012 at 8:27 am

    #1528674

  • RE: Optimizing Stored Procedures that utilize only local variables (and lots of them)

    jshahan (8/22/2012)

    I think to paraphrase each of your responses, you are advising against a sledgehammer approach in favor of nuanced troubleshooting that identifies precisely where problems actually exist and addressing...

    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

    August 22, 2012 at 8:21 am

    #1528668

  • RE: DELETE operation immediately in SUSPENDED mode

    Suspended means it's waiting for something. Check what it's waiting for (wait type, wait resource in sys.dm_exec_requests), it doesn't have to be a lock.

    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

    August 22, 2012 at 8:12 am

    #1528658

  • RE: Are the posted questions getting worse?

    jcrawf02 (8/22/2012)

    GilaMonster (8/22/2012)

    BrainDonor (8/22/2012)

    GilaMonster (8/22/2012)

    Nitpicking I can take, this is getting well beyond that.

    I have to say Gail that it was a fascinating thread up to a point, and your...

    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

    August 22, 2012 at 8:07 am

    #1528653

  • RE: DTA - Database tuning advisour?

    DTA will give you index suggestions. Don't create them all without checking. Test them out, implement the ones that help, ignore any that don't. The tool is not infallible.

    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

    August 22, 2012 at 7:29 am

    #1528624

Viewing 15 posts - 16,996 through 17,010 (of 49,552 total)