Scheduler node deadlock message.

Hi:

We seem to be hitting a performance problem on SQL Server 2005. We encounter the following message frequently in the event log. We have update stats, reindexing and everything else in place. However this message keeps getting logged.

All schedulers on Node 0 appear deadlocked due to a large number
of worker threads waiting on LCK_M_IS. Process Utilization 25%%

MS Experts (Product Gurus)/MVPs can you please offer some explanation or insight into the error and also a possible resolution on how to fix it. I have the following questions:

1). Is this related to poor queries performing badly and needs to be tuned?.

2). Is this a more DIsk I/O Issue and whether we need faster disks or disk arrays?

3). Is something configured wrongly on the SQL Server?

4). Are we handling our transactions badly and thus resulting in blocking or deadlocks?.

Please provide your valuable suggestions.

Thanks

Ankith

I forgot to mention the system configuration.

The Server is Windows Server 2003 (x64 Edition) with AMD Quad (4) Processors (2.2 GHZ) and has apprx 16 GB of RAM.

Awe is enabled.

|||

This message indicates that there are some pretty long blocking chains on your server.

Have a look at the blocking section in http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx for methods of troubleshooting blocking issues.

|||

Hi Jerome:

Thanks for the info. Is this error anyway related to Hard disk contention (because I see worker threads waiting on LCK_M_IS). Just want to get your opinion and see if we can also take a look at our hard disks.

Thanks.

|||

Based on the wait type it's highly unlikely that disk issues have anything to do with the problem. Start by looking at the connection that's holding incompatible lock and determine what it's doing.

sql

Scheduled Profiler

I need to get performance stats off a number of live servers. The stats need to cover the time from 07:00 to 19:00 every day.

I'm after things like CPU, Memory and IO, but also I want things like transactions and stored procedures.

Can profiler provide this kind of information, and can I schedule the jobs?

The systems are all running on Windows server 2003 and SQL Server 2005.

Thanks

Gary

Hi,

You should schedule a t-sql task with SQL Server Agent.

Use the sp_trace_create to create a trace, sp_trace_setevent to set up your events for monitoring and sp_trace_setstatus to start, stop or pause a trace.

I hope it helps.

Regards,

Janos

|||

Scheduling server traces is what you would want to do as Janos posted. Rather than trying to set up all of the statements and events yourself, it's much easier to just use Profiler to create the script file. Create the trace you want in Profiler and then go to the menu to File, Export and then select Script Trace Definition. This will create the server trace file that you can schedule in SQL Agent. You can then open these files in profiler, import them into a table for analysis, etc. It's the same file format that Profiler uses.

-Sue

|||

refer these links

http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm

http://msdn2.microsoft.com/en-us/library/ms191006.aspx

Madhu

scheduled delete performance cost

What would be the performance cost if I deleted all the rows from a table in MSSQL every 5 minutes?

I'm doing this to keep track of whos online. Every time a user goes to a new page they get logged into a table called WhosOnline. Every 5 minutes the log will get deleted.

What would be the performance cost of this and if its high, does anyone know a better way to keep track of whos Online?Forget it, I figured out how to do it. SessionID & UserName in a database.