December 17, 2019 at 9:11 am
Hello
We are using SQL server 2017
Microsoft SQL Server 2017 (RTM-CU15) (KB4498951) - 14.0.3162.1 (X64) May 15 2019 19:14:30 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )
On IOs Windows server 2016 - 64 bit - dual processor @ 2.4 GHz - 64 GB RAM
Currently from task manager we have Memory used 93 % out of which 51 GB used by SQL.
C drive has 4.99GB free out of 49.99 GB.
SQL server memory options has values 0 as minimum and 2147483647 MB as maximum.
Whenever we try to do update on a certain table that has around 550,000 records and 1 GB size
, it takes several minutes for simple update to be executed and causes the whole sql to become busy and we start getting timeout expired on webpages in our website.
This table is used by several views and queries running on website.We did rebuild all indexes over this table and views using it since fragmentation percentage was high, but it didn't solve the problem.
It is the main source of info for our website.
What might be the reason of such behavior ?
December 17, 2019 at 9:37 am
I'm not a DBA, but there are a few alarm bells right off the bat.
December 17, 2019 at 10:08 am
We have another Drive D where we have sql installed on that is not full
December 17, 2019 at 12:33 pm
There's not enough information here to go on. We could be looking at all sorts of things from bad or missing indexes, out of date statistics, blocking and locking, to a lack of resources (where you seem to be focused, but we don't have the evidence to back up this focus just yet).
First, get the execution plans for the UPDATE queries. See what's happening there. Are you doing table scans across the 500,000 rows? That's certainly going to slow things down. Code changes, indexes, statistics, might fix this issue, depending.
Next, capture query performance metrics and blocking using Extended Events. Here's a nice overview on how to set this up, but there are others out there. Understand where and why things are running slow. You may want to also capture wait metrics for this in order to see what's causing systems to lock up. All this data will let you know if it's just simple resource contention, or a lack of resources.
Finally, more as analysis piece than as a purely diagnostic set of information, I'd suggest capturing your waits and queues through performance monitor. This lets you know where the bottlenecks on your system are. You'll still need to do the steps above to better identify why the bottlenecks are occurring and what needs to be done to clear them up. However, knowing where your system is slow is more vital than knowing that it is.
"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
December 17, 2019 at 12:34 pm
In most cases, performance issues are in the actual code. If you post the ACTUAL execution plan, we might be able to see where the pain points are.
December 17, 2019 at 12:55 pm
In most cases, performance issues are in the actual code. If you post the ACTUAL execution plan, we might be able to see where the pain points are.
It doesn't have to be the actual plan. Granted, they are preferred, but there is always tons to learn from the estimated plans.
Sorry, I think I'm beginning to get twitchy on this topic.
"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
December 17, 2019 at 1:11 pm
Are there lots of foreign keys? Triggers? indexed / materialized views?
Thomas Rushton
blog: https://thelonedba.wordpress.com
December 18, 2019 at 10:19 pm
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply