Skip to main content

Executed command preview in Sql Server

SQL Server offers a lot of system views which allow to get information about each instance without interfering in system tables. All those system views are located in hidden system datadabase name 'Resource'.
All system views can be divided into at least two groups:
  • Structure views - which storing information about SQLSERVER properties and custom structure, and begins with schema INFORMATIN_SCHEMA.* . (compilant with ISO)
  • Dynamic views - storing information at runtime from last time instance starts; this begins with sys.dm_*
When we need to get information about executed connection in the all current opened session we can query to sys.dm_exec_connections. In this view we see two very important columns:
  • session_id: represent the IDs of all current opened session; note that session ID is presented in each query tab in Managment Studio (ex. 52).
  • most_recent_sql_handle: a handle to the most recent executed query for single session ID.
Now assume that we want to preview sql  query text for the most recent executed SQL in some session. From sys.dm_exec_connections dynamic view we can obtain a binary handle such as '0x1000003434DF44345323000000000000AFF0000' but how we can translate this?! In SQL SERVER one of the dynamic view can helps us. By quering sys.dm_exec_sql_text (this is table value function really)and pass handle as a 'parameter' we are able to get clear SQL command text.
For example:
SELECT * FROM sys.dm_exec_sql_text (0x1000003434DF44345323000000000000AFF0000)

So now it`s time to know how to get themost chargeable query to the SQL Server engine.

SELECT TOP (20) qs.max_logical_reads, st.[text]
FROM sys.dm_exec_query_stats AS qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.max_logical_reads DESC;

Thank You.
Labels:

Popular posts from this blog

Full-Text Search with PDF in Microsoft SQL Server

Last week I get interesting task to develop. The task was to search input text in PDF file stored in database as FileStream. The task implementation took me some time so I decided to share it with other developers. Here we are going to use SQL Server 2008 R2 (x64 Developers Edition), external driver from Adobe, Full-Text Search technology and FileStream technology.Because this sems a little bit comlicated let`s make this topic clear and do it step by step. 1) Enable FileStream - this part is pretty easy, just check wheter You already have enabled filestream on Your SQL Server instance - if no simply enable it as in the picture below. Picture 1. Enable filestream in SQL Server instance. 2) Create SQL table to store files  - mainly ther will be PDF file stored but some others is also be allright. Out table DocumentFile will be created in dbo schema and contain one column primary key with default value as sequential GUID. Important this is out table contains FileStream
more -->

Playing with a .NET types definition

In the last few days I spent some time trying to unify structure of one of the project I`m currently working on. Most of the changes were about changing variable types because it`s were not used right way. That is why in this post I want to share my observations and practices with you. First of all we need to understand what ' variable definition ' is and how it`s different from ' variable initialization '. This part should be pretty straightforward:   variable definition  consist of data type and variable name only <data_type> <variable_name> ; for example int i ; . It`s important to understand how variable definition affects your code because it behaves differently depends weather you work with value or reference types. In the case of value types after defining variable it always has default value and it`s never null value. However after defined reference type variable without initializing it has null value by default. variable initialization  is
more -->

Persisting Enum in database with Entity Framework

Problem statement We all want to write clean code and follow best coding practices. This all engineers 'North Star' goal which in many cases can not be easily achievable because of many potential difficulties with converting our ideas/good practices into working solutions.  One of an example I recently came across was about using ASP.NET Core and Entity Framework 5 to store Enum values in a relational database (like Azure SQL). Why is this a problem you might ask... and my answer here is that you want to work with Enum types in your code but persist an integer in your databases. You can think about in that way. Why we use data types at all when everything could be just a string which is getting converted into a desirable type when needed. This 'all-string' approach is of course a huge anti-pattern and a bad practice for many reasons with few being: degraded performance, increased storage space, increased code duplication.  Pre-requirements 1. Status enum type definition
more -->