locked
SQL table response is sporadically bad. RRS feed

  • Question

  • Hi Friends,
    I have a very simple query-
    select COUNT(1) from [dbo].[table1] with (nolock)
    When i run the query- sometime it takes 1 second and sometime 30 seconds. Can someone help me why the response time is sometime longer ans sometime good. What may be the possible reason- can please anyone help me?
    Wednesday, December 23, 2015 5:48 PM

Answers

  • Hi,

    I am sure when it took 30 seconds it was blocked.

    Look at below to check whether it was blocked when you run next time

    select * from sys.sysprocesses where blocked <> o
    And yes queries with NOLOCK hint can also be blocked. And stop using NOLOCK


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP


    Thursday, December 24, 2015 4:58 AM
    Answerer
  • Looks like blocking,.. As alternative , you can query a system tables

    SELECT
    t.name,
    [RowCount] = SUM
    (
    CASE
    WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
    ELSE 0
    END
    )
    FROM
    sys.tables t
    INNER JOIN sys.partitions p
    ON t.object_id = p.object_id
    INNER JOIN sys.allocation_units a
    ON p.partition_id = a.container_id

    WHERE name=...

    GROUP BY
    t.name
    ----ORDER BY [RowCount] DESC


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, December 24, 2015 6:13 AM

All replies

  • Hi,

    I am sure when it took 30 seconds it was blocked.

    Look at below to check whether it was blocked when you run next time

    select * from sys.sysprocesses where blocked <> o
    And yes queries with NOLOCK hint can also be blocked. And stop using NOLOCK


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP


    Thursday, December 24, 2015 4:58 AM
    Answerer
  • Looks like blocking,.. As alternative , you can query a system tables

    SELECT
    t.name,
    [RowCount] = SUM
    (
    CASE
    WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
    ELSE 0
    END
    )
    FROM
    sys.tables t
    INNER JOIN sys.partitions p
    ON t.object_id = p.object_id
    INNER JOIN sys.allocation_units a
    ON p.partition_id = a.container_id

    WHERE name=...

    GROUP BY
    t.name
    ----ORDER BY [RowCount] DESC


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, December 24, 2015 6:13 AM
  • Hi Sadikul Islam,

    What's the result when you run the following statement to check if the query is blocked?

    select * from sys.sysprocesses where blocked > 0

    Based on my test, if the process is blocked, then it will take a longer time to complete.

    Regards,

    Ice fan


    Ice Fan
    TechNet Community Support



    • Edited by Ice Fan Tuesday, January 12, 2016 7:16 AM
    Tuesday, January 12, 2016 5:12 AM