SQL Server- Improve SQL Server performance with a simple query

This is probably embarrassingly simple to a SQL professional, and it’s been around since SQL 2005, but I only discovered it today and it made a noticeable difference to my production databases so I’m going to share it just in case it’s new to anyone else.

The query

Run this query on SQL Server 2005 or later and it will list indexes that you could create to improve the query performance of your database, based on the workload of the server since the SQL service last restarted. (The query just lists the indexes, it doesn’t create them!)

SELECT    'CREATE INDEX IX_Auto_' +

          CONVERT(varchar(max), MID.index_handle) +

          ' ON ' +

          [statement] + 

          ' (' +

          COALESCE(equality_columns + ', ' + inequality_columns, equality_columns, inequality_columns) +

          ')' +

          ISNULL(' INCLUDE (' + included_columns + ')', '') AS create_statement,

          CONVERT(int, avg_total_user_cost * user_seeks * avg_user_impact) AS potential_saving,

          [statement] AS table_name,

          equality_columns,

          inequality_columns,

          included_columns,

          last_user_seek,

          avg_total_user_cost,

          user_seeks,

          avg_user_impact

FROM      sys.dm_db_missing_index_details MID

JOIN      sys.dm_db_missing_index_groups MIG ON MIG.index_handle = MID.index_handle

JOIN      sys.dm_db_missing_index_group_stats MIGS on MIGS.group_handle = MIG.index_group_handle

ORDER BY  avg_total_user_cost * user_seeks * avg_user_impact DESC

The results

Running the query will return results of the following form, with indexes that have the largest potential improvement first.

Column	Description
create_statement	A SQL statement to create the proposed index.
potential_saving	The relative query cost saving of this index.
table_name	The fully qualified name of the table that the index applies to.
equality_columns	Comma-separated list of columns that contribute to equality predicates of the form table.column = constant_value.
inequality_columns	Comma-separated list of columns that contribute to inequality predicates, for example, predicates of the form table.column > constant_value.
included_columns	Comma-separated list of columns needed as covering columns for the query. These are columns that aren’t used as key columns for the index, but are included for quick retrieval by queries.
user_seeks	Number of seeks caused by user queries that the index could have been used for.
last_user_seek	Date and time of last seek caused by user queries that the index could have been used for.
avg_total_user_cost	Average cost of the user queries that could be reduced by the index.
avg_user_impact	Average percentage benefit that user queries could experience if this index was implemented. The value means that the query cost would on average drop by this percentage if this index was implemented.

How it works

Every time the SQL query optimiser analyses a query, it works out the best indexes for the filtering it will need to do. If these indexes don't exist, it nevertheless remembers that they would have been useful, including how much quicker the query would have been any how many times they would have been used. After the database has been up and running for a while, SQL Server will have a pretty good idea of which indexes would make a significant difference to your live workload.
The query above uses the Missing Index dynamic management objects to list all these indexes in order of potential query cost saving.

Caveats

• Obviously if you don't understand SQL indexes at all, it's best not to fiddle with them. While there is a very good chance that the top few suggested indexes will be beneficial, there's always a chance that you could seriously degrade write performance, or that you'll lock the table for a prolonged period while the index is created.
• Don’t create every index in the list! It’s better to create them one at a time and measure the improvements – after a while the savings will become more trivial and it’s more likely that the decrease in write performance will negate the query benefits.
• Not every possible index that might help will be returned. See Limitations of the Missing Indexes Feature.

Further reading

Before you dive in, I strongly recommend having a quick read through Finding Missing Indexes in SQL Server Books Online.

Get COMPUTER NAME from SQL Query

Get the Computer Name using SQL Query

Select Host_Name() as Computer_Name

Get Computer Name, Get HOst Name

SQL SERVER 2014- Differential Database Backup with Memory-Optimized Tables

This blog describes how differential database backup is taken on database with one or more memory-optimized tables. For full database backups, please refer to Database Backup with Memory-Optimized Tables  for the details and Creating and Managing Storage for Memory-Optimized Objects to get more in-depth understanding of data/delta files referenced later in the section below.
For disk-based tables, the differential database backup includes only the pages that have changed since the last full database backup.  SQL Server 2014 supports differential backup on databases with memory-optimized tables. The differential backup of a database with memory-optimized tables contains the following data

• The differential backup component for the disk-based tables is exactly the same as it is in databases without memory-optimized tables
• Active transaction log. This is exactly the same as with full database backup.
• For a memory-optimized filegroup, the differential backup uses the same algorithm as a full database backup to identify data/delta files for backup but it then filters out the subset of files as follows:
  • Data File – A data file contains newly inserted rows and once it is full, it is closed. Once the data file is closed, it is only accessed in read-only mode. A data file is backed up only if it was closed after the last full database backup. In the other words, the differential backup only backs up data files containing the inserted rows since the last full database backup.
  • Delta File – A delta file stores references to the deleted data rows. A delta file is always backed up. Since any future transaction can delete a row, a delta file can be modified anytime in its life time, it is never closed. Note, the delta files typically take < 10% of the storage.

