Category Archives: Tech

Oct 15

SQL Server Trace Flag 834

Posted on by

While nosing in the Tuning options for SQL Server 2005 and SQL Server 2008 when running in high performance workloads article in Microsoft’s Knowledge Base, I found the following flag available for Windows 2003 servers with at least 8 GB of RAM running SQL 2005 or above.

Trace flag 834: Use Microsoft Windows large-page allocations for the buffer pool

Trace flag 834 causes SQL Server to use Microsoft Windows large-page allocations for the memory that is allocated for the buffer pool. The page size varies depending on the hardware platform, but the page size may be from 2 MB to 16 MB. Large pages are allocated at startup and are kept throughout the lifetime of the process. Trace flag 834 improves performance by increasing the efficiency of the translation look-aside buffer (TLB) in the CPU.

First, a better explanation of what the TLB is, how its efficiency can suffer, and how allocating large pages helps:

In the CPU, there’s a translation table of pages to their locations in memory called the Translation Lookaside Buffer. If you have more pages than will fit in the table, only the most recent address are kept in the CPU, and the whole table is in elsewhere. Just like data served out of SQL Server’s buffer pool is accessed much more quickly than data served off a disk, pages in the TLB are accessed much more quickly than if the address has to be retrieved out of the full table in main memory. The fewer memory pages you have, the more likely that they will all fit in the buffer in the CPU, avoiding those costly trips out to look at the full translation table. The 834 trace flag tells SQL Server to allocate larger pages for its buffer pool (2 MB to 16 MB, depending on your hardware, rather than 8 KB) so that there will be fewer of them.

Sounds intriguing! Would my high performance system benefit from this? The information out there is pretty slim, but here’s what I dug up.

  • This Usenet posting by a SQL MVP suggests that you should only use it if your system is CPU-bound rather than IO-bound and your signal to resource wait times are high. To see if your signal to resource wait times are high, shimmy on over to the sys.dm_os_wait_stats DMV:

    SELECT wait_type, signal_wait_time_ms / wait_time_ms
    FROM sys.dm_os_wait_stats
    WHERE wait_time_ms > 0
    ORDER BY wait_type

    SELECT SUM(signal_wait_time_ms) / SUM(wait_time_ms)
    FROM sys.dm_os_wait_stats

    Mine are all zero, and my production cluster sees about 1500 batch requests per second during its peak use.

  • Monitoring for translation lookaside buffer misses is also mentioned, but I can’t find any way to do this on a Windows system. There are a few Intel Pentium 4 manuals and an O’Reilly system tuning book that mention the existence of the TLB, but there are no perfmon counters to monitor. The O’Reilly book does mention a tool for Solaris.
  • Bob Ward has a post on the PSS blog regarding trace flag 834.

My conclusion is that this wouldn’t benefit me. Check your CPU usage, I/O stalling, I/O waits and signal-to-resource ratios to see if it would help you.

 

Sep 30

Opening a SQL file against a multiserver query window with Windows authentication fails

Posted on by

One of the exciting new SQL Server 2008 features for those of us deploying SQL changes to multiple servers is the new Multiple Server Query Execution. We immediately found that opening a SQL file against a server group fails if you use Windows authentication to connect to your database servers. The query window shows that all connections are disconnected, and a login failure shows up on every SQL Server in the group.

Workaround: Open a blank query window, and paste in the contents of the SQL script you want to run.

If you’re blighted by this bug as well, please vote it up in Microsoft Connect.

 

Jul 13

This version of Microsoft SQL Management Studio can only be used to connect to SQL Server 2000 and SQL Server 2005 servers

Posted on by

Yes it can, provided that you have kept your 2005 installation up to date!

We’re in the process of upgrading our environment to SQL Server 2008. Some folks have been unable to install SSMS 2008 for various exciting reasons related to an inability to apply Visual Studio 2008 SP 1 (a documented Microsoft bug). This isn’t really a problem — you can use SSMS 2005 to connect to SQL Server 2008, you just won’t get the new SSMS features.

