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.
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 NULLThe 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.ThanksChris |
|
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. |
|
|
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 |
|
|
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. |
|
|
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 |
|
|
|
|
|
|
|