Note, this optimization is only available in RTM. In CTP2, the differential database backup included all data/delta files just like in full database backup.
Let us walk through an example showing the difference in size of full database backup and a differential database backup. The example is based on the following database and the table schema. Please refer to blog State-transition-of-checkpoint-files-in-databases-with-memory-optimized-tables  for details on the state of CFPs in the following examples.
CREATE DATABASE imoltp
GO
ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE imoltp ADD FILE (name='imoltp_mod', filename='c:\data\imoltp_mod') TO FILEGROUP imoltp_mod
GO
use imoltp
go
-- create the table with each row around 8K
CREATE TABLE dbo.t_memopt (
       c1 int NOT NULL,
       c2 char(40) NOT NULL,
       c3 char(8000) NOT NULL,
       CONSTRAINT [pk_t_memopt_c1] PRIMARY KEY NONCLUSTERED HASH (c1)
WITH (BUCKET_COUNT = 100000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
go
-- load 8000 rows. This should load 5 16MB files
declare @i int = 0
while (@i < 8000)
begin
       insert t_memopt values (@i, 'a', replicate ('b', 8000))
       set @i += 1;
end
-- Do Manual checkpoint
Checkpoint

Example-1 Full and Differential database backup a database after loading 8000 rows and completing the manual checkpoint

• Checkpoint File Pairs (CFPs) – Since I am running on a machine with < 8 cores with < 16GB of physical memory, I have 8 CFPs in ‘PRECREATED’ state with 16MB sized data file and 1MB sized delta file. Also, there are 5 CFPs in ACTIVE state as explained in the blog State-transition-of-checkpoint-files-in-databases-with-memory-optimized-tables.

• Full Database Backup: Wait 5 minutes and then do the full database backup. The 5-minute wait is more of a safety factor to make sure relevant data files are not missed due to time-drift. This is not a concern in production environment there as typically there is significant time span between checkpoints, full database backup and subsequent differential database backup.

BACKUP DATABASE [imoltp] TO DISK = N'C:\data\imoltp-full-data.bak'

WITH NOFORMAT, INIT, NAME = N'imoltp-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

• Output:

Processed 304 pages for database 'imoltp', file 'imoltp' on file 1.

10 percent processed.

20 percent processed.

Processed 8064 pages for database 'imoltp', file 'imoltp_mod' on file 1.

30 percent processed.

40 percent processed.

Processed 8612 pages for database 'imoltp', file 'imoltp_log' on file 1.

100 percent processed.

BACKUP DATABASE successfully processed 16980 pages in 1.592 seconds (83.323 MB/sec).

• Result: The size of the full database backup is around 138 MB. This includes size around 70MB of transaction log and around 70MB of CFPs.

• Differential Database Backup: Since we have completed the full database backup, the differential backup will skip data files as no new using the following command

BACKUP DATABASE [imoltp] TO 

DISK = N'C:\data\imoltp-diff-data.bak' WITH DIFFERENTIAL, NOFORMAT, INIT, 

NAME = N'imoltp-diff Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

• Output:

Processed 48 pages for database 'imoltp', file 'imoltp' on file 1.

Processed 0 pages for database 'imoltp', file 'imoltp_mod' on file 1.

10 percent processed.

20 percent processed.

30 percent processed.

40 percent processed.

51 percent processed.

60 percent processed.

71 percent processed.

81 percent processed.

91 percent processed.

Processed 8613 pages for database 'imoltp', file 'imoltp_log' on file 1.

100 percent processed.

BACKUP DATABASE WITH DIFFERENTIAL successfully processed 8661 pages in 0.760 seconds (89.031 MB/sec).

• Result: The size of differential database backup is around 72 MB predominantly consisting of transaction log. The output shows that the existing data files in ‘ACTIVE’ state are not backed up as they were not changed since the last full database backup.

Example-2 Differential database backup after deleting 4000

 -- now delete 50% rows
declare @i int = 0
while (@i <= 8000)
begin
       delete t_memopt where c1 = @i
       set @i += 2;
end
go
checkpoint
go

• Checkpoint File Pairs (CFPs) – No changes as only deleted existing rows

• Differential Database Backup: Execute the following command

-- do the differential database backup

BACKUP DATABASE [imoltp] TO 

DISK = N'C:\data\imoltp-diff-data-del50-chkpt.bak' WITH  DIFFERENTIAL , NOFORMAT, INIT, 

NAME = N'imoltp-diff Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

• Output: the pages reported are computed at 8k size.

Processed 88 pages for database 'imoltp', file 'imoltp' on file 1.

Processed 47 pages for database 'imoltp', file 'imoltp_mod' on file 1.

10 percent processed.

20 percent processed.

30 percent processed.

40 percent processed.

50 percent processed.

60 percent processed.

Processed 8845 pages for database 'imoltp', file 'imoltp_log' on file 1.

100 percent processed.

BACKUP DATABASE WITH DIFFERENTIAL successfully processed 8980 pages in 0.941 seconds (74.552 MB/sec).

• Result: Note, that transaction log has few more pages to account for deleted rows and the checkpoint. Also, 47 pages from files in memory-optimized filegroup were included in the backup. Out of this 47, the 32 pages are from the empty data file that was created with manual checkpoint. The internal page size in data files is 256K or 32 8k pages. The first page in the data file contains header information therefore it is part of the backup. Other 15 pages are from delta files. The total size of the backup is around 74.4MB