At the risk of asking a stupid question -
Is anyone familiar with either a query against the systables or maybe
an outside tool that will provide a list of the most common joins that
have been made in user created views? I'm not talking about table
relationships that are established at the database level, but rather,
I'm referring to the ability to find which joins have been utilized in
poorly constructed databases where no relationships were established
in the first place.
Thanks in advance -
Cindy T.

Do you mean listing all poor running queries? You could list all in Profiler

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL




[quoted text, click to view]

[quoted text, click to view]

I am not sure that this would even be possible with an AI tool. You
would have to find VIEWs that restore split attributes, are used to
fix denormalized tables, etc.

[quoted text, click to view]

You might want to run a Profiler trace for a while, send output to a
new table, then query the table for accesses to a table that you're
interested in. You can also look at sysobjects and syscomments to
determine which views access a given table (especially if the Profiler
output cites the view rather than the underlying tables; I've mostly
used Profiler to deal with stored procedures accessing tables directly,

Thanks Jack and Celko -
I've done quite a bit of searching - find lots of tools for query
optimization and database documentation, but nothing along the lines
of "what joins tend to be used in this database" (thinking views more
than anything else). I can understand why it's not out there - the
many possible combinations of syntax, use of aliases, subqueries, etc.
- it would be a task, that's for sure.

I'm a contractor who is often asked to do reporting on databases that
are not only undocumented but also that lack table relationships -
where a great deal of time is spent trying to understand the
relationships between the tables that have been used to date anyway in
existing views.

Maybe I just need to explore query optimization tools more, including
the profiler - because in order for them to analyze what's being done,
joins are identified - my goal is trying to pull out that information
in a quick to access format that covers the entire database rather
than than just focusing on a single query plan.

If we had the ability to generate a database table that lists joins
that were used - imagine a tool where you plug in the names of 4
tables, for example, and then you get a report where you see all the
fields, join types, and join type operators that were used between
those 4 tables - along with the frequency of use. It seems it would
go a long way toward getting a database in better shape to document
common relationships.

Does that sound like a pipe dream?



[quoted text, click to view]

In "Microsoft SQL Server 2005 Waits and Queues" document you can find some
queries to find what index would be useful. It might not be exacly what you
seak but it will givie you other hints also.

MarcM

[quoted text, click to view]

[quoted text, click to view]

Good idea - thanks all!