Find executions count of SQL objects

Purpose:

To find how many times objects of some type of SQL objects were executed.

Solution:

This script refer to views - but you can change it to any other type by replacing the call from sys.views to the other relevant sys table.

; WITH QueryExecutions AS

(

SELECT

QueryText =

SUBSTRING

(

SQLTexts.text  ,

QueryStats.statement_start_offset / 2 ,

(

CASE QueryStats.statement_end_offset

WHEN -1 THEN LEN (SQLTexts.text)

ELSE QueryStats.statement_end_offset / 2

END

- QueryStats.statement_start_offset / 2

)

+ 1

) ,

ExecutionCount = QueryStats.execution_count

FROM

sys.dm_exec_query_stats AS QueryStats

CROSS APPLY

sys.dm_exec_sql_text (QueryStats.sql_handle) AS SQLTexts

)

SELECT

ViewSchema = SCHEMA_NAME (Views.schema_id) ,

ViewName = Views.name ,

UsageCount = SUM (ISNULL (QueryExecutions.ExecutionCount , 0))

FROM sys.views AS Views

LEFT OUTER JOIN

QueryExecutions

ON QueryExecutions.QueryText LIKE N'%[. ' + CHAR(9) + CHAR(10) + CHAR(13) + N'\[]' + 

Views.name + N'[ ;' + 

CHAR(9) + CHAR(10) + CHAR(13) + '\]]%' 

ESCAPE N'\' COLLATE DATABASE_DEFAULT

GROUP BY SCHEMA_NAME (Views.schema_id) , Views.name

ORDER BY UsageCount DESC, ViewSchema ASC, ViewName ASC

GO

Indexed Views does not using the index of the view

Problem:

Indexed Views does not using the index of the view...

Solution:

Add WITH (NOEXPAND) to the select (after the view):

FROM dbo.indexedViewName iv WITH (NOEXPAND)

But why the optimizer didn't use the index of the view without this hint?

If a query contains references to columns that are present both in an indexed view and base tables, it can happen....

Note: in order to use NOEXPAND, those settings must set to be ON: 

• ANSI_NULLS
• QUOTED_IDENTIFIER
• ANSI_WARNINGS
• CONCAT_NULL_YIELDS_NULL
• ANSI_PADDING
• ARITHABORT

Cannot schema bind view because name 'TableName' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Error message:

Cannot schema bind view 'dbo.ViewName' because name 'TableName' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Cause and solution:

Call tables with the schema name: dbo.TableName (not only TableName).

It's can happen in indexed views query.

Get the current date (today) at 00:00:00

DECLARE @CurrentDate datetime

SELECT @CurrentDate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))