How applications generally connect to database?

 1. When application try to connect, uses Config file (Configuration file--Resides in application server). Config file (.txt) should contains SQL Server instance name + user name [service account name--DBA team should add the account into SQL Server under security] + Password [Strong]

Application use all these details from Config file and point connection to SQL server database

Real time points:

> Any business 2 data centers maintains called PRODUCTION and DR data center.

> Always distance between data centers should not be more than 50 KM.

> Few people always work inside the data centers.

sp who3 script to check processes in SQL server

 --who3 to check active sessions in SQL Server.

SELECT

    SPID                = er.session_id

    ,BlkBy              = er.blocking_session_id      

    ,ElapsedMS          = er.total_elapsed_time

    ,CPU                = er.cpu_time

    ,IOReads            = er.logical_reads + er.reads

    ,IOWrites           = er.writes     

    ,Executions         = ec.execution_count  

    ,CommandType        = er.command         

    ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  

    ,SQLStatement       =

        SUBSTRING

        (

            qt.text,

            er.statement_start_offset/2,

            (CASE WHEN er.statement_end_offset = -1

                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2

                ELSE er.statement_end_offset

                END - er.statement_start_offset)/2

        )        

    ,STATUS             = ses.STATUS

    ,[Login]            = ses.login_name

    ,Host               = ses.host_name

    ,DBName             = DB_Name(er.database_id)

    ,LastWaitType       = er.last_wait_type

    ,StartTime          = er.start_time

    ,Protocol           = con.net_transport

    ,transaction_isolation =

        CASE ses.transaction_isolation_level

            WHEN 0 THEN 'Unspecified'

            WHEN 1 THEN 'Read Uncommitted'

            WHEN 2 THEN 'Read Committed'

            WHEN 3 THEN 'Repeatable'

            WHEN 4 THEN 'Serializable'

            WHEN 5 THEN 'Snapshot'

        END

    ,ConnectionWrites   = con.num_writes

    ,ConnectionReads    = con.num_reads

    ,ClientAddress      = con.client_net_address

    ,Authentication     = con.auth_scheme

FROM sys.dm_exec_requests er

LEFT JOIN sys.dm_exec_sessions ses

ON ses.session_id = er.session_id

LEFT JOIN sys.dm_exec_connections con

ON con.session_id = ses.session_id

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

OUTER APPLY 

(

    SELECT execution_count = MAX(cp.usecounts)

    FROM sys.dm_exec_cached_plans cp

    WHERE cp.plan_handle = er.plan_handle

) ec

ORDER BY

    er.blocking_session_id DESC,

    er.logical_reads + er.reads DESC,

    er.session_id

KILL all the connections for a database in SQL Server

 ——- KILL all the connections for a database ——

Use Master

Go

Declare @dbname sysname

Set @dbname = 'Put-Your-DB-Name'

Declare @spid int

Select @spid = min(spid) from master.dbo.sysprocesses

where dbid = db_id(@dbname)

While @spid Is Not Null

Begin

Execute ('Kill' + @spid)

Select @spid = min(spid) from master.dbo.sysprocesses

where dbid = db_id(@dbname) and spid > @spid

End

Top 10 Wait States in SQL Server

 select top 10 *

from sys.dm_os_wait_stats

where wait_type not in --remove common waits to identify worst bottlenecks

( 

'KSOURCE_WAKEUP', 'SLEEP_BPOOL_FLUSH', 'BROKER_TASK_STOP',

'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',   

'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',

'LAZYWRITER_SLEEP', 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 

'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'LAZYWRITER_SLEEP', 

'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE', 

'SLEEP_TASK', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',

'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS', 

'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH'

) 

order by wait_time_ms desc

Script to Identifying possible CPU pressure via signal wait time

 SELECT SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,

( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))

/ SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 )

AS PercentageSignalWaitsOfTotalTime

FROM sys.dm_os_wait_stats

Most active database in sql server

SELECT

DB_NAME(mf.database_id) AS databaseName,

name AS File_LogicalName,

CASE

WHEN type_desc = 'LOG' THEN 'Log File'

WHEN type_desc = 'ROWS' THEN 'Data File'

ELSE type_desc

END AS File_type_desc

,mf.physical_name

,num_of_reads

,num_of_bytes_read

,io_stall_read_ms

,num_of_writes

,num_of_bytes_written

,io_stall_write_ms

,io_stall

,size_on_disk_bytes

,size_on_disk_bytes/ 1024 AS size_on_disk_KB

,size_on_disk_bytes/ 1024 / 1024 AS size_on_disk_MB

,size_on_disk_bytes/ 1024 / 1024 / 1024 AS size_on_disk_GB

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs

JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id

AND mf.FILE_ID = divfs.FILE_ID

ORDER BY num_of_Reads DESC

Index--Missing Index on database Notification Mail

 --Index--Missing Index Notification Mail

 DECLARE @email_profile_name VARCHAR(100);

DECLARE @email_recipients VARCHAR(100);

set @email_profile_name = 'SQLajay' ; 

set @email_recipients = 'bandarucreations@sqldbanow.com';

If(OBJECT_ID('tempdb..#TempMissingIndex') Is Not Null)

Begin

    Drop Table #TempMissingIndex

End

create table #TempMissingIndex

(

    index_advantage int, 

    DB_info Varchar(350),

Equality_columns Varchar(350),

    Inequality_columns Varchar(350),

Included_columns Varchar(350)

)

--EXEC sp_MSforeachdb '

--IF ''?'' NOT IN(''master'',''tempdb'',''msdb'', ''model'')

BEGIN

USE [dba]; -- here you can the database which you want to see

Insert into #TempmissingIndex

SELECT 

user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage] ,

dbmid.[statement] AS [Database.Schema.Table] ,

dbmid.equality_columns ,

dbmid.inequality_columns ,

dbmid.included_columns

FROM sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK )

INNER JOIN sys.dm_db_missing_index_groups AS dbmig WITH ( NOLOCK ) ON dbmigs.group_handle = dbmig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS dbmid WITH ( NOLOCK ) ON dbmig.index_handle = dbmid.index_handle

WHERE dbmid.[database_id] = DB_ID()

and (user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )) > 50000

ORDER BY index_advantage DESC ;

END

IF EXISTS (SELECT * FROM #TempMissingIndex)

begin

DECLARE @tableHTML  NVARCHAR(MAX);

SET @tableHTML =

    N'<h1>Missing Index</h1>'

  + N'<table border="1">'

  + N'<tr><th>index_advantage</th><th>DB_info</th><th>Equality_columns</th><th>Inequality_columns</th><th>Included_columns</th></tr>'

  + CAST ( 

(  Select 

                   td = [PP].[index_advantage] , ''

, td = [PP].[DB_info] , ''

, td = [PP].[Equality_columns] , ''

, td = [PP].[Inequality_columns] , ''

,  td = [PP].[Included_columns]

FROM #TempMissingIndex PP

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX)

)

  + N'</table>';

EXEC msdb.dbo.sp_send_dbmail

@profile_name =@email_profile_name ,

@recipients = @email_recipients ,

@subject = 'Report',

@body = @tableHTML,

@body_format = 'HTML';

END;

Drop Table #TempMissingIndex