SQL Server Performance

selects blocking after Dell 2950 upgrade

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by jezemine, Mar 8, 2007.

  1. jezemine New Member

    We are experiencing some strange blocking issues after upgrading some hardware. Here's what's happening:

    A specific, reltively simple select query is sent to the SQL Server every five minutes, which usually runs under one second and returns < 200,000 rows. The query is executed from a remote server using the .Net SqlClient Data Provider. Examining sp_who2 active or sysprocesses shows that the SPID's status is SUSPENDED, the SPIDs command is SELECT, CPUTime is < 50, DiskIO is < 50. Over time, the SPID does not disappear from sysprocesses, does not increase in CPUTime nor DiskIO, and remains in sysprocesses for hours, doing no work. Other processes that attempt to query, update or truncate tables included in the query are blocked (as seen in BlkBy of sp_who2 active.) Every five minutes, when the query runs, a new SPID is added to sysprocesses exhibiting the same signs. The expected behavior is for the SPID to do it's one second worth of work and enter a sleeping and "awaiting command" state.

    we are stumped. The select query returns pretty much instantly, but appears to leave shared locks on the table so that other queries that would like to update the table can't obtain exclusive locks and they time out.

    So my question is, has anyone else encountered similar blocking issues after upgrading to Dell 2950 2x Dual Core Xeons?

    Here's the query that causes the blocking:



    SELECT
    SGF.DTK
    ,SGF.SGId
    ,SGF.FCCLRBaseLine
    ,SGF.FCCLRRealTime
    ,SGTSP.TSPCLR
    FROM
    SGFCs SGF
    LEFT OUTER JOIN
    SGTSP SGTSP ON
    (SGF.DTK = SGTSP.DTK)
    AND (SGF.SGId = SGTSP.SGId)
    WHERE
    ((SGF.DTK >= 3236925 AND SGF.DTK <= 3244845)
    AND (((SGF.DTK - 3236925) % 15) = 0))
    ORDER BY
    SGF.DTK, SGF.SGId



    here are the tables involved:



    CREATE TABLE [dbo].[SGFCs](
    [DTK] [int] NOT NULL,
    [SGId] [int] NOT NULL,
    [FCCLRBaseline] [tinyint] NOT NULL,
    [FCCLRRealtime] [tinyint] NOT NULL,
    [FCCLRBaselineConfidence] [real] NULL,
    [FCCLRRealtimeConfidence] [real] NULL,
    [DTK15] AS ([DTK]+(15)) PERSISTED,
    [DTK30] AS ([DTK]+(30)) PERSISTED,
    [DTK45] AS ([DTK]+(45)) PERSISTED,
    [DTK60] AS ([DTK]+(60)) PERSISTED,
    CONSTRAINT [PK_SGFCs] PRIMARY KEY CLUSTERED
    (
    [DTK] ASC,
    [SGId] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[SGTSP](
    [DTK] [int] NOT NULL,
    [SGId] [int] NOT NULL,
    [TSPCLR] [tinyint] NOT NULL,
    [TSPCLRConfidence] [real] NULL,
    [DTK15] AS ([DTK]+(15)) PERSISTED,
    [DTK30] AS ([DTK]+(30)) PERSISTED,
    [DTK45] AS ([DTK]+(45)) PERSISTED,
    [DTK60] AS ([DTK]+(60)) PERSISTED,
    [DTK_5] AS ([DTK]-(5)) PERSISTED,
    CONSTRAINT [PK_SGTSP] PRIMARY KEY CLUSTERED
    (
    [DTK] ASC,
    [SGId] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]




    The current (after upgrade) hardware specs are:

    Dell 2950
    2x Dual Core Xeon 5130 (4MB cache), 2GHz, 1333 Mhz FSB
    8 GB RAM
    Windows ENT Version 5.2.3790 SP 1 Build 3790
    SQL 2005 ENT SP1
    Dell BOIS 1.2.0 (10/18.2006)
    SMBIOS 2.4
    HAL Version 5.2.3790.1830 (srv03_sp1_rtm.050324-1447)

    Storage adapters:
    Dell SAS 5/I Integrated comtorller
    2x LSI 2Gb FC 7202 with 929X – StorPort


    hardware before upgrade (no blocking occured with this config):

    Dell 2850
    2x 3.0 Ghz Xeon (2MB cache), 933 Mhz FSB
    8 GB RAM
    Windows ENT Version 5.2.3790 SP 1 Build 3790
    SQL 2005 ENT SP1
    Dell BOIS A05 (1/9/2006)
    SMBIOS 2.3
    HAL Version 5.2.3790.1830 (srv03_sp1_rtm.050324-1447)

    Storage adapters:
    Dell PERC 4e/Di RAID Controller (SCSI U320)
    2x LSI 2Gb FC 7202 with 929X – StorPort


    www.elsasoft.org
  2. MohammedU New Member

    Is query blocked by itself or blocked some other query...
    Did you try using NOLOCK hint or setting the TRANSACTION ISOLATION LEVEL READ UNCOMMITED?



    MohammedU.
    Moderator
    SQL-Server-Performance.com
  3. jezemine New Member

    We are seeing in Activity Monitor and sp_who2 that various update queries (not shown above) are blocked by the select query above. However this makes no sense as the query above returns almost immediately, and so should only block for a very short time, if at all. Certainly after the client finishes iterating through the SqlDataReader all the shared locks held by the select query should be released, no? we are finding that they are not.

    We did experiment with adding the nolock hint to the select, and that caused the blocking problem to go away. However I don't like to do dirty reads - you shouldn't have to. I feel like peppering code with nolock hints to solve blocking problems is a band-aid type fix.

    www.elsasoft.org
  4. jezemine New Member

    I just found out that prod flattened the machine where this was occuring and reinstalled OS and SQL Server and now it doesn't repro. We are chalking it up to an unknown misconfiguration.

    www.elsasoft.org
  5. chornung New Member

    quote:Originally posted by jezemine

    We are experiencing some strange blocking issues after upgrading some hardware. Here's what's happening:

    A specific, reltively simple select query is sent to the SQL Server every five minutes, which usually runs under one second and returns < 200,000 rows. The query is executed from a remote server using the .Net SqlClient Data Provider. Examining sp_who2 active or sysprocesses shows that the SPID's status is SUSPENDED, the SPIDs command is SELECT, CPUTime is < 50, DiskIO is < 50. Over time, the SPID does not disappear from sysprocesses, does not increase in CPUTime nor DiskIO, and remains in sysprocesses for hours, doing no work. Other processes that attempt to query, update or truncate tables included in the query are blocked (as seen in BlkBy of sp_who2 active.) Every five minutes, when the query runs, a new SPID is added to sysprocesses exhibiting the same signs. The expected behavior is for the SPID to do it's one second worth of work and enter a sleeping and "awaiting command" state.

    we are stumped. The select query returns pretty much instantly, but appears to leave shared locks on the table so that other queries that would like to update the table can't obtain exclusive locks and they time out.

    So my question is, has anyone else encountered similar blocking issues after upgrading to Dell 2950 2x Dual Core Xeons?

    Here's the query that causes the blocking:



    SELECT
    SGF.DTK
    ,SGF.SGId
    ,SGF.FCCLRBaseLine
    ,SGF.FCCLRRealTime
    ,SGTSP.TSPCLR
    FROM
    SGFCs SGF
    LEFT OUTER JOIN
    SGTSP SGTSP ON
    (SGF.DTK = SGTSP.DTK)
    AND (SGF.SGId = SGTSP.SGId)
    WHERE
    ((SGF.DTK >= 3236925 AND SGF.DTK <= 3244845)
    AND (((SGF.DTK - 3236925) % 15) = 0))
    ORDER BY
    SGF.DTK, SGF.SGId



    here are the tables involved:



    CREATE TABLE [dbo].[SGFCs](
    [DTK] [int] NOT NULL,
    [SGId] [int] NOT NULL,
    [FCCLRBaseline] [tinyint] NOT NULL,
    [FCCLRRealtime] [tinyint] NOT NULL,
    [FCCLRBaselineConfidence] [real] NULL,
    [FCCLRRealtimeConfidence] [real] NULL,
    [DTK15] AS ([DTK]+(15)) PERSISTED,
    [DTK30] AS ([DTK]+(30)) PERSISTED,
    [DTK45] AS ([DTK]+(45)) PERSISTED,
    [DTK60] AS ([DTK]+(60)) PERSISTED,
    CONSTRAINT [PK_SGFCs] PRIMARY KEY CLUSTERED
    (
    [DTK] ASC,
    [SGId] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[SGTSP](
    [DTK] [int] NOT NULL,
    [SGId] [int] NOT NULL,
    [TSPCLR] [tinyint] NOT NULL,
    [TSPCLRConfidence] [real] NULL,
    [DTK15] AS ([DTK]+(15)) PERSISTED,
    [DTK30] AS ([DTK]+(30)) PERSISTED,
    [DTK45] AS ([DTK]+(45)) PERSISTED,
    [DTK60] AS ([DTK]+(60)) PERSISTED,
    [DTK_5] AS ([DTK]-(5)) PERSISTED,
    CONSTRAINT [PK_SGTSP] PRIMARY KEY CLUSTERED
    (
    [DTK] ASC,
    [SGId] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]




    The current (after upgrade) hardware specs are:

    Dell 2950
    2x Dual Core Xeon 5130 (4MB cache), 2GHz, 1333 Mhz FSB
    8 GB RAM
    Windows ENT Version 5.2.3790 SP 1 Build 3790
    SQL 2005 ENT SP1
    Dell BOIS 1.2.0 (10/18.2006)
    SMBIOS 2.4
    HAL Version 5.2.3790.1830 (srv03_sp1_rtm.050324-1447)

    Storage adapters:
    Dell SAS 5/I Integrated comtorller
    2x LSI 2Gb FC 7202 with 929X – StorPort


    hardware before upgrade (no blocking occured with this config):

    Dell 2850
    2x 3.0 Ghz Xeon (2MB cache), 933 Mhz FSB
    8 GB RAM
    Windows ENT Version 5.2.3790 SP 1 Build 3790
    SQL 2005 ENT SP1
    Dell BOIS A05 (1/9/2006)
    SMBIOS 2.3
    HAL Version 5.2.3790.1830 (srv03_sp1_rtm.050324-1447)

    Storage adapters:
    Dell PERC 4e/Di RAID Controller (SCSI U320)
    2x LSI 2Gb FC 7202 with 929X – StorPort


    www.elsasoft.org

  6. chornung New Member

    We are experiencing a very similiar situtation, but without Hardware upgrade. We did upgrade to SQL Server 2005 on that box. An interesting coincidence is that the issues was noticed after we applied our Windows Updates system wide. We also started to see some performance issues outside of the ugraded environment.

    I dont have an answer, but am experiencing the same issue.

    Did you upgrade to SQL 2005?
  7. jezemine New Member

    both before and after we were running SQL 2005 ENT SP1. the only change was the hardware.

    www.elsasoft.org
  8. chornung New Member

    We noticed the issue started after Windows updates were pushed down to that server on the 25th of Feb. The first time we really had issues was monday the 26th of februrary. The application it is locking is Dynamics GP 9. Are any of these things similar to your experience?
  9. jezemine New Member

    no. our app is a custom inhouse app. we are not running Dynamics GP 9, whatever that is. <img src='/community/emoticons/emotion-5.gif' alt=';)' /><br /><br />also, the problem went away after prod flattened the machine and reinstalled OS and sql server. so there's no way for me to know at this point what may have been causing it as we can no longer repro it.<br /><br /><br /><br /><hr noshade size="1">www.elsasoft.org <br />

Share This Page