SQL Server Performance Forum – Threads Archive

selects blocking after Dell 2950 upgrade

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

---

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

---

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

---

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

---

---

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?

---

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

---

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?

---

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 />

---

]]>