Find the dependent sql objects on a defined object

Find the dependent sql objects on a defined object:

Below query shows the dependent sql objects like tables, views etc. of defined object (here. YourObject).


SELECT referencing_schema_name, referencing_entity_name,referencing_id, referencing_class_desc, is_caller_dependentFROM sys.dm_sql_referencing_entities ('YourObject', 'OBJECT');GO

When was last tempdb cleared

Following query will help us to find when was tempdb cleared? Means when was last usage stats cleared? This will show how many days are passed when usage stats are cleared.

SELECT  DATEDIFF(DAY, sd.crdate, GETDATE()) AS days_historyFROM    sys.sysdatabases sdWHERE   sd.[name] = 'tempdb' ;

Finding the Last Sql Server Restarted.

Following query will help in finding the last Sql Server restarted date.

This is very useful query when you are looking for unused indexes. Before dropping unused indexes,  always check last sql server restarted by using following query.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

#Optimization TIP: Finding the Missing Indexes in your Sql Database.

Following Query will help you find the missing indexes in your database along with Total Costing of that Index. 

PS: Adding new Indexes is a tricky job. It may vary from scenario to scenario. You can add Indexes having highest Total costing and having least or none included columns. Complex Composite Indexes should be avoided.

SELECT *
FROM (
 SELECT OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) AS [TableName]
  ,ISNULL(dm_mid.equality_columns, '') + CASE 
   WHEN dm_mid.equality_columns IS NOT NULL
    AND dm_mid.inequality_columns IS NOT NULL
    THEN ','
   ELSE ''
   END + ISNULL(dm_mid.inequality_columns, '') [Column]
  ,dm_mid.included_columns
  ,ROUND(dm_migs.avg_total_user_cost * dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans), 0) AS [Total Cost]
  ,'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') + CASE 
   WHEN dm_mid.equality_columns IS NOT NULL
    AND dm_mid.inequality_columns IS NOT NULL
    THEN '_'
   ELSE ''
   END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + ']' + ' ON ' + dm_mid.statement + ' (' + ISNULL(dm_mid.equality_columns, '') + CASE 
   WHEN dm_mid.equality_columns IS NOT NULL
    AND dm_mid.inequality_columns IS NOT NULL
    THEN ','
   ELSE ''
   END + ISNULL(dm_mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
 FROM sys.dm_db_missing_index_groups dm_mig
 INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle
 INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle
 WHERE dm_mid.database_ID = DB_ID()
 ) x
ORDER BY x.[Total Cost] DESC
GO

Optimization Techniques- Finding Most expensive Queries in Sql Server

Following query will help you finding the most expensive queries in your sql database. This query will tell you about Total Logical Reads, Total Logical Writes, Last Logical Reads, Last Logical Writes etc.

Run the following query in your sql server query window:

SELECT TOP 10 qs.execution_count

        ,qs.total_logical_reads

        ,qs.last_logical_reads

        ,qs.total_logical_writes

        ,qs.last_logical_writes

        ,qs.total_worker_time

        ,qs.last_worker_time

        ,qs.total_elapsed_time / 1000000 total_elapsed_time_in_S

        ,qs.last_elapsed_time / 1000000 last_elapsed_time_in_S

        ,qs.last_execution_time

        ,qp.query_plan

        ,SUBSTRING(qt.TEXT, (qs.statement_start_offset / 2) + 1, (

                       (

                               CASE qs.statement_end_offset

                                      WHEN - 1

                                              THEN DATALENGTH(qt.TEXT)

                                      ELSE qs.statement_end_offset

                                      END - qs.statement_start_offset

                               ) / 2

                       ) + 1)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_worker_time DESC -- CPU time

SQL Optimization Techniques- Finding Heavy Stored Procedures in Sql Server

Following query will help you finding the most heavy stored procedures in your sql database. This query will tell you Number of Execution Count, AVG CPU time to execute the respective SP and Total CPU Time of the respective SP. 

Run the following query in your sql server query window:

SELECT DB_NAME(dest.dbid) AS [Database name]

        ,OBJECT_NAME(dest.objectid, dest.dbid) AS [Stored procedure name]

        ,[Execution Count]

        ,[Avg CPU time (ms)]

        ,[Total CPU time (ms)] [Avg Elapsed time (ms)]

        ,[Creation time]

        ,[Last Execution time] [Total Elapsed time (ms)]

        ,[Plan Count]

        ,getdate() AS [Recorded date]

        ,sql_handle

        ,dest.TEXT AS 'Text'

FROM (

        SELECT TOP 100 sql_handle

               ,COUNT(DISTINCT plan_handle) AS [Plan Count]

               ,MAX(execution_count) AS [Execution Count]

               ,SUM(total_worker_time / execution_count) / 1000 AS [Avg CPU time (ms)]

               ,SUM(total_elapsed_time / execution_count) / 1000 AS [Avg Elapsed time (ms)]

               ,MAX(creation_time) AS [Creation time]

               ,MAX(last_execution_time) AS [Last Execution time]

               ,SUM(total_worker_time / 1000) AS [Total CPU time (ms)]

               ,SUM(total_elapsed_time / 1000) AS [Total Elapsed time (ms)]

        FROM sys.dm_exec_query_stats

        GROUP BY sql_handle

        ORDER BY SUM(total_worker_time / execution_count) / 1000 DESC

        ) deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

WHERE dest.dbid = DB_ID();