Questions, Answers, and Tips About SQL Server

Still with us? Let's assume that bumping logwrite sleep to 50 milliseconds lets SQL Server batch 20 INSERT statements per log write; the increase would let you perform 10 transactions per physical I/O. In theory, you can now do 1500 INSERT statements per second, assuming the disks are still performing 150 physical I/O operations per second (150 I/Os * 10 INSERT statements per I/O).

See the benefit now? Don't get too excited though--you won't want to fiddle with this parameter under most circumstances. In general, increasing logwrite sleep tends to be more useful in high-volume, high-concurrency OnLine Transaction Processing (OLTP) environments with very short transactions. In these conditions, your application has many users simultaneously issuing many INSERT/UPDATE/DELETE transactions that commit very quickly. Increasing logwrite sleep will help your application if it can improve throughput (the number of transactions processed per second) without having a serious impact on response time for individual transactions. The downside of increasing the parameter is that individual transactions might take longer. Their commit is delayed, and blocking might increase because the open transactions hold locks and other resources longer. You can use NT Performance Monitor to observe whether increasing logwrite sleep's value has increased the number of transactions written per log record (SQL Server: I/O--Transactions Per Log Record) without a noticeable drop in response time for individual queries.

Q: SQL Enterprise Manager (SEM) doesn't let me scroll when I'm looking at long messages in the error log. Can I do anything?

Looking at the error log with SEM is handy, but you can get very frustrated when you can't scroll to the end of a long message. Until Microsoft decides to spend five minutes of developer time to add a scroll bar to the user interface, you can use xp_readerrorlog, an undocumented extended stored procedure that returns the error log as a query result (see last month's column for a description of many other undocumented extended stored procedures). Another approach is to double-click on the error message from SEM to pop up a dialog box that shows the entire message.

Tip: Using Trace Flag 1140
Brian just came across this tip on a private Microsoft newsgroup reserved for the SQL Server Most Valuable Professionals (MVPs). Usually, private threads are part of a nondisclosure agreement, but Microsoft said we could pass this on. An MVP's customer had a large database that seemed to work fine most of the time. However, occasionally, the database seemed to stop for a few seconds--up to a minute or even more. Investigations determined that CHECKPOINT bottlenecks weren't the problem, but rather INSERT statements that normally completed very quickly.

Now, you probably know that SQL Server uses extents to manage space utilization for tables and indexes; extents are 16KB blocks of eight 2KB pages. SQL Server adds new extents to objects as they grow and ordinarily finds these extents by searching the list of available extents from beginning to end looking for ones not in use. Microsoft Product Support Services tells us that the search can take a long time on large (multigigabyte) databases and that allocating the new extents from the end of the chain might be faster than searching the entire chain from the beginning. (Note: We're greatly oversimplifying the SQL Server internals, but our description is accurate enough to explain what trace flag 1140 does.)

The solution is to set trace flag 1140 to tell SQL Server to grab free extents from the end of the chain rather than starting from the first extent and walking the chain until it finds a free one. Of course, the downside to this strategy is that your database might start looking like Swiss cheese because, along the chain, you'll have empty extents that SQL Server will never reuse. This situation will cause your database to grow much faster than usual. But tuning SQL Server is a series of tradeoffs and always requires field testing to find out what works best in a certain environment. Trace flag 1140 may help in one of your databases.

Worthwhile Links
Database administrators will be interested in Bill Wunder's site (http://www.nyx.net/~bwunder/sql/list.html), where they'll find several scripts and stored procedures--and a dozen or so links.

Send your questions to either of us or collectively to sqlqa@winntmag.com. And be sure to check out the new, very excellent Windows NT Magazine SQL Server page at http://www.winntmag.com/sql. It features more awesome Q&As, links to SQL-related Web sites, and more.