Long-performing queries on a single table

SQL Server Administration
41

No, not on 4, three (the 4th one can never be "seeked-on" anyway).

Were the three in the given order? The query plan should show a seek and scan. If you don't get the seek first, then it won't help. No guarantee it will, anyway, as it depends on what % of rows need to be scanned, but it gives the best chance to reduce the overhead of the query.

42

CREATE CLUSTERED INDEX [IX_tmp_1] ON #tmpOut ([DATE] ASC, [MACHINENUM] ASC, [TIME] ASC);

25 seconds to return an empty RS.

43

are you doing the conversion on the where filter?

replace(convert(char(8),getdate(),11),'/','')

44

But did the query plan show a seek or not??

Clock time can be affected by a huge number of things.

Good luck with this.

45

No, that has been moved to variable declaration section, the where clause is now matching against values in the variables

46

There is a clustered index scan on the insert, a clustered index seek on the select.
Yeah, I know it can be affected by a number of things, I'm trying to pin down the specific thing now :slight_smile: Thanks!

47

what does your latest query look like now?
also please post table schema you are selecting from
also what happened to the CPU memory issue?

48

Here's the code, along with the temp table definition. After additional testing, I do not believe either CPU or memory is a problem. At least - I cannot see how, when the system is at 15% CPU, the DB instance in question is using 7% and 235 Mb RAM (out of 2 GB allocated) while this select is running. And that instance is doing a whole bunch of other stuff besides running my piddly select, and my process is not visibly affecting anything. It just takes 20+ seconds to return.

Right now I'm scrambling to set up a separate VM and move this DB out on its own, so I can at least eliminate the possibility of some third-party cause affecting this.

--sp_helptext SP_ChkOutBoundGETVOS8

SELECT CONVERT(nvarchar, GETDATE(), 121) as [Start Time]

DECLARE @T_MachineNum int = 3

--

-- Error Code - Required output parameter!

--@intErrorCode int OUTPUT

DECLARE @T_ID decimal

DECLARE @T_TYPE int

DECLARE @T_DATE nvarchar(6) = replace(convert(char(8),getdate(),11),'/','');

DECLARE @T_TIME nvarchar(6) = replace(convert(char(8),getdate(),8),':','')

--DECLARE @T_VX varchar(15)

SELECT @T_ID = 0

SELECT CONVERT(nvarchar, GETDATE(), 121) AS [After variable declare and population]

CREATE TABLE #tmpOut(

[id] [decimal](18, 0) NOT NULL,

[TYPE] nvarchar NULL,

[MACHINENUM] [int] NULL,

[DATETIMEPR] nvarchar NULL,

[DATE] nvarchar NULL,

[TIME] nvarchar NULL,

[PRIORITY] nvarchar NULL)

SELECT CONVERT(nvarchar, GETDATE(), 121) AS [After temp table declare]

CREATE CLUSTERED INDEX [IX_tmp_1] ON #tmpOut ([DATE] ASC, [MACHINENUM] ASC, [TIME] ASC);

--CREATE CLUSTERED INDEX [IX_tmp_1] ON #tmpOut ([MACHINENUM] ASC, datetimepr ASC, [DATE] ASC, [TIME] ASC);

--CREATE NONCLUSTERED INDEX [IX_tmp_2] ON #tmpOut ([PRIORITY] desc);

--CREATE NONCLUSTERED INDEX [IX_tmp_3] ON #tmpOut ([time] asc);

SELECT CONVERT(nvarchar, GETDATE(), 121) AS [After index creation]

INSERT INTO #tmpOut ([id], [TYPE], [MACHINENUM], [DATETIMEPR], [DATE], [TIME], [PRIORITY])

SELECT [id], [TYPE], [MACHINENUM], [DATETIMEPR], [DATE], [TIME], [PRIORITY] FROM OUTBOUND WITH(NOLOCK)

SELECT CONVERT(nvarchar, GETDATE(), 121) AS [After temp table population]

SELECT TOP 1

@T_ID = id,

