Other Scenarios

Plan Caching and Reuse

Dynamic Management Views and functions are useful when exploring plan reuse and the following objects are most helpful:

sys.dm_exec_cached_plans

sys.dm_exec_query_plan

sys.dm_exec_sql_text

sys.dm_exec_plan_attributes

sys.dm_exec_cached_plan_dependent_object

The following sql query is useful to exploring the current plan cache contents in buffer pool:

 
SELECT DB_NAME( st.dbid) AS DatabaseName, 
st.dbid AS Database_ID,
cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.text AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY 

 sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY 

 sys.dm_exec_sql_text(cp.plan_handle) AS st

WHERE st.[dbid] = DB_ID()

ORDER BY UseCounts DESC;

Robocopy files to another Drive

Robocopy files to another Drive with estimated time to complete
If you ever plan to move the sql data(mdf) or log file (LDF) to another drive then try to use robocopy is much better than windows file copying and takes less time.

In that command prompt e.g:
F:\Robocopy  "E:\SQLData" "F:\SQLData" mydb.mdf mydb.mdf /ETA

E:\Robocopy  "D:\SQLLogs" "G:\SQLLogs" mydb_log.ldf mydb_log /ETA

How to import sql server profiler trace file into table

If you want to analyse the sql profiler output then import into table using a fn_trace_gettable.

USE Mydbname
GO
SELECT * INTO trace_table
FROM ::fn_trace_gettable('C:\Temp\myFile.trc', default) 

An attempt was made to send an email when no email session has been established

When you get this error message on your sql server agent Error Logs
Make sure to enable profiles on sql server agent propertise

1) Right click SQL Server Agent ---> Propertise
2) Select Alert Systems
3) Checkbox Enable Mail profile