FILLFACTOR is really not that complicated

I keep coming across code that explicitly sets the FILLFACTOR option on an index for no good reason. FILLFACTOR is not complicated - it leaves some percentage of index pages empty in order to prevent page splits and resulting fragmentation and low page density during inserts into the middle of a key range. It can be a useful option iff:

- the table will actually get rows inserted into it!

- there is no monotonically increasing index key (like a primary key identity column)

- the author is intimately familiar with the usage pattern of the table and knows how to balance the additional I/O required to read the extra pages with the I/O saved by preventing page splits

Otherwise you're just taking an automatic IO performance hit of n% (where n is 100-(FILLFACTOR)) for no good reason.

Particularly ridiculous is the code that I've found which does something like this:

CREATE TABLE #tempstuff (foo int, bar varchar(20))

INSERT INTO #tempstuff SELECT foo, bar FROM dbo.sometable

CREATE INDEX ix_reallyNotUseful ON #tempstuff (foo, bar) WITH FILLFACTOR = 90

First off, make the index clustered. If this table will not see any more inserts, there's no reason to leave it as a heap and then have to create surrogate keys in order to build the non-clustered index. Might as well physically order it by the index keys and gain the additional performance of the clustered index.

Second, the table is not getting any more inserts after this. Why would you care about leaving extra space on the pages? Set the FILLFACTOR to 100!

This has been a public service rant from your irritable neighbor database engineer.

Conflicting BULK INSERTs

Let me start by saying that this is not a post about concurrent BULK INSERTs into a single table or SELECTs during a BULK INSERT, both of which the SQL CAT has covered pretty effectively, along with many other questions about bulk data loading. My problem is with concurrent BULK INSERTs into different tables on the same database.

Basically, there are two different data sets, one large and one small, that get BULK INSERTed into two different tables in the same DB on different schedules. Like Alan's bicycle in Cryptonomicon, every so often the two schedules will align and the chain will fall off, or more accurately, the big BULK INSERT will hold up the small BULK INSERT. There doesn't seem to be a reason for the SQL engine to block one with the other; they're not hitting the same table, so locking should not be a problem, and the machine in question is a dual quad-core, so SOS_SCHEDULER_YIELDs are not prevalent.

OK, if it's not the SQL engine, it's probably a shared resource issue. Sure enough, an inspection of the wait types during a concurrent execution of these inserts revealed a lot of PAGEIOLATCH_SH, indicating a disk problem. But this database is in Simple recovery mode, so it shouldn't be doing a lot of logging on a bulk-logged operation like BULK INSERT. We should just be writing dirty pages to memory and then checkpointing at our leisure, right? Why are the disks getting hit so hard? Unless we don't have enough memory to write those dirty pages...

Which a perfmon counter log revealed to be precisely the case. The box only has 3.5 GB of RAM, has a number of other applications running, and shows high levels of paging and low Page Life Expectancy. The lack of memory available to SQL means that the larger dataset being BULK INSERTed won't fit into RAM, so checkpointing starts almost immediately and blocks the smaller BULK INSERT from completing until the slow local disks can finish with the bigger one. The easiest and cheapest solution will almost certainly be to add RAM, if only I can talk Systems Engineering into digging some up.

Performance of LIKE vs = in SQL queries

Got a question from a coworker yesterday about the relative performance of LIKE vs = in a SQL query, and had to think about it a bit. He wanted to know whether there was a performance penalty to using LIKE if there were no wildcards. In other words, if this:

SELECT * FROM tbFoo WHERE colA LIKE 'bar'

would perform equivalently to this:

SELECT * FROM tbFoo WHERE colA = 'bar'

My knee-jerk response was that the = would be faster, but I thought about it and realized that the query optimizer would actually see them as the same thing. A check of the query plans against a quickly-created tbFoo confirmed it. So that's what I told him.

Except that I realized a moment later that there was a major caveat - the query optimization depends on how the statement is parameterized. If it's purely ad hoc SQL and being compiled at run-time, then the statements are equivalent, but if there's going to be any plan re-use, either by including the statement in a stored proc or preparing it and executing via sp_executesql, the LIKE will impose a significant penalty.

This is because the optimizer doesn't know at compile time whether the parameter to the LIKE operator will contain a wild card, so it can't use the more specific optimization (including index selection) that it could with an = operator. So if you mostly pass parameters without wildcards, you will be executing a suboptimal query plan. Keep this in mind when designing your query!

---

I realized after writing this that this topic has been covered in more depth and with more eloquence by Erland Sommarskog and any number of other SQL MVPs and bloggers, but this post is already written, so I'm releasing it into the wild anyway.

CPU Affinity Masking plus High CPU = Login Timeouts

Recently we saw an alarming issue with one of our database servers. Under heavy but not prohibitive load (60-80% CPU), it stopped accepting new connections intermittently. Obviously this is bad for any server, especially one that handles thousands of client connections simultaneously.

Some of the hard drives were under heavy load, especially tempdb, but another anomaly was a long-running query using many linked server connections (11!) that had been killed but was stuck in rollback several hours later. It was consuming near 100% of the cycles on one CPU core. We tried a variety of the usual DBA tricks to get rid of this spid, but nothing worked. It wasn't clear to us how this could cause the server to stop accepting connections, however.

Another oddity was the appearance in the System logs of messages like this:

The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.

I asked our Systems guys about this, and they said that this had been noticed a few months ago and a workaround had been put in place as per KB 931279 - the CPU affinity mask had been set in SQL Server.

Hmm.

This happened again a week later, minus the spid stuck in rollback, but with one CPU slammed at 100% again. We had engaged Microsoft PSS to assist with the problem, but so far all they had told us was that we had tempdb IO issues, which we knew. (DVNation, please finish the Windows drivers for your IODrives so we can use them for tempdb!)

So here's my theory, cooked up with one of the other DBAs: the login issues are being caused by the combination of affinity masking and one CPU at 100%. This could happen because schedulers are affinitized by the mask to a single CPU, making them unable to hop CPUs when one is under heavy load. User logins round-robin between schedulers, so if a scheduler is stuck to a single CPU and that CPU is not making enough cycles available to log someone in, eventually the login will timeout and fail.

Plausible? Anyone else seen this kind of issue?

UPDATE:

I was right. After removing the affinity masking, we no longer saw login timeouts, even when the server was near 100% CPU load. Be careful with those affinity masks.

WRITELOG waits

My team has been assisting another team off and on for several months with performance tuning their overnight DB processes. There are many possible bottlenecks in their process, but since the app writing into the DB is highly complex and multi-tiered, we're trying to focus on the internal DB issues first.

The first big win was dropping triggers from the tables being written to, which resulted in a 20% reduction in overall job time. Now the biggest WAITTYPE during the process is WRITELOG, with about 50-60% of the overall time in both QA and Prod. NETWORKIO is next, and I'd really like to SET NOCOUNT ON, since these writes are created as individual transactions and dropping the count should reduce the NETWORKIO substantially, but that's been problematic so far.

Digging into the WRITELOG waits, the disk queues are pretty low (0-5 range going against 80 or so physical disks on a big Hitachi SAN with a ton of cache), so the storage guys have dismissed the disk as the problem. However, I think it's still the disk, just not in the usual way.

The standard problem is that the disk subsystem runs out of write cache and has to start de-staging data to disk, and the physical disks can't write data as fast as it's coming in. That's not the case here, because the cache on the SAN is huge (70+ GB, according to the SAN guys), it can accept data faster than the logs are created, and it hasn't gotten close to the high watermark, when it starts de-staging. So what's the problem?

Well, in this case, the disks attached to the SQL Server have been attached as a single logical drive, so all the IOs get queued by Windows against that drive. Therein lies the problem, I believe. The SAN is fast, but every operation goes against that same queue, so one slow read for a piece of data not in the cache can slow down all the writelog IOs queued up behind it. This is what I believe is creating the WRITELOG waits.

The proposed solution is simple: add another logical drive dedicated to logs. Even if it's hitting the same physical disks, the addition of another disk queue should allow the log IOs to flow through without being blocked by slower data IOs or data reads. Stay tuned to see if it works.

Addendum: We tried this before at Capital IQ, splitting up sets of disks into multiple metaLUNs and comparing them against the same number of disks in one metaLUN and showed barely any performance gain (within the margin of measurement error). However, the difference between that situation and this one was the amount of cache involved - we were always past cache on the CapIQ SAN, so the disks were the bottleneck. Here I suspect it's the queue.

Lists vs. temp tables

Dynamic SQL is in heavy use at my new office, and I'm not sure how I feel about a lot of it. For example, one of my coworkers showed me a neat trick by which one can append to a string with each successive row returned in a select statement, like so:

select @list = @list + cast(foo as varchar(10)) + ',' from #test2

But I noticed that this technique was used in many pieces of code in our system, and it bothered me, because I was pretty sure that creating and joining to temp tables would be faster than assembling long strings and then using them with "in" clauses. I wasn't positive, however, so I assembled this test script:

SET NOCOUNT ON

if OBJECT_ID('tempdb..#timing') is not null
drop table #timing

if OBJECT_ID('tempdb..#test1') is not null
drop table #test1

if OBJECT_ID('tempdb..#test2') is not null
drop table #test2

declare
@i int
, @items int

set @items = 1022

create table #timing
(
testrun varchar(100)
, setupStartTime datetime
, setupEndTime datetime
, setupElapsed as datediff(ms, setupStartTime, setupEndTime)
, queryStartTime datetime
, queryEndTime datetime
, queryElapsed as datediff(ms, queryStartTime, queryEndTime)
, totalElapsed as datediff(ms, setupStartTime, setupEndTime) + datediff(ms, queryStartTime, queryEndTime)
)

select
identity(int, 10403, 96) as foo
, cast('some text' as varchar(20)) as bar
into
#test1

set @i = 0

while @i < @items begin insert into #test1 select 'cowabunga' set @i = @i + 1 end insert into #timing (testRun, setupStartTime) values ('Temp Table', getDate()) select foo into #test2 from #test1 create clustered index ix on #test2 (foo) update #timing set setupEndTime = getDate() where testRun = 'Temp Table' create clustered index ix on #test1 (foo) insert into #timing (testRun, setupStartTime) values ('List', getDate()) declare @list varchar(7000), @curID int set @list = '(' select @curID = min(foo) from #test2 --while @curID is not null --begin -- select @list = @list + cast(@curId as varchar(10)) + ',' -- select @curId = min(foo) from #test2 where foo > @curID
--end

select @list = @list + cast(foo as varchar(10)) + ',' from #test2


set @list = substring(@list, 0, len(@list) - 1) + ')'

declare @sql varchar(8000)
set @sql = 'select * from #test1 where foo in ' + @list

update #timing
set setupEndTime = getDate()
where testRun = 'List'

print(@sql)


update #timing
set queryStartTime = getDate()
where testRun = 'List'

exec (@sql)

update #timing
set queryEndTime = getDate()
where testRun = 'List'



update #timing
set queryStartTime = getDate()
where testRun = 'Temp Table'

select t1.* from #test1 t1
inner join #test2 t2
on t1.foo = t2.foo

update #timing
set queryEndTime = getDate()
where testRun = 'Temp Table'

select * from #timing

The results of this test vary somewhat, so it would be best to run the whole thing in a loop to assemble a statistically valid set of data, but the difference between the two methods is so pronounced that the SD doesn't really matter. Ready for the (approximate) difference?

Temp tables are 3x faster then strings.

Here's a typical result of this script running on our QA server, an 8-CPU 20-GB box running SQL 2000 build 2171:


Running it repeatedly yielded similar results with some higher outliers, but very few lower ones. The next problem will be how to propose changing our coding practices and re-writing a lot of stored procs. Stay tuned for that one, and possibly an entry about my subsequent de-hiring.