DMV - Active queries sorted by CPU usage

select
           s.session_id,

           db_name(s.database_id) DB,

           s.login_time,

           s.host_name,

           s.program_name,

           s.login_name,

           s.cpu_time,

           s.memory_usage,

           s.total_scheduled_time,

           s.total_elapsed_time,

           s.endpoint_id,

           s.last_request_start_time,

           s.last_request_end_time,

           s.reads,

           s.writes,

           s.logical_reads,

           s.is_user_process,

           s.row_count,

           s.open_transaction_count,

           t.text as [command]

from sys.dm_exec_sessions s

inner join sys.dm_exec_connections c...

Generate script to recreate indexes

For me, this particular code to generate a script to recreate indexes has more practical use in a replication environment. 

For example, the default replication setting is to exclude the...

Powershell One Liners: Get current cpu utilization on all your SQL Servers

# Assuming you have list of servers in servers.txt, each server name in its own line
$ComputerNames = get-content servers.txt

# Method 1 - Using the Get-Counter cmdlet#...

Powershell One Liners: Get status of SQL Instances on All SQL Servers

# Assuming you have list of servers in servers.txt, each sever name in its own line
$ComputerNames=get-content servers.txt
Get-WmiObject -Query "select * from win32_service where...

SQL Server monitoring with built-in features

Do you think you need to use commercial tools like SQL Diagnostic Manager, SQL Sentry, Spotlight etc.. ?  Or do you think...

Check if any database has auditing configured

Here is a DMV script to check whether a or any database has the auditing configured and running, along with...

Find Orphaned databases in SQL Server

Do you ever wonder if there are any databases in your environment that may just be there but not being...

DMV To List Foreign Keys With No Index

I was entrusted with a new database for a critical application that was having general performance issue and the CPU...

How To Audit Data Changes In SQL Server?

Scenario:
I was at a new client, with their previous and only DBA / DEVELOPER/ MASTER OF ALL of 8 years all...

How to get alerted anytime SQL Service is restarted?

You may and should have monitoring in place to monitor state of your servers, services, jobs, critical and not critical...