SELECT*

The below query explains about fragmentation on each index on each table on a specific database. Fragmentation and page count should be greater than 1000 USE DatabaseName go SELECT B . name AS TableName , C . name AS IndexName , C . fill_factor AS IndexFillFactor , D . rows AS RowsCount , A . avg_fragmentation_in_percent , A . page_count FROM sys . dm_db_index_physical_stats ( DB_ID (),NULL,NULL,NULL,NULL) A INNER JOIN sys . objects B ON A . object_id = B . object_id INNER JOIN sys . indexes C ON B . object_id = C . object_id AND A . index_id = C . index_id INNER JOIN sys . partitions D ON B . object_id = D . object_id AND A . index_id = D . index_id WHERE C . index_id > 0 and A . avg_fragmentation_in_percent > 30 order by A . avg_fragmentation_in_percent desc

---This query is useful to get the currently running CPU related long running queries. SELECT  getdate() as runtime, qs.last_execution_time, qs.Execution_count as Executions, qs.total_worker_time as TotalCPU, qs.total_physical_reads as PhysicalReads, qs.total_logical_reads as LogicalReads, qs.total_logical_writes as LogicalWrites, qs.total_elapsed_time as Duration, qs.total_worker_time/qs.execution_count as [Avg CPU Time], substring (qt.text,qs.statement_start_offset/2,(case when qs.statement_end_offset = -1 then len (convert (nvarchar(max), qt.text)) * 2 else qs.statement_end_offset end - qs.statement_start_offset)/2) as query_text, qt.dbid as DBID, qt.objectid as OBJECT_ID, cast ( query_plan as xml) as XMLPlan FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(plan_handle) where CONVERT(VARCHAR(5),qs.last_execution_time,108)>'01:00'--Give Service now ticket TIME here. AND CONVERT(VA

One day we got a request asking for upgrade present sql server environment (Microsoft SQL Server 2014 SP1) to Microsoft SQL Server 2014 (SP2 CU11). So we followed the below process. And the environment is in Always on. So we have decided first we will do the patching in Secondary replica, then we can do it in Primary replica. So we have ran the. exe file that we have downloaded from the et( https://support.microsoft.com/en-us/help/4052725/cumulative-update-10-for-sql-server-2014-sp2 ). After we successfully installing the patching in secondary replica and trying to bring the sql server instance is failed with the below error messages in the event viewer  After seeing the above errors we are under impression that we need rebuild the master database. But don't do that.We are not able to restart the services and it is throwing above error in the event viewer.  So to bring the services up we used -T902 trace flag in the startup parameters of sql server service. T

select scheduler_id , current_tasks_count , current_workers_count , active_workers_count , work_queue_count     from sys . dm_os_schedulers    where status = 'Visible Online' go select is_preemptive , state , last_wait_type , count (*) as NumWorkers from sys . dm_os_workers WHERE last_wait_type IN ( 'HADR_WORK_QUEUE' , 'HADR_NOTIFICATION_DEQUEUE' , 'PREEMPTIVE_HADR_LEASE_MECHANISM' )    Group by state , last_wait_type , is_preemptive     order by count (*) desc   go /* from this stored procedure check count of "maxworkers" and "WorkersCreated" */ Sp_server_diagnostics select last_wait_type , count (*) as NumRequests from sys . dm_exec_requests     group by last_wait_type     order by count (*) desc go select   is_user_process , count (*) as RequestCount from sys . dm_exec_sessions s     inner join sys . dm_exec_requests r     on s . session_id = r

/*  There is only one master key, you can not create  multiple */ use master go CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPasswordHere' GO /*  Creating certificate in the master database, after creating  master key */ use master GO CREATE CERTIFICATE MyFirstCert WITH SUBJECT = 'TDECertificate' ; GO /* Enable databse encryption key but you can not see under this specific user database. */ USE G GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyFirstCert GO ALTER DATABASE G SET ENCRYPTION ON;  /*  Backing up certificate */ use master go BACKUP CERTIFICATE MyFirstCert TO FILE = 'C:\Certificates\MyFirstCert' WITH PRIVATE KEY ( FILE = 'C:\Certificates\MyFirstCertKey' , ENCRYPTION