May 1, 2015 at 12:44 pm
We have a SQL Server 2008R2 system that has heavy usage to one specific table. I have tuned basically all I can as far as making sure SQL Statements are using good indexes. From time to time a group of folks will log into Mgt Studio and run SQL Statements like this, leave the query open and once in a while it will cause blocking to other SQL running our online system
The query is like this: select ID,* from tablename with (nolock) where ID like 'MSPRYy%'
The results come back within less than 1 second. However, they leave this window open which is what causes this to be a HEAD BLOCKER and blocks other SQL Statements from running. I am just starting to research this issue. Any help would be greatly appreciated.
May 1, 2015 at 12:51 pm
I'm not sure how this query can be a blocker with the NOLOCK hint. When I run a query using the NOLOCK hint the only lock taken is a Shared Database Lock. I can even do an insert into the table when I am reading using the NOLOCK hint.
How are you determining this query is the lead blocker?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 1, 2015 at 12:54 pm
I know.. that is what I thought as well. I open up Activity Monitor and find the Session ID that says head blocker with a 1 in it. Right click on that to SHOW THE SQL running and that was what it says. After I kill it everything frees up. I thought the NO LOCK would not cause a block.... I am confused as well.
May 1, 2015 at 1:11 pm
I'd put together a trace to verify that this is the issue. I don't see any way that can be the ONLY statement in the batch because it can't be taking locks. Try querying sys.dm_tran_locks to verify the locks it is taking.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 3, 2015 at 4:26 am
Markus (5/1/2015)
We have a SQL Server 2008R2 system that has heavy usage to one specific table. I have tuned basically all I can as far as making sure SQL Statements are using good indexes. From time to time a group of folks will log into Mgt Studio and run SQL Statements like this, leave the query open and once in a while it will cause blocking to other SQL running our online systemThe query is like this: select ID,* from tablename with (nolock) where ID like 'MSPRYy%'
The results come back within less than 1 second. However, they leave this window open which is what causes this to be a HEAD BLOCKER and blocks other SQL Statements from running. I am just starting to research this issue. Any help would be greatly appreciated.
Quick questions,
1. Can you post the DDL for this and the related tables?
2. Any foreign keys not covered by an index?
3. What does sys.dm_tran_locks show when this happens?
😎
May 4, 2015 at 5:30 am
I have not queried dm_tran_locks when this has happend. There are no foreign keys on this table. I do have DB settings set to this:
ALTER DATABASE MYDB_PRD
SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE MYDB_PRD
SET READ_COMMITTED_SNAPSHOT ON;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ETABLE](
[PERSON_ID] [bigint] NULL,
[EMP_ID] [char](11) NOT NULL,
[JOB_COD] [char](6) NULL,
[DEPT_ID] [char](10) NULL,
[LOC_ID] [char](10) NULL,
[CO_COD] [char](3) NULL,
[EMP_TYP] [char](25) NULL,
[NAM] [char](50) NULL,
[STATUS] [char](1) NULL,
[W_PHONE1] [char](24) NULL,
[NEW_EMP] [char](1) NULL,
[PREF_NAM] [char](65) NULL,
[char](240) NULL,
[UID] [char](8) NULL,
[PAYGROUP] [char](3) NULL,
[MAINT_TMS] [datetime] NOT NULL,
[COMMENTS] [char](100) NULL,
[FIRST_NAM] [char](30) NULL,
[LAST_NAM] [char](34) NULL,
[M_NAM] [char](1) NULL,
[W_AREA] [char](10) NULL,
[W_DIVISION] [char](10) NULL,
[W_REGION] [char](10) NULL,
[W_ORGANIZATION] [char](10) NULL,
[W_ORG_DESCR] [char](30) NULL,
[W_REGION_DESCR] [varchar](240) NULL,
[W_DIVISION_DESCR] [varchar](240) NULL,
[W_AREA_DESCR] [varchar](240) NULL,
[NT_LOGON_ID] [char](8) NULL,
[NT_DOMAIN] [char](25) NULL,
[GU_ID] [char](22) NULL,
[W_PHONE2] [char](24) NULL,
[CNTRY_COD] [char](2) NULL,
[NATIONAL_ID] [char](15) NULL,
[ADDRESS1_NUM] [char](55) NULL,
[ADDRESS2_NUM] [char](55) NULL,
[CITY] [char](30) NULL,
[STATE] [char](6) NULL,
[HOME_POSTAL] [char](12) NULL,
[BIRTH_MONTH] [char](2) NULL,
[HIRE_DT] [smalldatetime] NULL,
[SITE_NUM] [int] NULL,
[JOBTITLE] [varchar](700) NULL,
[DEPTNAME] [varchar](240) NULL,
[SETID_DEPT] [char](5) NULL,
[SETID_JOBCODE] [char](5) NULL,
[SETID_LOCATION] [char](5) NULL,
[COUNTRY_LOCATION] [char](3) NULL,
[ADDRESS1_LOCATION] [char](55) NULL,
[ADDRESS2_LOCATION] [char](55) NULL,
[CITY_LOCATION] [char](30) NULL,
[STATE_LOCATION] [char](6) NULL,
[POSTAL_LOCATION] [char](12) NULL,
[JOB_FAMILY] [char](150) NULL,
[JOB_FAMILY_DESCR] [char](30) NULL,
[OFFICER_CD] [char](1) NULL,
[CO_DESC] [varchar](240) NULL,
[LOC_DESC] [char](30) NULL,
[EXTENSION] [char](6) NULL,
[LANG_CD] [char](2) NULL,
[OFFICER_DESCR] [char](30) NULL,
[MANAGER_LEVEL] [char](150) NULL,
[MANAGER_DESCR] [char](30) NULL,
[UTC_OFFSET_NUM] [smallint] NULL,
[BIRTH_DT] [datetime] NULL,
[COMBINATION_NUM] [smallint] NULL,
[SYNC_FLG] [char](1) NULL,
[BIRTH_DAY] [char](2) NULL,
[ENTITY_ID] [char](22) NULL,
[FAX_FON] [char](24) NULL,
[IDS_FLG] [char](1) NULL,
[COMP_FREQUENCY] [char](1) NULL,
[USER_STATUS_DESCR] [char](80) NULL,
[BRAND_COD] [char](150) NULL,
[BUSINESS_GROUP_ID] [bigint] NULL,
[BUSINESS_GROUP_DESCR] [char](240) NULL,
[DEPT_ORG_ID] [char](22) NULL,
[W_ORG_ORG_ID] [char](22) NULL,
[W_REGION_ORG_ID] [char](22) NULL,
[W_DIVISION_ORG_ID] [char](22) NULL,
[W_AREA_ORG_ID] [char](22) NULL,
[W_DISTRICT_ORG_ID] [char](22) NULL,
[W_DISTRICT] [char](1) NULL,
[W_DISTRICT_DESC] [char](240) NULL,
[EXCLUDE_FLG] [char](1) NOT NULL,
[SUPERVISOR_EMP_ID] [char](11) NULL,
[HOME_CELL_FON] [char](24) NULL,
[WORK_CELL_FON] [char](24) NULL,
[KNOWN_AS_NAM] [varchar](80) NULL,
[PAY_RATE_HASH] [char](128) NULL,
[NAT_ID_HASH] [char](128) NULL,
CONSTRAINT [PK_ETABLE] PRIMARY KEY CLUSTERED
(
[EMP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [XETABLEUID] UNIQUE NONCLUSTERED
(
[UID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ETABLE] ADD CONSTRAINT [DF_ETABLE_EXCLUDE_FLG] DEFAULT ('N') FOR [EXCLUDE_FLG]
GO
ALTER TABLE [dbo].[ETABLE] ADD CONSTRAINT [XETABLEUID] UNIQUE NONCLUSTERED
(
[UID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
May 4, 2015 at 9:25 am
I would think SQL would also need a schema stability lock on the table, to make sure the structure doesn't change while it is being read.
If you use "SELECT ... INTO new_table_name" in the same db, I've seen SQL lock the underlying metadata tables for objects.
Maybe it's a SELECT ... INTO that gets blocked??
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply