Erland Sommarskog
Erland Sommarskog SQL-Konsult AB
Stockholm, Sweden
Actions
Erland Sommarskog is an independent consultant based in Stockholm. He has worked with SQL Server since 1991. He was first awarded SQL Server MVP in 2001, and he has been re-awarded every year since. His focus is on systems development with the SQL Server Database Engine and his passion is to help people to write better SQL Server applications.
Awards
Area of Expertise
Topics
How sp_sqltrace Can Help with Your Performance Troubleshooting
sp_sqltrace is open-source tool written by Lee Tudor which is an excellent tool when you to find out why a specific process is slow, particularly when it's running loops. It's also very powerful when you want to look at a single plan from a batch that runs hundreds of statements. You can even use it as a statement debugger.
Analysing and Resolving Deadlocks.
About every developer and DBA has to deal with deadlocks in their work. This session starts with a brief introduction to define what a deadlock is. The next step is to learn how you get information about deadlocks, how to get hold of the deadlock XML. From here I proceed to explain how to read that XML, and particularly what are the important things you should look for.
Before moving on how to resolve deadlocks, there is an interlude with a recap on the most important locking concepts in SQL Server, since without understanding about the locking mechanisms in SQL Server, it will be difficult to analyse and resolve deadlocks.
The second half of the session discusses various ways on how you can prevent deadlocks. Sometimes it is not entirely possible or feasible to entirely prevent deadlocks, and the session discusses which mitigating actions you can take in these cases.
Don’t Let Your Permissions Be Hijacked!
You are sysadmin on a production server, and on this server, there are databases where there are users with power permissions such as the db_owner or db_ddladmin roles. They have no server-level permissions, but there may be rogues who want to perform actions beyond what their own permissions allow them to. One they can achieve this is to have you to unknowingly run code that perform these actions, using your almighty permissions – or by another word hijacking them. For instance, if you have set up a reindexing job for all databases, this is a great opportunity for permission hijacking.
Not only sysadmin can be the victim of such attacks, but a developer who has permissions to create stored procedures and triggers can attack a user who is in the db_owner role to extend his or her permission in the database.
In this session I will discuss some of the possible attacks on this theme and what means you can take to protect yourself against them. This includes some best practices for Agent jobs.
Packaging Permissions in Stored Procedures
: The basic mechanism when we want to give users access to data or actions in SQL Server in a controlled way through stored procedure is ownership chaining. This is something we use every day as SQL workers, although we may not be aware of it. Every once in a while, we run into situations where it seems that it is not sufficient to put a statement in a stored procedure, but that users need to be granted explicit permissions – something we may not always be comfortable with, because that would permit the users to do things we do not want them to be able to.
There are two additional mechanisms in SQL Server we can employ in these situations: certificate signing and the EXECUTE AS clause. In this session I will start by exploring how ownership chaining works and its limitations. I will then proceed to explore the other two options and I will particularly focus on certificate signing, as this is the preferred method. At first it may seem overly complex, but I will show how it easily can be automated. EXECUTE AS may seem simpler, but I will point out potential problems with it, not the least if you try to use it on server level and consider to set the database as TRUSTWORTHY. This session is directed towards both developers and DBAs, as this session deals with problems you may face in either roles.
SQL Titbits for the Inexperienced
This session is directed to you who have been using SQL left-handedly a year or so. You have learnt to write the basic queries, but there are still some gaps in your SQL knowledge, and overall SQL is a little confusing to you. This session aims at filling some of these gaps by covering a few titbits about SQL and give you some tips that will be useful to you. This includes really basic things as what GO really is and the traps you can run into with NULL values. You will also learn a little about data types and how you should use UNION. Finally, I will talk about CTEs, temp tables and table variables and you will get some ideas what to use when.
Error and Transaction Handling in SQL Server
Most modern programming environments offers exception handling in some form, and SQL Server provides the popular TRY-CATCH concept. However, error handling in SQL Server is not as straight¬forward as one may wish, but it is marred with inconsistencies and unexpected behaviour. This session starts off by displaying the some of the surprises you can encounter, both when you use TRY-CATCH and when you don't. The session discusses why it is important to handle unexpected errors and presents a recipe for writing CATCH block where focus is on keeping things simple. You will learn why should not make your error handling "fancy", even if may seem to be a good idea in theory. The session also covers a few things to think of on client level.
This is a level 200 session main aimed at people who write T-SQL code as a developer or a DBA.
Dynamic Search Conditions
A common requirement in database applications is that users want a function to search a set of data from a large set of possible search conditions. The challenge is to implement such searches in a way that is both maintainable and efficient in terms of performance. This session looks at the two main techniques to implement such searches and highlights their strengths and limitations.
Don't Use Cursors or Why You Maybe Should Use a Cursor After All
You might have heard "don't use cursors, they are slow!". In this presentation, you will learn what this actually means: you should normally write set-based statements instead and I will explain why they generally are faster than writing your own loops. But I will also look at situations where using a loop for one reason or another is preferable, and you will learn that the best way to run a loop in most cases is exactly a cursor, provided that you implement it properly. The presentation also gives some tips how you can troubleshoot performance problems with loops.
Don't Bite Off More You Can Chew - Take it in Chunks
Any SQL programmer with some experience knows that loops are bad and that you should work with all data at once in set-based statements. However, you may have experienced situations where this strategy did not work out well and you ran into problems like out-growing the transaction log or blocking other users. You can solve this by operating on the data in chunks. Implementing chunking is not that difficult, but there are still pitfalls you can run into. In this session, I will discuss in what situations you may want to use chunking. I will give some best practices for how to implement chunking for good performance and I will highlight some things you need to keep in mind, for instance, recovering from interruptions. I will also look at how you can use chunking for error handling: You want process many rows and you want the good rows to succeed even if some rows yield errors – something you cannot do in a single set-based statement.
This is a level 300/400 session for persons who have been working with
T-SQL development for a few years.
All You Wanted to Know About Collations
Collations is a topic that tends to confuse SQL Server professionals. It is not that the implementation of collations in SQL Server is extremely confusing, but computer people are not always prepared for the surprises that human language can offer. For instance, did you know that V and W could be considered equal? Or that i and I could be different in a case-insensitive comparison?
In this light-minded presentation I cover what a collation controls and I give a brief introduction to Unicode and other character sets, before I dissect a collation name like Latin1_General_100_CI_AS_KS_WS_SC_UTF8 to explain what all those components mean. I illustrate this by giving examples from different languages. I give particular attention to the UTF-8 collations added in SQL 2019. I cover two areas where collations induce some pain: metadata and collation conflicts. Finally, time permitting, I highlight two cases where the collation has a big impact on performance.
SQL-wise this is a level 200 session, but all the talk about human language has the potential melt the brains of experienced SQL people. The session is directed to developers and DBAs, not the least if you engage in data modelling.
Deadlocks – Analysing, Preventing and Mitigating
Deadlocks happens in the best of families, and an occasional deadlock is nothing to be alarmed of. But too many deadlocks can hamper throughput or be irritating for the users who encounter them, and in this case you need to investigate the deadlocks to see how you can reduce the pain.
I start this session with discussing how you can get information about deadlocks. Next, I will go through the XML report for a deadlock and highlight things you should pay attention to. I the second half of the session, I will discuss various devices you can apply to prevent deadlocks. I will also discuss possible mitigations for deadlocks that are too difficult to prevent entirely.
The topic of deadlocks is a complex one, so don’t expect to learn everything, but the hope is that this session will open your eyes for things to look out for, next time you have to analyse a deadlock.
This is a level 300/400 session for developers and DBAs who need to perform troubleshooting in production systems.
Dynamic Pivot – Making Columns out of Rows
You have a result set in relational format, but you want to pivot the result set for presentational reasons, so that that the values in some column(s) in the result set become columns in what the user sees. Very often, you don’t know these values beforehand, so the pivot operation needs to be dynamic.
To be able to write a dynamic pivot, you need two master two things: writing static pivot queries and dynamic SQL.
In this session, we will start learning how to write static pivot queries. In case you already feel acquainted with the PIVOT operation in T-SQL, here is a teaser: we will not use that operator, but a much more flexible scheme.
Next, we will move on to the basics of dynamic SQL. Dynamic SQL itself is quite an advanced feature, but we will only have time for the basics – and some words of caution.
Finally, we will put these things together and learn how to write dynamic pivots. We will look at one example in detail, but the download material come with a few more examples to study on your own.
This is a level-200 session. Preferably, you should have been working with writing queries and stored procedures for two-three years to have full benefit of the session.
Please note that Sessionize is not responsible for the accuracy or validity of the data provided by speakers. If you suspect this profile to be fake or spam, please let us know.
Jump to top