TDE

https://www.red-gate.com/simple-talk/sql/sql-development/encrypting-sql-server-transparent-data-encryption-tde/


https://www.mssqltips.com/sqlservertip/3146/configuring-transparent-data-encryption-with-sql-server-2012-alwayson-availability-groups/

Estimated Completion time for a query

select  T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id)
        , R.cpu_time, R.total_elapsed_time, R.percent_complete,estimated_completion_time,
convert(varchar,(total_elapsed_time/(1000))/60) + 'M ' + convert(varchar,(total_elapsed_time/(1000))%60) + 'S' AS [Elapsed],
convert(varchar,(estimated_completion_time/(1000))/60) + 'M ' + convert(varchar,(estimated_completion_time/(1000))%60) + 'S' as [ETA]
from    sys.dm_exec_requests R
        cross apply sys.dm_exec_sql_text(R.sql_handle) T

Find Tempdb usage

/*
Lists the TempDB usage per each active session.
It helps identifying the sessions that use the tempdb heavily with internal objects.

When the internal objects usage is high, the session is probably using big hash tables or spooling in worktables. It could be a symptom
of an inefficient plan or a missing index.

Shrinking a TempDB full of internal objects will probably have no effect, because the engine will not release the deallocated space.


*/


;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                   EST.text,
                   ERQ.statement_start_offset / 2,
                   CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
--- Changed from inner join to left outer join to return rows for sessions that aren't currently actively running queries.
left outer join sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC, 5 DESC

Database Backups for all databases For Previous Week

SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

Top 10 Free SQL Server Tools

1. Plan Explorer

If you find yourself having to look at SQL Server execution plans (and you should be) this is THE tool for you. SQL Sentry Plan Explorer builds upon the graphical execution plan view in SSMS to make working with query plans a more efficient and “pleasurable” experience. No more scrolling through massive plans and hunting through property windows to find the metrics you need.

2. sp_Blitz

Like Batman’s utility belt, this tool does pretty much everything. If you’ve been handed a SQL Server instance and need to give it the once over in order to get a handle on things then this is what you use to get the job done. sp_Blitz helps you rapidly assess configuration, security, health, and performance issues. As if that were not awesome enough, the results are organised in a prioritized list and provide simple explanations of each risk, plus a column of links that you can copy/paste into your browser for more information about each issue. The authors, Brent Ozar (Blog|Twitter) and his team, have even put together a tutorial video on how this tool works. A wonderful example of #sqlWinning and most certainly worthy of a hat tip.

3. sp_WhoIsActive

Want to know what is happening on your SQL Server instance right now? This is the tool you use. It’s my favourite initial diagnosis instrument. When I’ve got a production issue on my hands and nothing more to go on than “is there something wrong with SQL Server?” this is my go-to weapon. The creation of Adam Machanic (Blog|Twitter), sp_WhoIsActive enables me to triage an instance of SQL Server so that I can immediately get a feel for what’s currently happening and quickly decide what my next steps should be. As well as all currently executing commands and what they’re waiting on, I can optionally pull back additional information like the execution plan and blocking chain.

4. Management Data Warehouse (MDW)

Yet another fantastic product feature available right out of the box that I’m surprised more folks are not taking advantage of. It contains a significant amount of performance monitoring insight and capability that many vendors will happily charge you for. Using the MDW I have been able to make production performance insight data directly available to development teams by extending the default implementation using SQL Reporting Services.

5. Index Defrag Script v4.1 

Everyone has their favourite Index Defrag script. Some folks (suckers) like to author their own index maintenance scripts and everyone else likes to use Ola Hellengren’s ;-) Of course not being one to follow the crowd, this little beauty written by Michelle Ufford(Blog|Twitter) is my own personal favourite. I’ve been very happily using this script for index maintenance of production environments for many years and many versions. It gets the job done extremely well and deservers more press.

6. sp_BlitzIndex

