Question

Shyam Sunder Singh on Wed, 21 Oct 2015 15:25:26


Hi Team,

In production server, I just wanted to check the logs for some users so I just run the below query:

SELECT top 100  CONVERT(xml,logproperties),* from EventLog where loguserid  is null   order by LogCreateDate desc

In table have about 100 millions records.

Unfortunately , its take some time and that time my entire server was down.

I just want to know is it possible that entire server down it I just run SELECT * query ?


Shyam Sunder Singh S.S.E ( .NET Technologies)


Sponsored



Replies

Tom Phillips on Wed, 21 Oct 2015 15:55:52


What do you mean the "server was down"? 

This is likely one of two possibilities.  Either you were blocking the EventLog table for a long period causing applications to not be able to write to the EventLog table, or your query was using all the server resources.

Shyam Sunder Singh on Wed, 21 Oct 2015 16:20:57


Thanks Tom Phillips.

That was the actual reason.I just checked the sql logs and verify that EventLog tabe got locked due to long running query :(.

Thanks.


Shyam Sunder Singh S.S.E ( .NET Technologies)


Erland Sommarskog on Wed, 21 Oct 2015 21:14:11


Thanks Naomi.

Has Tom changed his hairstyle recently or what is going on? :-)

That was the actual reason.I just checked the sql logs and verify that EventLog tabe got locked due to long running query :(.

So while I often recommend people to stay away from NOLOCK in appliction code, this is a prime example when you need to use NOLOCK. If EventLog is a table that all process writes to, and there is no suitable index, SQL Server may decide to sort the entire table, and you can't take a table lock just because you are curious. That could cause a major outage - and it did.

The sorting operation on such a large table is also quite expensive, and could also devastate your server if it is not well-equipped with memory.