February 21, 2020 at 5:39 pm
For a direct, single table update, the alias shouldn't be needed.
If the FROM clause with the same table name appears, or especially if there any JOINs, it's absolutely critical to use the alias rather than the original table name.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 21, 2020 at 9:43 pm
For a direct, single table update, the alias shouldn't be needed.
Correct... it shouldn't be. But sometimes and in a most unpredictable fashion, it works out better.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 24, 2020 at 7:16 am
A picture of a plan isn't a plan, all the good stuff is in the properties.
However, assuming that estimated plan is correct, and you're getting a clean index seek, then it's likely the issue may simply be I/O throughput. If you can, capture the actual plan (extended events is your buddy here) to see how that compares to the estimated plan, especially in terms of row counts. Then, focus on I/O. A single row update should be relatively painless. Emphasis on should. Unless you're just moving a ton of data around, in which case, hardware may be the solution, not query tuning.
Hi Grant,
I am little bit stuck by the word "I/O throughput". Is it something related to data transfer or MB/sec?
February 24, 2020 at 9:00 am
I'm going to be very kind, a lot of people would have shouted.
I/O is input/output (sometimes refered to as Inbound / Outbound)
throughput means how much traffic you get. you can measure this in either direction or both I and O
typically it refers to disks, but it could be disk controllers or network. I cant speak for grant, but I guess he's talking about DISK I/O.
my advice....
Get some metrics , google what is good and bad for your hardware setup and then post some details back here
MVDBA
Hi Grant,
I am little bit stuck by the word "I/O throughput". Is it something related to data transfer or MB/sec?
Basically, how fast can you read from your disk into memory. I/O is Input/Output and can relate to disks, memory, or your network. For most people, most of the time, we're talking disks & memory, both. The bottleneck for most systems is usually the disk, so a focus there is always a good idea.
It is data transfer, but there's more. There's lots of documentation out there on this (a whole chapter in my query tuning book). I'd look at disk seconds/read as a foundational measure of how quick or slow your disk is performing. Also look to the wait statistics found in sys.dm_os_wait_stats or sys.dm_db_wait_stats depending on if you're in Azure or not. Again, there's lots more to this, but that should help to get you started.
This all assumes that execution plan is reflective of reality. Basically, if you're going directly to the row you need to modify, there's no blocking or resource contention, but things are running slow, it's the hardware, not the software.
"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
February 24, 2020 at 6:06 pm
Thanks for the clarification Grant & Mike.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply