Will blocking or self blocking incresses Disk I/O ?

Question

Hey all,

My Cluster resouces restarting now and than,we observed that just few few minites before (8 min) SQL Server Thread count incressed(jumped) from 124 to 277.afte that we are seeing % disk time also incressed.

that means SQL Server thead are causing more disk I/O's,this threads are due to blocking or due to self blocking.

Environment:

SQL Server 2000/Windows 2003

Please suggest me ASAP,It is great help

  

---

SNIVAS

Answer 1

Why don't you start SQL Server Trace and see what causing this disk I/Os

---

http://uk.linkedin.com/in/ramjaddu

Answer 2

The main effect of blocking is the increase the execution duration of blocked queries. Disk I/O may increase as a side effect.

Optimization article:

http://www.sqlusa.com/articles/query-optimization/

Disk resources optimization:

http://www.sqlusa.com/bestpractices/configureharddisk/

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 3

Thanks Ram and SQLUSA,

 

Server were becoming busy any day(s) of a month,some times continues some times after 25 days..like that ,so it is not possible to run profiler such a logn time,but we invoked the block box it does not have the I/O information.

we have winodws counters invoked only I saw Obnarmal is SQL Server threads it is pileted 124 to 277 at 8:31PM and trace was stopped 8:34PM and servers restarted by cluster services at 8:39PM.

Here why SQL Server threads piled up ? In what cases it piled?

I appreciate you help.

---

SNIVAS

Answer 4

Hi SNIVAS,

Regarding to your description, please verify block issue to run query as below:
Select * from sys.sysprocesses
Now you can  the SPID number and block clomuns from the sysprocesses this management view to check any blocked Spid.

For more information, please refer to sys.sysprocesses (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms179881.aspx

Meanwhile please check the SQL error log, if you have error log related please post for further troubleshooting.

---

Regards, Amber zhang

Answer 5

if there is excessive blocking then yes the number of threads will increase. But it does not mean that it will increase the disk I|O as other than the blocking threads others will not do anything.

As RamJaddu said, you need to find out why you have high disk I\O's . Also you need to resolve the blocking issues.

Check the below links to resolve blocking

http://support.microsoft.com/kb/224453

http://support.microsoft.com/kb/271509

HTH

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com