July 9, 2010 at 5:38 am
Hello:
I 'm starting to manage a very big non-documented database created by another people.
I wish to know if there is a way (an script or something like that) to identify wich are the most frequently used joins. Perhaps by using DMVs data I can retrieve wich columns are being used to build joins between two tables.
This would help me to build some kind of preliminary documentation.
Thanks!
July 9, 2010 at 5:51 am
Example:
If we got the traditional Orders table and OrderItems table as follows:
Orders (id int,OrderDate smalldatetime)
OrderItems ( ItemID int, OrderID int , ItemPrice money)
Several storeprocs and views might do this :
select * from orders o inner join orderitems oi on oi.orderid=o.id
Well... I need something that returns me this information:
--------------------------------------------------------
SP/View name | Column A | Column B |
--------------------------------------------------------
View1 | Orders.ID | OrderItem.OrderID |
--------------------------------------------------------
July 9, 2010 at 7:36 am
lverea-783150 (7/9/2010)
Hello:I 'm starting to manage a very big non-documented database created by another people.
I wish to know if there is a way (an script or something like that) to identify wich are the most frequently used joins. Perhaps by using DMVs data I can retrieve wich columns are being used to build joins between two tables.
This would help me to build some kind of preliminary documentation.
Thanks!
Yes, there is a way, but I've never tried to put it all together like this before. Basically you can query the execution plan cache using DMV's. The execution plans are XML. So you can put together an XQuery query against the plan cache to pull out the join operations to see what is being joined to what. If you aggregate, you should be able to see common JOINs. Another option, and I don't think it's quite as good, is to again hit the DMV's to pull the queries out of the cache and try to parse the JOIN statements. I think that'll be messy.
But honestly, why? I'd focus on the queries themselves. Identify the most frequently called, the longest running, most expensive. The rest will take care of itself.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply