Boris Hristov

Founder of 356labs

Founder of 356labs
and PowerPoint MVP.
Speaker, Trainer & Author.
A guy that loves to do sports, have fun and enjoy life. Gallery / Blog / Contact

Copyright © 2019 ·

T-SQL Tuesday #67:
Extended Events and…
Distributed Replay?

June 9, 2015 By Boris Hristov 8 Comments

TSQL2sDaySome of you may know that I am kind of a huge fan of Distributed Replay – a technology that not that many people know about, but at the same time a feature that can help you a ton especially when you are planning an upgrade, migration or you simply have a performance problem. DReplay has a problem though – you need to capture your application workload and that needs to happen with a Profiler trace and as you probably have heard already – Profiler is deprecated. Recently, however, Robert Dorr from Microsoft just mentioned as part of some other discussion that the latest version of the RML Utilities – 9.04.0004 – can convert an .xel file to a .trc. I immediately asked myself – so does that mean that now I can capture my workload with xEvent session -> convert the workload to a trace file and then replay it with Distributed Replay? Let’s find out…

By the way, this post was not going to be possible(as you will find out why below) without the help of Robert, so thank him and not me!

The first thing I did was to open a Profiler Replay template(as it includes exactly all the events that you need to capture in order to make DReplay work) and take a look at every event that was captured by it and select those same events for my xEvent session. Replay_to_xEvent_Events
Once I have done that, I started the session and ran my workload by retaining the events into a file on my local machine. Once the workload was captured, I downloaded the latest version of the RML Utilities and I remember that Robert mentioned that in order to convert the .xel to .trc we need to use the -M parameter. I did not find any documentation on this, so I started guessing how the command should look like. At one point I ended up having this:

readtrace -I"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XECapture_0_130781709497210000.xel" -M"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\DReplayTRC.trc"

However, when I tried to convert the file it was throwing errors for events that were not captured. That was kinda strange because remember – I mimicked the same events from the Replay template of Profiler. I read the output log which I found in some nasty directory – C:\Users\YourUser\AppData\Local\Temp. Once I reconfigured the session with all the new events, I again captured the workload and tried to convert to a trace file.

…………………………………………………………………………………………………………………………………………………………………

A lot hours later I was going back and forth, capturing, trying to convert and receiving those same errors for missing events. At that point I decided to write to Robert and ask him if there is some guidance or article, or something that I can use in order to make that work. He replied to me that there is a .sql script that will create the xEvent session with everything needed in order to make that work as part of the samples in the download. Ooooooh… okay! Let’s go there:

xEvent_to_trc_session_template

After entering the path where the .xel file should be, I again captured my workload and tried to convert. This time something different happened when I ran my readtrace command:

readtrace -I"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XECapture_0_130781709497210000.xel" -M"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\DReplayTRC.trc"

The Reporter.exe opened up with analysis of the workload, but more importantly – there was no DReplayTRC.trc in the destination folder I selected?Reporter
Went back and forth, back and forth again with no success(again – there is no documentation on this – nothing anywhere). I again wrote to Robert and he said that the -M parameter does not accept output(and he was surprised that it was not throwing me an error), so I needed to do -M -o”the path to the file”. This means that if I run the below, it should work:

readtrace -I"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XECapture_0_130781709497210000.xel" -M -o"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\DReplayTRC.trc"

And it did. Partially… The problem this time was that the -o parameter in this case was accepting DreplayTRC.trc as a folder and placing all the generated files there + there was a .trc file for each session from the workload and I did not want that. Again to the command line, this time using readtrace /?
ReadTrace_Help
Seeing the -MS parameter I was now optimist that I will finally be able to convert to a single trace file. Let’s see (+changed the directory output folder):

readtrace -I"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XECapture_0_130781709497210000.xel" -MS -o"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\DReplayTRC"

xEvent_to_trc_Session_output
Finally! We have managed to convert a .xel file containing our application workload to a SQL Profiler trace .trc file. Now that .trc file can be given to the Distributed Replay Controller to prepare it for replay…

I don’t know how you feel about this process, but I kinda feel that there should be a better way for doing all of these things in the future. If Distributed Replay is “the technology that we are going to be using for replaying mission critical workload in the future” as Microsoft said in 2012, then let’s hope that first DReplay will get a GUI(because you remember how many people were using Extended Events in SQL Server 2008R2, don’t you?) and second why not DReplay “accepting” .xel files in first place?

Thanks for hosting this one, Jes! Hoping you will be able to count to more than 100 at the end of this Tuesday! 🙂

Oh, by the way, if you want to stop the Reporter.exe to show up at the end of each export, just put -T18 at the end of the cmd command above. It should look like this.

readtrace -I"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XECapture_0_130781709497210000.xel" -MS -o"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\DReplayTRC" -T18

Don’t ask. 🙂

Filed Under: SQL Server Tagged With: Distributed Replay, Extended Events, RML Utilities, SQL Profiler, T-SQL Tuesday

T-SQL Tuesday #66: Monitoring

May 12, 2015 By Boris Hristov 2 Comments

TSQL2sDay

T-SQL Tuesday is here once again and I need to be quick this time(I believe this is the second time I have to write a T-SQL Tuesday blog post in a 30 minute period). However, that does not mean that the message I am going to deliver to you is not important. It’s completely the opposite(or at least, I think so) and if I have to be honest, the post itself is not that technical. It’s more or less touching on the business side of supporting a service and should be read by IT managers and decision makers and not only by you, SQLFamily 🙂 . Let me tell you what I mean…

Less then a year ago I left HP to become a consultant and up until now, I was not engaged to create a monitoring solution of any kind(except for one recent nasty performance issue). That is something that makes me happy, if I have to be honest, because for the 3 years I spent as a Senior DBA for HP Enterprise Services, I have seen and done a lot for numerous Fortune 500s.

HP and all other major vendors that are in the “supporting services” business many times have their own monitoring solutions which makes a DBA’s life even easier because there is no need for creating or using Alerts, Jobs, Policy-Based Management and custom scripts(except if that monitoring software cannot give you what you need as a DBA). There’s a problem, though. Big companies(and not only) are sometimes slow(OK, not sometimes – very often!) and here is the moment when why I said this post is more for the IT decision makers, will clarify. When you, as a customer pay such company(huge amount of money) to support your SQL Servers and you are one of those few that really strives to be at the cutting edge of the technologies, please do yourself a favour and ask not just whether or not the team has the experience to deliver that service for you. Ask them to show you how are they going to monitor that same service. You want to see that in action. Think of that as a PoC on monitoring and here is a proof why you need to do that with just a simple example. Imagine this…

When SQL Server 2012 was released we saw the birth of this great technology called AlwaysOn Availability Groups(and many customers, for one reason or another, wanted it immediately). Your service provider, though – do they have a working monitoring solution for it or is it that the DBAs have to figure it out by themselves? Is that solution generating incidents(hopefully they have some ITIL framework implemented) that are giving the correct picture of the current status of your databases? Is that monitoring software also working just fine and not generating some absurd incident tickets just because it was not written well and adapted to that cool new piece of technology? To say it in simple words – is your service provider providing you an official guarantee that their monitoring system supports that new HA solution? I hope that you are probably understanding why I am asking those questions, right? Oh, one more – if they do not provide an official way(and the DBAs have to build their own solution) of supporting your service, do they plan to do it and how much time will it take to get it working – weeks, months, years? Recall from the beginning of the post – some companies are slow to adapt. This is where I want to leave you by warning you, IT managers, that you may be thinking that some service providers have everything set up in the most brutally perfect way, but that’s not always the case and I want you to know that and act accordingly.

Cathrine, thanks for the invitation and for this great topic! I am expecting quite some posts this time… 🙂

Filed Under: SQL Server Tagged With: AlwaysOn Availability Groups, Enterprise, Hewlett-Packard, Monitoring, Policy-Based Management, SQL Agent, SQL Agent Alerts, SQL Agent Jobs, T-SQL Tuesday

Teaching and Learning.
The NOEXEC Thing.

April 14, 2015 By Boris Hristov 9 Comments

TSQL2sDay

The year 2015 started in a crazy, but good way. I was engaged to deliver more than 7 courses just in the first half of the year and for those of you who are trainers, you guys know that every single time when you teach a class you always learn something new. Most of the times it’s because your students have some specific case that they want to ask for or it may be because of a weird thought they have and just want to check what’s your opinion of it. This is where the inspiration, let’s call it, for this blog post is also coming from – we have the “teach something new” idea and I was teaching when I learned this and now I again will, in a way, teach it. So here’s the thing…