It’s natural to think that your indexes improve your performance. Sure that’s what they’re supposed to do sure but there are cases where having indexes can actually be a hindrance to performance. Things like duplicate and unused indexes can be silent killers, dragging your performance down quietly in the background. You can use sp_BlitzIndex to evaluate your overall index health and to identify areas where you can make adjustment and improvement to nip potential future problems in the bud.

7. Compression Estimator

SQL Server Compression is one of the more underused features of the product, most likely because a lot of folks don’t know how to use it to their gain. You can download this utility from Codeplex and use it in order to identify how much space you could save, as well as evaluate which type of compression you should consider (ROW or PAGE). Armed with this type of information, you can make a stronger case to both management and your customers as to why you should be taking advantage of the SQL Compression feature.

8. Central Management Server

Managing a large number of SQL instances can be tricky. CMS is a SQL Server feature for making the job of administering multiple servers easier. You can centralise your registered server lists, ensuring your entire team is working from the same reference and run multiple queries against “groups” of servers using a single query. It’s a great feature and I encourage you to learn more about how it can simplify the administration of your environment.

9. ClearTrace

Here’s a great tool for working with SQL Server trace files. ClearTrace imports SQL Server 2000, SQL Server 2005 and SQL Server 2008 trace and profiler files into SQL Server and displays summary performance information. It’s easy to use and helps you get to the bottom of performance problems fast.

10 . SQL Server 2012 Diagnostic Information Queries

SQL Server DMVs were are godsend when they were introduced into the SQL Server product. Just ask anyone who still has to touch a SQL 2000 instance how much fun they are having. DMV’s are of course not pure magic by themselves, they need to be built and combined together into useful T-SQL queries. Thanks to Glenn Berry (Blog|Twitter), the hard work has already been done for you. Glenn has put together a wide variety of awesome T-SQL queries that you can use in order to diagnose the health and performance of your SQL Server instances.

SQL Script to Monitor CPU Utilization

This post helps you in understanding and using SQL Script to Monitor CPU utilization. There are certain scenarios where we can quickly use this script to get SQL Server CPU utilization.

• When there is a performance issue, and you need to quickly check the CPU usage
• When doing Root Cause Analysis
• When we only have access to SQL Server not to Windows

SQL Server CPU Utilization history report for last N minutes:

Below is the SQL Script to Monitor CPU utilization. This script captures the CPU usage history report from last 10 min. This can be customized by changing the variable “@lastNmin” value.

/***** Script: SQL Server CPU Utilization report from last N minutes *****/

/***** Support: SQL Server 2008 and Above *****/

/***** Tested On: SQL Server 2008 R2 and 2014 *****/

/***** Output: 

SQLServer_CPU_Utilization: % CPU utilized from SQL Server Process

System_Idle_Process: % CPU Idle - Not serving to any process 

Other_Process_CPU_Utilization: % CPU utilized by processes otherthan SQL Server

Event_Time: Time when these values captured

*****/

DECLARE @ts BIGINT;

DECLARE @lastNmin TINYINT;

SET @lastNmin = 10;

SELECT @ts =(SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info); 

SELECT TOP(@lastNmin)

SQLProcessUtilization AS [SQLServer_CPU_Utilization], 

SystemIdle AS [System_Idle_Process], 

100 - SystemIdle - SQLProcessUtilization AS [Other_Process_CPU_Utilization], 

DATEADD(ms,-1 *(@ts - [timestamp]),GETDATE())AS [Event_Time] 

FROM (SELECT record.value('(./Record/@id)[1]','int')AS record_id, 

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int')AS [SystemIdle], 

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int')AS [SQLProcessUtilization], 

[timestamp]      

FROM (SELECT[timestamp], convert(xml, record) AS [record]             

FROM sys.dm_os_ring_buffers             

WHERE ring_buffer_type =N'RING_BUFFER_SCHEDULER_MONITOR'AND record LIKE'%%')AS x )AS y 

ORDER BY record_id DESC; 

SQL Server Database wise CPU Utilization

From above script we come to know that SQL Server is utilizing high CPU, now the next step is to find out which database from the SQL instance is causing the high CPU utilization. Below is the SQL Script to Monitor CPU Utilization database wise.

/***** Script: Database Wise CPU Utilization report *****/

/***** Support: SQL Server 2008 and Above *****/

/***** TestedOn: SQL Server 2008 R2 and 2014 *****/

/***** Output: 

SNO: Serial Number

DBName: Databse Name 

CPU_Time(Ms): CPU Time in Milliseconds

CPUPercent: Let’s say this instance is using 50% CPU and one of the database is      using 80%. It means the actual CPU usage from the database is calculated as: (80 / 100) * 50 = 40 %

*****/

WITH DB_CPU AS

(SELECT DatabaseID, 

DB_Name(DatabaseID)AS [DatabaseName], 

SUM(total_worker_time)AS [CPU_Time(Ms)] 

FROM sys.dm_exec_query_stats AS qs 

CROSS APPLY(SELECT CONVERT(int, value)AS [DatabaseID]  

FROM sys.dm_exec_plan_attributes(qs.plan_handle)  

WHERE attribute =N'dbid')AS epa GROUP BY DatabaseID) 

SELECT ROW_NUMBER()OVER(ORDER BY [CPU_Time(Ms)] DESC)AS [SNO], 

DatabaseName AS [DBName], [CPU_Time(Ms)], 

CAST([CPU_Time(Ms)] * 1.0 /SUM([CPU_Time(Ms)]) OVER()* 100.0 AS DECIMAL(5, 2))AS [CPUPercent] 

FROM DB_CPU 

WHERE DatabaseID > 4 -- system databases 

AND DatabaseID <> 32767 -- ResourceDB 

ORDER BY SNO OPTION(RECOMPILE); 

SQL Server Query Wise CPU Utilization

From the above scripts you confirmed that there is a huge CPU utilization from one of the SQL Instance and you identified the database which is causing high CPU. Now the next step is to identify top 10 queries which are causing high CPU utilization. Here is the SQL Script to Monitor CPU Utilization query level. This is a wonderful script provided by SQLBlog and SQLKnowlwdge.

Note: Remember that it returns the list of costly queries which causes high CPU utilization when only the CPU usage is >=80% from last 10 Min, otherwise it returns nothing. You can modify the script as per your needs.

/***** Script: Top 10 queries that causes high CPU Utilization *****/

/***** Support: SQL Server 2008 and Above *****/

/***** TestedOn: SQL Server 2008,R2 and 2014 *****/

/***** Output: All query related details *****/

/***** Note: This script returns list of costly queries when CPU utilization is >=80% from last 10 min ****/

SET NOCOUNT ON

DECLARE @ts_now bigint 

DECLARE @AvgCPUUtilization DECIMAL(10,2) 

SELECT @ts_now = cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info

-- load the CPU utilization in the past 10 minutes into the temp table, you can load them into a permanent table

SELECT TOP(10) SQLProcessUtilization AS [SQLServerProcessCPUUtilization]

,SystemIdle AS [SystemIdleProcess]

,100 - SystemIdle - SQLProcessUtilization AS [OtherProcessCPU Utilization]

,DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [EventTime] 

INTO #CPUUtilization

FROM ( 

      SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 

            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 

            AS [SystemIdle], 

            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 

            'int') 

            AS [SQLProcessUtilization], [timestamp] 

      FROM ( 

            SELECT [timestamp], CONVERT(xml, record) AS [record] 

            FROM sys.dm_os_ring_buffers 

            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 

            AND record LIKE '%<SystemHealth>%') AS x 

      ) AS y 

ORDER BY record_id DESC

-- check if the average CPU utilization was over 80% in the past 10 minutes

SELECT @AvgCPUUtilization = AVG([SQLServerProcessCPUUtilization] + [OtherProcessCPU Utilization])

FROM #CPUUtilization

WHERE EventTime > DATEADD(MM, -10, GETDATE())

IF @AvgCPUUtilization >= 80

BEGIN

SELECT TOP(10)

CONVERT(VARCHAR(25),@AvgCPUUtilization) +'%' AS [AvgCPUUtilization]

, GETDATE() [Date and Time]

, r.cpu_time

, r.total_elapsed_time

, s.session_id

, s.login_name

, s.host_name

, DB_NAME(r.database_id) AS DatabaseName

, SUBSTRING (t.text,(r.statement_start_offset/2) + 1,

((CASE WHEN r.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2

ELSE r.statement_end_offset

END - r.statement_start_offset)/2) + 1) AS [IndividualQuery]

, SUBSTRING(text, 1, 200) AS [ParentQuery]

, r.status

, r.start_time

, r.wait_type

, s.program_name

INTO #PossibleCPUUtilizationQueries

FROM sys.dm_exec_sessions s

INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id

INNER JOIN sys.dm_exec_requests r ON c.connection_id = r.connection_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

WHERE s.session_id > 50

AND r.session_id != @@spid

order by r.cpu_time desc

-- query the temp table, you can also send an email report to yourself or your development team

SELECT *

FROM #PossibleCPUUtilizationQueries

END

-- drop the temp tables

IF OBJECT_ID('TEMPDB..#CPUUtilization') IS NOT NULL

drop table #CPUUtilization

IF OBJECT_ID('TEMPDB..#PossibleCPUUtilizationQueries') IS NOT NULL

drop table #PossibleCPUUtilizationQueries

Script to find Top 20 Costliest Stored Procedures – High CPU:

 This script sourced from here. It results a list of stored procedures which are utilizing high CPU. This script goes through the buffer cache and find out these results based on Total and Average worker thread times. Below is the SQL Script to Monitor CPU Utilization from the stored procedure point of view.

/***** Script: Top 20 Stored Procedures using High CPU *****/

/***** Support: SQL Server 2008 and Above *****/

/***** Tested On: SQL Server 2008 R2 and 2014 *****/

/***** Output: 

SP Name: Stored Procedure Name

TotalWorkerTime: Total Worker Time since the last compile time

AvgWorkerTime: Average Worker Time since last compile time

execution_count: Total number of execution since last compile time

Calls/Second: Number of calls / executions per second

total_elapsed_time: total elapsed time

avg_elapsed_time: Average elapsed time

cached_time: Procedure Cached time

*****/

SELECT TOP (20) 

  p.name AS [SP Name], 

  qs.total_worker_time AS [TotalWorkerTime], 

  qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 

  qs.execution_count, 

  ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],

  qs.total_elapsed_time, 

  qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], 

  qs.cached_time

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]

WHERE qs.database_id = DB_ID()

ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

Script to find Top 20 Costliest Queries – High CPU

 This script sourced from here. It results a list of queries which are utilizing high CPU. Below is the SQL Script to Monitor CPU Utilization from Ad-hoc queries.

Script to find Top 20 Costliest Queries – High CPU

Transact-SQL

/***** Script: Top 20 Stored Procedures using High CPU *****/

/***** Support: SQL Server 2008 and Above *****/

/***** Tested On: SQL Server 2008 R2 and 2014 *****/

/***** Output: Queries, CPU, Elapsed Times, Ms and S ****/

SELECT TOP (20)

    st.text AS Query,

    qs.execution_count,

    qs.total_worker_time AS Total_CPU,

    total_CPU_inSeconds = --Converted from microseconds

    qs.total_worker_time/1000000,

    average_CPU_inSeconds = --Converted from microseconds

    (qs.total_worker_time/1000000) / qs.execution_count,

    qs.total_elapsed_time,

    total_elapsed_time_inSeconds = --Converted from microseconds

    qs.total_elapsed_time/1000000,

    qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp

ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);