Posted on

Dear SQL DBA: Training Resources for SQL Newbies

New to learning SQL Server? Get the scoop on free online resources to look up information, ask questions of community members, and learn SQL Server from the ground up.

Links from the episode

Clickable links are here.

Join in the fun

Attend the Dear SQL DBA podcast — live! Register here to get an invitation.

Got a question for Dear SQL DBA? Ask away.

Subscribe:  If you’d rather listen on the go, subscribe on iTuneslisten on Google Play, or plug this URL into your favorite podcasting app: http://dearsqldba.libsyn.com/rss#

Audio only version

YouTube version

Posted on

Perks for My SQLPASS Summit Precon: Prevent ‘Fake News’ in Your Data! Isolation Levels Demystified

I’m proud, giddy, and just plain overly excited to be giving a pre conference session at the SQL PASS Summit in Seattle, Washington on November 5, 2018.

This is going to be a fun, demo-packed day which will teach you skills you can use throughout your career as a developer, database administrator, or data professional.

Why attend?

While features and tricks change from year to year (or even month to month), understanding isolation levels is critical for your long term future.

The Session: Prevent ‘Fake News’ in Your Data! Isolation Levels Demystified

Are your users seeing bad data? If you’re using the default isolation level of read committed in SQL Server, chances are that sometimes your users get incorrect results. If NOLOCK hints lurk in your code, the odds are even higher that sometimes your customers see information that just isn’t right.

In this day-long, demo packed session, you’ll learn why a single statement may read rows twice, miss rows entirely, or return combinations of data that never existed in the database — and why that’s not a bug. You’ll learn what “read phenomena” are, which isolation levels are vulnerable to them, and the performance trade-offs which come from raising your isolation level to protect your users from bad data.

You’ll see how isolation levels work with newer technologies such as columnstore indexes, In-Memory OLTP, and Always On Availability Groups. You’ll discover why version-based isolation levels can be awesome, and what you need to look out for with these isolation levels to avoid race conditions that produce– you guessed it– incorrect results.

At the end of the day, we’ll pull together all this information into a guide. You’ll leave the seminar with the tools and knowledge to choose the right isolation levels for new and existing applications based on business and performance requirements.

What Does it Cost?

A single pre-conference session is $499. You can also bundle pre-conference sessions with registration at the full Summit.

The Perks

Attendees at my pre-conference session will not only get all the demos we show live, and a download of the PDF of slides, but also….

Perk: Watch It Again Later

Access to a recorded online session of my 8 hour seminar, “Conquer Blocking & Isolation Levels” for a full year.

Want to revisit a topic and get the nitty gritty details? You’ll be able to watch anytime, online.

Note: the live pre-conference sessions at PASS aren’t recorded for purchase anymore– however, you can buy recordings of “regular” sessions during the week from PASS in different formats.

Perk: A Month of SQLChallenges!

For four weeks following the conference, I’ll be hosting SQLChallenges on blocking and isolation level problems.

  • Attendees will get access to the problems and a chance to apply the skills they’ve learned.
  • I’ll hold a live, online session going over sample solutions for the problem. Attend, share your solution in Slack if you’d like, and ask questions.
  • Can’t make the SQLChallenge live? The recording will be posted and you’ll get online access for a year. All SQLChallenges support Q&A in the course pages.

Perk: Private Follow-Up Quizzes

Want even more learning? Following the SQLChallenges, attendees will get access to a series of four weekly quizzes about blocking and isolation levels to confirm your knowledge.

Combining this will the month of SQLChallenges will fully establish your new knowledge in place, getting you the most value for your dollar.

Register here

I hope to see you at the PASS Summit! Sign up here to join my pre-conference session.

Postscript: SQL Symposium subscribers get access to all these perks as well. As sustaining members during the first year of SQLWorkbooks, SQL Symposium members get it all 🙂 

Posted on

Two Free Sessions Next Week: Fighting Bad Data and Deadlocks

I am excited and honored to be giving two free online sessions next week. Both events are sponsored by the fine folks over at Quest Software.

Why is That Data Wrong? How Choosing the Wrong Isolation Level Causes Bad Results

Tuesday, June 12, Noon Pacific
24 Hours of PASS, Sponsored by Quest Software

If you haven’t thought much about isolation levels in SQL Server, chances are your applications can return inconsistent data to your users: data that looks completely wrong. If your user re-runs their report or reloads their screen, the data may look right the second time… but after this happens, your customer feels that they can’t trust your data.

In this session, you will learn why we have “isolation levels,” and how the read committed isolation level works by default in SQL Server. You’ll see how easy it is to make a query return inconsistent results using these default settings, and why this is allowed to happen. We’ll dig into an example where blocking causes a query to return “impossible” query results. You’ll leave the session with a fresh understanding of why choosing the right isolation level is critical to the success of your applications.

Register for this free event here

Check out more awesome sessions at the 24 Hours of PASS

DBA vs Deadlock: How to Out-Index a Deadly Blocking Scenario

Thursday, June 14, 8:30 AM Pacific
Sponsored by Quest Software

Deadlocks strike fear into the hearts of even seasoned DBAs — but they don’t have to!

In this session, you’ll get the code to cause a sample deadlock in SQL Server. You’ll see how to interpret the deadlock graph to find out where the conflict lies, and how to design an index to make the deadlock disappear. You’ll leave the session with the steps you need to confidently tackle future deadlocks.

Register for this free online session here

See you next week!

 

Posted on

Row Width Impact on Version Store Usage under Snapshot Isolation

A question came up in my webcast today for subscribers on the topic of Snapshot and Read Committed Snapshot Isolation – what impact will enabling these have on my SQL Server instance?

I talked a bit about how, for tempdb, this can vary widely depending on the number of updates and deletes running against the instance. (This is a simplification, there is more overhead, but this is often the biggest component.)

But after the webcast, I realized that the impact on the version store isn’t simply on the number of updates and deletes run. It will also vary based on the width of your rows — how many columns are in the table, what data types the columns have, and what data you have in the table.

Even if you change one very small column, the entire row is versioned – sort of

My favorite thing about SQL Server is that it includes rich tools to let us learn how the engine works by setting up simple demos. It’s truly awesome.

So I went to work to demonstrate row width impact on the version store — when only a tiny bit column is changed in the row.

Here’s how I did the test:

  • I created two tables, dbo.Narrow and dbo.Wide. They each each have a bit column named bitsy, along with some other columns.
  • I inserted one row in each table, but I put a lot more data into the row in dbo.Wide.
  • I allowed snapshot isolation on the database
  • I began a transaction in another session under snapshot isolation and left the transaction open (so version store cleanup wouldn’t kick in while I looked around)
  • I updated the bit column named bitsy for the single row in each table, thereby generating a row-version in tempdb for each table

The code I ran to test this is here, if you’d like to play around with it.

The row in dbo.Narrow is 38 bytes of in-row data

I measured the width of the largest row in dbo.Narrow using sys.dm_db_index_physical_stats. It only has one row, so the max width IS our row width. It’s a lil bitty 38 bytes.

The row in dbo.Wide is 8,054 bytes of in-row data, and 8,012 bytes off row

On the other side of the scale is dbo.Wide. This takes up 8,050 of in-row data (this happened because the ‘j’ column is a char column, which is fixed width). It also is using 8,012 bytes of LOB_DATA storage, because I used the REPLICATE function to put a lot of garbage into the k column in dbo.Wide.

Let’s look at the version store!

We can query the version store through sys.dm_tran_version_store. Querying this in detail on a production system may not be great if there are a lot of versions, but this is an idle test instance and I’ve only updated two rows.

There are two rows in my version store. The Narrow table has 24 bytes of versioning data, the Wide table has 8,040 bytes of versioning data.

Observation 1: my whole row was versioned except for the LOB data

Large object data stored on LOB pages, like that in my NVARCHAR(MAX) column, can be versioned. Snapshot Isolation and Read Committed Snapshot Isolation work with LOB data too, and it’s easy to verify that that they can be versioned by updating the column named ‘k’ in this demo.

But in this case, I don’t see them in the version store.

That’s awesome– because I didn’t update the column that’s stored on LOB pages! I only updated a bit type column on that row. SQL Server is being clever about this.

Observation 2: my versioned rows are 14 bytes smaller

Comparing the size of my row-versions with the rows in the table:

  • dbo.Narrow: 38 bytes in the table row, 24 bytes in the row version = 14 bytes difference
  • dbo.Wide: 8,054 bytes of in-row data in the table row, 8,040 bytes in the version store = 14 bytes difference

That 14 bytes is a transaction timestamp/pointer piece of information in row of the data table itself that is an additional part of the overhead of row versioning — but it doesn’t have to be copied into tempdb as well.

Summing up: Wider rows will generally mean more tempdb usage for versioning

Even if you’re just updating one tiny column in your table, the entire row will be versioned — with one important exception.

My testing shows that data stored on LOB pages isn’t appearing in the version store unless I modify that data itself (not simply modifying another column in the row). Note: I haven’t found this explicitly documented while searching, and I only tested this against SQL Server 2017, so there may well be times when this is not the case– but it does make sense that SQL Server could do this quite elegantly for LOB pages.

What should I do if I am considering enabling Snapshot Isolation?

If you have the ability to load test or run a ‘replay’ workload outside of your production environment, that’s absolutely the best bet.

If that isn’t realistic in your world, you can baseline performance on your current production workload, then test allowing snapshot isolation against the database before anything uses it at all. Versioning will still take place, and you can compare performance with your established baseline.

Want to learn more about transaction isolation?

Join me for my upcoming online SQL Seminar on isolation levels and blocking (a few seats are currently on sale) – or come to my in-person pre-conference session at the SQLPASS Summit on preventing fake news in your data.

Posted on

Code: Fixing a Filtered Index Which is Causing Data Modifications to Fail

This question came up in a webcast recently: if a filtered index is causing data modifications to fail, does disabling the filtered index fix the problem?

I wasn’t 100% sure — I couldn’t remember the last time I’d tried, if I ever had. So….

Let’s test it!

First, let’s reproduce the problem: we’ll create a filtered index on a table, then set up a session who can’t read from it due to an ANSI_SETTINGS conflict. (A list of required SET OPTIONS for filtered indexes is documented here.)

Here’s my filtered index

I created this in the WideWorldImporters sample database.

CREATE INDEX ix_SalesOrders_filtered_IsUndersupplyBackordered_OrderDate_INCLUDES ON
  Sales.Orders ( OrderDate )
  INCLUDE ( OrderID, CustomerID, SalespersonPersonID, PickedByPersonID, ContactPersonID, BackorderOrderID, ExpectedDeliveryDate, CustomerPurchaseOrderNumber, IsUndersupplyBackordered, Comments, DeliveryInstructions, InternalComments, PickingCompletedWhen, LastEditedBy, LastEditedWhen)
  WHERE (IsUndersupplyBackordered = 0);
GO

Now, let’s make our session incompatible with the index

All I have to do to make an insert fail is violate one of those required ANSI settings in my session.

Here we go!

SET ANSI_NULLS OFF;
GO

BEGIN TRAN
  INSERT Sales.Orders ( [CustomerID], [SalespersonPersonID], [PickedByPersonID], [ContactPersonID], [BackorderOrderID], [OrderDate], [ExpectedDeliveryDate], [CustomerPurchaseOrderNumber], [IsUndersupplyBackordered], [Comments], [DeliveryInstructions], [InternalComments], [PickingCompletedWhen], [LastEditedBy], [LastEditedWhen])
  SELECT TOP (1) [CustomerID], [SalespersonPersonID], [PickedByPersonID], [ContactPersonID], [BackorderOrderID], [OrderDate], [ExpectedDeliveryDate], [CustomerPurchaseOrderNumber], [IsUndersupplyBackordered], [Comments], [DeliveryInstructions], [InternalComments], [PickingCompletedWhen], [LastEditedBy], [LastEditedWhen]
  FROM Sales.Orders;

This results in the Error 1934

Msg 1934, Level 16, State 1, Line 25
INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

OK! Now to disable the filtered index

I suck all the pages out of the filtered index, leaving only the metadata behind:

ALTER INDEX ix_SalesOrders_filtered_IsUndersupplyBackordered_OrderDate_INCLUDES ON
  Sales.Orders DISABLE;
GO

And now, when I retry my insert….

(1 row affected)

It worked! As long as this filtered index is disabled, the insert works again for the session with the incompatible ANSI_NULLS setting.

Personally, I’d rather drop the filtered index

Rebuilding the filtered index will cause this problem to happen again — until all sessions modifying the table have the required se ssion settings in place.

So to prevent actual rebuilds, I’d rather drop the index until everything is in order for the index to be recreated.

 

Posted on

Free Session! DBA vs Deadlock: How to Out-Index a Deadly Blocking Scenario

I’ll be doing a free online webcast with the fine folks at Quest Software this June. Join us to practice solving and preventing deadlocks in SQL Server.

DBA vs Deadlock: How to Out-Index a Deadly Blocking Scenario

June 14, 2018
8:30 AM – 9:30 AM PST

Deadlocks strike fear into the hearts of even seasoned DBAs — but they don’t have to!

In this session, you’ll get the code to cause a sample deadlock in SQL Server. You’ll see how to interpret the deadlock graph to find out where the conflict lies, and how to design an index to make the deadlock disappear.

You’ll leave the session with the steps you need to confidently tackle future deadlocks.

Register for this free online session here.

Posted on

What is Deferred Name Resolution, and How Can It Go Wrong?

I got a question recently about why a particular bit of code was failing when running.

My first thought: oh, that’s probably related to… whatsitcalled.

You know, whatsitcalled, one of those features you notice just often enough so that it’s name doesn’t come to mind immediately.

It’s deferred.

In this case, it was actually named “Deferred Name Resolution.”

Deferred name resolution lets you create a procedure referencing something that doesn’t exist

In this case, I’m creating a temporary stored procedure (out of laziness, it means I don’t have to clean up a quick demo) –

CREATE OR ALTER PROCEDURE #test
AS
IF 1=0
       EXECUTE dbdoesnotexist.dbo.someproc;
GO

The database dbdoesnotexist does NOT exist, but I’m still allowed to create the procedure.

When I do so, I get an informational message:

The module '#test' depends on the missing object 'dbdoesnotexist.dbo.someproc'. The module will still be created; however, it cannot run successfully until the object exists.

This can be useful in some cases where you’ll be querying a table or procedure that may not exist all the time, but which will exist when a certain code block is run.

You can’t always count on deferred name resolution

What if our code refers to something that may exist, but isn’t accessible?

Here’s a slightly different code sample:

CREATE DATABASE offlinedb;
GO
ALTER DATABASE offlinedb SET OFFLINE;
GO

CREATE OR ALTER PROCEDURE #test
AS
IF 1=0
       execute offlinedb.dbo.someproc;
GO

This results in error 942

Creating the procedure fails in this case. The error given is:

Msg 942, Level 14, State 4, Procedure #test, Line 5 [Batch Start Line 17]
Database 'offlinedb' cannot be opened because it is offline.

If I set the empty database “offlinedb” to be online, then deferred name resolution works and I can create #test. If I drop “offlinedb”, same thing– no problems.

But while offlinedb exists in an offline state, I get error 942 at the time I attempt to create the procedure.

Takeaways

Little quirks like this are a good argument to configure test and pre-production/staging environments in ways that mirror production as much as possible.

And if you think you might run into this situation, it might be worth using a bit of Dynamic SQL to avoid it!