Transaction Isolation Levels and sp_executesql

SQL Server has several transaction isolation levels; probably the one most people are familiar with is the default of “read committed”. However, you may not realize it, but running dynamic code via sys.sp_executesql doesn’t necessarily…

What SQL Text is that Session Running?

Use the code below if you have a long, complicated, stored procedure or piece of dynamic SQL running on a server, and you’d like to see exactly which piece of SQL Text, or code, is…

On Default Schemas and “Search Paths”

Default schemas in SQL Server can be a blessing, since they reduce the need to specify the schema when creating DDL statements in T-SQL. However, relying on the default schema when creating DML statements can…

List Recently Created Objects

The code below provides a list of all SQL Server objects created in the past “x” number of days. Dynamic T-SQL is used to construct a query for each database, including system databases. Each query…

Blocking Example Code

In our previous post we looked at detecting and analyzing blocking. This post contains a script with blocking example code. Output from the analysis samples contained in the previous post is also provided. Blocking Example…

Example Deadlock Code

Deadlocks happen. As you’re about to see in this post, deadlocks are actually quite easy to create. In my previous post I showed how to monitor and analyze deadlock events using Extended Events. In this…