-
May 11th, 2018, 05:02 AM
#41
Re: transaction (process id nn) was deadlocked...
Sorry - couldn't get back online last night - had too many emails to deal with when I got back to my home office.
Originally Posted by MMock
...Also, I have to ask because this occurred to me on the drive home, this same query is in VB6 getting run at the same interval, why no deadlocks there? Is it because of OC's on errro resume next code that they're happening but we don't know???
Yes - having an ON ERROR RESUME NEXT would ignore that potential error.
Originally Posted by FunkyDexter
...You can then set up an index to support each individual select. The query will then be supported by the index and should perform much better (though it does add some extra burden at the back end as the result sets are merged for the union).
Although the MERGE work is additional, it's all memory as opposed to the scan/seek work which is most likely disk I/O.
Having a dozen people hit the database for the SAME three second query at the same time is questionable. Especially since all dozen will get the same 3 or 4 rows back - right?
It could be argued that a better technique would be that the "first" person in that 30 second window causes a staging table to be built with those 3 or 4 rows. Everyone else in that same 30-second window gets those "saved" results.
As soon as a new 30 second window is reached that staging table is cleared and re-loaded with 3 or 4 different rows. For the rest of THAT 30 second window those "saved" results are given to all subsequent client calls.
In my mind it's all about scalability. If you create a refresh routine that requires a 3 second query to return data - and you want to do the refresh every 30 seconds - you can quickly see how the math of multiple users will not scale well past 10 users (doing simple math - the actual point of break would need to be watched in production).
The method I mention above, just for comparison purposes, will scale to ANY number of users - as each user is simply reading 3 rows - probably memory cached since they were so recently re-built.
If the idea of this much of an architecture change is not acceptable, then the only other option is to work on getting the existing method to take way less time. The obvious area, as FD also mentioned, is to change that WHERE clause.
If you created three small views that ONLY return the key fields needed to run that bigger query - then you can change the source of the big query to be the "results of these smaller VIEW's" and JOIN on to that all those other tables. Since we are only grabbing a handful of rows the bigger query will run instantly. If the three smaller views still take "seconds" to run - as opposed to milliseconds - then it's all for nothing. At that point you would need to investigate additional indexes to make this work. Indexes slow down INSERT and UPDATE statements in a HUGE way - so care must be taken in this area as well.
Knowing that you created this bottleneck for the database engine to content with, and using error-ignoring techniques to make the client unaware, is not acceptable to my purist-SQL mind.
Cringe-worthy stuff
-
May 11th, 2018, 05:34 AM
#42
Re: transaction (process id nn) was deadlocked...
Although the MERGE work is additional, it's all memory as opposed to the scan/seek work which is most likely disk I/O.
Yep, and if the where's a highly selective the Merge cost is likely to be trivial. I really only included my "though" to cover my own ass. Applied across very non-selective where clauses the union trick can occasionally be slower - really shouldn't apply in this case though.
It could be argued that a better technique would be that the "first" person in that 30 second window causes a staging table to be built with those 3 or 4 rows. Everyone else in that same 30-second window gets those "saved" results.
Ooh, I REALLY like that out of the box thinking That's DEFINITELY an avenue worth pursuing if you can sell it to the manager.
If you created three small views
they don't need to be views (although it might be desirable from a functional separation point of view), you can just union three queries:-
[code]
Select *
From dbo.Trips
WHERE (dbo.Trips.TripStatus <> N'CP')
AND (dbo.Trips.TripStatus <> N'PP')
AND (dbo.Trips.EmployeeID IS NULL OR dbo.Trips.EmployeeID = N'')
AND (dbo.Trips.Trip_Type <> N'Rental')
AND (dbo.TripTypes.ShopItem = 0)
AND (dbo.Trips.Trip_Type <> 'SDII')
Union
Select *
From dbo.Trips
Where (dbo.Trips.TripStatus <> N'CP')
AND (dbo.Trips.TripStatus <>
Last edited by FunkyDexter; May 11th, 2018 at 05:43 AM.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
May 11th, 2018, 05:44 AM
#43
Re: transaction (process id nn) was deadlocked...
Weird. My post got corrupted and nothing I do seems to fix it
I was trying to say that the only index required would be on tripStatus, TripType and ShopItem. That would probably be enough to support all three parts of the query.
the first part of the query also might want splitting into two to eliminate the OR in the employee id clause. though since employeeID isn't part of the index, SQLServer will probably seek the index and then apply a scanned filter to the result.
Last edited by FunkyDexter; May 11th, 2018 at 05:47 AM.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
May 11th, 2018, 05:55 AM
#44
Re: transaction (process id nn) was deadlocked...
Agreed - it's all about building the parts in a way where you carefully examine the cost of making each pass. Basically we are looking for 3 or 4 primary keys - the best way to get those is what needs to be done.
Since I always use SPROCS I would have approached this from this angle - build a TEMP table or TABLE variable with the 3 or 4 primary keys that currently need to be handed to users.
Code:
Declare @IdList Table (KeyField int)
-- or whatever the actual field name and data type is
Insert into @IdList
Select KeyField
From dbo.Trips Where...
If you can get this Table variable filled with the 3 or 4 primary key's needed then the final result is simply
Code:
Select ...
From @IdList IDL
Left Join dbo.Trips on dbo.Trips.KeyField=IDL.KeyField
Left Join...
-- and note there is NO "where" clause needed
-
May 11th, 2018, 06:06 AM
#45
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
Good morning and thanks for coming back! I've extracted a few of your points and sent them to OC/manager to get his input.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 06:14 AM
#46
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
I was just poking around at the Trips table looking at its indexes and thought this was an interesting one:
Code:
CREATE NONCLUSTERED INDEX [ix_UnassignedTasks] ON [dbo].[Trips]
(
[ScheduledTime] ASC,
[TripStatus] ASC,
[Trip_Type] ASC,
[EmployeeID] ASC
)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 = 80)
GO
Comments?
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 06:32 AM
#47
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
Also I should clarify that I falsely accused OC of On Error Resume Next. But I thought there had to be something because why would this problem seem new to .NET? What I see in his VB6 routine that runs this query is On Error GoTo ErrorHand and then:
Code:
ErrorHand:
MsgBox "Problem refreshing XXX" & vbCrLf & Err.Description, vbExclamation
End
End Sub
So users could've been getting this same error and not known exactly what it was. But if a lot of users were getting this error I would think they would've made it a priority to get it fixed.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 06:41 AM
#48
Re: transaction (process id nn) was deadlocked...
Originally Posted by MMock
So users could've been getting this same error and not known exactly what it was. But if a lot of users were getting this error I would think they would've made it a priority to get it fixed.
VB6 with ADO is not .Net with SqlClient - it's hard to imagine what is different from then to now.
-
May 11th, 2018, 06:43 AM
#49
Re: transaction (process id nn) was deadlocked...
Originally Posted by MMock
I was just poking around at the Trips table looking at its indexes and thought this was an interesting one:
Code:
CREATE NONCLUSTERED INDEX [ix_UnassignedTasks] ON [dbo].[Trips]
(
[ScheduledTime] ASC,
[TripStatus] ASC,
[Trip_Type] ASC,
[EmployeeID] ASC
)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 = 80)
GO
Comments?
Well - that is an interesting index. That index defines the WHERE clause that needs to be used INITIALLY to pull primary keys into consideration.
All that other OR's stuff and <> stuff - what all has to happen in a second query that reduces the population further.
That would be my attack plan.
-
May 11th, 2018, 06:52 AM
#50
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
Originally Posted by szlamany
that all has to happen in a second query that reduces the population further.
I'm sorry...so what goes into the first query?
Another question just occurred to me - how does this get tested? I think you mentioned we only see the time a query takes in SSMS in seconds. How will I know how much I've improved the query? Obviously tweak - release - does error still occur - if yes, tweak... isn't a viable approach.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 07:04 AM
#51
Re: transaction (process id nn) was deadlocked...
It's got scheduled time as the first element, which means it won't be usable for the OR sections that don't care about scheduled time. It'll help with this where clause:-
Code:
( (dbo.Trips.TripStatus <> N'CP')
AND (dbo.Trips.TripStatus <> N'PP')
AND (dbo.Trips.Trip_Type <> N'Rental')
AND (dbo.TripTypes.ShopItem = 0)
AND (dbo.Trips.Trip_Type <> 'SDII')
AND (dbo.Trips.ScheduledTime IS NULL)
)
But the other two sections will require an index that doesn't have scheduled time at the front. If it were the last field it would have been ideal.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
May 11th, 2018, 07:06 AM
#52
Re: transaction (process id nn) was deadlocked...
We have your WHERE clause down to this - right?
Code:
WHERE (
(dbo.Trips.TripStatus <> N'CP')
AND (dbo.Trips.TripStatus <> N'PP')
AND (dbo.Trips.EmployeeID IS NULL OR dbo.Trips.EmployeeID = N'')
AND (dbo.Trips.Trip_Type <> N'Rental')
AND (dbo.TripTypes.ShopItem = 0)
AND (dbo.Trips.Trip_Type <> 'SDII')
)
OR
( (dbo.Trips.TripStatus <> N'CP')
AND (dbo.Trips.TripStatus <> N'PP')
AND (dbo.Trips.Trip_Type <> N'Rental')
AND (dbo.TripTypes.ShopItem = 0)
AND (dbo.Trips.Trip_Type <> 'SDII')
AND (dbo.Trips.ScheduledTime IS NULL)
)
OR
( (dbo.Trips.TripStatus = N'NE')
AND (dbo.Trips.Trip_Type <> 'SDII')
AND (dbo.TripTypes.ShopItem = 0)
)
I see lots of fields in this WHERE clause that are in that index. I see some that are not. I want to see a query run that ONLY has the fields in that index - by the name of the index it's intended for this purpose.
What is the primary key of the TRIPS table? Tell me that and I'll give you TWO queries to test.
-
May 11th, 2018, 07:14 AM
#53
Re: transaction (process id nn) was deadlocked...
Originally Posted by FunkyDexter
It's got scheduled time as the first element, which means it won't be usable for the OR sections that don't care about scheduled time.
That statement gives us a clue as to where this went wrong. The original WHERE clause must have been more simple - probably no OR statements. The OC built a nice index to feed this query - knowing it was important it ran instantly.
Then time move along and user needs make us revisit this query. OR statements are added - making the index useless - and causing the engine to struggle to serve the result set back to the client.
[jest] If I was a DBA I would be noting the coder who did this injustice and making sure to code review all their future changes! [/jest]
-
May 11th, 2018, 07:14 AM
#54
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
Originally Posted by szlamany
We have your WHERE clause down to this - right?
Can you say how that is different than the original? (Assuming easier for you to say than for me to dissect).
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 07:18 AM
#55
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
Originally Posted by szlamany
[jest] If I was a DBA I would be noting the coder who did this injustice and making sure to code review all their future changes! [/jest]
Well you just blew my chances of being able to invite OC to read through this thread, in jest or not, LOL!
Seriously, he just emailed me and said go for it. He said he wrote the query 15 years ago and didn't know its performance had begun to suffer. He was appalled over the 3 seconds it's taking!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 07:26 AM
#56
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
The PK of Trips is "Control".
Yes the query is what you have in #52.
I would like to point out that when I run the query broken out into three queries that the query in #52 has as OR clauses, the same one row is returned (today it is returning one row not 3, as today so far we only have one unassigned task in our system).
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 07:27 AM
#57
Re: transaction (process id nn) was deadlocked...
Originally Posted by MMock
Can you say how that is different than the original? (Assuming easier for you to say than for me to dissect).
All I did was copy/paste your WHERE clause into a SSMS window and press ENTER at each AND and OR spot to line things up.
And then of course I put ()'s around each "island of AND's" so the OR's were clearly being using as needed.
I can remove my jest if you want!
-
May 11th, 2018, 07:30 AM
#58
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
No it's fine (your jest).
Another task just appeared. When I saw the new row in the grid (I'm running it live to see if *I* get deadlocked), I ran the 3 parts of the query again and all 3 returned the two rows.
...Now it is back to the one.
(See why we refresh!)
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 07:46 AM
#59
Re: transaction (process id nn) was deadlocked...
The ix_UnassignedTasks index is built like this:
[ScheduledTime] ASC,
[TripStatus] ASC,
[Trip_Type] ASC,
[EmployeeID] ASC
ScheduledTime is the first segment. Down below I see ScheduledTime in only ONE of the OR groups. We need to step back and talk about the other parts of each OR group to see if we can find a part of the condition that can exploit an index - like TripStatus or TripType.
Code:
WHERE (
(dbo.Trips.TripStatus <> N'CP')
AND (dbo.Trips.TripStatus <> N'PP')
AND (dbo.Trips.EmployeeID IS NULL OR dbo.Trips.EmployeeID = N'')
AND (dbo.Trips.Trip_Type <> N'Rental')
AND (dbo.TripTypes.ShopItem = 0)
AND (dbo.Trips.Trip_Type <> 'SDII')
)
OR
( (dbo.Trips.TripStatus <> N'CP')
AND (dbo.Trips.TripStatus <> N'PP')
AND (dbo.Trips.Trip_Type <> N'Rental')
AND (dbo.TripTypes.ShopItem = 0)
AND (dbo.Trips.Trip_Type <> 'SDII')
AND (dbo.Trips.ScheduledTime IS NULL)
)
OR
( (dbo.Trips.TripStatus = N'NE')
AND (dbo.Trips.Trip_Type <> 'SDII')
AND (dbo.TripTypes.ShopItem = 0)
)
One way to do this would be to put the following query in SSMS and turn on SHOW ACTUAL EXECUTION PLAN. It should give you a suggested index right out of the box.
Code:
Select Control From dbo.Trips
Where (dbo.Trips.TripStatus <> N'CP')
AND (dbo.Trips.TripStatus <> N'PP')
AND (dbo.Trips.EmployeeID IS NULL OR dbo.Trips.EmployeeID = N'')
AND (dbo.Trips.Trip_Type <> N'Rental')
AND (dbo.TripTypes.ShopItem = 0)
AND (dbo.Trips.Trip_Type <> 'SDII')
This WHERE clause is the FIRST OR group - try all three - tell me what it says for suggested indexes.
And then we can further discuss how to start reading the execution plan information - it's verbose - but it's really not that complicated. In the long run all SQL is doing is shuffling cards of data and counting cards while playing the dealer.
-
May 11th, 2018, 07:58 AM
#60
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
You are missing a table - TripTypes. I assumed you want me to add the join...
1.
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Trips] ([EmployeeID],[Trip_Type],[TripStatus])
INCLUDE ([Control])
GO
2.
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Trips] ([ScheduledTime],[Trip_Type],[TripStatus])
INCLUDE ([Control])
GO
3. No missing index suggested
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 08:05 AM
#61
Re: transaction (process id nn) was deadlocked...
For trip types - you had said it was a trivial number of rows - that join should be meaningless.
Ok - no INDEXES suggested? What version of MS SQL are you using?
Also - post a screen shot of what an execution plan looks like so I can help direct you into checking it out.
-
May 11th, 2018, 08:09 AM
#62
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
No indexes suggested on the 3rd query. You saw the other two?
MS SQL version = 12.0 SP2
OK, screen shot coming up...
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 08:14 AM
#63
Re: transaction (process id nn) was deadlocked...
OH - sorry - misread.
So - it gave us the obvious - the two fields that are in common are TRIP TYPE and TRIP STATUS.
If you remove all the part of the WHERE CLAUSE that are not TRIP TYPE and STATUS, how many MORE rows are returned?
If the amount is low - 10,000 or less maybe - that would be a good first query to run. And have an index focused on those two fields.
Then you pull the 10,000 rows with an INNER QUERY - and then apply the other parts of the WHERE clause to filter the 10,000 rows down to 1, 2 or 3 rows.
That is where this is headed. I need to step away for 30 minutes. Back soon...
-
May 11th, 2018, 08:17 AM
#64
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
I can't upload an attachment anymore. Last time I tried it didn't work either. I choose a file from my computer say Upload and nothing happens.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 08:19 AM
#65
Re: transaction (process id nn) was deadlocked...
What does it kind of look like? How many branches?
-
May 11th, 2018, 08:23 AM
#66
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
Originally Posted by szlamany
If you remove all the part of the WHERE CLAUSE that are not TRIP TYPE and STATUS, how many MORE rows are returned?
2606 rows retutrned
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 08:27 AM
#67
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
Originally Posted by szlamany
What does it kind of look like? How many branches?
If a branch is a level there are 3.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 09:23 AM
#68
Re: transaction (process id nn) was deadlocked...
2600 rows is a worthy index - filtering that down to the less set is possible in very little time.
It's the third query though - no filter suggestion. When you run that and look at the execution plan one of the "last nodes" will tell you how many rows it had to scan. Are you seeing scans that reach the 500,000 rows in your trips table?
-
May 11th, 2018, 09:24 AM
#69
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
I know you're busy and this isn't your job, but I am the one who is stepping away next.
I have one daughter coming home from college and I am going to get her after I get the other daughter who's taking an APUSH exam today (I wonder who's more stressed this morning, her or me?! [She is striving for a 5, I am just striving for a smooth-running query ])
I will check in when I get home to see if you're ready for me to try anything.
I started to take a stab at writing the inner query. I just obviously have to be 100% sure I am not changing the query and all the correct rows are still returned! This is an important query because our customers are waiting to be serviced and we have techs that we don't want to have any downtime.
Thanks and talk soon!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 09:25 AM
#70
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
Yes to #68. I forgot to say I have until 11:00...another 35 minutes!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 09:32 AM
#71
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
Originally Posted by szlamany
It's the third query though...When you run that and look at the execution plan one of the "last nodes" will tell you how many rows it had to scan. Are you seeing scans that reach the 500,000 rows in your trips table?
Do you mean last node to the right or last node on the bottom? I don't see anything that says "scan".
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 09:33 AM
#72
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
What I do see is an index seek that's 25%. And Lookup also 25%. Are those relevant?
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 09:37 AM
#73
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
I am reading how to interpret the execution plan. I see you can hover over the arrows and get #rows information if you want to know any of that.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 09:45 AM
#74
Re: transaction (process id nn) was deadlocked...
What I do see is an index seek that's 25%. And Lookup also 25%. Are those relevant?
Yes. That's what you want to see. Scan = Bad. Seek = Good. It explains why you're not getting a suggested index on that one - it's already got an index that supports it. If you hover over that seek you should see the name of the index it's using.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
May 11th, 2018, 09:49 AM
#75
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
OK. But a scan on a table with 65 rows is ok (one of the lookup tables that's not transactional) ?
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 11th, 2018, 10:32 AM
#76
Re: transaction (process id nn) was deadlocked...
But a scan on a table with 65 rows is ok
Yeah. There's no firm rule for this stuff but the bigger the table the more time a scan's going to take. 65 records is pretty trivial though.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
May 14th, 2018, 05:08 AM
#77
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
So from what I see, nothing is screaming out "fix me". Can you suggest a good place to start?
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 14th, 2018, 05:32 AM
#78
Re: transaction (process id nn) was deadlocked...
I thought we got up to the point of seeing that TWO of the queries were suggesting an index - one that you do not have yet.
And this third query - it's not suggesting an index - FD indicated that meant it already had an index. You can find that index name while hovering over those EXECUTION PLAN panels.
Regardless - the idea here is that you cannot use any of these indexes if you have OR's - and you can prove that by running the original query and seeing what the execution plan says for index scan vs. seek passes.
-
May 14th, 2018, 05:33 AM
#79
Re: transaction (process id nn) was deadlocked...
I don't want to tread on Steve's toes (too many cooks and all that) so disregard what I'm saying if he comes if he comes back into the thread. But my broad suggestion to performance tune this would be:-
1. Break the query into three totally separate queries, one with each "OR section" of your Where clause.
2. Run each in isolation with View Actual Execution Plan turned on
3. Check the execution plan and it will suggest any indexes that should create
*
4. Create the indexes
5. Union the three queries together and see if it improves performance
* You might want to post the index hints here before you go ahead and create them. Indexes are generally a good thing but, as Steve pointed out earlier, they do have a cost. Either Steve or I should be able to eyeball them and see if we can tweak them a little.
I get the impression you're already part way through that process but I'm not sure exactly how far.
Edit> Cross posted with Steve
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
May 14th, 2018, 06:00 AM
#80
Thread Starter
PowerPoster
Re: transaction (process id nn) was deadlocked...
Originally Posted by szlamany
I thought we got up to the point of seeing that TWO of the queries were suggesting an index - one that you do not have yet.
Oh, right. Sorry! I was focused on no scans.
First query suggested index:
CREATE NONCLUSTERED INDEX x ON Trips (EmployeeID, Trip_Type, TripStatus) INCLUDE (Control)
2nd query suggested index:
CREATE NONCLUSTERED INDEX y ON Trips (ScheduledTime, Trip_Type, TripStatus) INCLUDE (Control)
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|