Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Slow Query

Author  Topic 

ChrisK1506
Starting Member

6 Posts

Posted - 2014-07-23 : 09:26:03
Hi,

I am fairly new to SQL and wondered if anyone could assist me. I have the following query which is being flagged as an expensive query in the activity monitor. Is there anything I can do to speed it up?

UPDATE
TOP (1) dbo.KeyingQueue WITH (ROWLOCK)
SET
LockUid = @lockUid,
LockTimeStamp = GETDATE()
WHERE
FormTypeId = @formTypeId AND
KeyingStatusId = @operatorKeyingStatusId AND
Region = @region AND
LockUid IS NULL

The following index has been created and I can see from the execution plan that it is being used when executing the query.

CREATE NONCLUSTERED INDEX [IX_KeyingQueue_GetNext] ON [dbo].[KeyingQueue]
(
[FormTypeId] ASC,
[KeyingStatusId] DESC,
[Region] ASC
)
INCLUDE ( [LockUid]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]


There are roughly 200,000 rows in the KeyingQueue table.

Any help would be appreciated.

Thanks

Chris

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-23 : 10:45:24
Well, for starters, why do you specify TOP(1) and WITH (ROWLOCK)? Surely those are not required. Also, note this about TOP:

quote:

When TOP is used with INSERT, UPDATE, MERGE, or DELETE, the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in these statements. If you need to use TOP to insert, delete, or modify rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause that is specified in a subselect statement.



Second, what is the CI (clustered index) on the table?

Finally, since you don't specify any details about what constitutes "expensive" it's kinda hard to know where to look for any problems (though knowing the CI would help). It's hard to imagine that an update against such a small table would cause notable performance issues.
Go to Top of Page

ChrisK1506
Starting Member

6 Posts

Posted - 2014-07-23 : 11:00:22
Thanks for your response.

Top (1) is being used because we only want to set a lock on 1 row in the table and the WHERE clause would return many rows. Therefore we only want one of these records.

As far as I can see there is no Clustered Index on this table at all????

Our customer was reporting that this operation was taking roughly 30 seconds and the "Average duration (ms)" column in Activity Monitor was stating 2760 which much larger than any other queries in the list.

I hope this makes some sense, sorry if I'm using the wrong jargon, as I'm fairly new to this.

Thanks
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-24 : 07:56:30
"Therefore we only want one of these records".

OK, but which one? Since there's no ORDER BY clause, it will be determined by SQL Server, which may not be what you want. Consider it a random choice.

You still don't need the ROWLOCK hint. SQL figures this out by itself and usually does a far better job than we ever could. Microsoft advises that hints only be used in special circumstances (and yours is not that special)

If you have no CI, then the table is a heap. New rows are added at the end with no regard to any key values which can lead to your data being all over creation, depending on how data is inserted and deleted. It also means that your NCI points to page/file/slot instead of a CI entry. Your query might cause seeks all over the disk since there is no logical ordering of data. Bad performance of your query is a likely byproduct.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-24 : 10:50:43
CREATE NONCLUSTERED INDEX [IX_KeyingQueue_GetNext] ON [dbo].[KeyingQueue]
(
[FormTypeId] ASC,
[KeyingStatusId] DESC,
[Region] ASC,
[LockUid] ASC
)
INCLUDE ([LockTimeStamp] ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -