2

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?

1
  • 2
    The free SQL Sentry Plan Explorer will at the very least show you the base tables being used (run a select from the view and switch to the join diagram tab), but even that may not prove to be overly helpful, depending on how complicated that diagram ends up being... Disclaimer: I work for SQL Sentry. Apr 24 '14 at 11:18
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);
0

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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.