Occasionally, though, a particularly laggardly developer who is just now trying for the first time to connect to our development 2008 instance will email me a screencap of the following dialog box and testily inform me that it NOT TRUE that you can use 2005 to connect to 2008 (they’re fond of capslock, those developers).

The problem is on the workstation end — a failure to keep SSMS 2005 properly patched. Simply apply SQL Server 2005 SP 2 (it came out in 2007, for crying out loud!), and you’ll have no problems using SSMS 2005 to connect to SQL Server 2008.

Jun 18

My Favorite New SQL Trick: Deadlock Trace Flag

Posted on by

It’s not a new SQL trick, but it’s new to me. By adding -T1204 to SQL Server’s startup parameters, you get a lovely (perhaps not attractive, but informative) spew of information into the error log every time a deadlock occurs. You can then make helpful announcements like “your query failed because it was fighting with the index rebuilder for access to the statistics on this particular column, and the index rebuilder won.”

For example, around midnight this morning, we received an email from an app saying that a SQL statement it ran was chosen as the deadlock victim. A few minutes later, the database emailed me the deadlock trace from the error log.

The error log output contains a snippet of the statement that was killed:

Input Buf: Language Event: SELECT DISTINCT location.id, location.name, location.lineage FROM location INNER JOIN webuser_role_location_cascaded wrl ON location.id = wrl.idLocation INNER JOIN role_location ON wrl.idRoleLocation = role_location.id INNER

And what it was trying to use:

METADATA: database_id = 5 STATS(object_id = 494624805, stats_id = 8)

And what the other contender was doing:

SPID: 62 ECID: 0 Statement Type: ALTER INDEX Line #: 10

What does this globbety-gook tell me? Well, the index rebuilder was building an index — I can see that from the ALTER INDEX — and the METADATA bit tells me exactly what the two queries were fighting over — statistic number 8 on object 494624805 in database 5. I happen to know what database 5 is in my system, but if it’s one you don’t recognize, a quick trip to the sys.databases DMV will tell you:

SELECT name FROM sys.databases WHERE database_id = 5

Use the OBJECT_NAME() function in that database to see what table the statistic is on.

SELECT OBJECT_NAME(494624805)

Look in the sys.stats DMV to see what statistic number 8 is.

SELECT name FROM sys.stats WHERE object_id = 494624805 AND stats_id = 8

Jan 30

How to Retrieve SMS Text Messages from an iPhone Backup

Posted on by

(This post was written when I had an iPhone 3G. SMS conversations may no longer be extractable in this format, but I will leave the post up until I know otherwise.)

I recently wanted to extract an SMS conversation from my phone so I could write a document based on it. Using a free backup extractor and the SQLite database program that comes pre-installed on OS X, I was able to dump the conversation into a spreadsheet in less than five minutes. You’ll need to have synced your phone with your computer first, as the extractor gets the SMS archive from the phone backup made by iTunes.

Note that these instructions are only applicable to Mac OS X, and the extractor requires Leopard. I found the SQLite commands in an excellent Google Code article, but that method required that I jailbreak my phone and install an SFTP server, which interested me not at all.

  1. Download the free iPhone/iPod Touch Backup Extractor program from SuperCrazyAwesome.com.
  2. Run it. The program will give you a list of available iPhone backups that iTunes has made when you sync your phone with your computer. Choose the latest one.
  3. From the list of apps whose data you could extract, choose “Other Programs” at the bottom. Choose a folder on disk to extract the data to — it will make a number of folders and files, so you should create a new folder for it.
  4. Open Terminal. Change your current directory to “Other Files/Library/SMS” within the folder that you chose in the previous step.
  5. Type the following commands, hitting the return key after each:

    sqllite3 sms.db

    .output output.txt

    SELECT * FROM message;

    .quit

You can now close Terminal. In the “Other Files/Library/SMS” within the folder to which you extracted the backups, there is now a file called output.txt. This is a pipe-delimited text file that you can import into Excel. Some of the columns are easily recognizable — the phone number to which the text message came from or was sent to, and the text of the message. To get all of the messages exchanged with a particular person, simply sort by the phone number column to get them all together.