SQL Server Mentalist

Hi Folks,

In this article we are going to cover How to List expensive queries CHILD.

In this post we are going to discuss following points:

• Problem Statement of SQL Script:
• Description of SQL Script:
• Input Parameter of SQL Script
• SQL Script Code
• User Level to execute

Problem Statement of SQL Script:

How to find List of expensive queries within a view?

Description of SQL Script:

This query will list most expensive queries in a view.

Input Parameter of SQL Script

ColumnName, ViewType,ViewName,View Schema Id

For help use these tables:

SELECT *
FROM sys.all_columns

SELECT *
FROM sys.all_views

SQL Script Code

SELECT clmns.NAME AS [Name]
    ,clmns.column_id AS [ID]
    ,clmns.is_nullable AS [Nullable]
    ,clmns.is_computed AS [Computed]
    ,CAST(ISNULL(cik.index_column_id, 0) AS BIT) AS 
    [InPrimaryKey]
    ,clmns.is_ansi_padded AS [AnsiPaddingStatus]
    ,CAST(clmns.is_rowguidcol AS BIT) AS [RowGuidCol]
    ,ISNULL(ic.is_not_for_replication, 0) AS 
    [NotForReplication]
    ,CAST(COLUMNPROPERTY(clmns.object_id, clmns.NAME, 
            N'IsFulltextIndexed') AS BIT) AS [IsFullTextIndexed]
    ,CAST(ISNULL(COLUMNPROPERTY(clmns.object_id, clmns.NAME, 
                N'IsDeterministic'), 0) AS BIT) AS 
    [IsDeterministic]
    ,CAST(ISNULL(COLUMNPROPERTY(clmns.object_id, clmns.NAME, 
                N'IsPrecise'), 0) AS BIT) AS [IsPrecise]
    ,CAST(ISNULL(cc.is_persisted, 0) AS BIT) AS [IsPersisted]
    ,ISNULL(clmns.collation_name, N'') AS [Collation]
    ,CAST(ISNULL((
                SELECT TOP 1 1
                FROM sys.foreign_key_columns AS colfk
                WHERE colfk.parent_column_id = clmns.column_id
                    AND colfk.parent_object_id = clmns.
                    object_id
                ), 0) AS BIT) AS [IsForeignKey]
    ,clmns.is_identity AS [Identity]
    ,CAST(ISNULL(ic.seed_value, 0) AS BIGINT) AS [IdentitySeed]
    ,CAST(ISNULL(ic.increment_value, 0) AS BIGINT) AS 
    [IdentityIncrement]
    ,(
        CASE 
            WHEN clmns.default_object_id = 0
                THEN N''
            WHEN d.parent_object_id > 0
                THEN N''
            ELSE d.NAME
            END
        ) AS [Default]
    ,(
        CASE 
            WHEN clmns.default_object_id = 0
                THEN N''
            WHEN d.parent_object_id > 0
                THEN N''
            ELSE schema_name(d.schema_id)
            END
        ) AS [DefaultSchema]
    ,(
        CASE 
            WHEN clmns.rule_object_id = 0
                THEN N''
            ELSE r.NAME
            END
        ) AS [Rule]
    ,(
        CASE 
            WHEN clmns.rule_object_id = 0
                THEN N''
            ELSE schema_name(r.schema_id)
            END
        ) AS [RuleSchema]
    ,CAST(clmns.is_filestream AS BIT) AS [IsFileStream]
    ,CAST(clmns.is_sparse AS BIT) AS [IsSparse]
    ,CAST(clmns.is_column_set AS BIT) AS [IsColumnSet]
    ,usrt.NAME AS [DataType]
    ,sclmns.NAME AS [DataTypeSchema]
    ,ISNULL(baset.NAME, N'') AS [SystemType]
    ,CAST(CASE 
            WHEN baset.NAME IN (
                    N'nchar'
                    ,N'nvarchar'
                    )
                AND clmns.max_length <> - 1
                THEN clmns.max_length / 2
            ELSE clmns.max_length
            END AS INT) AS [Length]
    ,CAST(clmns.precision AS INT) AS [NumericPrecision]
    ,CAST(clmns.scale AS INT) AS [NumericScale]
    ,ISNULL(xscclmns.NAME, N'') AS [XmlSchemaNamespace]
    ,ISNULL(s2clmns.NAME, N'') AS [XmlSchemaNamespaceSchema]
    ,ISNULL((
            CASE clmns.is_xml_document
                WHEN 1
                    THEN 2
                ELSE 1
                END
            ), 0) AS [XmlDocumentConstraint]
    ,CASE 
        WHEN usrt.is_table_type = 1
            THEN N'structured'
        ELSE N''
        END AS [UserType]
FROM sys.all_views AS v
INNER JOIN sys.all_columns AS clmns ON clmns.object_id = v.
    object_id
LEFT OUTER JOIN sys.indexes AS ik ON ik.object_id = clmns.object_id
    AND 1 = ik.is_primary_key
LEFT OUTER JOIN sys.index_columns AS cik ON cik.index_id = ik.
    index_id
    AND cik.column_id = clmns.column_id
    AND cik.object_id = clmns.object_id
    AND 0 = cik.is_included_column
LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = clmns.
    object_id
    AND ic.column_id = clmns.column_id
LEFT OUTER JOIN sys.computed_columns AS cc ON cc.object_id = clmns.
    object_id
    AND cc.column_id = clmns.column_id
LEFT OUTER JOIN sys.objects AS d ON d.object_id = clmns.
    default_object_id
LEFT OUTER JOIN sys.objects AS r ON r.object_id = clmns.
    rule_object_id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.
    user_type_id
LEFT OUTER JOIN sys.schemas AS sclmns ON sclmns.schema_id = usrt.
    schema_id
LEFT OUTER JOIN sys.types AS baset ON (
        baset.user_type_id = clmns.system_type_id
        AND baset.user_type_id = baset.system_type_id
        )
    OR (
        (baset.system_type_id = clmns.system_type_id
            )
        AND (baset.user_type_id = clmns.user_type_id
            )
        AND (baset.is_user_defined = 0)
        AND (baset.is_assembly_type = 1)
        )
LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns ON 
    xscclmns.xml_collection_id = clmns.xml_collection_id
LEFT OUTER JOIN sys.schemas AS s2clmns ON s2clmns.schema_id = 
    xscclmns.schema_id
WHERE (clmns.NAME = @_msparam_0)
    AND (
        (v.type = @_msparam_1)
        AND (
            v.NAME = @_msparam_2
            AND SCHEMA_NAME(v.schema_id) = @_msparam_3
            )
        )

User Level to execute

300

Hope you will like How to List expensive queries CHILD.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :

Link Resource Website

For More information related to BI World visit my Mentalist Blog

SQL Server Mentalist >> SQL Learning Blog

Business Intelligence Mentalist >> BI World

Infographic Mentalist >> Image worth explaining thousand Words

Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ

DBA Mentalist >>Advance SQL Server Blog

Microsoft BI Mentalist >> MS BI Development Update

Connect With me on

| FaceBook |Twitter | linkedIn| Google+ | WordPress | RSS |