It’s been a while, but I’m finally participating in T-SQL Tuesday again! This month Erik Darling (blog|twitter) is hosting. Ever looked at a piece of code and gotten excited or maybe a bit upset? I know I have, repeatedly. Well, that’s what Erik would like to hear about this month.

I’ve generally written about these as they happen so this is going to have to be something I’ve learned about more recently. Fortunately, a new job means learning lots of new stuff. Before I tell you what the code is I need to point something out. My memory is iffy at best. When I thought of what I wanted to write about I’d completely forgotten that Erik was hosting. So no, I’m not kissing up.

A very popular tool at my new office is sp_quickiestore, written by, you guessed it, Erik. Query Store is an awesome tool that stores information about query performance and usage. sp_quickiestore is a quick and easy tool to search through this information.

EXEC sp_quickiestore @database_name = 'DBName', @Start_Date = '7/8/2023';

In this particular case, I’m pulling just the queries that have been run so far today for a specific database. There are also options that let you specify an end date, the SP name, text in the query, etc. Basically, anything you can think of to get you to the data you want.

Then once there you have, right up front, the query text, query plan, and some other basic information. As you scroll right you get a list of the top waits, first and last execution times, duration, CPU time, etc, etc. Heck, it even tells you how much tempdb space the query used. (Which has come in way handier than I ever expected.)

In other words, if you are using Query Store, this stored procedure will let you slice, dice, chop, or julienne your data to make it quick and easy to get to and use.

It’s that time of the month again! It’s T-SQL Tuesday and this month Tom Zika (blog|twitter) is hosting. Tom would like us to talk about What makes code production grade?

One of the things that I love most about being a DBA is how often the answer is it depends. (Pro-tip: Never say it depends without talking about why it depends.) Production grade could mean how robust the code is (how often will it fail and what happens if it does), how well it performs (it’s all well and good if the code blazes when you’re dealing with 10 rows but how about 10 million?), and how easy is it to maintain? (Yes, your code may still be in use 10, 15, even 20 years later. Someone’s going to have to deal with it.)

I’ll be honest, all of that is pretty variable. It depends. There are lots of different types of code used in production.

For example, if your code is something you’ll be using yourself, you may not care about how robust it is. I mean you need to make sure it won’t do any harm if it fails, either through restartable steps, rolling back transactions, etc. But if it fails you’re right there to fix whatever went wrong and move on. You can almost think of it as a set of notes on how to do a process. Maintenance is also less of an issue because no one else is likely to be looking at it but you. You still probably want some documentation in case it’s been a while since you used the code and you’ve forgotten how that tricky bit works. And performance? Well, how patient are you? I’m not overly patient so performance is pretty important for code I’m running manually.

Next, you have code that you’ve written, that someone else is going to be running manually. Say something you are posting on the internet. This code needs to be pretty robust. You don’t know how skilled the person running it is and they might run into problems. Performance doesn’t really change here. It’s still pretty important. Someone is waiting on it to finish and who knows how patient they are? And last but not least is maintenance/documentation. This time it’s super important. Someone else needs to be able to pick this code up, with no help from you, run it without an issue, make changes that could range from minor to major, and deal with any problems that could come up. Document the heck out of it.

Then there is code that’s going into an application. Now we need to make sure this code not only doesn’t break but if for some reason it does it needs to handle it gracefully. There isn’t a developer there of any type to fix things so it has to handle it itself or at least report the error gracefully with useful information. Performance again is important but no more than in the last two cases. Maintainability/documentation is somewhere between the first two cases. You may not be the one making changes or needing to understand the code, but, at least in theory, any work on the code will be done in a test environment with plenty of time to work out what’s going on.

Last but not least (at least for this post) you have automated or batch code. This code has no human involved so how it deals with failures is going to depend on the requirements. Sometimes the code can fail and move on, sometimes it will need to retry, and of course, sometimes it needs to alert someone to a problem. Performance is actually less of an issue here. I don’t want to sit and wait even five minutes for a piece of code to finish, but code running in the background? I may not care if it takes hours or even days. Maintainability/documentation is going to be exactly the same as application code. Changes and testing may not be done by you, but they should be made when there is time to figure things out. You still want to be kind to the next person but they won’t be doing anything on the fly (hopefully).

So next time you are wondering is my code production ready? First stop and think about what the code will be used for and how that affects production grade.

Which took me on a complete side thought. It would be cool if there was a way to get SQL to generate the best possible plan for a given query and then use Query Store hints to force that plan onto the query. I.e. pre-generate it. That way it doesn’t matter if SQL spends 5 hours on Sunday shaving a few milliseconds off of that query. If the query is important enough a DBA might be willing to go that route.

But I digress. Where else is good enough, good enough? Well, I was just pointing out to someone that if you use a job to pull index usage information, say every hour, you run the risk of losing as much as an hours worth of information between the last time the job ran, and the time the instance is restarted. But who cares. The information you are getting is good enough. When you are talking about how often an index is used you care about ranges anyway. Is it used at all, is it used occasionally, is it used a lot. That one hour probably won’t matter. It might if it happens to contain the only time the index was used but generally that’s pretty unlikely.

Obviously when we are talking about ACID (i.e. transactions) we are aiming for 100%. Frequently perfect is the only good enough. But if we are talking about usage statistics, progress on something, a fair number of auditing usages, etc, at that point we care more about getting the information quickly and cleanly without interfering with business usage, than we do about 100% precision.

So, when you are working on that amazing data collation process, take a step back and decide, can I work like the optimizer? Is good enough, good enough?

I want to start this by saying this is a moderately advanced book. The information in it ranges from 300 on up, but I don’t consider that unreasonable for an optimization book. If you haven’t been writing code for a while you won’t really appreciate everything in it. That said, if you are a junior when it comes to your SQL skills, you’ll still get a lot out of this, just make a point of re-reading after you get a bit more experience.

The book started with what for me was a fair amount of review material. How to get execution plans, what’s in them, STATISTICS TIME and IO, trace vs profiler, etc. I have to tell you I loved it. Reviewing the basics is a great way to start building on them and helps firm them up in your memory.

From there Benjamin moved on to collecting information in more detail using DMVs, DMFs, traces, extended events and even covered the data collector. I’ve used the data collector exactly once. It’s not overly popular. So why would Benjamin cover it? Well, I can’t speak for him, but I can tell you in my opinion it really helps to know what’s available in breadth if not in detail. That way if something comes up you know where you can start digging into and aren’t just failing around.

Benjamin also does a fair amount of internals (which is where the 300 and up comes from). He talks about how the optimizer works, different types of operators, statistics, query hints and lots more. I could wish for more information on Query Store but to be fair he does mention that he goes into more detail in his book High-Performance SQL Server.

So over all what do I think over all? Well, let’s just say, that having gotten a free copy of the e-book to review, having read the book, I’m now going to spend money (and I’m really cheap) on buying a hardcopy.

So why do data people tend to avoid (or even actively dislike) loops? (Can you say cursor anyone?). Scaleability! Loops just don’t scale well. A loop that is fast at 100 loops is going to take twice as long at 200 loops, five times as long at 500 loops and one hundred times as long at 10,000 loops. That’s a problem in the database world when at 10,000 rows a table is still considered small and depending on your experience a mid-sized table might be 1,000,000 rows or more. As in all things I like examples, so here’s a simple one.

I’m creating a table with an identity column (the primary key/clustered index) and a date column. I’m going to record times spent updating each row one at a time and just updating the entire table. Then I’m going to add 10 rows and run again, 10 rows and run again, etc until I have 7500 rows. (Not a lot but it takes a while to run.) Quick note to everyone who reads this and thinks “But …”. I’m aware this is a really simple example. If you have buts that you think will significantly change the outcome feel free to run a test yourself and if by some odd chance (unlikely but hey, could happen) feel free to put the results in the comments, or even better blog them and link the blog in the comments :).

-- Create work table & log table
CREATE TABLE LoopSpeed ([RowCount] INT, LoopTimeInMS INT, BatchTimeInMS INT);
CREATE TABLE LoopSpeedTest (Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ADate DATETIME);
GO
DECLARE @RowCount INT = 0;
DECLARE @LoopCount INT = 0;
DECLARE @StartTime DATETIME, @EndTime DATETIME 
DECLARE @LoopTimeInMS INT, @BatchTimeInMS INT

-- 750 loops at 10 row increments.
WHILE @RowCount < 7501
BEGIN
	-- Add 10 rows
	INSERT INTO LoopSpeedTest (ADate) 
			VALUES (GetDate()), (GetDate()), (GetDate()), (GetDate()), 
					(GetDate()), (GetDate()), (GetDate()), (GetDate()), 
					(GetDate()), (GetDate());
	
	SELECT @RowCount = COUNT(1) FROM LoopSpeedTest;
	SET @LoopCount = 0;

	SET @StartTime = GetDate();
	-- Loop that updates each row in the table one at a time.
	WHILE @LoopCount < @RowCount
	BEGIN
		SET @LoopCount = @LoopCount + 1;
		-- Update one row.
		UPDATE LoopSpeedTest SET ADate = GetDate()
		WHERE Id = @LoopCount;
	END
	-- Get duration of loop;
	SET @LoopTimeInMS = DATEDIFF(millisecond,@StartTime,GetDate());

	SET @StartTime = GetDate();
	-- Update all of the rows in the table in a batch.
	UPDATE LoopSpeedTest SET ADate = GetDate();
	-- Get duration of batch;
	SET @BatchTimeInMS = DATEDIFF(millisecond,@StartTime,GetDate());

	INSERT INTO LoopSpeed VALUES (@RowCount, @LoopTimeInMS, @BatchTimeInMS);

END

First let me point out that I’m running this on a very old, cheap laptop and I’m measuring milliseconds. It doesn’t take much to get wild variations in the numbers. That’s why the loop time has such wild variations at times. That said, I’m sure you notice the slow but steady increase in time taken to run the loop. Now imagine if I wanted to run an update over a million rows? We’d no longer be measuring in milliseconds, we’d be measuring in minutes or even hours. Lastly, I want you to notice that pretty flat line along the bottom. That is the very very slow increase in time that the batch update takes. At 10 rows it was approximately 0ms and at 7500 it was 20. Yes, the duration does increase with the number of rows. If nothing else, I/O does take time after all. But SQL is built to handle large numbers (these are really pretty minuscule numbers for SQL to be honest) of rows at a time.

Let me give you an analogy. Let’s say we are baking a cake and I ask you to go to the store to get the ingredients.

• 1/2 cup unsalted butter, softened
• 1 1/2 cups sugar
• 3 large eggs
• 2 1/4 cups all-purpose flour
• 1 teaspoon salt
• 3 1/2 teaspoons baking powder
• 1 1/4 cups whole milk
• 1 teaspoon vanilla
• 2 1/2 cups buttercream frosting

 
I start by asking you to get me the butter. You run to the store, get the butter and come back. Then I ask for some sugar. Then eggs, etc. Assuming you don’t just get frustrated and start yelling at some point it’s going to take a while right?

But if I had you that entire list, you run to the store, get each item and come back. Much faster right? And if I double the length of the list it’s going to take longer to get but not nearly as long as if I ask you to go to the store for each of the additional items one at a time. (Fortunately computers are very patient and don’t actually get angry about being asked to do one thing at a time.)

Conclusion
There is a good reason that database professionals tend to avoid loops. They are slow, inefficient, and scale poorly when compared to batch processing. That said, you still need to know how to use them as occasionally you have no choice. A simple example is changing the read/write status of every database on the instance. Those situations are pretty rare though. Batches really are the way to go if at all possible.

Have you ever used a temp table? Handy things aren’t they. They allow us to store data into a, well, temporary structure. I’m going to guess 99.5% of you have used them and probably use them frequently. But have you used a temporary stored procedure?

CREATE PROCEDURE #Print1
AS
PRINT 1;

This is going to act pretty similarly to a temporary table. If you use a single # it’s a local temporary SP and can only be accessed by this session. Two ##s and it’s a global temporary SP. Interestingly these act very slightly differently. First of all, while global temp tables go away once everyone is done with them (i.e. all locks are gone) and the session that created it is closed, global temp stored procedures go away once the session that created it is closed. No big surprise, there are no locks on stored procedures for them to go away. In fact, even if it’s running in another session it still goes away when the creation session is closed. The run will complete but no other session can start a new run.

Access to global temporary stored procedures is slightly different from global temp tables as well. A global temp table is accessible, as long as it exists, by any other session. A global SP is also accessible by any other session, but only by users that have the connect permission to the database where it was created. Yes, even though a temporary stored procedure exists in tempdb it still seems to have a tie back to the database where it was created. No clue why though.

Ok, now that we’ve talked about how they act (you can check out how security seems to work on them here) why would you want to use them? Well, I was initially introduced to them by Kendra Little (b/t) when she talked about the fact that when tuning stored procedures (particularly pieces of stored procedures) you have to remember that local variables and parameters act differently. Basically, the idea is that if you are tuning a query in a SP, you might pull that query out of the code, set up some variables and run the query. Guess what? Your query plan may not be the same as it was in the stored procedure because parameters are handled differently. I’m not going into a lot of detail here but you can look up parameter sniffing (yes it’s usually a good thing) and forced parameterization to get more information.

In the end, I’ve recently started using these more and more when tuning queries, or if I want to run some test code in a stored procedure but don’t want to leave code behind (I’m terrible about remembering to clean up after myself).

I got kind of excited about these and thought Hey, I wonder what other types of temporary objects I can create?? Yea, turned out I couldn’t find any. You get errors if you try to create temporary views and functions and if you try to create a temporary index you just get an index with a # at the beginning of its name.

Oh well. I’m still excited about temporary stored procedures.

I did some looking because that sounds really odd. I mean that’s a LOT of time for just over a thousand rows. Ok, so I check the server speed, drive speed, anything blocking, wait stats etc. All the usual suspects. No luck. Then I checked the table size:

exec sp_spaceused [TableName];

That’s an pretty big table for the number of rows. Particularly given that it’s all data and almost no index. Ok, let’s look at the time/io breakdown:

SET STATISTICS TIME, IO OFF

SELECT [Col1], [Col2], [Col3], [Col4], [Col5]
      ,[Col6], [Col7], [Col8], [Col9], [Col10]
  FROM [dbo].[TableName]
GO

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(1134 rows affected)
Table ‘TableName’. Scan count 1, logical reads 182, physical reads 0, read-ahead reads 0, lob logical reads 53742, lob physical reads 18, lob read-ahead reads 27384.

SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 69110 ms.

Hmm, 182 logical reads. Not a whole lot. But wait, lob logical reads 53742, lob physical reads 18, lob read-ahead reads 27384. Or a total of 81144 reads. That’s a fair amount. In case you weren’t aware lob stands for large object. Lob objects are things like varchar(max), nvarchar(max), filestream, xml and varbinary. Not all of the data will be in an lob page. For example my understanding is if you use a varchar(max) to store 15 bytes it will still be stored in the normal data page. It’s only when it no longer fits it gets pushed to an lob page. Either way I looked over the data types for the table. There were some INTs, VARCHARs, a couple of DATETIMEs and an XML.

An XML. Ok, let’s try this differently.

SET STATISTICS TIME, IO OFF

SELECT [Col1], [Col2], [Col3], [Col4], [Col5]
      ,[Col6], [Col7], [Col8], [Col9] /*, [Col10] */
  FROM [dbo].[TableName]
GO

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(1134 rows affected)
Table ‘TableName’. Scan count 1, logical reads 181, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 129 ms.

Well look at that. One less logical read, no lob reads and it took all of 129 ms. That’s 537 times faster.

This prompted a quick conversation with the client.

Me: Do you really need to pull that XML column every time you do the query?
Client: Well we need to keep it. We need that data.
Me: I get that. But when you do the query to pull all the rows do you need the XML for all of them at the same time? Or do you look at the basic info in a grid, scroll through it and then bring up a single detail page with the XML for just that single row.
Client: Oh yea. That’s exactly what we do.

So yes, not pulling a single column, that wasn’t needed most of the time, on a tiny, tiny table made a huge difference. Only pull the data you need people. * is a really bad habit, and even in this case where they were pulling the columns by name they still need to pay attention and only pull the columns they were actually going to use.

-- Set up demo table
CREATE TABLE DisableIndexes (
	Id INT NOT NULL IDENTITY (1,1)
		CONSTRAINT pk_ci_DisableIndexes PRIMARY KEY
	,Col1 INT
	,Col2 INT
	);

CREATE NONCLUSTERED INDEX ix_DisableIndexes_Col1 ON DisableIndexes(Col1);
CREATE NONCLUSTERED INDEX ix_DisableIndexes_Col2 ON DisableIndexes(Col2);
GO

Disabling an individual index is pretty simple.

ALTER INDEX ix_DisableIndexes_Col1 ON DisableIndexes DISABLE;
GO
SELECT name, is_disabled 
FROM sys.indexes 
WHERE object_id = object_id('DisableIndexes');
GO

Easy right? So how about re-enabling it? Well, there is no ENABLE option on the ALTER INDEX command. For a good reason too. Once you disable the index SQL is no longer updating it. That means that the information there is completely invalid (or more importantly SQL has no idea what parts of it are or are not valid). So the only way to get it working again is to rebuild it.

ALTER INDEX ix_DisableIndexes_Col1 ON DisableIndexes REBUILD;
GO
SELECT name, is_disabled 
FROM sys.indexes 
WHERE object_id = object_id('DisableIndexes');
GO

Awesome! That was really easy, right? Of course on a large table it may take a while, but again, depending on the table size, index size, number of indexes etc, it can still be faster to disable the indexes, load the table, and rebuild the indexes than to just do the load with the indexes in place.

Now, I’m sure at least one of you is thinking “I can rebuild all of the indexes on a table in one command, can I disable them all with one command?” And yes dear reader you can .. but ..

ALTER INDEX ALL ON DisableIndexes DISABLE; 
GO
SELECT name, is_disabled 
FROM sys.indexes 
WHERE object_id = object_id('DisableIndexes');
GO

You’ll notice something important here, right? That the PRIMARY KEY (which also happens to be the clustered index) is also disabled. The biggest problem here is that the CI (clustered index) is disabled. Since the CI is the table this means that it’s now impossible to use the table.

-- Either command (or UPDATE/DELETE commands) will give the same error
SELECT * FROM DisableIndexes;
INSERT INTO DisableIndexes VALUES (1,1);

Msg 8655, Level 16, State 1, Line 36
The query processor is unable to produce a plan because the index ‘pk_ci_DisableIndexes’ on table or view ‘DisableIndexes’ is disabled.

Disability the primary key in and of itself isn’t a huge issue (remember that the PK doesn’t have to be the CI). It is a unique constraint though. And fair warning, if you disable a unique index then you will be able to insert non-unique data and the rebuild will fail if you do.

So, in the end, if you have a heap you can disable all of the non-clustered indexes at once. If you have a clustered index then you can’t. You could disable all of them and then rebuild just the CI but for a large table that could take quite a bit of extra, unnecessary time.

Baselines

Why are baselines important? Have you ever heard The server is slow? Is it? Can you be sure? I mean what exactly is slow? It’s such a personal thing. I’ve seen people complain about queries running slower than usual when they were actually, provably, faster. The only way you can tell is if you know how fast things are on average. In fact, it’s handy to have a normal range. I should probably point out that normal does not mean acceptable. It will, however, tell you if things are getting worse. On top of that if you want useable alerts without a ton of false positives then you need a baseline. That way you can tell when a setting is no longer normal.

Anyway, enough about why, here’s your homework. Read this post by the Tiger Team.

SQL Server Performance Baselining Reports Unleashed for Enterprise Monitoring !!!

You could also read this SE question and its responses (there are also a fair number of good links):

Creating SQL Server Performance Baseline Monitoring

Once you’ve done the reading take some time and think about how you can implement baseline reporting in your environment. Actually setting it up is probably a bit much for this post (although I may add something like that in later) but at least start thinking about it.

tl;dr; update These numbers are frequently very important when comparing two runs of a query during performance tuning. If one has high logical reads and the other has high physical, disregard the results on the high physical run and try again.

I/O and Logical I/O are two pieces of information you will see all over the place.

• SET STATISTICS IO as physical reads and logical reads
• sys.dm_exec_requests and sys.dm_exec_sessions as reads and logical_reads
• sys.dm_exec_procedure_stats and sys.dm_exec_query_stats as x_logical_reads and x_physical_reads

 
Just to name a few.

There is an important difference between the two. Physical reads/IO are page reads from the disk. Logical reads/IO are pages read from memory. So why do we care? Reading from the disk is massively slower than reading from memory.

Side note: Simplifying dramatically, data gets stored in memory when it is read from disk to satisfy a query. It remains in memory until it is pushed out as new data needs to be moved to memory. Data that gets referenced more often is less likely to get pushed out of memory.

Now, realistically, pages are always going to have to be read from disk, at least initially and if you have a reasonable amount of memory then once the data is loaded to memory it should stay there for a while. (See page life expectancy.) So I ask again, why do we care? Honestly, most of the time I don’t. I add the two numbers together and that tells me how many pages the query is having to read. Hopefully, the vast majority are from memory (logical) but that will change depending on a ton of factors, not the least of which is Is this the first time in the recent past that the query has been run; or the second; or the twenty-second? We have absolutely no control over factors like that, so when tuning, or whatever, pay attention to the total, not the individual numbers.

That said, there are times when you care about them separately, but honestly, I couldn’t tell you what they are off the top of my head.