@T_TYPE = [type]

FROM

#tmpOut WITH(NOLOCK)

WHERE

machinenum = @T_MachineNum

AND

datetimepr <> 'INPROGRESS'

AND

(

[date] = @T_DATE

AND

[time] <= @T_TIME

)

ORDER BY

[PRIORITY] desc, [time] asc

IF (@@ROWCOUNT > 0)

BEGIN

BEGIN TRAN

--UPDATE outbound SET datetimepr = 'INPROGRESS', [date] = replace(convert(char(8),getdate(),11),'/',''), [time] = replace(convert(char(8),getdate(),8),':','') where id = @T_ID

PRINT 'Updated OUTBOUND to "inProgress"';

COMMIT TRAN

SELECT CONVERT(nvarchar, GETDATE(), 121) AS [After found record]

SELECT @T_ID AS id, vxname, @T_TYPE AS [type] FROM outboundvx WITH(NOLOCK) WHERE [type] = @T_TYPE

SELECT CONVERT(nvarchar, GETDATE(), 121) AS [After select final data]

--SELECT @T_VX=VXNAME FROM outboundvx WITH(NOLOCK) WHERE @T_TYPE = [type]

--SELECT @T_ID AS id, @T_VX AS vxname, @T_TYPE AS [type]

END

ELSE

BEGIN

SELECT 0 AS id, 'VOICENOT' AS vxname, 1 AS [type]

END

SELECT CONVERT(nvarchar, GETDATE(), 121) AS [After all is done]

DROP TABLE #tmpOut

SELECT CONVERT(nvarchar, GETDATE(), 121) AS [After dropping temp table]

49

please post table definition for OUTBOUND and please slap some sample data here in the form of a DML

select 1 as id, 'chicken waffles' as type, 23 as machinenum union
select 2 as id, 'tater fries' as type, 22 as machinenum union

post about 25 rows please, for all the columns of OUTBOUND

50

Hold on.. Actually, I just copied that table to another SQL instance that is running on the same VM and using the same disks for everything, including tempdb. And the query runs without a hitch, in about 420 ms, every time. The only difference between the two environments is the SQL Instance Name. Same VM, same SQL version, same storage disks. I think I have my SQL instance corrupted somehow.

51

yes, but if it starts raining in Seattle and a butterfly flutters its wing in Taiwan, that might change.

52

This here is real data with phone numbers replaced by placeholders. Not the requested 25 records, but I almost never have 25 records there at any given time, this is a realistic scenario.

CREATE TABLE [dbo].[OUTBOUND](
[id] [numeric](18, 0) NOT NULL,
[DATETIMEPR] nvarchar NULL,
[DATE] nvarchar NULL,
[TIME] nvarchar NULL,
[LINE] nvarchar NULL,
[PID] nvarchar NULL,
[PRIORITY] nvarchar NULL,
[TYPE] nvarchar NULL,
[DNIS] nvarchar NULL,
[LASTDATE] nvarchar NULL,
[LASTTIME] nvarchar NULL,
[TRIES] nvarchar NULL,
[FODFILE] nvarchar NULL,
[FODNUM] nvarchar NULL,
[MACHINENUM] [int] NULL,
[CallBlastOnOff] nvarchar NULL,
[SecsOnHold] nvarchar NULL,
[CLAnnounce] nvarchar NULL,
[CLPhoneExt] nvarchar NULL,
[CallerID_ANI] nvarchar NULL,
[XferFrom] nvarchar NULL,
[IsXfer] nvarchar NULL,
[DNISEXT] nvarchar NULL,
[TIMEZONE] nvarchar NULL,
[PHBRDCampaignID] [int] NULL,
[PHBRDListDetailID] [int] NULL,
[CallGUID] nvarchar NULL,
[FaxID] nvarchar NULL
) ON [PRIMARY]
GO
INSERT [dbo].[OUTBOUND] ([id], [DATETIMEPR], [DATE], [TIME], [LINE], [PID], [PRIORITY], [TYPE], [DNIS], [LASTDATE], [LASTTIME], [TRIES], [FODFILE], [FODNUM], [MACHINENUM], [CallBlastOnOff], [SecsOnHold], [CLAnnounce], [CLPhoneExt], [CallerID_ANI], [XferFrom], [IsXfer], [DNISEXT], [TIMEZONE], [PHBRDCampaignID], [PHBRDListDetailID], [CallGUID], [FaxID]) VALUES (CAST(12905587 AS Numeric(18, 0)), N'INPROGRESS', N'190410', N'102147', N'011', N'13', N'5', N'5', N'2125551212', NULL, NULL, N'1', N'', N'2125551212', 2, N'', N'', N'', N'', N'2125551212', N'DNIS', N'0', NULL, NULL, NULL, NULL, N'190410102128013', NULL)
INSERT [dbo].[OUTBOUND] ([id], [DATETIMEPR], [DATE], [TIME], [LINE], [PID], [PRIORITY], [TYPE], [DNIS], [LASTDATE], [LASTTIME], [TRIES], [FODFILE], [FODNUM], [MACHINENUM], [CallBlastOnOff], [SecsOnHold], [CLAnnounce], [CLPhoneExt], [CallerID_ANI], [XferFrom], [IsXfer], [DNISEXT], [TIMEZONE], [PHBRDCampaignID], [PHBRDListDetailID], [CallGUID], [FaxID]) VALUES (CAST(12905762 AS Numeric(18, 0)), N'INPROGRESS', N'190410', N'111154', N'052', N'97', N'5', N'5', N'2125551212', NULL, NULL, N'1', N'', N'2125551212', 2, N'', N'', N'', N'', N'2125551212', N'DNIS', N'0', NULL, NULL, NULL, NULL, N'190410111130097', NULL)
INSERT [dbo].[OUTBOUND] ([id], [DATETIMEPR], [DATE], [TIME], [LINE], [PID], [PRIORITY], [TYPE], [DNIS], [LASTDATE], [LASTTIME], [TRIES], [FODFILE], [FODNUM], [MACHINENUM], [CallBlastOnOff], [SecsOnHold], [CLAnnounce], [CLPhoneExt], [CallerID_ANI], [XferFrom], [IsXfer], [DNISEXT], [TIMEZONE], [PHBRDCampaignID], [PHBRDListDetailID], [CallGUID], [FaxID]) VALUES (CAST(12905789 AS Numeric(18, 0)), N'INPROGRESS', N'190410', N'111930', N'046', N'148', N'1', N'1', N'2125551212', NULL, NULL, N'1', N'', N'', 2, N'', N'', N'', N'', N'2125551212', N'DNIS', N'0', NULL, NULL, NULL, NULL, N'190410111827148', NULL)
INSERT [dbo].[OUTBOUND] ([id], [DATETIMEPR], [DATE], [TIME], [LINE], [PID], [PRIORITY], [TYPE], [DNIS], [LASTDATE], [LASTTIME], [TRIES], [FODFILE], [FODNUM], [MACHINENUM], [CallBlastOnOff], [SecsOnHold], [CLAnnounce], [CLPhoneExt], [CallerID_ANI], [XferFrom], [IsXfer], [DNISEXT], [TIMEZONE], [PHBRDCampaignID], [PHBRDListDetailID], [CallGUID], [FaxID]) VALUES (CAST(12906004 AS Numeric(18, 0)), N'INPROGRESS', N'190410', N'120756', N'038', N'147', N'5', N'5', N'2125551212', NULL, NULL, N'1', N'', N'2125551212', 2, N'', N'', N'', N'', N'2125551212', N'DNIS', N'0', NULL, NULL, NULL, NULL, N'190410120748147', NULL)
INSERT [dbo].[OUTBOUND] ([id], [DATETIMEPR], [DATE], [TIME], [LINE], [PID], [PRIORITY], [TYPE], [DNIS], [LASTDATE], [LASTTIME], [TRIES], [FODFILE], [FODNUM], [MACHINENUM], [CallBlastOnOff], [SecsOnHold], [CLAnnounce], [CLPhoneExt], [CallerID_ANI], [XferFrom], [IsXfer], [DNISEXT], [TIMEZONE], [PHBRDCampaignID], [PHBRDListDetailID], [CallGUID], [FaxID]) VALUES (CAST(12905639 AS Numeric(18, 0)), N'INPROGRESS', N'190410', N'104037', N'031', N'17', N'5', N'5', N'2125551212', NULL, NULL, N'1', N'', N'2125551212', 3, N'', N'', N'', N'', N'2125551212', N'DNIS', N'0', NULL, NULL, NULL, NULL, N'190410104032017', NULL)
INSERT [dbo].[OUTBOUND] ([id], [DATETIMEPR], [DATE], [TIME], [LINE], [PID], [PRIORITY], [TYPE], [DNIS], [LASTDATE], [LASTTIME], [TRIES], [FODFILE], [FODNUM], [MACHINENUM], [CallBlastOnOff], [SecsOnHold], [CLAnnounce], [CLPhoneExt], [CallerID_ANI], [XferFrom], [IsXfer], [DNISEXT], [TIMEZONE], [PHBRDCampaignID], [PHBRDListDetailID], [CallGUID], [FaxID]) VALUES (CAST(12905773 AS Numeric(18, 0)), N'INPROGRESS', N'190410', N'111509', N'024', N'120', N'5', N'5', N'2125551212', NULL, NULL, N'1', N'', N'2125551212', 3, N'', N'', N'', N'', N'2125551212', N'DNIS', N'0', NULL, NULL, NULL, NULL, N'190410111448120', NULL)
INSERT [dbo].[OUTBOUND] ([id], [DATETIMEPR], [DATE], [TIME], [LINE], [PID], [PRIORITY], [TYPE], [DNIS], [LASTDATE], [LASTTIME], [TRIES], [FODFILE], [FODNUM], [MACHINENUM], [CallBlastOnOff], [SecsOnHold], [CLAnnounce], [CLPhoneExt], [CallerID_ANI], [XferFrom], [IsXfer], [DNISEXT], [TIMEZONE], [PHBRDCampaignID], [PHBRDListDetailID], [CallGUID], [FaxID]) VALUES (CAST(12905776 AS Numeric(18, 0)), N'INPROGRESS', N'190410', N'111541', N'011', N'144', N'1', N'1', N'2125551212', NULL, NULL, N'1', N'', N'', 3, N'', N'', N'', N'', N'2125551212', N'DNIS', N'0', NULL, NULL, NULL, NULL, N'190410111427144', NULL)
INSERT [dbo].[OUTBOUND] ([id], [DATETIMEPR], [DATE], [TIME], [LINE], [PID], [PRIORITY], [TYPE], [DNIS], [LASTDATE], [LASTTIME], [TRIES], [FODFILE], [FODNUM], [MACHINENUM], [CallBlastOnOff], [SecsOnHold], [CLAnnounce], [CLPhoneExt], [CallerID_ANI], [XferFrom], [IsXfer], [DNISEXT], [TIMEZONE], [PHBRDCampaignID], [PHBRDListDetailID], [CallGUID], [FaxID]) VALUES (CAST(12905870 AS Numeric(18, 0)), N'INPROGRESS', N'190410', N'113743', N'032', N'139', N'5', N'5', N'2125551212', NULL, NULL, N'1', N'', N'2125551212', 3, N'', N'', N'', N'', N'2125551212', N'DNIS', N'0', NULL, NULL, NULL, NULL, N'190410113738139', NULL)
INSERT [dbo].[OUTBOUND] ([id], [DATETIMEPR], [DATE], [TIME], [LINE], [PID], [PRIORITY], [TYPE], [DNIS], [LASTDATE], [LASTTIME], [TRIES], [FODFILE], [FODNUM], [MACHINENUM], [CallBlastOnOff], [SecsOnHold], [CLAnnounce], [CLPhoneExt], [CallerID_ANI], [XferFrom], [IsXfer], [DNISEXT], [TIMEZONE], [PHBRDCampaignID], [PHBRDListDetailID], [CallGUID], [FaxID]) VALUES (CAST(12905958 AS Numeric(18, 0)), N'INPROGRESS', N'190410', N'120057', N'016', N'10', N'5', N'5', N'2125551212', NULL, NULL, N'1', N'', N'2125551212', 3, N'', N'', N'', N'', N'2125551212', N'DNIS', N'0', NULL, NULL, NULL, NULL, N'190410120051010', NULL)
INSERT [dbo].[OUTBOUND] ([id], [DATETIMEPR], [DATE], [TIME], [LINE], [PID], [PRIORITY], [TYPE], [DNIS], [LASTDATE], [LASTTIME], [TRIES], [FODFILE], [FODNUM], [MACHINENUM], [CallBlastOnOff], [SecsOnHold], [CLAnnounce], [CLPhoneExt], [CallerID_ANI], [XferFrom], [IsXfer], [DNISEXT], [TIMEZONE], [PHBRDCampaignID], [PHBRDListDetailID], [CallGUID], [FaxID]) VALUES (CAST(12905973 AS Numeric(18, 0)), N'INPROGRESS', N'190410', N'120359', N'031', N'69', N'5', N'5', N'2125551212', NULL, NULL, N'1', N'', N'2125551212', 3, N'', N'', N'', N'', N'2125551212', N'DNIS', N'0', NULL, NULL, NULL, NULL, N'190410120311069', NULL)
INSERT [dbo].[OUTBOUND] ([id], [DATETIMEPR], [DATE], [TIME], [LINE], [PID], [PRIORITY], [TYPE], [DNIS], [LASTDATE], [LASTTIME], [TRIES], [FODFILE], [FODNUM], [MACHINENUM], [CallBlastOnOff], [SecsOnHold], [CLAnnounce], [CLPhoneExt], [CallerID_ANI], [XferFrom], [IsXfer], [DNISEXT], [TIMEZONE], [PHBRDCampaignID], [PHBRDListDetailID], [CallGUID], [FaxID]) VALUES (CAST(12905977 AS Numeric(18, 0)), N'INPROGRESS', N'190410', N'120403', N'034', N'5', N'5', N'5', N'2125551212', NULL, NULL, N'1', N'', N'2125551212', 3, N'', N'', N'', N'', N'2125551212', N'DNIS', N'0', NULL, NULL, NULL, NULL, N'190410120352005', NULL)
INSERT [dbo].[OUTBOUND] ([id], [DATETIMEPR], [DATE], [TIME], [LINE], [PID], [PRIORITY], [TYPE], [DNIS], [LASTDATE], [LASTTIME], [TRIES], [FODFILE], [FODNUM], [MACHINENUM], [CallBlastOnOff], [SecsOnHold], [CLAnnounce], [CLPhoneExt], [CallerID_ANI], [XferFrom], [IsXfer], [DNISEXT], [TIMEZONE], [PHBRDCampaignID], [PHBRDListDetailID], [CallGUID], [FaxID]) VALUES (CAST(12905997 AS Numeric(18, 0)), N'INPROGRESS', N'190410', N'120625', N'006', N'136', N'5', N'5', N'2125551212', NULL, NULL, N'1', N'', N'2125551212', 3, N'', N'', N'', N'', N'2125551212', N'DNIS', N'0', NULL, NULL, NULL, NULL, N'190410120619136', NULL)
INSERT [dbo].[OUTBOUND] ([id], [DATETIMEPR], [DATE], [TIME], [LINE], [PID], [PRIORITY], [TYPE], [DNIS], [LASTDATE], [LASTTIME], [TRIES], [FODFILE], [FODNUM], [MACHINENUM], [CallBlastOnOff], [SecsOnHold], [CLAnnounce], [CLPhoneExt], [CallerID_ANI], [XferFrom], [IsXfer], [DNISEXT], [TIMEZONE], [PHBRDCampaignID], [PHBRDListDetailID], [CallGUID], [FaxID]) VALUES (CAST(12906024 AS Numeric(18, 0)), N'INPROGRESS', N'190410', N'121034', N'031', N'69', N'5', N'5', N'2125551212', NULL, NULL, N'1', N'', N'2125551212', 3, N'', N'', N'', N'', N'2125551212', N'DNIS', N'0', NULL, NULL, NULL, NULL, N'190410121027069', NULL)
INSERT [dbo].[OUTBOUND] ([id], [DATETIMEPR], [DATE], [TIME], [LINE], [PID], [PRIORITY], [TYPE], [DNIS], [LASTDATE], [LASTTIME], [TRIES], [FODFILE], [FODNUM], [MACHINENUM], [CallBlastOnOff], [SecsOnHold], [CLAnnounce], [CLPhoneExt], [CallerID_ANI], [XferFrom], [IsXfer], [DNISEXT], [TIMEZONE], [PHBRDCampaignID], [PHBRDListDetailID], [CallGUID], [FaxID]) VALUES (CAST(12906103 AS Numeric(18, 0)), N'INPROGRESS', N'190410', N'122753', N'035', N'11', N'5', N'5', N'2125551212', NULL, NULL, N'1', N'', N'2125551212', 3, N'', N'', N'', N'', N'2125551212', N'DNIS', N'0', NULL, NULL, NULL, NULL, N'190410122703011', NULL)

