DEV Community: Evgeniy Gribkov

How to see a history of queries ran on a SQL server

Evgeniy Gribkov — Fri, 02 Oct 2020 06:47:05 +0000

You can review query history in one of the following ways:

Queries are saved in the cache via system representations like sys.dm_exec_query_stats, sys.dm_exec_sql_text и sys.dm_exec_query_plan For example, you can output 100 queries with the highest execution time (including all delays) using the following script:

with s as ( select top(100) creation_time, last_execution_time, execution_count, total_worker_time/1000 as CPU, convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime], qs.total_elapsed_time/1000 as TotDuration, convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur], total_logical_reads as [Reads], total_logical_writes as [Writes], total_logical_reads+total_logical_writes as [AggIO], convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0)) as [AvgIO], [sql_handle], plan_handle, statement_start_offset, statement_end_offset, plan_generation_num, total_physical_reads, convert(money, total_physical_reads/(execution_count + 0.0)) as [AvgIOPhysicalReads], convert(money, total_logical_reads/(execution_count + 0.0)) as [AvgIOLogicalReads], convert(money, total_logical_writes/(execution_count + 0.0)) as [AvgIOLogicalWrites], query_hash, query_plan_hash, total_rows, convert(money, total_rows/(execution_count + 0.0)) as [AvgRows], total_dop, convert(money, total_dop/(execution_count + 0.0)) as [AvgDop], total_grant_kb, convert(money, total_grant_kb/(execution_count + 0.0)) as [AvgGrantKb], total_used_grant_kb, convert(money, total_used_grant_kb/(execution_count + 0.0)) as [AvgUsedGrantKb], total_ideal_grant_kb, convert(money, total_ideal_grant_kb/(execution_count + 0.0)) as [AvgIdealGrantKb], total_reserved_threads, convert(money, total_reserved_threads/(execution_count + 0.0)) as [AvgReservedThreads], total_used_threads, convert(money, total_used_threads/(execution_count + 0.0)) as [AvgUsedThreads] from sys.dm_exec_query_stats as qs with(readuncommitted) order by convert(money, (qs.total_elapsed_time))/(execution_count*1000) desc ) select s.creation_time, s.last_execution_time, s.execution_count, s.CPU, s.[AvgCPUTime], s.TotDuration, s.[AvgDur], s.[AvgIOLogicalReads], s.[AvgIOLogicalWrites], s.[AggIO], s.[AvgIO], s.[AvgIOPhysicalReads], s.plan_generation_num, s.[AvgRows], s.[AvgDop], s.[AvgGrantKb], s.[AvgUsedGrantKb], s.[AvgIdealGrantKb], s.[AvgReservedThreads], s.[AvgUsedThreads], --st.text as query_text, case when sql_handle IS NULL then ' ' else(substring(st.text,(s.statement_start_offset+2)/2,( case when s.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2 else s.statement_end_offset end - s.statement_start_offset)/2 )) end as query_text, db_name(st.dbid) as database_name, object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as [object_name], sp.[query_plan], s.[sql_handle], s.plan_handle, s.query_hash, s.query_plan_hash from s cross apply sys.dm_exec_sql_text(s.[sql_handle]) as st cross apply sys.dm_exec_query_plan(s.[plan_handle]) as sp

Using SQL Server Profiler
Using Extended Events
Using Query Store, starting from the 2016 version.
Using third-party tools like DBeaver (Database\Transaction Log):

Using SQL Complete (SQL Complete\Execution History) in SSMS:

Set up Extended Events in SQL Server

Evgeniy Gribkov — Thu, 01 Oct 2020 11:03:12 +0000

Extended events are used to monitor the DBMS’s state.
There is a standard preconfigured session called system_health, and the most important criteria are gathered in it:

Despite deadlocks being collected in the system_health session, it’s often handy to create a separate session for this:

The code for Deadlocks session creation will be similar to this:
CREATE EVENT SESSION [Deadlocks] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET filename=N'Deadlocks') WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON) GO

Extended events can also be created by a popular monitor called Spotlight:

The code for Spotlight session creation will be similar to this:

