Performance Tuning with SQLFacts Tools

Question

Performance Tuning with SQLFacts Tools

Wingenious

SSCommitted

Points: 1646
More actions
April 7, 2023 at 12:00 am

#4167755

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.

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

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

eliassal Default port Points: 1467 More actions · Answer 1

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

Wingenious SSCommitted Points: 1646 More actions · Answer 2

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 ago by Wingenious.

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

Wingenious SSCommitted Points: 1646 More actions · Answer 3

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.

eliassal Default port Points: 1467 More actions · Answer 4

eliassal

Default port

Points: 1467

April 7, 2023 at 12:46 pm

#4170533

Here is the output of the sql statement

eliassal Default port Points: 1467 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 6

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)

Wingenious SSCommitted Points: 1646 More actions · Answer 7

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.

eliassal Default port Points: 1467 More actions · Answer 8

eliassal

Default port

Points: 1467

April 7, 2023 at 8:03 pm

#4170693

I agree with Wingeniuos,

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 9

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)

Wingenious SSCommitted Points: 1646 More actions · Answer 10

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.

eliassal Default port Points: 1467 More actions · Answer 11

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