Using Extended Events To Capture Backup and Restore Progress

 If you are running a DATABASE BACKUP or RESTORE command manually, SQL Server will show you the progress at a specified % completion interval. For the BACKUP, the default is...

Find identical duplicate indexes

The purpose of this query is to find identical duplicate non-clustered indexes i.e. indexes on same table with same columns in same order. If you have a need to...

Find identical duplicate indexes - Revised

I realized yesterday I posted this quickly after testing only against SQL 2016 version. So I decided to also test against SQL 2014 and 2012 versions to make sure...

Capture Trend in Dead Lock Occurances

Deadlocks are usually detected and resolved automatically by SQL Server by killing one of the SPIDs involved in it.  You may have an alert setup to notify you and/or users...

Do you have out of date index statistics?

As you may all know, having up to date index statistics is the key to make sure the query optimizer can choose the most optimal execution plan for any...

Setup SQL Server Alert to get automatically notified of database blockings

Suppose you want to or need to know anytime your SQL Server is experiencing blocking where queries are being blocked and it may or may not escalate to a...

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...