On domain build DO executes query like

select msg.error, msg.description from [master].[sys].[sysmessages] msg join [master].[sys].[syslanguages] lang on msg.msglangid = lang.msglangid where lang.langid = @@LANGID and msg.error in (2627, 2601, 515, 547)

In some scenarios (i don't really know why) it takes more than two seconds. I assume it is used for exception messages, so, could you optimize it in two steps:

  1. Use lazy initialize of this messages
  2. Use another query, like this:

        DECLARE @MSGLANGID int;

    SELECT @MSGLANGID = msglangid FROM [master].[sys].[syslanguages] lang WHERE lang.langid = @@LANGID

    SELECT msg.error , msg.description FROM [master].[sys].[sysmessages] msg WHERE msg.msglangid = @MSGLANGID AND msg.error IN ( 2627, 2601, 515, 547 )

Exec plan

asked Nov 29 '13 at 02:11

pil0t's gravatar image

pil0t
207555763

edited Nov 29 '13 at 07:35


One Answer:

Hi pil0t,

try rebuilding the indexes. Usually this helps. But before you do that, it will be interesting to take a look at the execution plans for the above-mentioned queries.

Hope that helps.

answered Nov 29 '13 at 07:27

Dmitri%20Maximov's gravatar image

Dmitri Maximov
22111211

Exec plan: http://goo.gl/nnWkkV

(Nov 29 '13 at 07:36) pil0t pil0t's gravatar image

Thanks. I tried these queries on my side with misc variations and your one showed the best results. I think, this is worth fixing.

(Nov 29 '13 at 12:50) Dmitri Maximov Dmitri%20Maximov's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA