August 21, 2017 at 9:44 am
Hi,
tried to create below index
CREATE NONCLUSTERED INDEX IDX_POSITION_ID_FROM_DATE_TO_DATE
ON [CR].[TEMP] ([POSITION_ID],[FROM_DATE],[TO_DATE])
INCLUDE ([EMP_ID],[ACTIVITY_ID],[VESSEL_ID],[PRINCIPAL_COMPANY_ID],[MGR_COMPANY_ID],[MAGNT_COMPANY_ID])
index creation is not completed after 30 minutes.
issue is in client database.
index successfully created in my system, same client database is used.
no of record is same in both database ( 170000 records)
sql server is 2014
pls advise the solution
below is created for below table
CREATE TABLE [CR].[TEMP](
[ID] numeric(18,0) DEFAULT (NEXT VALUE FOR [CR].[SEQ_EMP_ACTIVITY]) NOT NULL,
[EMP_ID] numeric(18,0) NOT NULL,
[CONTRACT_PROFILE_ID] numeric(18,0) NOT NULL,
[ACTIVITY_ID] numeric(18,0) NOT NULL,
[VESSEL_ID] numeric(18,0) NOT NULL,
[POSITION_ID] numeric(18,0) NOT NULL,
[PAY_POSITION_ID] numeric(18,0) NOT NULL,
[CREW_CHANGE_ID] numeric(18,0) NOT NULL,
[CREW_CHANGE_PARENT_ID] numeric(18,0) NOT NULL,
[REASON_ID] numeric(18,0) NOT NULL,
[SIGNOFF_REASON_ID] numeric(18,0) NOT NULL,
[COST_CENTRE_ID] numeric(18,0) NOT NULL,
[SUB_COST_CENTRE_ID] numeric(18,0) NOT NULL DEFAULT(-1),
[SIGNON_PORT_ID] numeric(18,0) NOT NULL,
[SIGNOFF_PORT_ID] numeric(18,0) NOT NULL,
[PRINCIPAL_COMPANY_ID] numeric(18,0) NOT NULL,
[MGR_COMPANY_ID] numeric(18,0) NOT NULL,
[MAGNT_COMPANY_ID] numeric(18,0) NOT NULL,
[EMP_TRAINING_RESERVE_ID] numeric(18,0) NOT NULL DEFAULT(-1),
[RECORD_TYPE] varchar(3) NOT NULL DEFAULT('MAN'),
[FROM_DATE] date NOT NULL,
[TO_DATE] date NULL,
[CALCULATED_TODATE] AS (isnull([TO_DATE],dateadd(year,(100),[FROM_DATE]))) PERSISTED NOT NULL,
[LEAVE_EARNED] numeric(5,2) NULL,
[LEAVE_CONSUMED] numeric(5,2) NULL,
[LEAVE_ADJUST] numeric(5,2) NULL,
[NEGATIVE_LEAVE] numeric(5,2) NULL,
[BACK2BACK_EARNED] numeric(5,2) NULL,
[BACK2BACK_CONSUMED] numeric(5,2) NULL,
[BACK2BACK_ADJUST] numeric(5,2) NULL,
[SIGNON_BUDGET_CODE_ID] numeric(18,0) NOT NULL DEFAULT(-1),
[SIGNOFF_BUDGET_CODE_ID] numeric(18,0) NOT NULL DEFAULT(-1),
[REMARKS] varchar(256) NULL,
[SIGNOFF_REMARKS] varchar(256) NULL,
[UPDATED_ON] datetime NOT NULL DEFAULT(GETUTCDATE()),
[UPDATED_BY] varchar(16) NOT NULL,
[CONCUR_ID] uniqueidentifier NOT NULL DEFAULT(NEWID()),
[SIGNOFF_SUB_REASON_ID] numeric(18,0) NOT NULL DEFAULT(-1),
[DOCUMENT_ID] numeric(18,0) NOT NULL DEFAULT(-1),
[INSTITUTION_ID] numeric(18,0) NOT NULL DEFAULT(-1),
CONSTRAINT [PK_EMP_ACTIVITY] PRIMARY KEY CLUSTERED
(
[ID] 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
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'SELF JOIN USING PARENT_ID AND ID' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'EMP ID' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'EMP_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'EMP CONTRACT PROFILE ID' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'CONTRACT_PROFILE_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'RANK ON BOARD' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'POSITION_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'SALARY IS PAID AS PER THIS RANK' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'PAY_POSITION_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'LINK FROM PLANNING' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'CREW_CHANGE_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'IN CASE OF ON BOARD PROMOTION,' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'CREW_CHANGE_PARENT_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'REASON FOR THIS ACTIVITY' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'REASON_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'SIGN OFF REASON' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'SIGNOFF_REASON_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'COST CENTRE' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'COST_CENTRE_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'SUB COST CENTRE' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'SUB_COST_CENTRE_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'SING ON PORT' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'SIGNON_PORT_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'SING OFF PORT' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'SIGNOFF_PORT_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'PRINCIPAL' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'PRINCIPAL_COMPANY_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'MANAGER' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'MGR_COMPANY_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'MANNING AGENT' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'MAGNT_COMPANY_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'TRAINING RESERVATION ID' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'EMP_TRAINING_RESERVE_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'manual or system(entries generated via crew activities), STB - Stand by' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'RECORD_TYPE'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'NEGATIVE LEAVE/ADVANCE LEAVE' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'NEGATIVE_LEAVE'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'BACK TO BACK ADJUST' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'BACK2BACK_EARNED'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'BACK TO BACK ADJUST' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'BACK2BACK_CONSUMED'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'BACK TO BACK ADJUST' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'BACK2BACK_ADJUST'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'SIGN ON BUDGET CODE' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'SIGNON_BUDGET_CODE_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'SIGN OFF BUDGET CODE' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'SIGNOFF_BUDGET_CODE_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'REMARKS' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'REMARKS'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'SIGN OFF REMARKS' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'SIGNOFF_REMARKS'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'CM.APPLICATION_USER.USER_NAME' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'UPDATED_BY'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'USED FOR CONCURRENCY CHECKING DURING UPDATE' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'CONCUR_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'SIGN OFF SUB REASON' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'SIGNOFF_SUB_REASON_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'TRAINING COURSE' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'DOCUMENT_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'TRAINING INSTITUTION. KEPT FOR DIRECTLY ADDING TRAINING ACTIVITY' ,@level0type=N'SCHEMA' ,@level0name=N'CR' ,@level1type=N'TABLE' ,@level1name=N'EMP_ACTIVITY' ,@level2type=N'COLUMN' ,@level2name=N'INSTITUTION_ID'
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT CHK_EMP_ACTIVITY_RECORD_TYPE CHECK (([RECORD_TYPE]='MAN' OR [RECORD_TYPE]='SYS' OR [RECORD_TYPE]='STB'))
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_EMP_ID FOREIGN KEY (EMP_ID) REFERENCES CM.PERSONNEL_MF(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_CONTRACT_PROFILE_ID FOREIGN KEY (CONTRACT_PROFILE_ID) REFERENCES CR.CONTRACT_PROFILE(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_ACTIVITY_ID FOREIGN KEY (ACTIVITY_ID) REFERENCES CR.ACTIVITY_MF(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_VESSEL_ID FOREIGN KEY (VESSEL_ID) REFERENCES CM.VESSEL_MF(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_POSITION_ID FOREIGN KEY (POSITION_ID) REFERENCES CM.POSITION_MF(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_PAY_POSITION_ID FOREIGN KEY (PAY_POSITION_ID) REFERENCES CM.POSITION_MF(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_CREW_CHANGE_ID FOREIGN KEY (CREW_CHANGE_ID) REFERENCES CR.CREW_CHANGE(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_CREW_CHANGE_PARENT_ID FOREIGN KEY (CREW_CHANGE_PARENT_ID) REFERENCES CR.CREW_CHANGE(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_REASON_ID FOREIGN KEY (REASON_ID) REFERENCES CM.LOOKUP(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_SIGNOFF_REASON_ID FOREIGN KEY (SIGNOFF_REASON_ID) REFERENCES CM.LOOKUP(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_COST_CENTRE_ID FOREIGN KEY (COST_CENTRE_ID) REFERENCES PR.COST_CENTRE_MF(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_SUB_COST_CENTRE_ID FOREIGN KEY (SUB_COST_CENTRE_ID) REFERENCES PR.SUB_COST_CENTRE(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_SIGNON_PORT_ID FOREIGN KEY (SIGNON_PORT_ID) REFERENCES CM.PORT_MF(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_SIGNOFF_PORT_ID FOREIGN KEY (SIGNOFF_PORT_ID) REFERENCES CM.PORT_MF(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_PRINCIPAL_COMPANY_ID FOREIGN KEY (PRINCIPAL_COMPANY_ID) REFERENCES CM.COMPANY_MF(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_MGR_COMPANY_ID FOREIGN KEY (MGR_COMPANY_ID) REFERENCES CM.COMPANY_MF(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_MAGNT_COMPANY_ID FOREIGN KEY (MAGNT_COMPANY_ID) REFERENCES CM.COMPANY_MF(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_EMP_TRAINING_RESERVE_ID FOREIGN KEY (EMP_TRAINING_RESERVE_ID) REFERENCES CR.EMP_TRAIN_RESERVE(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_SIGNON_BUDGET_CODE_ID FOREIGN KEY (SIGNON_BUDGET_CODE_ID) REFERENCES CR.BUDGET_CODE_MF(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_SIGNOFF_BUDGET_CODE_ID FOREIGN KEY (SIGNOFF_BUDGET_CODE_ID) REFERENCES CR.BUDGET_CODE_MF(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_SIGNOFF_SUB_REASON_ID FOREIGN KEY (SIGNOFF_SUB_REASON_ID) REFERENCES CR.SUB_REASON_MF(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_DOCUMENT_ID FOREIGN KEY (DOCUMENT_ID) REFERENCES CR.DOCUMENT_MF(ID);
GO
ALTER TABLE [CR].[EMP_ACTIVITY] WITH CHECK ADD CONSTRAINT FK_EMP_ACTIVITY_INSTITUTION_ID FOREIGN KEY (INSTITUTION_ID) REFERENCES CM.COMPANY_MF(ID);
GO
Regards
Binu
August 21, 2017 at 9:58 am
What is the wait type for the SPID that's attempting to create the index?
John
August 21, 2017 at 10:09 am
Are any other processes running in the client database? Have you checked for blocking?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 21, 2017 at 10:16 am
Hi John,
how can identify wait type for the SPID
Binu
August 21, 2017 at 10:19 am
Hi Phil,
Another index have created for same table.
How can check blocking
Regards
Binu
August 21, 2017 at 10:45 am
here is a query you can use to see user requests, their wait info, and if anyone is blocking them:/* running requests */
SELECT s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.status AS session_status,
db_name(er.database_id) AS database_name, er.status AS request_status, er.command,
er.wait_type, er.wait_time / 1000.0 AS wait_sec, er.last_wait_type, er.wait_resource,
er.blocking_session_id, bs.host_name AS blocking_host, bs.program_name AS blocking_program, bs.login_name AS blocking_login,
er.transaction_id, er.open_transaction_count,
er.cpu_time / 1000.0 AS cpu_sec, er.total_elapsed_time / 1000.0 AS total_elapsed_sec, er.start_time, er.reads, er.writes, er.logical_reads,
CASE er.transaction_isolation_level WHEN 1 THEN 'ReadUncomitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' ELSE 'Unspecified' END AS isol_level,
OBJECT_NAME(st.objectid, st.dbid) AS object_name, er.sql_handle, er.plan_handle, st.[text]
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions s ON er.session_id = s.session_id
LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg ON er.session_id = mg.session_id AND er.request_id = mg.request_id
LEFT OUTER JOIN sys.dm_exec_sessions bs ON er.blocking_session_id = bs.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE s.is_user_process = 1
AND s.session_id <> @@SPID
AND er.last_wait_type <> 'SP_SERVER_DIAGNOSTICS_SLEEP'
August 21, 2017 at 9:47 pm
Hi,
as per below query there no wait type and blocking
/* running requests */
SELECT s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.status AS session_status,
db_name(er.database_id) AS database_name, er.status AS request_status, er.command,
er.wait_type, er.wait_time / 1000.0 AS wait_sec, er.last_wait_type, er.wait_resource,
er.blocking_session_id, bs.host_name AS blocking_host, bs.program_name AS blocking_program, bs.login_name AS blocking_login,
er.transaction_id, er.open_transaction_count,
er.cpu_time / 1000.0 AS cpu_sec, er.total_elapsed_time / 1000.0 AS total_elapsed_sec, er.start_time, er.reads, er.writes, er.logical_reads,
CASE er.transaction_isolation_level WHEN 1 THEN 'ReadUncomitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' ELSE 'Unspecified' END AS isol_level,
OBJECT_NAME(st.objectid, st.dbid) AS object_name, er.sql_handle, er.plan_handle, st.[text]
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions s ON er.session_id = s.session_id
LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg ON er.session_id = mg.session_id AND er.request_id = mg.request_id
LEFT OUTER JOIN sys.dm_exec_sessions bs ON er.blocking_session_id = bs.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE s.is_user_process = 1
AND s.session_id <> @@SPID
AND er.last_wait_type <> 'SP_SERVER_DIAGNOSTICS_SLEEP'
output
request_status - running
wait type - NULL
blocking_session_id - 0
blocking_host - NULL
blocking_program - NULL
open_transaction_count - 1
cpu_sec - 306.109000
total_elapsed_sec - 306.109000
reads - 0
writes - 0
logical_reads - 1567
isol_level - Serializable
any further check , pls advise
Regards
Binu
August 22, 2017 at 2:09 am
That's not the full output of that query. And did you run it while the index creation statement was running?
John
August 22, 2017 at 1:57 pm
Is there a DBA that can help you with this? You probably shouldn't be the one deploying this index if you don't know how to get blocking or wait types information. I'm not saying that to be rude or mean, but thats pretty basic trouble shooting and you could be killing the client environment and not know it.
August 22, 2017 at 6:29 pm
Are you sure the index is not created?
How are you determining the index is not created? It sounds like you went and created another index on the same table.
What is the result of this:select *
from sys.indexes where name ='IDX_POSITION_ID_FROM_DATE_TO_DATE'
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 23, 2017 at 8:25 am
Hi John,
below is full output.
output is taken when executing the query
session_id host_name program_name client_interface_name login_name session_status database_name request_status command wait_type wait_sec last_wait_type wait_resource blocking_session_id blocking_host blocking_program blocking_login transaction_id open_transaction_count cpu_sec total_elapsed_sec start_time reads writes logical_reads isol_level object_name sql_handle plan_handle text
51 MSCSMCY-SHIPMAT Microsoft SQL Server Management Studio - Query .Net SqlClient Data Provider Administrator running SHIPMATE_1 runnable CREATE INDEX NULL 0.000000 SOS_SCHEDULER_YIELD 0 NULL NULL NULL 163522173 1 587.649000 591.452000 2017-08-23 17:09:15.850 0 0 1567 Serializable NULL 0x01000C00F582920120D0D3DC0600000000000000000000000000000000000000000000000000000000000000 0x06000C00F5829201600BF8AE0600000001000000000000000000000000000000000000000000000000000000 CREATE NONCLUSTERED INDEX IDX_POSITION_ID_FROM_DATE_TO_DATE ON [CR].[EMP_ACTIVITY] ([POSITION_ID],[FROM_DATE],[TO_DATE]) INCLUDE ([EMP_ID],[ACTIVITY_ID],[VESSEL_ID],[PRINCIPAL_COMPANY_ID],[MGR_COMPANY_ID],[MAGNT_COMPANY_ID])
Regards
Binu
August 23, 2017 at 8:26 am
Hi Jaison,
Below query is blank output
select * from sys.indexes where name ='IDX_POSITION_ID_FROM_DATE_TO_DATE'
Regards
Binu
August 23, 2017 at 9:05 am
binutb - Wednesday, August 23, 2017 8:25 AMHi John,
below is full output.
output is taken when executing the querysession_id host_name program_name client_interface_name login_name session_status database_name request_status command wait_type wait_sec last_wait_type wait_resource blocking_session_id blocking_host blocking_program blocking_login transaction_id open_transaction_count cpu_sec total_elapsed_sec start_time reads writes logical_reads isol_level object_name sql_handle plan_handle text
51 MSCSMCY-SHIPMAT Microsoft SQL Server Management Studio - Query .Net SqlClient Data Provider Administrator running SHIPMATE_1 runnable CREATE INDEX NULL 0.000000 SOS_SCHEDULER_YIELD 0 NULL NULL NULL 163522173 1 587.649000 591.452000 2017-08-23 17:09:15.850 0 0 1567 Serializable NULL 0x01000C00F582920120D0D3DC0600000000000000000000000000000000000000000000000000000000000000 0x06000C00F5829201600BF8AE0600000001000000000000000000000000000000000000000000000000000000 CREATE NONCLUSTERED INDEX IDX_POSITION_ID_FROM_DATE_TO_DATE ON [CR].[EMP_ACTIVITY] ([POSITION_ID],[FROM_DATE],[TO_DATE]) INCLUDE ([EMP_ID],[ACTIVITY_ID],[VESSEL_ID],[PRINCIPAL_COMPANY_ID],[MGR_COMPANY_ID],[MAGNT_COMPANY_ID])Regards
Binu
Binu
Is the wait type always SOS_SCHEDULER_YIELD if you run the query several times? What happens if you add percent_complete from dm_exec_requests to the query and then see how quickly that number increases? How large is the EMP_ACTIVITY table in MB or GB and in rows?
John
August 23, 2017 at 10:15 am
wait type is always SOS_SCHEDULER_YIELD
below is output from dm_exec_requests
session_id RunTime start_time total_elapsed_time
51 2017-08-23 18:53:34.987 2017-08-23 18:51:57.033 97950
table size and rows
name rows reserved data index_size unused
EMP_ACTIVITY 171783 64944 KB 56408 KB 8112 KB 424 KB
Regards
Binu
August 23, 2017 at 10:31 am
171k rows is pretty small. Are you executing the index create from the server direct?
Have you tried to kill the create statement?
How long does the kill take to rollback?
There doesn't appear to be any blocking, but are there by chance heavy cases of locking on the table?
Is the query wrapped in a transaction without the commit tran by chance?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply