Should this take hours?

Question

Should this take hours?

Edvard Korsbæk

SSCertifiable

Points: 5991
More actions
January 13, 2017 at 3:52 am

#317582

Hi all!
I had a query, that did take unresonable long time (minutes), and checked the execution plan.
It said, that i should make an index to make it much faster, so so i did:
/*
Missing Index Details from ExecutionPlan1.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 99.2477%.
*/
USE [Easyplan_drift]
GO
CREATE NONCLUSTERED INDEX [Employeeid_employeegroupid_shifttype]
ON [dbo].[dutyrostershift] ([employeeid],[employeegroupid],[shifttype]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
It has now been running for more than 30 minutes.
The table has app. 900000 records
Is this 'Expected behaviour'?
Best regards
Edvard Korsbæk

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Edvard KorsbÃ¦k - Friday, January 13, 2017 3:52 AM
Hi all!
I had a query, that did take unresonable long time (minutes), and checked the execution plan.
It said, that i should make an index to make it much faster, so so i did:
/*
Missing Index Details from ExecutionPlan1.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 99.2477%.
*/
USE [Easyplan_drift]
GO
CREATE NONCLUSTERED INDEX [Employeeid_employeegroupid_shifttype]
ON [dbo].[dutyrostershift] ([employeeid],[employeegroupid],[shifttype]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
It has now been running for more than 30 minutes.
The table has app. 900000 records
Is this 'Expected behaviour'?
Best regards
Edvard KorsbÃ¦k

Sometimes, yes.
Always take these recommendations with a pinch of salt: compare with the indexes you already have and always test, which is what you are doing. Recommendations for simple queries are usually good. The quality falls as queries get more complex.
That's a heck of a long time for so few rows so I'd guess that the query is complex. Would you like to share? An actual exec plan would be the best way to do this.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Are you creating the index directly on production during a time that the server is active? If so, the create will definitely take longer than it would otherwise.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eric M Russell SSC Guru Points: 125524 More actions · Answer 3

When what should be a fast query becomes a long running query, the first thing to do is confirm if blocking is taking place on the request's SPID.
900,000 rows is not a lot of data, unless it's getting a lot of write activity for some reason. Having more indexes on a table can potentially mean that other SPIDs will hold locks longer during insert, update, and delete operations, so too much indexes can potentially cause more blocking for readers.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Grant Fritchey SSC Guru Points: 398712 More actions · Answer 4

Be sure that you have measurements of the I/O and execution time of the query from before adding that index. You don't want to tune just on whether or not an execution plan looks a certain way. You want to be sure that you're seeing real performance benefits.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ScottPletcher SSC Guru Points: 100950 More actions · Answer 5

I suggest reviewing the overall index definitions and stats on the table. Perhaps even the clustering index needs changed to get the best overall performance. If the clus index does need corrected, and you don't do that, then by messing about with extra non-clus index(es) you'll be doing a lot more work of a lot less gain.

If you are willing to do that, and have full authority to run requests against the system views, let me know and we can work on that.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.