I know this question is closed, but I hope it will help someone.
The problem is on our disks ( cluster ).
Using PerfMon, I could create some counters ( disk reads and writes ) and the reads counter is fixed on 100%.
I am seeing the following wait in
I see some posts here, saying this warning is because of high I/O. SQL Server Books Online defines the SQL wait type PAGEIOLATCH_SH as:
Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode.
A user process will request some data which is not currently in the buffer cache. At that point – SQL Server will attempt to allocate a buffer page – and while the data is moved from disk to buffer cache an exclusive PAGEIOLATCH_EX is created on the buffer. At the same time a PAGEIOLATCH_SH is created on the buffer from the user process perspective.
I've created index and stats, and also used the SQL Server Profiler to help me.
Is there any way to improve the query? How can I improve the handling of lots of
We have been having this problem for about a week, and I can't figure out what to do.
SELECT TOP 30 codCliente FROM ( SELECT t1.CodCliente , codcampo , valor , t1.chavealeat FROM tblCliente AS t1 WITH(NOLOCK) INNER JOIN tblClienteDetalhe AS t2 WITH (NOLOCK) ON t1.codcliente = t2.codcliente AND CodCampo IN (-1, 4) WHERE codStatus IN(0) AND t1.ChavePeriodo < getdate() AND t1.CodStatusLigacao = 0 AND EXISTS ( SELECT codcliente FROM tblclientedetalhe WITH (NOLOCK) WHERE codcampo = 3 AND valor = '2' AND codcliente = t1.codcliente ) AND EXISTS ( SELECT codcliente FROM tblclientedetalhe WITH (NOLOCK) WHERE codcampo = 6 AND Convert(DateTime,Valor) BETWEEN '2015-03-01' AND '2015-03-31' AND DateDiff(day,Valor,GetDate()) > 15 AND codcliente = t1.codcliente ) AND NOT EXISTS ( SELECT 0 FROM tblPesquisa WITH (NOLOCK) WHERE tblPesquisa.CodCliente = t1.CodCliente ) AND EXISTS ( SELECT codcliente FROM tblclientedetalhe WITH (NOLOCK) WHERE codcampo = 4 AND valor = '161' AND codcliente = t1.codcliente ) ) AS Cliente PIVOT ( MAX(Valor) FOR codCampo in () ) AS PivotTable WHERE ( = '161') ORDER BY chavealeat;
As i said bellow in comments, there are other queries with this Wait Status. I'm waiting for a job to finish to be sure it's not the problem.