How It Works: Enumeration of sys.messages

I ran into an issue which has some aspects I found interesting and thought you might as well.

When you do a select against the sys.messages virtual table the data is retrieved from the resource files (.RLL) files stored under <<Instance>>\Binn\Resource directory.   This is done by loading the RLL library and retrieving the resource string information then materializing the row in the result set.

What I found interesting is that by default SQL Server will materialize a row for each of the installed resource languages.  For example the following select will return 11 rows …

select * from sys.messages where message_id = 605

… matching the 11 installed resource RLLs.

Internally all entries from syslanguages are searched for.   On my SQL Server 2008 installation syslanguages DMV holds 33 rows.   So the SQL Server searches for 22 more rows/message which don't have matching RLL files.

To make your sys.messages queries faster add the 'language_id = ####' predicate to the where clause.   SQL Server will then only look for the specific language and the query performance will be significantly increased.

select

* from sys.messages where language_id = 1033

Bob Dorr - Principal SQL Server Escalation Engineer