high PAGEIOLATCH_SH waits on server [closed]

EDIT:

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 sp_WhoIsActive:

PAGEIOLATCH_SH:Database_Name:1(*)

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.

And this:

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 ANDs?

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 ([4])
    ) AS PivotTable
WHERE ([4] = '161')
ORDER BY chavealeat;

EDIT1:

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.

This may not directly answer your question, however you have multiple potential issues in your query, that are immediately visible to me.

AND Convert(DateTime,Valor) BETWEEN '2015-03-01' AND '2015-03-31'
AND DateDiff(day,Valor,GetDate()) > 15

1. Why are you doing CONVERT(DateTime, Valor) ? Is Valor not a DateTime type in the table? If not, you’re doing that wrong.

2. Running a function on a column (DateDiff(day, Valor, GetDate())) and comparing that to a value makes it non-SARGable. Try this, instead:

   AND Valor > DATEADD(DAY, -15, GETDATE())

3. BETWEEN (x and y) may not function the way you think it does. You’re better off to use the following construct since it explicitly defines the precise date range you desire.

   WHERE Valor >= '2015-03-01T00:00:00' AND Valor < '2015-04-01T00:00:00'

4. WITH (NOLOCK) is NOT a go-faster button! See http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ and http://sqlperformance.com/2015/04/t-sql-queries/the-read-uncommitted-isolation-level for excellent details about why that is a non-starter most of the time.