CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

How It Works: Enumeration of sys.messages

How It Works: Enumeration of sys.messages

  • Comments 1

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

image

… matching the 11 installed resource RLLs.

image

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

Leave a Comment
  • Please add 6 and 2 and type the answer here:
  • Post
Page 1 of 1 (1 items)