Are the posted questions getting worse?

  • Jeff Moden (8/18/2011)


    I've never used them. I've always used something else because they didn't use to be available. Not sure I'd actually use them, either, because of the following restriction...

    No complex problems for me to use them on, but for the same reason as Jeff, I've felt they were a little crippled.

  • SQLkiwi (8/18/2011)


    Ninja's_RGR'us (8/18/2011)


    Please post your results with the version(s) you tested on.

    Added my contribution.

    Thanks a lot, but it still looks like we have work to do on this one. 😉

  • Jeff Moden (8/18/2011)


    Ninja's_RGR'us (8/18/2011)


    Sorry to break the rules but..

    I've possibly discovered a bug that corrupts your db when doing a deployment script. I've tested on 2K5 sp3 and 2K8 R2 RTM, I need more feedback for other versions (up to denali).

    I don't have a test server to install anything I want so I'd love to get your help.

    http://www.sqlservercentral.com/Forums/FindPost1161964.aspx

    All you have to do is restore a db and run a test script and post the errors you get, if any.

    Please post your results with the version(s) you tested on.

    TIA.

    Very cool, Remi. It's not often that one can play with a "hard-break"... they always seem to be intermittent. I'll try to make some time to look at it. Thanks for posting the setup code.

    Well I'm batting 1000 ATM. Fails in 2k5, 2k8 R2 and Denali CTP 3 with latest updates. And with over 100 runs on my end.

    Looks like part of the equation is "massive" data and insert that cause page split on the clustered index (index id 1 being the one corrupt on 2k5, same thing on denali, different on 2k8 R2). A similar code with only 1 row doesn't fail on Denali (thanks to SqlWiki for that one).

    If you ever wanted to play with a hard error, I think this is your chance ;-).

    I'd still love to see the results with 2k5 SP4 & 2K8 SP2 (or whatever the latest is right now). Anybody's welcomed!

    Looks more and more like I'll be posting a connect item on this.

  • Jeff Moden (8/18/2011)


    Almost forgot to ask... how many "fields" did you have per "record" and what was the average length of each record in bytes for the delimited string method you were using? I'm, of course, asking so I can set up a test. Thanks again, Jason.

    Now your testing my memory. That was my previous place. I limited the delimited strings to 8k and did have to break up some of our inserts because of that limit.

    Talking person records with most string fields being limited to varchar(75) and some we allowed up to 250, but I can't remember exactly how many fields. We took the records and inserted across 7 different tables. But we were also able to break the inserts up across 7 different synchronous proc calls as opposed to a single proc call and let the app take care of the heavy lifting (OO languages are better at looping).

    I don't have much for good details, just general memories at this time. Too many months and too much work in between that job and current.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I wonder if the new splitter from the "Tally OH!" article would have helped here. I've not done any heavy testing with it against VARCHAR(MAX) but some folks have stated that it works just fine. Personally, I find that any splitter that "joins" with VARCHAR(MAX) is instantly twice as slow.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • WayneS (8/18/2011)


    Steve Jones - SSC Editor (8/18/2011)


    Well it looks like we might have an SSC party at the PASS Summit after all for those of you that are going. No deal with PASS, but we'll likely sell tickets and have fun.

    Look for some details soon.

    It looks like the stars are starting to align with respect to me being able to go to the PASS Summit. My company needs to confirm that they have the money in the training budget for the conference, plus two pre-con days. Then, when presenting at a UG last night, the UG leader might forward his free ticket to me if no-one contacts him about it shortly (he can't go due to work commitments). Come on stars, just a little more alignment is needed.

    Tell your company I'll be there :w00t::w00t::hehe::hehe::smooooth::smooooth::smooooth:

    btw: Yesterday I was in a meeting concerning our new SAN. On the training topic the inital course was 3 days and was worth $2300.

    Do I need to point out you can pick the sessions you are interested in, related to the issues you have, MSSupport is on site and available for all your questions, a number of skilled peers will be on site and will be happy to reflect / help out on your concerns.

    And of course there are a number of MVP on site. Always nice to have a chat with these guys/galls !;-)

    Getting the confirmation you are doing fine with your systems is as valuable as fixing your issues - which I'm sure you'll be able to fix some, like I did over the years I've been granted the privilege to attend SQLPass.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Jeff Moden (8/18/2011)


    I've never used them. I've always used something else because they didn't use to be available. Not sure I'd actually use them, either, beccause of the following restriction...

    Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

    ... and because if the data is already in SQL Server somewhere, I don't need to pass a TVP for that information to a stored procedure.

    The restriction is for the procedure that the parameter is passed to. You can't update or delete from that table within the procedure.

    They're incredibly useful when writing parent and multiple child records from a front end with one procedure. I'll have to haul out the front-end code that's used for one, but if you profile all that happens is that a table variable is defined and the rows inserted into it before the procedure starts.

    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
  • Jeff Moden (8/18/2011)


    I wonder if the new splitter from the "Tally OH!" article would have helped here. I've not done any heavy testing with it against VARCHAR(MAX) but some folks have stated that it works just fine. Personally, I find that any splitter that "joins" with VARCHAR(MAX) is instantly twice as slow.

    A random tangent on the "Tally Oh!" article - I was just playing around to see if I can modify it to work on varbinary(max) data that is full of 0x00's. For this application, speed isn't as big an issue - but using a traditional tally table takes me three passes and a temp table with this particular mess o' bits, and I wanted to get it down to one pass. So thanks again, Jeff, for taking the time to write up the results of your long and harried sessions with the dust bunnies and the beer popsicles. They are very much appreciated.

    -Ki

  • Thank You everyone for the input on TVP. It looks like something worth investing my time to do some performance testing.

    -Roy

  • Jeff Moden (8/18/2011)


    I wonder if the new splitter from the "Tally OH!" article would have helped here. I've not done any heavy testing with it against VARCHAR(MAX) but some folks have stated that it works just fine. Personally, I find that any splitter that "joins" with VARCHAR(MAX) is instantly twice as slow.

    The main advantage of a TVP over a string splitter is that multiple columns with enforced data types are easier to manage. The second is that you can define a difference between NULL and an empty string much more easily. The third is performance, if the data is already in an array or dataset in the application, since you can eliminate a fair number of steps by using a TVP instead of concatenating and then re-parsing the data.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • For those who followed. Paul and I have each posted a connect item for a nasty issue with rollback (corrupts your db). It seems to take a rather weird series of dml + ddl to reproduce. But it certainly looks like any normal deployement script I've seen out there.

    Rollback / Corruption bug

    https://connect.microsoft.com/SQLServer/feedback/details/684806/rollback-causes-db-to-go-suspect-only-repairable-with-repair-allow-data-loss

    Paul's version (errors out with object not found, but the object is actually there)

    https://connect.microsoft.com/SQLServer/feedback/details/684732/error-3316-in-routine-xdesrmreadwrite-rollbacktolsn

  • ...Ninja's_RGR'us Please post your results with the version(s) you tested on.

    TIA.

    2005 SP4 x86 - Same issue

    http://www.sqlservercentral.com/Forums/FindPost1162490.aspx

    -Dan B

  • skrilla99 (8/19/2011)


    ...Ninja's_RGR'us Please post your results with the version(s) you tested on.

    TIA.

    2005 SP4 x86 - Same issue

    http://www.sqlservercentral.com/Forums/FindPost1162490.aspx

    -Dan B

    Thanks again, that was the last big elephant in the room. ;-).

    So latest version of 2k5 and denali CTP 3 are confirmed. I think we can assume the same behavior anywhere in between.

    If anybody else wants to try the script on the latest sps for 2k8 and R2 it would be great.

    TIA.

  • Ninja's_RGR'us (8/19/2011)


    For those who followed. Paul and I have each posted a connect item for a nasty issue with rollback (corrupts your db). It seems to take a rather weird series of dml + ddl to reproduce. But it certainly looks like any normal deployement script I've seen out there.

    Rollback / Corruption bug

    https://connect.microsoft.com/SQLServer/feedback/details/684806/rollback-causes-db-to-go-suspect-only-repairable-with-repair-allow-data-loss

    Paul's version (errors out with object not found, but the object is actually there)

    https://connect.microsoft.com/SQLServer/feedback/details/684732/error-3316-in-routine-xdesrmreadwrite-rollbacktolsn

    Cool - good going Remi on the perseverance in seeing this through. I bet when Paul files something, it gets plenty of immediate attention.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Ninja's_RGR'us (8/19/2011)


    skrilla99 (8/19/2011)


    ...Ninja's_RGR'us Please post your results with the version(s) you tested on.

    TIA.

    2005 SP4 x86 - Same issue

    http://www.sqlservercentral.com/Forums/FindPost1162490.aspx

    -Dan B

    Thanks again, that was the last big elephant in the room. ;-).

    So latest version of 2k5 and denali CTP 3 are confirmed. I think we can assume the same behavior anywhere in between.

    If anybody else wants to try the script on the latest sps for 2k8 and R2 it would be great.

    TIA.

    I have VMs for both, so I'll try to get to that this weekend. (A gentle prodding reminder wouldn't hurt...)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 15 posts - 29,146 through 29,160 (of 66,749 total)

You must be logged in to reply to this topic. Login to reply