How Are Table Variables Different In SQL Server 2019?

WallaWallaBingBang


Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Why Functions Are Bad in SQL Server Where Clauses

Raised Right


It seems like every time I check out a server, the query plans are a nightmare. Users are freaking out, and management’s coffee is more Irish than Colombian.

Many times, the issue is that people are using presentation layer functions for relational processes. The where clause, joins, group by, and order by parts of a query.

Think about built-in string and date functions, wrapped around columns, and the problems they can cause.

These are things you should actively be targeting in existing code, and fighting to keep out of new code.

Nooptional


When you’re trying to get rid of them, remember your better options

  • Cleaning data on input, or via triggers: Better than wrapping everything in RTRIM/LTRIM
  • Using computed columns: Better than relying on runtime calculations like DATEADD/DATEDIFF
  • Breaking queries up: Use UNION ALL to query for either outcome (think ISNULL)
  • Using indexed views: If you need to calculate things in columns across tables
  • Creating reporting tables: Sometimes it’s easier to denormalize a bit to make writing and indexing easier
  • Using #temp tables: If you have data that you need to persist a calculation in and the query to generate it is complicated

Note the things I’m not suggesting here:

  • CTEs: Don’t materialize anything
  • @table variables: Cause more problems than they solve
  • Views: Don’t materialize unless indexed
  • Functions: Just no, thanks

More Work


Yes, finding and fixing this stuff is more work for you. But it’s a whole lot less work for the optimizer, and your server, when you’re done.

If that’s the kind of thing you need help with, drop me a line.

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

How SQL Server Statistics Get Updated Automatically

Spawning Monsters


Here we go again, with me promising to blog about something later.

This time it’s an attempt to explain how SQL Server chooses which statistics to update.

It’s not glamorous, and it may even make you angry, but you know.

They can’t all be posts about…

*checks notes*

*stares into the camera*

*tears up notes*

*tears up*

*stares off camera until someone cuts to commercials*

And We’re Back


Let’s start with the query we’re going to use to examine our statistics.

    SELECT      t.name, 
	            s.name, 
				s.stats_id,
				sp.last_updated, 
				sp.rows, 
				sp.rows_sampled, 
				sp.modification_counter
    FROM        sys.stats AS s
    JOIN        sys.tables AS t
        ON s.object_id = t.object_id
    CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
    WHERE       t.name = 'UserStats';

Right now, the results aren’t too interesting, because we only have a statistics object attached to the Primary Key.

We’re not gonna touch that column. We’re gonna use another column.

This query will get system generated statistics created on the AccountId column.

    SELECT COUNT(*)
    FROM   dbo.UserStats AS u
    WHERE  u.AccountId > 1000 
    AND    u.AccountId < 9999
	OPTION(RECOMPILE);
SQL Server Statistics
How nice of you to ask.

By itself, this isn’t very interesting. Let’s create an index, too.

    CREATE INDEX ix_AccountId ON dbo.UserStats ( AccountId );
SQL Server Statistics
Take Me Out Tonight

The index created statistics, too. With the equivalent of a full scan! See that rows_sampled column?

I mean, why not, if you’re already scanning the whole table to get the data you need for the index, right?

Right.

I’m gonna use a couple updates to flip values around.

	UPDATE u
	SET u.AccountId = u.UpVotes + u.DownVotes
	FROM dbo.UserStats AS u
	WHERE 1 = 1;
	
	UPDATE u
	SET u.AccountId = u.UpVotes - u.DownVotes
	FROM dbo.UserStats AS u
	WHERE 1 = 1;

Don’t ask me why I swallowed a fly.

But the WHERE 1 = 1 is enough to get SQL Prompt to not warn me about running an update with no where clause.

SQL Server Statistics
Modifideded.

Both stats objects have been modified the same number of times.

Let’s run our COUNT query and see what happens!

SQL Server Statistics
Oh, dammit.

We can see that only the stats for the index were updated (and with the default sampling rate, not a full scan).

Now let’s create another stats object with FULLSCAN.

    CREATE STATISTICS s_AccountId ON dbo.UserStats ( AccountId ) WITH FULLSCAN;

We’ll also go ahead and run an update again.

SQL Server Statistics
B-b-b-b-back

And then our COUNT query…

SQL Server Statistics
Ayeeeeeeee

SQL Server took two perfectly good fully sampled statistics and reduced them to the default sampling.

This doesn’t hurt our query, but it certainly is annoying to see.

That’s why newer versions of SQL Server allow you to persist the sampling rate.

Latest and Greatest


A lot of the stuff people call “rocket science” about statistics options, like auto create and auto update stats, are there for a reason.

When you let SQL Server make choices, they’re not always the best ones.

Tracking this stuff down and understanding when and if it’s a problem is hard work, though. Don’t flip those switches lightly, my friends.

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Why Does My Serial Query Plan Have Parallel Wait Stats Like CXPACKET And CXCONSUMER?

Harkening


In dramatic fashion, I’m revisiting something from this post about stats updates.

It’s a quick post, because uh… Well. Pick a reason.

Get In Gear


Follow along as I repeat all the steps in the linked post to:

  • Load > 2 billion rows into a table
  • Create a stats object on every column
  • Load enough new data to trigger a stats refresh
  • Query the table to trigger the stats refresh

Except this time, I’m adding a mAxDoP 1 hint to it:

SELECT COUNT(*)
FROM dbo.Vetos
WHERE UserId = 138
AND   PostId = 138
AND   BountyAmount = 138
AND   VoteTypeId = 138
AND   CreationDate = 138
OPTION(MAXDOP 1);

Here’s Where Things Get Interesting


SQL Server Wait Stats
Bothsies

Our MaXdOp 1 query registers nearly the same amount of time on stats updates and parallelism.

SQL Server Query Plan
If this is madness…

But our plan is indeed serial. Because we told it to be.

By setting maxDOP to 1.

Not Alone


So, if you’re out there in the world wondering why this crazy kinda thing goes down, here’s one explanation.

Are there others? Probably.

But you’ll have to find out by setting MAXdop to 1 on your own.

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.