1

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%.

High readsI 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.

Warninigs

closed as off-topic by swasheck, Paul White, RolandoMySQLDBA, Colin 't Hart, Marian Apr 28 '15 at 15:20

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – swasheck, Paul White, RolandoMySQLDBA, Colin 't Hart, Marian
If this question can be reworded to fit the rules in the help center, please edit the question.

9

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.