Tools and techniques for untangling monster views (nested views)

Question

First a bit of context (venting). I'm working with a new vendor product which is really nothing more than a SQL Server database with a .NET interface.

First few weeks with this client and users are complaining about performance constantly with even the most bread and butter reporting functionality. I finally get DB access and discover the worst set of nested views (Is nested view a good database design?) I've seen in my life. We're talking a tree of views that is 5 nodes deep and each node has 3-5 leaves. So a single query has 20+ views behind it. It's mind bogglingly bad. The explain plan looks like a fractal after ten million iterations. I can't even read it.

I need to untangle at least this one monster view. But I have no ideas on how. I found this post (http://www.midnightdba.com/Jen/2010/06/detangling-nested-views/) which has a handy SQL script that lists the referred objects but that information doesn't get me terribly far on getting the actual table references for each field in the parent view. If I were to do it by hand, it would probably take 2 weeks. If I got it down to inline form, are there any tools that could remove redundancies and unused fields and optimize the query?

Answer 1

You can enumerate nested views through this query:

SELECT DISTINCT
    sd.referenced_major_id,
    OBJECT_NAME(sd.referenced_major_id) as This_view,
    sd.object_id,
    OBJECT_NAME(sd.object_id) as need_this_view_to_work

FROM
    sys.sql_dependencies sd
WHERE 
    EXISTS (SELECT 1 FROM sys.views v WHERE sd.object_id = v.object_id)
    AND EXISTS (SELECT 1 FROM sys.views v WHERE sd.referenced_major_id = v.object_id);

Answer 2

To enumerate the nesting

;WITH
cRefobjects AS (
-- Anchor level a view which refers to another view
SELECT DISTINCT
sed.referencing_id,
sed.referenced_id,
s.name AS SchemaName,
o.name as ViewName,
Convert(nvarchar(2000), N'>>'+ s.name+'.'+o.name) COLLATE DATABASE_DEFAULT as NestViewPath,
o.type_desc,
1 AS level
FROM sys.sql_expression_dependencies sed
INNER JOIN sys.objects o ON
o.object_id = sed.referencing_id and
o.type_desc ='VIEW'
INNER JOIN sys.schemas AS s ON
s.schema_id = o.schema_id
LEFT OUTER JOIN sys.objects o2 ON
o2.object_id = sed.referenced_id and
o2.type_desc IN ('VIEW')
WHERE
o2.object_id is null
 
UNION ALL
-- Recursive part, retrieve any higher level views, build the path and increment the level
SELECT
sed.referencing_id,
sed.referenced_id,
s.name AS sch,
o.name as viewname,
Convert(nvarchar(2000),cRefobjects.NestViewPath + N'>' + s.name+'.'+o.name) COLLATE DATABASE_DEFAULT,
o.type_desc,
level + 1 AS level
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects o ON
o.object_id = sed.referencing_id and
o.type_desc ='VIEW'
INNER JOIN sys.schemas AS s ON
s.schema_id = o.schema_id
INNER JOIN cRefobjects ON
sed.referenced_id = cRefobjects.referencing_id
)
SELECT DISTINCT SchemaName+'.'+ViewName as ViewName, NestViewPath, type_desc, level
FROM cRefobjects
WHERE level > 1
ORDER BY level desc, viewname
OPTION (MAXRECURSION 32);

From a blog post I wrote here :-

https://www.sqlservice.se/how-to-query-metadata-to-discover-nested-views/