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

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

Useful System functions in Sql server

Following are the some of the most useful system functions   which helps us to get useful information.

·         Command: sp_helpdb ‘database_name’

Example: EXEC sp_helpdb N'AdventureWorks2012';

Details: informs about a specified database.

·         Command: sp_helpindex ‘table_name’

Example: EXEC sp_helpindex N'tblEmployee';

Details: gives information about all the indexes created on defined table.

·         Command: sp_helpstats ‘table_name’

Example: EXEC sp_helpstats N'tblEmployee';

Details: gives information about columns and indexes on the specified table.

·         Command: sp_ helptext ‘table_name’

Example: EXEC sp_helptext N'sp_getAllEmployees';

Details: gives the definition of Stored Procedures, User defined Functions, User defined Rule, Trigger, view, Computed Column.

·         Command: sp_help  ‘Object_name’

Example: EXEC sp_help N'sp_getAllEmployees';

Details: gives information about database object, like in this case, for SP, it gets sp details and parameters information like name, type, length etc .

Find the list of indexes in sql database.

Tip #5 Find List of Indexes in Sql Database.

I am posting some of the most useful queries which help a dba/ developer to optimize database performance. Please reference to different Tips in other blogs as well.

Following query will help in getting the list of all indexes in your database. Run the following query in your sql server query window:

SELECT *
INTO #tmp
FROM (
 SELECT DISTINCT T.[name] AS [table_name]
  ,I.[name] AS [index_name]
  ,AC.[name] AS [column_name]
  ,I.[type_desc]
  ,I.[is_unique]
  ,I.[data_space_id]
  ,I.[ignore_dup_key]
  ,I.[is_primary_key]
  ,I.[is_unique_constraint]
  ,I.[fill_factor]
  ,I.[is_padded]
  ,I.[is_disabled]
  ,I.[is_hypothetical]
  ,I.[allow_row_locks]
  ,I.[allow_page_locks]
  ,IC.[is_descending_key]
  ,IC.[is_included_column]
 FROM sys.[tables] AS T
 INNER JOIN sys.[indexes] I
  ON T.[object_id] = I.[object_id]
 INNER JOIN sys.[index_columns] IC
  ON I.[object_id] = IC.[object_id]
 INNER JOIN sys.[all_columns] AC
  ON T.[object_id] = AC.[object_id]
   AND IC.[column_id] = AC.[column_id]
 WHERE T.[is_ms_shipped] = 0
  AND I.[type_desc] <> 'HEAP'
 ) m
ORDER BY m.table_name
 ,m.index_name

SELECT * FROM #tmp