On the first day of my training on SQL Server Development, one of my students asked:

Can we somehow open a new query window and start writing our queries there, but if we by mistake hit the F5 or the Execute button not execute anything?

My first though was – yeap, just put the whole code into a comment like this:


/*

your code here

*/

However, my students said that’s not an option because what may also happen is selecting a piece of the code and executing it and they didn’t want that. At this point I have no other ideas, but I promised I will check for them during the break. So after we ended the module we were discussing and I said something like: “Coffee time now!”, one of the students said:

“Have you tried this NOEXEC” thing?

I will be honest with all of you in the same way I was with the group – I haven’t. I haven’t even heard of it before. So during the break I take it for a spin and did a quick demo for them. Here’s how this works…


SET NOEXEC ON
-- session level option and enabled till you explicitly disable it
-- or just open a new session

USE AdventureWorks2012
GO
-- some queries...
SELECT * FROM Person.Person

TRUNCATE TABLE Person.Person

SELECT COUNT(*) FROM Person.Person

When you run that whole thing, you will notice that nothing gets executed. Same if you first run only the SET NOEXEC ON command and then try to run any of the below queries – it just says that the command completed successfully, but nothing is returned. That will remain true until you execute SET NOEXEC again and set it to OFF. So if I want to execute any of those 2 queries I will have to first do the following:


-- SET NOEXEC ON
SET NOEXEC OFF
-- execute that and then you are able to execute any of the queries below

USE AdventureWorks2012 GO

SELECT COUNT(*) FROM Person.Person -- see – nothing was deleted…

So once the students saw how this works, they were quite happy because that was, as they said, “a perfect solution for them”(forgot to mention – they are professional developers, not just students that want to learn something new). They have had some problems in the past which were caused by cases like this – writing or opening a sql file with dozens of SQL queries inside and running the whole thing against production instance(of course – by mistake). You know what happened after that, right?

Thanks for hosting this one, Mike! I hope I indeed taught something new!

Filed Under: Public Speaking, SQL Server Tagged With: Database Development, MCT, NOEXEC, SQL Server Teaching, SQL Server Training, T-SQL, T-SQL Tuesday

T-SQL Tuesday #64
Partitioning and the
Serializable Isolation Level

March 10, 2015 By Boris Hristov 1 Comment

TSQL2sDay

I am just coming back from SQLBits where I delivered a 75 minute session on isolation levels and one of the things that I thought about during my preparation which I figured may be interesting for the audience is a perfect fit for number 64 of our favourite T-SQL Tuesdays. This “thing” involves table partitioning and the serializable isolation level. Let’s me show you what I mean…

Serializable isolation level makes sure that no concurrency phenomenons like dirty reads, non-repeatable reads and even phantom records are possible. The latter are the ones that we want to stop at for a sec and the reason for that is because I want to spend a moment talking about how serializable makes it possible that phantom records will not happen. In serializable isolation level SQL Server takes the so called Key-Range locks in order to not only lock the already read records(as it’s doing in Repeatable Read), but to actually lock the whole range of values preventing the chances of a concurrent transaction to actually insert a record that may be returned once you run that same SELECT query as part of your transaction. OK. Sounds reasonable. Is that… true though? Or if it is, can there be something that can worry us here? Let’s take a look an example.

-- Create database to play with
USE master
GO

IF EXISTS (
 SELECT NAME
 FROM sys.databases
 WHERE NAME = N'IsolationLevels'
 )
 DROP DATABASE IsolationLevels
GO

CREATE DATABASE IsolationLevels
GO

-- Create a table and insert 10000 records

USE IsolationLevels
GO

IF OBJECT_ID('DataTable', 'U') IS NOT NULL
 DROP TABLE DataTable
GO

CREATE TABLE DataTable (
 ColA INT PRIMARY KEY
 ,ColB INT
 )

SET NOCOUNT ON;
GO

DECLARE @a INT = 1;
DECLARE @b INT = 2;

WHILE (@a < 10000)
BEGIN
 INSERT INTO DataTable
 VALUES (@a, @b);

 SELECT @a = @a + 1;
 SELECT @b = @b + 1;
END;
GO

Now that I have some data, lets run a serializable query and see those key-range locks.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

BEGIN TRAN

SELECT ColA
FROM DataTable
WHERE ColB < 5000
-- leave the transaction open in order to be able to check the locks

-- Script to find what locks are we holding. Run in another session</pre>

SELECT request_session_id
 ,DB_NAME(resource_database_id) AS [Database]
 ,resource_type
 ,resource_subtype
 ,resource_associated_entity_id
 ,request_type
 ,request_mode
 ,resource_description
 ,request_mode
 ,request_owner_type
FROM sys.dm_tran_locks
WHERE request_session_id > 50
 AND resource_database_id = DB_ID()
 AND request_session_id <> @@SPID
ORDER BY request_session_id;

SELECT OBJECT_NAME() -- paste from resource_associated_entity_id to find the table name

Table Level Lock

Ammm? Where are the key-range locks? Why are we holding a shared lock on the whole table!? How can that be when we actually selected less than 5000 records(so that’s not lock escalation in action)? It can be because the serializable isolation level has one small gotcha – in order for the queries to utilize the power of Key-Range locking, the query has to have a supportive index. So if that’s true if I create a nonclustered index on the ColB, it should work, correct? Let’s check!

USE IsolationLevels
GO

CREATE NONCLUSTERED INDEX [IX_DataTable_ColB] ON dbo.DataTable (ColB ASC)
GO

-- Now rerun the query and again check the locks

Key Range Locks

That’s the result that we were expecting! RangeS-S locks on the number of rows + 1(you can add a filter on the resource_type column to see just the KEYs – the value will be 4999). Why +1? Because remember – we are locking not just the rows, we are locking the range of values which means that we are locking all the records from the first selected one to the last + the one after the last one. This way we lock the whole range, so please be aware of that small gotcha – we need supportive index for our serializable query. Otherwise we may lock the whole table…

That being said, let’s go back to my post title. Partitioning and serializable? Are you thinking about the same as I do, SQLFamily? (remember B1 and B2). What’s going to happen if my table is partitioned and I run my SELECT queries against that partitioned table in serializable mode? Am I, if I don’t have supportive index for my query, going to end up locking the whole table or am I going to lock only the partition where my data is? Hm… what’s going to happen if I have a supportive index then? Will something change? And what if one of the partitions has more than 5000 records(which is probably true in 100% of the cases where we use partitioning), but if so to what level and what type of locks will my query take if it has supportive index and if not(because again – 5000 is the number of locks after which a query will make SQL Server escalate those locks to a higher level – either table or partition)? I thought that’s quite an interesting case, so while in London, I decided to spend some time on it. Here is a script for you to create a test database that can cover all of those cases together with a script to create an extended event session to monitor for lock escalation events:

USE master
GO

-- Drop the database if it already exists
IF EXISTS (
 SELECT NAME
 FROM sys.databases
 WHERE NAME = N'LockEscalationTest'
 )
 DROP DATABASE LockEscalationTest
GO

-- Create an empty database to play with
CREATE DATABASE LockEscalationTest;
GO

USE LockEscalationTest;
GO

-- Create three partitions: up to 7999, 8000-15999, 16000+
CREATE PARTITION FUNCTION MyPartitionFunction (INT) AS RANGE RIGHT
FOR
VALUES (
 8000
 ,16000
 );
GO

CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionFunction ALL TO ([PRIMARY]);
GO

-- Create a partitioned table
CREATE TABLE MyPartitionedTable (
 c1 INT
 ,c2 INT
 );
GO

CREATE CLUSTERED INDEX MPT_Clust ON MyPartitionedTable (c1) ON MyPartitionScheme (c1);
GO

-- Fill the table
SET NOCOUNT ON;
GO

DECLARE @a INT = 1;
DECLARE @b INT = 2;

WHILE (@a < 17000) BEGIN INSERT INTO MyPartitionedTable VALUES ( @a ,@b ); SELECT @a = @a + 1; SELECT @b = @b + 1; END; GO -- Create an Extended Event Session with Lock Escalation event only -- After created: Go to Management -> Extended Events -> Sessions -> Enable the session and Watch Live data
CREATE EVENT SESSION [Lock Escalation] ON SERVER ADD EVENT sqlserver.lock_escalation
 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 = OFF
 )
GO

Here is the data distribution:

Partitioning Data Distribution

So now that we have the table, let’s start running queries and see what behavior we can expect.

USE LockEscalationTest
GO

ALTER TABLE MyPartitionedTable SET (LOCK_ESCALATION = TABLE);
-- Set it to AUTO to enable Lock Escalation on Partition Level
GO

-- Enable the execution plan
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

SELECT *
FROM dbo.MyPartitionedTable
WHERE c2 < 7000 -- hitting just the first partition -- Change the predicate to c1 when you want to use clustered index seek -- Change the predicate to use c2 when you want to see what's happening without supportive index -- Use the first or second partition as partitions where lock escalation can happen -- Use the third partition (for example where c1 > 16000) to see the behavior without any lock escalations possible

-- ROLLBACK
-- Check the current locking
-- Use both scripts in another session

-- Returns the partitions and their IDs
SELECT [partition_id]
,[object_id]
,[index_id]
,[partition_number]
FROM sys.partitions
WHERE object_id = OBJECT_ID('MyPartitionedTable');
GO

-- Correlate with the first script to see what's being locked
SELECT request_session_id
,DB_NAME(resource_database_id) AS [Database]
,resource_type
,resource_subtype
,resource_associated_entity_id
,request_type
,request_mode
,resource_description
,request_mode
,request_owner_type
FROM sys.dm_tran_locks
WHERE request_session_id > 50
AND resource_database_id = DB_ID()
AND request_session_id <> @@SPID
-- AND request_type LIKE 'Key'
ORDER BY request_session_id;

Here are the results of all combinations I came up with(click to enlarge). If you want to simulate those, just change the predicate choosing from c1 (and index seek) or c2 (an index scan) together with a condition like < 7000 (to hit one partition, but with more than 5000 records) or > 16000 (to hit one partition, but with less than 5000 records)
Partitioning with Serializable Results

I hope this gives you an idea what behavior and what consequences can Serializable have on your application in terms of both scalability and performance. Of course, I understand that a setup like this may be quite rare, but that does not make it impossible (and we are all geeks, so why not taking a look at such cases).

Thanks, Russ, for hosting #64. Looking forward to the other posts already!

Filed Under: SQL Server Tagged With: Concurrency Control, Isolation Levels, Lock Escalation, Partitioning, Serializable Isolation Level, T-SQL Tuesday

T-SQL Tuesday #63
How do you manage security?

February 10, 2015 By Boris Hristov 2 Comments

TSQL2sDay

Security – a topic that is even more dear to us in the last few months after witnessing so many major flaws with huge, huge organizations. There are quite some things that worried and continue to worry me when we talk about securing data in SQL Server, but one of them really stands out in my mind. Let me tell you which one.

Have you ever worked in a company where a piece of software(in my case a backup solution) requires both local admin and sysadmin permissions inside SQL Server in order to work? Hope you haven’t. And I hope that even if you do, you will be able to fight and change “the situation”.

The software that we were “forced” to use because of “how that company works” was using the NT Authority\SYSTEM account as its login inside SQL Server. Actually it’s Windows service was running under the LocalSystem account and that was not something we were able to change(again – don’t ask why). So up until SQL Server 2012 NT Authority\SYSTEM is by default part of the sysadmin role and if you don’t know that – go check it out for yourself and decide whether or not it should be the case in your environment. As of 2012, Microsoft decided to revoke the sysadmin role from this login and of course that lead to some problems for us, but in general it was a great decision and here is just one more reason why. Have you ever heard about psexec? That’s a simple .exe file(that’s also free) that you can use to actually impersonate NT Authority\SYSTEM locally on the OS level. Now that means that if I am able to run the SQL Server Management Studio via psexec, I will be sysadmin inside of that instance! That technique is used very often whenever you want to recover your access to SQL Server or you just don’t want to wait for anyone to give you those permissions(OK, I said it. I admit I have done that). A complete article on the topic can be found here.

So do you think that leaving the sysadmin role to NT Authority\SYSTEM is right? Do you think that the fact that there is even a small chance of someone seeing that article and trying that on your production SQL Server box is secure? And what about the fact that such an “operation” does not actually require any reboots of the instance/machine? Yeap. Leaving you here hopefully thinking…

Thanks for hosting T-SQL Tuesday #63, Kenneth! Looking forward to the summary post already!

Filed Under: SQL Server Tagged With: Security, SQL Server Logins, T-SQL Tuesday

  • 1
  • 2
  • 3
  • …
  • 6
  • Next Page »