Performance Tuning with SQLFacts Tools

  • Comments posted to this topic are about the item Performance Tuning with SQLFacts Tools

    Creator of SQLFacts, a free suite of tools for SQL Server database professionals.

  • Thanks for the info, very usefull. However, when I try to execute

    QueryTracker

    for a SP in a DB whaich has collation "French_CI_AI"and Server default same , master DB as well

    I get

    Msg 468, Level 16, State 9, Line 208

    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "French_CI_AI" in the equal to operation.

    Msg 468, Level 16, State 9, Line 231

    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "French_CI_AI" in the charindex operation.

    Msg 468, Level 16, State 9, Line 253

    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "French_CI_AI" in the charindex operation.

    So I applied to another DB with "SQL_Latin1_General_CP1_CI_AS" collation and get the same error

     

    Have you seen this?

    Thanks

     

  • I have heard of the problem before, when there's a difference in collation between the instance and the database. I think you are saying the instance and the database are using the same collation, so I'm not sure where the discrepancy is occurring. I could try to avoid the problem by adding a COLLATE clause to every comparison between DMV string data and catalog view string data, but that's a project I have not yet done.

    • This reply was modified 1 year, 1 month ago by  Wingenious.

    Creator of SQLFacts, a free suite of tools for SQL Server database professionals.

  • What does this query (executed in the context of the database in question) say about collations?...

    SELECT SERVERPROPERTY('Collation'), DATABASEPROPERTYEX(DB_NAME(), 'Collation')

    There are other tools in the suite that look for mismatches in collation.

     

    Creator of SQLFacts, a free suite of tools for SQL Server database professionals.

  • Here is the output of the sql statement

  • yes indeed it iis a lot of work to add Collate in every equality, I started to look at it but then I stopped

  • First, let me say that I have a grand and genuine appreciation for people that write tools and share them.

    I don't, however, have an appreciation for code that doesn't even have a flower box that tells what what the code is supposed to do.

    --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)

  • There are three text files of documentation (about 100K total), including one titled README that tells what the code is supposed to do.

    Creator of SQLFacts, a free suite of tools for SQL Server database professionals.

  • I agree with Wingeniuos,

  • Wingenious wrote:

    There are three text files of documentation (about 100K total), including one titled README that tells what the code is supposed to do.

    Ah.  Got it.  Thanks.

    You might want to add that to future write-ups.  You might also want to add to the README note on your front page indicating that it contains the list and descriptions for what each script does.  It'll help folks "figure things out" a bit more quickly.

    I'll still stand my ground on my previous comment, though. 😉

    --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)

  • A new version of SQLFacts has been released.

    Many tools were adjusted to address a potential issue with collations other than default US, as mentioned by eliassal (see above).

    THANKS!  --  to eliassal for testing the change in an environment experiencing the problem.

    Creator of SQLFacts, a free suite of tools for SQL Server database professionals.

  • Thanks Wingenious, you can say Salam ELIAS instead of my nickname eliassal if you like 🙂

Viewing 12 posts - 1 through 11 (of 11 total)

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