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.
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
|[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.
|[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]|
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.