Find Most executed queries

SELECT TOP 50
QueryState.execution_count
,OBJECT_NAME(objectid)
,query_text = SUBSTRING(
qt.text,
QueryState.statement_start_offset/2,
(CASE WHEN QueryState.statement_end_offset = -1
THEN len(convert(nvarchar(max), qt.text)) * 2
ELSE QueryState.statement_end_offset
END - QueryState.statement_start_offset)/2)
,qt.dbid
,dbname = db_name(qt.dbid)
,qt.objectid
FROM sys.dm_exec_query_stats QueryState
CROSS APPLY sys.dm_exec_sql_text(QueryState.sql_handle) as qt
ORDER BY QueryState.execution_count DESC

Get all queries running against any specific table

/*

Get list of all queries hitting any specific tables

*/

SELECT DISTINCT TOP 100

ProcedureName = OBJECT_SCHEMA_NAME(sqlTxt.objectid) + '.' + OBJECT_NAME(sqlTxt.objectid)

,SQLStatement = SUBSTRING(

sqlTxt.Text

,(QueryState.statement_start_offset/2)+1

,CASE QueryState.statement_end_offset

WHEN -1 THEN DATALENGTH(sqlTxt.text)

ELSE QueryState.statement_end_offset

END - (QueryState.statement_start_offset/2) + 1

)

,DiskReads = QueryState.total_physical_reads --- Disk reads

,MemoryReads = QueryState.total_logical_reads --–Logical Reads are memory reads

,ExecutionCount = QueryState.execution_count --Execution Count

,CPUTime = QueryState.total_worker_time  --CPU Time

,DiskWaitAndCPUTime = QueryState.total_elapsed_time

,MemoryWrites = QueryState.max_logical_writes

,DateCached = QueryState.creation_time

,DatabaseName = DB_Name(sqlTxt.dbid) --Database name

,LastExecutionTime = QueryState.last_execution_time

,cte.*

FROM sys.dm_exec_query_stats AS QueryState

CROSS APPLY sys.dm_exec_sql_text(QueryState.sql_handle) AS sqlTxt

CROSS APPLY sys.dm_sql_referenced_entities(

OBJECT_SCHEMA_NAME(sqlTxt.objectid) + '.' + OBJECT_NAME(sqlTxt.objectid)

, 'OBJECT'

) cte

WHERE sqlTxt.dbid = db_id() --Get detail for current database

AND cte.referenced_schema_name + '.' + cte.referenced_entity_name = 'dbo.tblEmployee'

Missing index with create statements

Missing index :- Scripts will help you to get all the missing index and prepare script for new index.

/*
Description:- This Query will provide you detail of missing indexes on a table and also 
 prepare sql script for new index
*/
SELECT
DISTINCT
   @@SERVERNAME AS [ServerName]
   , DB_NAME() AS [DatabaseName]
   , SCHEMA_NAME([systemObject].[schema_id]) AS [SchemaName]
,[systemObject].Object_ID AS ObjectID
   , [systemObject].[name] AS [ObjectName]
   , CASE [systemObject].[type]
      WHEN 'U' THEN 'Table'
      WHEN 'V' THEN 'View'
      ELSE 'Unknown'
     END AS [ObjectType]
   , [MissingIndexDetail].[equality_columns] AS [EqualityColumns]
   , [MissingIndexDetail].[inequality_columns] AS [InequalityColumns]
   , [MissingIndexDetail].[included_columns] AS [IncludedColumns]
   , [MissingIndexGroupState].[user_seeks] AS [ExpectedIndexSeeksByUserQueries]
   , [MissingIndexGroupState].[user_scans] AS [ExpectedIndexScansByUserQueries]
   , [MissingIndexGroupState].[last_user_seek] AS [ExpectedLastIndexSeekByUserQueries]
   , [MissingIndexGroupState].[last_user_scan] AS [ExpectedLastIndexScanByUserQueries]
   , [MissingIndexGroupState].[avg_total_user_cost] AS [ExpectedAvgUserQueriesCostReduction]
   , [MissingIndexGroupState].[avg_user_impact] AS [ExpectedAvgUserQueriesBenefitPct],
   IndexName='[ix_' + tbl.name + N'_' 
                    + REPLACE(REPLACE(REPLACE(REPLACE(
                        ISNULL(equality_columns,N'')+ 
                        CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN N'_' ELSE N'' END
                        + ISNULL(inequality_columns,''),',','')
                        ,'[',''),']',''),' ','_') 
                    + CASE WHEN included_columns IS NOT NULL THEN N'_includes' ELSE N'' END + N']' ,

     IndexCreateScripts=N'CREATE INDEX [ix_' + tbl.name + N'_' 
                    + REPLACE(REPLACE(REPLACE(REPLACE(
                        ISNULL(equality_columns,N'')+ 
                        CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN N'_' ELSE N'' END
                        + ISNULL(inequality_columns,''),',','')
                        ,'[',''),']',''),' ','_') 
                    + CASE WHEN included_columns IS NOT NULL THEN N'_includes' ELSE N'' END + N'] ON ' 
                    + [statement] + N' (' + ISNULL(equality_columns,N'')
                    + CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN N', ' ELSE N'' END
                    + CASE WHEN inequality_columns IS NOT NULL THEN inequality_columns ELSE N'' END + 
                    ') ' + CASE WHEN included_columns IS NOT NULL THEN N' INCLUDE (' + included_columns + N')' ELSE N'' END
                    + N' WITH (' 
                        + N'FILLFACTOR=80, ONLINE=ON' 
                    + N')'
                    + N';'
FROM 
   [sys].[dm_db_missing_index_details] AS [MissingIndexDetail]
   CROSS APPLY sys.dm_db_missing_index_columns  ([MissingIndexDetail].index_handle)
   LEFT JOIN [sys].[dm_db_missing_index_groups] AS [MissingIndexGroup]
      ON [MissingIndexDetail].[index_handle] = [MissingIndexGroup].[index_handle]
   LEFT JOIN [sys].[dm_db_missing_index_group_stats] AS [MissingIndexGroupState]
      ON [MissingIndexGroup].[index_group_handle] = [MissingIndexGroupState].[group_handle]
   INNER JOIN [sys].[objects] AS [systemObject]
      ON [MissingIndexDetail].[object_id] = [systemObject].[object_id]
inner join sys.databases db on [MissingIndexDetail].database_id=db.database_id
inner join sys.tables tbl on [systemObject].object_id=tbl.object_id
WHERE
   [MissingIndexDetail].[database_id] = DB_ID()  -- Look in the Current Database
   AND [systemObject].[type] IN ('U','V')     -- Look in Tables & Views
   AND [systemObject].[is_ms_shipped] = 0x0   -- Exclude System Generated Objects
   --and( tbl.name like '%Accounting%' or tbl.name like '%treasury%') --Add table name for filter
--and [MissingIndexGroupState].[avg_user_impact]>=50; -- filter impact percentage 


go

How we can determine table column dependencies within a SQL database?

I came a situation during some analysis where i had to figure out all dependency of a column across database. This also required when we do some changes on table columns and wanted to know the impact of such changes. One way is that  we can use SYSCOMMENTS table.  It is a table which contains entries for each views, rules, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure. The column TEXT in the syscomments table contains the actual code for all these objects, and knowing it you can write a code to check the dependencies. I am explaining different approach.

/* Table Name for which we need to find the dependency of columns.*/

Declare @TableName varchar(max)='tblSupplierRequestOrder'

/* Column name for which we need to find the dependnecy in a database*/

Declare @ColumnName varchar(max)='OrderDate'

select  OBJECT_NAME(k.parent_object_id) as parentTableName

          , c1.name as parentColumn

          , OBJECT_NAME(k.referenced_object_id) as referencedTableName

          , c2.name as referencedColumn

    from    sys.foreign_keys k

            inner join sys.foreign_key_columns f

              on  f.parent_object_id = k.parent_object_id

            inner join sys.columns c1

              on  c1.column_id = f.parent_column_id

              and c1.object_id = k.parent_object_id

            inner join sys.columns c2

              on  c2.column_id = f.referenced_column_id

              and c2.object_id = k.referenced_object_id

    where   c2.name = @ColumnName

    and     OBJECT_NAME(k.referenced_object_id) =@TableName

Common uses of stored Procedure sp_MSforeachtable

We will get the brief idea about undocumented procedure sp_MSforeachtable of the Master database.

sp_MSforeachtable can be used to loop through all the tables in your databases.

Some of the basic usages of this stored procedures are:-

1.     Display the size of all tables in a database

USE NORTHWIND

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

2.      Display Number of Rows in all Tables in a database

USE ANYDBNAME

EXEC sp_MSforeachtable 'SELECT ''?'', Count(*) as NumberOfRows FROM ?'

3.     Rebuild all indexes of all tables in a database

USE ANYDBNAME

GO

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

GO

4.     Disable all constraints of all tables in a database

USE ANYDBNAME

EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

5.     Disable all Triggers of all tables in a database

USE ANYDBNAME

EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

6.     Delete all data from all tables in your database

-- disable referential integrity

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

GO

EXEC sp_MSForEachTable '

 IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1

  DELETE FROM ?

 else

  TRUNCATE TABLE ?

'

GO

-- enable referential integrity again

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

GO

To RESEED all table to 0, use this script

EXEC sp_MSForEachTable '

IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1

DBCC CHECKIDENT (''?'', RESEED, 0)

'

GO

7.     Reclaim space from dropped variable-length columns in tables or indexed views

USE ANYDBNAME

EXEC sp_MSforeachtable 'DBCC CLEANTABLE(0,''?'') WITH NO_INFOMSGS; ';

8.     Update Statistics of all Tables in a database

USE ANYDBNAME

EXEC sp_MSforeachtable 'UPDATE statistics ? WITH ALL'

Please comment if you have any query.

Get each table space and their rows count

Sometime we need to know how much table space are used to store the data and also wish to know the number of rows stored in it, this query help you to get all the detail.

SELECT
 SCHEMA_NAME(o.schema_id) + ',' + OBJECT_NAME(p.object_id) AS name,
 reserved_page_count * 8 as space_used_kb,
 row_count
FROM sys.dm_db_partition_stats AS p
JOIN sys.all_objects AS o ON p.object_id = o.object_id
WHERE o.is_ms_shipped = 0
ORDER BY SCHEMA_NAME(o.schema_id) + ',' + OBJECT_NAME(p.object_id)

What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL server’s query optimizer for a stored procedure or ad hoc query. Execution plans are very useful for helping a developer understand and analyze the performance characteristics of a query or stored procedure, since the plan is used to execute the query or stored procedure.

In many SQL systems, a textual execution plan can be obtained using a keyword such as EXPLAIN, and visual representations can often be obtained as well. In Microsoft SQL Server, the Query Analyzer has an option called “Show Execution Plan” (located on the Query drop down menu). If this option is turned on, it will display query execution plans in a separate window when a query is run.

HOW WE CAN GET WHO IS CONNECTED TO DATABASE?

Some time we need to know that who is using database as of now or who all are connected to specific database.

We can use sysprocesses to know the all the detail for specific database.

sysprocesses contains information about processes that are running on an instance of SQL Server. These processes can be client processes or system processes. To access sysprocesses, you must be in the master database context, or you must use the master.dbo.sysprocesses three-part name.

If a user has VIEW SERVER STATE permission on the server, the user will see all executing sessions in the instance of SQL Server; otherwise, the user will see only the current session.

Column name	Data type	Description
spid	smallint	SQL Server session ID.
kpid	smallint	Windows thread ID.
blocked	smallint	ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified). -2 = The blocking resource is owned by an orphaned distributed transaction. -3 = The blocking resource is owned by a deferred recovery transaction. -4 = Session ID of the blocking latch owner could not be determined due to internal latch state transitions.
waittype	binary(2)	Reserved.
waittime	bigint	Current wait time in milliseconds. 0 = Process is not waiting.
lastwaittype	nchar(32)	A string indicating the name of the last or current wait type.
waitresource	nchar(256)	Textual representation of a lock resource.
dbid	smallint	ID of the database currently being used by the process.
uid	smallint	ID of the user that executed the command. Overflows or returns NULL if the number of users and roles exceeds 32,767. For more information, see Querying the SQL Server System Catalog.
cpu	int	Cumulative CPU time for the process. The entry is updated for all processes, regardless of whether the SET STATISTICS TIME option is ON or OFF.
physical_io	bigint	Cumulative disk reads and writes for the process.
memusage	int	Number of pages in the procedure cache that are currently allocated to this process. A negative number indicates that the process is freeing memory allocated by another process.
login_time	datetime	Time at which a client process logged into the server. For system processes, the time at which the SQL Server startup occurred is stored.
last_batch	datetime	Last time a client process executed a remote stored procedure call or an EXECUTE statement. For system processes, the time at which the SQL Server startup occurred is stored.
ecid	smallint	Execution context ID used to uniquely identify the subthreads operating on behalf of a single process.
open_tran	smallint	Number of open transactions for the process.
status	nchar(30)	Process ID status. The possible values are: dormant = SQL Server is resetting the session. running = The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. For more information, see Using Multiple Active Result Sets (MARS). background = The session is running a background task, such as deadlock detection. rollback = The session has a transaction rollback in process. pending = The session is waiting for a worker thread to become available. runnable = The task in the session is in the runnable queue of a scheduler while waiting to get a time quantum. spinloop = The task in the session is waiting for a spinlock to become free. suspended = The session is waiting for an event, such as I/O, to complete.
sid	binary(86)	Globally unique identifier (GUID) for the user.
hostname	nchar(128)	Name of the workstation.
program_name	nchar(128)	Name of the application program.
hostprocess	nchar(10)	Workstation process ID number.
cmd	nchar(16)	Command currently being executed.
nt_domain	nchar(128)	Windows domain for the client, if using Windows Authentication, or a trusted connection.
nt_username	nchar(128)	Windows user name for the process, if using Windows Authentication, or a trusted connection.
net_address	nchar(12)	Assigned unique identifier for the network adapter on the workstation of each user. When a user logs in, this identifier is inserted in the net_address column.
net_library	nchar(12)	Column in which the client's network library is stored. Every client process comes in on a network connection. Network connections have a network library associated with them that enables them to make the connection. For more information, seeNetwork Protocols and TDS Endpoints.
loginame	nchar(128)	Login name.
context_info	binary(128)	Data stored in a batch by using the SET CONTEXT_INFO statement.
sql_handle	binary(20)	Represents the currently executing batch or object. Note   This value is derived from the batch or memory address of the object. This value is not calculated by using the SQL Server hash-based algorithm.
stmt_start	int	Starting offset of the current SQL statement for the specified sql_handle.
stmt_end	int	Ending offset of the current SQL statement for the specified sql_handle. -1 = Current statement runs to the end of the results returned by the fn_get_sql function for the specified sql_handle.
request_id	int	ID of request. Used to identify requests running in a specific session.

SQL Query:-

select spid, status, loginame,

hostname, blocked, db_name(dbid) as databasename, cmd

from master..sysprocesses

where db_name(dbid) like '%TSQL%'

and spid > 50