1 Like
53

did you try this out before posting? please vet locally first?

55

This site is stripping the nvarchar size definition from the create statement. trying again. the inserts are all good.

CREATE TABLE [dbo].[OUTBOUND](

[id] [decimal] (18, 0) NOT NULL,

[DATETIMEPR] [nvarchar] (13) NULL,

[DATE] [nvarchar] (6) NULL,

[TIME] [nvarchar] (6) NULL,

[LINE] [nvarchar] (4) NULL,

[PID] [nvarchar] (4) NULL,

[PRIORITY] [nvarchar] (1) NULL,

[TYPE] [nvarchar] (2) NULL,

[DNIS] [nvarchar] (10) NULL,

[LASTDATE] [nvarchar] (6) NULL,

[LASTTIME] [nvarchar] (6) NULL,

[TRIES] [nvarchar] (1) NULL,

[FODFILE] [nvarchar] (18) NULL,

[FODNUM] [nvarchar] (10) NULL,

[MACHINENUM] [int] NULL,

[CallBlastOnOff] [nvarchar] (1) NULL,

[SecsOnHold] [nvarchar] (2) NULL,

[CLAnnounce] [nvarchar] (1) NULL,

[CLPhoneExt] [nvarchar] (10) NULL,

[CallerID_ANI] [nvarchar] (10) NULL,

[XferFrom] [nvarchar] (4) NULL,

[IsXfer] [nvarchar] (1) NULL,

[DNISEXT] [nvarchar] (8) NULL,

[TIMEZONE] [nvarchar] (3) NULL,

[PHBRDCampaignID] [int] NULL,

[PHBRDListDetailID] [int] NULL,

[CallGUID] [nvarchar] (15) NULL,

[FaxID] [nvarchar] (30) NULL

) ON [PRIMARY]
56

your top query returns 0 rows :face_with_monocle:

57

precisely! and it takes me 25 seconds to return 0 rows!

58

Tried it on my dev machine "0" seconds. your aws/server/os/instance/db/mdf/ldf is suspect.

59

One thing I recommended you could try

60

LOL yeah, I tried it on another instance, as I said, and it works. Setting up a brand new instance now, gonna re-create teh DB from scratch, repopulate, and see if this fixes the problem

61

I dont think that is going to work. I think it is a scaling issue. I asked if something in your ecosystem has changed that includes more telephone number more customers. you move it to new instance with one simple query and not real life LIVE, it will trip up and fall head first. humpty dumpty :wink: