Michael J. Swart

“Just because you can, doesn’t mean you should.” SQL Server has a number of limits and restrictions (many of the limits are described here). Most of the time these restrictions don’t feel restrictive at all. I can’t think of a single scenario where I’d want more than 128 characters to name a column. And I don’t think I’ll ever need to select more than 4096 columns at a time. I get the feeling that if you need over 10% of what Microsoft restricts you to, then you’re probably doing it wrong.

So I turn that thought into Swart’s Ten Percent Rule:

If you’re using over 10% of what SQL Server restricts you to, you’re doing it wrong.

Always? No not always. It depends of course on what restriction we’re talking about. Sometimes the rule applies more than other times. I’d like to evaluate a number of restrictions here and judge whether the rule applies or not. I’ll be assigning each 10% rule a “Risk that you’re doing it wrong”. The levels are Severe, High, Elevated, Guarded or Low.

SQL Statement Length

Max Size: 256 megabytes (Using the default network packet size.)
10% of restriction: 25 megabytes
Risk of doing it wrong: Severe

Let’s take a step back. Why do you want to execute a statement that’s 25 megabytes long? The only scenario I can think of is that you’re sending data to the database to be parsed and stored. There’s much better ways to send data to the database then this (See Erland Sommarskog’s definitive Arrays and Lists in SQL Server).

Or maybe you’re creating a one-off huge IN clause to pass to SQL. Better to join to a temp table or a table valued parameter.

Identifier Length

Max Size: 128 characters
10% of restriction: 12 characters.
Risk of doing it wrong: Low

Twelve characters is not a lot to work with when coming up with names for tables or columns. If you had a column or table name with more than 12 characters, I wouldn’t be at all concerned. For comparison, Adventurework’s average column is 11 characters long. Their largest column is “HomeAddressSpecialInstructions” at 30 characters. My friend, Karen Lopez, told me she once came across this beast of a column name: “RetailTransactionLineItemModifierEventReasonCode” at 48 characters. That’s getting extreme (despite intellisense), but 12 is fine.

Number of Columns in a Table

Max Size: 1024 Columns
10% of restriction: 102 columns
Risk of doing it wrong: High

Maybe you need a lot of columns because you’re treating a table like a spreadsheet. The worst offender I’ve ever seen had a whopping 96 columns. It was designed by someone who didn’t know any better. By the way, the most notable modeling smell coming from this table was the columns named [Int01] to [Int30]). In this case, the database designer was definitely doing it wrong.

Maybe you need a lot of columns to support growing functionality. As an example, say you have a table like

CREATE TABLE UserProfile
(
    UserId int not null primary key,
    PhotoFileId int null,
    PreferenceTags varchar(1000) not null,
    CanEditWidget bit not null,
    CanViewWidget bit not null,
    CanAddWidget bit not null,
    IsMonitored bit not null,
    ...,
    IsApprovedForFastTrack bit not null
)

Then the thing to ask yourself is why is this list of columns changing so much? In this example, we see a growing list of boolean attributes (which will probably continue to grow as the application evolves). In this case it makes sense to redesign your schema using:

CREATE TABLE UserProfile
(
    UserId int not null primary key,
    PhotoFileId int null,
    PreferenceTags varchar(1000) not null
)
 
CREATE TABLE UserPermissions
(
    UserId int not null 
        references UserProfile(UserId),
    PermissionId int not null
        references Permissions(PermissionId),
    IsGranted bit not null
)

But maybe you really do need this number of columns, you’re probably storing semi-structured data. By semistructured data, I always think of how one would store cascading style sheets or health records.

Semistructured data is really difficult to model. As an alternative to these difficult-to-manage columns, consider storing the values in an xml column (accepting that you can fetch, but probably no longer want to filter). If you need something more radical, maybe consider a schema-less json document database like couchdb (I can’t believe I said that).

SQL Server has something called wide tables which use sparse columns but you’re on your own here. It’s not something I could recommend.

Simultaneous Server Connections

Max Size: 32,767
10% of restriction: 3,277.
Risk of doing it wrong: Guarded

The number of connections your SQL Server instance can support can be found by this query

SELECT @@MAX_CONNECTIONS as [this many]

and the number of open connections on the server is basically this

SELECT count(1) as [this many] FROM sys.dm_exec_sessions

It’s not that unusual to have 1000 or 2000 connections open. It is rarer to see that many active queries. But it’s not unusual to see 1000s of connections, most of whom are sleeping. It’s possible you have a large number of services and web servers connecting to a variety of databases on your server. With ado.net connection pools, it’s common to have a bunch of idle connections.

The best advice I can give is to get a baseline. Watch the connections regularly in order to understand what is normal. Maybe start with

select 
    count(1) as [connections], 
    count(case [status] when 'sleeping' then null else 1 end) as [active connections],
    db_name([dbid]) as [database name],
    [hostname], 
    [loginame],
    [program_name]
from sys.sysprocesses
where [dbid] NOT IN (0)
group by [hostname], [loginame], [dbid], [program_name]
order by connections desc

Then go from there. Once you know what’s normal, you can then watch for changes to the status quo.

Database Size

Max Size: 512 Petabytes
10% of restriction: 51 Petabytes

51 Petabytes is unwieldy in so many ways I’m not going to list them here. I’m not saying such big data sizes are impossible with a centralized RDBMS like SQL Server. But I am saying that if you’re not sure about whether you can scale SQL Server to that size and you’re reading my blog looking for advice then…

Risk of doing it wrong: Severe

Other metrics

I think Swart’s Ten Percent Rule applies to a lot of things. Try to apply it the next time you come across a SQL Server restriction and let me know if you find anything interesting.

Google reader is dying, but I don’t think it’s the end of RSS and blogs. Blogs, I think, will always be a great medium for technical communities. But it has got me thinking about where and how I spend my time.

For those who care, I’m going to begin splitting time between SQL blogging and drawing. For the past couple years, you may notice that I’ve always combined both.

In practical terms, that probably means simpler or fewer illustrations here at MichaelJSwart.com.

But it also means I’ll begin posting stuff like the following over at deviantArt: http://mjswart.deviantart.com/ rather than here.  Or at least all the digital stuff and any analog stuff I feel like scanning.

Cheers!

I list some blog topics that I don’t plan to complete.

I have a rule of thumb that I try to follow when writing. If I can write something in a simpler way I do. If I can cut out words, sentences or whole paragraphs without altering the meaning, I do. Sometimes the thing I’m left with is so short, simple and straightforward that I wouldn’t even bother tweeting it.

For example, a couple weeks ago, after a shortening revision, I wound up with “Index your tables properly while designing databases”. That’s not the title, that’s the whole thing. Something so short and simple doesn’t deserve a blog post and so neither did the long version of that post!

So I’m cleaning house. I want to post a bunch of ideas that were never good enough on their own to make the cut.

Knowledge Philanthropy

Those two words sound great together don’t they? I’ve always felt like more of a knowledge consumer. In fact this blog you’re reading is an effort on my part to “give back”. But no matter what, I’ve always felt like I’ve received way more than I’ve given.

Your Database is Not Perfect

But it doesn’t need to be. It needs to be stable, scalable, and easy to maintain. It needs to meet any requirements you care to define. Anything after that is going to be a harder sell.

Measuring the Effects of Fragmentation

An interesting experiment I started, but it led me past DBA-land into SAN Admin land and I was not comfortable saying anything with authority here.

Q & A for questions I made up:

I must have written the following while half asleep and I have absolutely no idea what it could mean:

ala Esquire: Fake… subtly bad advice “Jesus Bless You” Generic advice that gets suddenly personal: Why do delis always give pickles with sandwiches?” “Ordering Pheasant”

Quotestring — Safe way to use Data as Code?

No, I wouldn’t count on it.

Hey bud!

The following was inspired by comments at “Last Word On Scans” It was drawn for drawing’s sake.

Range Scans on Multicolumn Keys

Say you have a table with an index on (LastName, FirstName) and you want to select all the rows which sort between “Lucas, George” and “Spielberg, Stephen”. I’ve never been able to find straightforward syntax that results in a query plan with a simple range scan.

I’m a Paul White Fan

Not very many of my friends and family hold up their side of the conversation when I talk about databases. But at the PASS Summit, there are hundreds of people who will! And I think that’s why I love it. I always intended to write about my experience at 2012′s summit, but I couldn’t think of anything meaningful to write. I knew that Paul White’s session was a highlight for me, but I didn’t have much else to say.

On Being Competent

Billy Joel described himself once like this:

“… I consider myself to be an inept pianist, a bad singer, and a merely competent songwriter. What I do, in my opinion, is by no means extraordinary. I am, as I’ve said, merely competent. But in an age of incompetence, that makes me extraordinary.”

That particular quote probably struck me at a time when I wasn’t feeling particularly perfect enough at what I do.

UNDELETE

Is here! Oh actually, april fools. It’s actually called Point in time restore, and it’s been available for a while. And the syntax is a little different…

This blog post is meant to help people troubleshoot page latch contention on 2:1:103. If that’s what brought you to this page, then hopefully you find it useful. But first…

Initial Investigation

Last week I was asked to help tackle a production outage. Queries were slow enough that the system was considered unavailable. And just like any other performance problem I started by having SQL Server tell me what was wrong with itself.

• I first started with sp_whoisactive but it took about 30 seconds for it to return. Several queries were blocked by others and some of the lead blockers were waiting for a PAGELATCH_EX on 2:1:103
• Then I checked on the most common waits using a query found at Paul Randal’s article: Wait statistics, or please tell me where it hurts. (I don’t have that page bookmarked, every time I’m in trouble, I google “Tell me where it hurts Paul Randal”). I learned that PAGELATCH_EX contention was our most serious wait type. Paul mentions that he sees this kind of contention most commonly on tempdb.
• I followed a link from Paul’s article to Robert Davis’s Breaking Down TempDB Contention. A script there helped me to discover that while there was lots of tempdb contention, it was all on page 2:1:103, which is not PFS, GAM or SGAM.

Finding More Info

So I looked for more help.

• Web Search: My work colleague found and then pointed me to Latch waits on 2:1:103? You are probably creating too many temp tables in SQL Server by Microsoft’s Matt Wrock who faced the same problem. If you found this page because you’re facing the same issue then stop reading this article and go read Matt’s first. He explains what’s going on better than I could. Think of this article as a kind of sequel to his article.
• StackOverflow: (serverfault in this case) has TempDB contention on sysmultiobjrefs SQL 2005. The best answer there is Matt pointing back to his blog article.
• Microsoft Support who helped us out, but maybe not as quick as we’d like. To be fair, the turnaround time that we would have been happy with was measured in nanoseconds.

With that information it became clear to us that we were creating temp tables too often. And we were creating them in a way that made it impossible for SQL Server to cache. Did you know that? That SQL Server can cache temp tables? When a query is done with a temp table, SQL Server can truncate that table and give it to the next query to avoid having to create it again. Cached temp tables reduce tempdb contention including contention on this page. But as it turns out, SQL Server cannot cache temp tables from ad hoc queries.

But who creates temp tables that often? We did, just by using a table valued parameter in a parameterized query. Since SQL Server began supporting table valued parameters (introduced in SQL Server 2008), we have been gradually moving towards this practice in lieu of sending xml to be shredded.

… And More Info Including Some Other Links

I can’t help including some extra articles I found on 2:1:103 contention.

Robert Davis blogged about 2:1:103 contention in Tempdb Contention That Can’t Be Soothed. His advice is to remove statements in code like “SELECT … INTO”.  However, I believe that such statements contribute to PAGELATCH contention only if the statement is not part of a stored procedure (i.e. can’t be cached). I also think there are more common causes of 2:1:103 PAGELATCH contention than SELECT … INTO statements.

But Robert did link to a demo by Paul Randal (an absolutely amazing 5 minute demo linked from a 2011 issue of his newsletter). Paul tells us

• That “the SQL Team knows about this. It’s a known issue. Hopefully something will be done about it in one of the future releases.”
• But unlike SGAM or GAM contention, there’s absolutely nothing you can do about this to spread the contention around.

From a DBA point of view there’s not much that can be done. So as DB Developers, we have to find a workaround. But before I get to that, I want to mention some things that were not so useful.

… And Some Bad Info

There was some red herrings out there…

• Add more tempdb files (nope, wrong kind of tempdb contention).
• Many resources suggested enabling trace flag T1118. It’s a trace flag that eases tempdb contention, but not this kind.
• Get faster tempdb disks? No, PAGELATCH contention is for in-memory copies of pages, not disk (that’s PAGEIOLATCH).
• Tune the queries in question? Not a bad idea, but this problem is about the number of problem queries, not the performance of each.
• A fix from Microsoft Support: Fix: Poor performance in SQL Server 2008 R2 when table-valued functions use many table variables. Oooh… so close, but we weren’t using table-valued functions. And the workarounds they list (disabling AUTO_UPDATE_STATISTICS) did not help. But maybe it might help you?
• The Object:Created event. Whether traced with Profiler or collected with an Extended Events session, this event can report on created temp tables. Maybe I can use this event to tell me which queries are creating the tempdb tables. Nope, not this time! This event  has two drawbacks which make it useless for troubleshooting 2:1:103 contention:
  • The Object:Created event reports the creation of temp tables even when they’re cached (which don’t need a latch on 2:1:103).
  • The Object:Created event doesn’t report the declaration of any table variables (which may need a latch on 2:1:103).

Strategy: So What Do We Do?

Knowing is half the battle right? But that means we still have a lot of work to do. I’m going to recap what we know so far. We know that we have trouble when there are queries that:

• are executed frequently
• create temp tables (either explicitly or by declaring table valued parameters).
• are not cached (Microsoft explains when temp tables are not cached. In my case, it was because they were ad-hoc queries)
• require a page latch on 2:1:103.

There is a performance counter that can track the all of the above (except maybe for that last bullet). It’s called Temp Tables Creation Rate and it’s found in the perf counter category “General Statistics”. Now this is a metric you can trust. We found that a high temp table creation rate was tightly correlated to the trouble we were seeing. So when troubleshooting, look at this performance counter (and leave the “Object:Create” event alone).

So now what do we do? First, we must find the ad hoc queries that create these temp tables. Then, we have to put them in stored procedures so that the temp tables can be cached. Alternatively, we could reduce the need for creating them. It’s a workaround, but it’s what we’ve got.

Finding Such Queries Is Difficult

But here’s the hard part. In a high volume system, it’s difficult to identify exactly which queries are causing the most trouble. Microsoft support can go through tons of collected trace and performance data to try to find such queries, but it’s a long process. On our side, we looked at sys.dm_os_waiting_tasks:

Select *
From sys.dm_os_waiting_tasks

And we saw all the contention on 2:1:103, but when we tried to look up the SQL text for it

Select wt.*, st.text
From sys.dm_os_waiting_tasks wt
left join sys.dm_exec_requests er
    on er.session_id = wt.session_id
outer apply fn_get_sql(er.sql_handle) st
where wt.resource_description is not null

The text was often unavailable. Basically, I’m guessing maybe the dmv’s I was using weren’t quick enough to tell me which queries were suffering from (or causing) contention on 2:1:103. So I decided to look through the cache for query candidates that might create temp tables. here’s what I came up with. It’s not a comprehensive list and there might be false positives, but it might be enough to go on. If you know your applications well, you can tailor the filters below to something more relevant for you.

select cp.plan_handle, sql_handle, text, refcounts, usecounts
from sys.dm_exec_cached_plans cp
join sys.dm_exec_query_stats qs
    on cp.plan_handle = qs.plan_handle
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
where (
       (
        st.text like N'%READONLY%'  -- possible table valued parameter (prepared queries)
        or st.text like N'%TABLE %' -- possible table creation
        or st.text like '%#%'       -- possible use of temp table
        or st.text like '%INTO%'    -- possible table creation
       )
       and objtype in ('prepared', 'ad hoc') -- temp tables not cachable with these
)
order by usecounts desc

Going Forward

Personally after helping identify and implement the workarounds. I’m doing a couple things:

• I’m recommending that developers not create temp tables or declare table variables that cannot be cached. For now, this means we use stored procedures for any query that uses table variables or temp tables.
• We now have thresholds on our performance tests which look at the performance counter Temp Tables Creation Rate.
• I created a Microsoft Connect item. If you’re troubleshooting the same problem, head over there and let Microsoft know you’re having trouble too.