CREATE EVENT SESSION [SpotlightMonitoring_com_sqlserver] ON SERVER ADD EVENT sqlos.wait_info( ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.is_system,sqlserver.plan_handle,sqlserver.session_id,sqlserver.tsql_frame,sqlserver.username) WHERE ([sqlserver].[is_system]=(0) AND [opcode]=(1) AND ([duration]>(0) OR [signal_duration]>(0)) AND [wait_type]<>(755) AND [wait_type]<>(177) AND [wait_type]<>(413) AND [wait_type]<>(285) AND [wait_type]<>(359) AND [wait_type]<>(137) AND [wait_type]<>(132) AND [wait_type]<>(232) AND [wait_type]<>(231) AND [wait_type]<>(230) AND [wait_type]<>(356) AND [wait_type]<>(141) AND [wait_type]<>(140) AND [wait_type]<>(143) AND [wait_type]<>(144) AND [wait_type]<>(121) AND [wait_type]<>(813) AND [wait_type]<>(393) AND [wait_type]<>(296) AND [wait_type]<>(801) AND [wait_type]<>(839) AND [wait_type]<>(382) AND [wait_type]<>(151) AND [wait_type]<>(651) AND [wait_type]<>(767) AND [wait_type]<>(661) AND [wait_type]<>(855) AND [wait_type]<>(775) AND [wait_type]<>(857) AND [wait_type]<>(856) AND [wait_type]<>(884) AND [wait_type]<>(871) AND [wait_type]<>(854) AND [wait_type]<>(859) AND [wait_type]<>(858) AND [wait_type]<>(853) AND [wait_type]<>(873) AND [wait_type]<>(881) AND [wait_type]<>(782) AND [wait_type]<>(780) AND [wait_type]<>(841) AND [wait_type]<>(798) AND [wait_type]<>(799) AND [wait_type]<>(130) AND [wait_type]<>(786) AND [wait_type]<>(96) AND [wait_type]<>(816) AND [wait_type]<>(605) AND [wait_type]<>(1021) AND [wait_type]<>(991) AND [wait_type]<>(993) AND [wait_type]<>(883) AND [wait_type]<>(131) AND [wait_type]<>(283) AND [wait_type]<>(335) AND [wait_type]<>(347) AND [wait_type]<>(273) AND [wait_type]<>(100) AND [wait_type]<>(102) AND [wait_type]<>(107) AND [wait_type]<>(105) AND [wait_type]<>(109) AND [wait_type]<>(108) AND [wait_type]<>(754) AND [wait_type]<>(165) AND [wait_type]<>(348) AND [wait_type]<>(756) AND [wait_type]<>(392) AND [wait_type]<>(221) AND [wait_type]<>(800) AND [wait_type]<>(897) AND [wait_type]<>(358) AND [wait_type]<>(202) AND [wait_type]<>(32) AND [wait_type]<>(868) AND [wait_type]<>(409) AND [wait_type]<>(752) AND [wait_type]<>(394) AND [wait_type]<>(398) AND [wait_type]<>(407) AND [wait_type]<>(408) AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server (Monitoring)') AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server (Monitoring 2)') AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server XEventsReader (Monitoring)'))), ADD EVENT sqlos.wait_info_external( ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.is_system,sqlserver.plan_handle,sqlserver.session_id,sqlserver.tsql_frame,sqlserver.username) WHERE ([sqlserver].[is_system]=(0) AND [opcode]=(1) AND [duration]>(0) AND [wait_type]<>(755) AND [wait_type]<>(177) AND [wait_type]<>(413) AND [wait_type]<>(285) AND [wait_type]<>(359) AND [wait_type]<>(137) AND [wait_type]<>(132) AND [wait_type]<>(232) AND [wait_type]<>(231) AND [wait_type]<>(230) AND [wait_type]<>(356) AND [wait_type]<>(141) AND [wait_type]<>(140) AND [wait_type]<>(143) AND [wait_type]<>(144) AND [wait_type]<>(121) AND [wait_type]<>(813) AND [wait_type]<>(393) AND [wait_type]<>(296) AND [wait_type]<>(801) AND [wait_type]<>(839) AND [wait_type]<>(382) AND [wait_type]<>(151) AND [wait_type]<>(651) AND [wait_type]<>(767) AND [wait_type]<>(661) AND [wait_type]<>(855) AND [wait_type]<>(775) AND [wait_type]<>(857) AND [wait_type]<>(856) AND [wait_type]<>(884) AND [wait_type]<>(871) AND [wait_type]<>(854) AND [wait_type]<>(859) AND [wait_type]<>(858) AND [wait_type]<>(853) AND [wait_type]<>(873) AND [wait_type]<>(881) AND [wait_type]<>(782) AND [wait_type]<>(780) AND [wait_type]<>(841) AND [wait_type]<>(798) AND [wait_type]<>(799) AND [wait_type]<>(130) AND [wait_type]<>(786) AND [wait_type]<>(96) AND [wait_type]<>(816) AND [wait_type]<>(605) AND [wait_type]<>(1021) AND [wait_type]<>(991) AND [wait_type]<>(993) AND [wait_type]<>(883) AND [wait_type]<>(131) AND [wait_type]<>(283) AND [wait_type]<>(335) AND [wait_type]<>(347) AND [wait_type]<>(273) AND [wait_type]<>(100) AND [wait_type]<>(102) AND [wait_type]<>(107) AND [wait_type]<>(105) AND [wait_type]<>(109) AND [wait_type]<>(108) AND [wait_type]<>(754) AND [wait_type]<>(165) AND [wait_type]<>(348) AND [wait_type]<>(756) AND [wait_type]<>(392) AND [wait_type]<>(221) AND [wait_type]<>(800) AND [wait_type]<>(897) AND [wait_type]<>(358) AND [wait_type]<>(202) AND [wait_type]<>(32) AND [wait_type]<>(868) AND [wait_type]<>(409) AND [wait_type]<>(752) AND [wait_type]<>(394) AND [wait_type]<>(398) AND [wait_type]<>(407) AND [wait_type]<>(408) AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server (Monitoring)') AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server (Monitoring 2)') AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server XEventsReader (Monitoring)'))), ADD EVENT sqlserver.sp_statement_completed(SET collect_statement=(0) ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.username) WHERE ([sqlserver].[is_system]=(0) AND [duration]>(0) AND ([physical_reads]>(0) OR [logical_reads]>(0) OR [writes]>(0) OR [cpu_time]>(0)) AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server (Monitoring)') AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server (Monitoring 2)') AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server XEventsReader (Monitoring)'))), ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(0) ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.username) WHERE ([sqlserver].[is_system]=(0) AND [duration]>(0) AND ([physical_reads]>(0) OR [logical_reads]>(0) OR [writes]>(0) OR [cpu_time]>(0)) AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server (Monitoring)') AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server (Monitoring 2)') AND NOT [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],'Spotlight Diagnostic Server XEventsReader (Monitoring)'))), ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.ring_buffer(SET max_events_limit=(1000),occurrence_number=(1000)) WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=15 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_NODE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) GO

We can also use other profilers, such as the one from Microsoft or those from third-party companies like Devart (Event Profiler for SQL Server):

You can also specify filters:

You can also configure what columns will be displayed:

An example of a compiled trace file:

The best way to synchronize (one way) a table in SQL Server

Evgeniy Gribkov — Wed, 30 Sep 2020 09:31:23 +0000

To synchronize two tables, you can use either built-in tools like SSIS or third-party ones.
You can learn about synchronizing table data one-way via SSIS by referring to this link:

You can also synchronize two tables with the help of a third-party tool called dbForge Data Compare that integrates with SSMS:

Before the comparison process starts, you can configure the source and target servers and databases:

Column mapping can be set for the table:

“Columns details” displays column definition information from two tables: source database on the left and target database on the right.
Column mapping is particularly useful when column names are different in the source and the target.

As a result, we can see data differences:

Column visibility and sorting can also be configured:

You can generate a script or perform data synchronization after finding the differences.
Keep in mind that it’s important to make sure table definitions match before comparing or synchronizing data between two tables. This can be done with the help of standard methods, like Visual Studio or SSDT, or via 3rd-party tools.

To do this, you need to create a new SQL Server Database project as follows:

Next, you’ll need to import the database:

In the window that will be opened, you need to configure a connection to the necessary database and then press Start to initiate the import process:

After this, you will be able to see folders, subfolders, and definitions of database objects in the project:

In the same way, you will need to create a project and perform import for the second database.

Now, to compare the database schemas, you just need to right-click any of the two projects and then click Schema Compare… in the context menu:

As a result, a database schema comparison window will be opened.
Here you need to select source and target projects and press Compare to start the comparison process:

Also, you can use other tools for database comparison, such as dbForge Schema Compare, which integrates with SSMS:

You can configure the source and target for schema comparison:

You can also set table mapping in the corresponding tab:

“Columns details” displays column definition information from two tables: source database on the left and target database on the right.

This is particularly useful when column names are different in the source and the target.

After schemas are compared, you can select the necessary table and see the table definition differences in two databases:

At the bottom left, the definition code of the source database table is displayed, and that of the target database is displayed at the bottom right.

If needed, a synchronization script for any schema table can be generated.

How To Read a Transaction Log in a SQL Server

Evgeniy Gribkov — Fri, 13 Mar 2020 16:51:56 +0000

You can read the transaction log with the help of undocumented functions called fn_dblog и fn_dump_dblog:

I. fn_dblog – an undocumented SQL Server function that allows you to monitor the active part of the transaction log in real time.

Let’s execute the following query:

Select top(100) * FROM sys.fn_dblog(NULL,NULL)

The result is as follows:

Usually, the amount of retrieved data is very large, so it’s better to filter it. For example, let’s select only those transactions that insert rows into a table:

SELECT top(100) [Current LSN],
       [Operation],
       [Context],
       [Transaction ID],
       [Begin time]
       FROM sys.fn_dblog
   (NULL, NULL)
  WHERE operation IN
   ('LOP_INSERT_ROWS');

Here’s a possible result of such a query:

We can retrieve all row deletion transactions in a similar way using this query:

SELECT top(100) [begin time],
       [rowlog contents 1],
       [Transaction Name],
       Operation
  FROM sys.fn_dblog
   (NULL, NULL)
  WHERE operation IN
   ('LOP_DELETE_ROWS');

In the set resulting from this query, rows will look similar to the following:

The information about inserted or deleted rows will be stored in these columns:
• RowLog Contents 0
• RowLog Contents 1
• RowLog Contents 2
• RowLog Contents 3
• RowLog Contents 4
• Description
• Log Record

Different columns are used for each transaction type. To get the required info, you need to know which columns are used for which transactions, but it can be rather difficult because there’s no official documentation describing this.
Inserted and deleted rows are stored as HEX values. To extract data from these values, you need to know the format they’re stored in, understand state bits, know the total number of columns, etc.

Next, we need to convert binary data to a table while considering column data types. The conversion method is different for all data types.

fn_dbLog is a great free tool that allows you to read transaction logs. However, this functionality has some caveats – it’s difficult to navigate through the data as it contains records about system tables, only the active part of the log is displayed, and there’s no information on updates of BLOB values.

With minimal transaction log protocolling, the UPDATE operation does not contain the full values before and after changes, but rather stores only the part that was changed (SQL Server can write that a value was changed from ‘G’ to ‘D’, while in reality, the word ‘GLOAT’ was changed to ‘FLOAT’ ). In this case, you would need to manually restore all intermediary states of the record from the moment it was inserted to the moment you’re interesed in.

When a BLOB object is deleted, it’s not written to the log – only the fact that it was deleted will be recorded. To restore a deleted BLOB object, you need to find a pair for this deletion in the log. It’s a previously performed insertion which is, most probably, not stored in the log’s active part anymore.

II. fn_dump_dblog – as of yet, an undocumented function that allows you to look through the transaction log from it’s backup copy (either a compressed or uncompressed one):

SELECT top(100) [Current LSN],
       [Operation],
       [Context],
       [Transaction ID],
     [transaction name],
       [Description]
FROM fn_dump_dblog
(NULL,NULL,N'DISK',1,N'E:\Backups\JobEmpl.bak',
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT);

The fn_dump_dblog function, just as fn_dblog, returns 129 columns. This is why it’s important to specify the necessary columns in the query.

However, the limitations here are the same as with the fn_dbLog function, including data decryption.

III. DBCC PAGE – an undocumented function that allows you to look through the contents of MDF and LDF files:

DBCC PAGE ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

However, the result of the DBCC PAGE command is not displayed in SQL Server Management Studio by default. To display this result properly, the trace flag 3604 should be turned on:

DBCC TRACEON (3604, -1)

After this, you can execute the desired command.

DBCC PAGE has the same limitations as previously described functions when it comes to data decryption.

However, a more convenient approach is to use off-the-shelf solutions for reading transaction logs and rolling back changes. For instance, the RedGate SQL Log Rescue or dbForge Transaction Log.

Comparison of Two Database Schemas in SQL Server

Evgeniy Gribkov — Thu, 12 Mar 2020 15:25:34 +0000

To synchronize the schemas of two databases, you can use both standard approaches like Visual Studio and SSDT and third-party tools.

This is how you can do it in Visual Studio:

First of all, Create a SQL Server Database project:

Fig.1 Creating a SQL Server Database project

Then import the database:

Fig.2 Navigating to the database import window

In the window that will be opened, configure the connection to the necessary database and press Start to initiate the import process:

Fig.3 Configuring and starting the database import process

After this, you will be able to see folders, subfolders, and definitions of database objects in the project:

Fig.4 Project after import

In the same way, you will need to create a project and perform import for the second database.

Now, to compare the database schemas, you just need to right-click any of the two projects and then click Schema Compare… in the context menu:

Fig.5 Navigating to the database schema comparison window

As a result, the database schema comparison window will be opened.
Here you need to select source and target projects and then press Compare to initiate the comparison process:

Fig.6 Database schema comparison window

You can also use other tools to compare database schemas, such as dbForge Schema Compare which integrates with SSMS and is included in the SQL Tools bundle:

Fig.7 Comparing database schemas in SSMS

Here you can configure the source and target for schema comparison:

Fig.8 Configuring the source and target for schema comparison

Table mapping can be configured in the corresponding tab:

Fig.9 Selecting a table for mapping

Fig.10 Configuring field mapping in the table

“Columns details” displays column definition information from two tables: source database on the left and target database on the right.

This is particularly useful when column names are different in the source and the target.

After schemas are compared, you can select the necessary table and see the table definition differences in two databases:

Fig.11 Table schema comparison between two databases

At the bottom left, the definition code of the source database table is displayed, and that of the target database is displayed at the bottom right.

If needed, a synchronization script for any schema table can be generated.