Tempdb and AAGs

I have been working on standing up about a multi-node Availability Group “cluster” (I put quotes around the cluster because…well…AGs aren’t clusters, are they?). Now that the big node is stood up, I noticed some alerts about tempdb contention. Honestly, I’m not shocked. This environment is going to be a large data warehouse environment, and the developers using it aren’t always mindful of the result size of their queries, so I had already allocated eight tempdb files. I know Microsoft recommends adding four files at a time after establishing the first 8. [caption id="attachment_107" align="alignleft" width="275"] Buford mocking me

Buford is mocking me[/caption] So with that, I add four tempdb files and don’t think too much about it. Except, when I added the files, I didn’t think to expand them (one of those just before lunch things). A couple of days later, I noticed the added files were just sitting there, not being used. They were still the same size I had created them at. Hmmm, why is that? A little research turned up that SQL will give more weight to the larger, more active files. Damn you proportional fill!!!

Guess I'll fix that

So I start out trying to rectify my n00bish mistake. The first task, getting the first eight files shrank down to make room to enlarge the other 4.

[box]I know I could have just restarted. Yes, I know that shrinking tempdb is futile. Yes, I know that, according to Brent Ozar, I am playing in the public outhouse. But as this server was kind of in use, I decided against a restart. Besides, it’s an opportunity to learn right?[/box]

As I was saying, I need to rebalance these files out. So I needed to shrink some of them down. That’s when I started running into the work table errors. Dammit. Error: Page blah of blah: could not be moved because it is a work table page.

Off to Google. Within a few minutes of Googling, I find an article at Brent Ozar addressing the problem. Tara Kizer to the rescue, use DBCC FREEPROCCACHE. Success, some of the files shrink.

[box]Tara’s article also links to Paul Randall’s blog and Microsoft’s KB article telling us it’s ok to shrink to TempDB. Thanks for finding that Tara![/box]

Others, not so much. In fact, I get another “error”:

Luckily I find an answer over on SQLServerCentral. All I need to do is bump the size of the file(s) I can’t shrink by a few MBs to get SQL to quit thinking they are in the process of being shrank…shrunk?…whatever the correct adjective is. Thankfully that worked and I could 1) shrink the original 8 files to a consistent size and 2) increase the size of the 4 added files.

What’s the moral of the story? I shouldn’t add temp files right before lunch and when you add temp files, make sure you add them at the right size…..otherwise, SQL will ignore them until the restart.