Missing Index Script in SQL Server Database 2016

Performance Tuning is quite interesting and Index plays a vital role in it. A proper index can improve the performance and a bad index can hamper the performance. In this blog post we will discuss about Missing Index. Here is the script from my script bank, which I use to identify missing indexes on any database.

Last Used Database in SQL Server

In SQL Server 2005, can you easily determine the last time someone queried a database.

SELECT d.name,
last_user_seek = MAX(last_user_seek),
last_user_scan = MAX(last_user_scan),
last_user_lookup = MAX(last_user_lookup),
last_user_update = MAX(last_user_update)
FROM sys.dm_db_index_usage_stats AS i
JOIN sys.databases AS d ON i.database_id=d.database_id

GROUP BY d.name

SQL Relay

SQL RELAY

How to Find Free Log Space in SQL Server?

This post will show you how to monitor free log space in SQL Server.

Method 1: Using SQLPERF

DBCC SQLPERF ('LOGSPACE')
GO

Method 2: sys.dm_db_log_space_usage
  
SELECT total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB,
used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB,
(total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024
AS free_log_space_in_MB
FROM sys.dm_db_log_space_usage;

How to Validate Email Address in SQL Server?

How to Validate Email Address in SQL Server-Let us create a column called Email Address in the table named Contacts. Once we create the table, we will insert few valid and invalid email address in it.

Different Methods to Know COMPATIBILITY LEVEL of a Database

There are many methods to know the compatibility levels of a database.

1) Use system stored procedure sp_helpdb
   
EXEC sp_helpdb TEST

How to Change Compatibility of Database

There are two different ways how we can change the compatibility of database to SQL Server.

1) Using Management Studio

For this method first to go database and right click over it. Now select properties.

2) Using T-SQL Script.

You can execute following script and change the compatibility settings to 120.

USE [master] GO
ALTER DATABASE [TEST] SET COMPATIBILITY_LEVEL = 120
GO