February 28, 2023 at 3:06 pm
For now, rebuilding all the indexes on the tables every 1 hour is giving some relief. query returns in few milli secs.
February 28, 2023 at 3:17 pm
For now, rebuilding all the indexes on the tables every 1 hour is giving some relief. query returns in few milli secs.
but... why? Two rows or sometimes empty? Honestly, sounds like your statistics need updating not index rebuilding (you get the stats for free after an index rebuild). Give me a minute to look at the exec plan.
"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 28, 2023 at 3:24 pm
So, yeah, about what I expected. A clustered index scan and nothing else.
So, your #1 problem query is SELECT * from a table with two rows in it?
Dude, go home. You're winning the struggle. Everyone else here is dealing with much, much worse problems.
So, a query without a WHERE clause will only, ever, get a scan. No amount of indexing will change that. You're getting a scan. Since it's SELECT *, it's only ever going to scan the clustered index.
And that's it. That's the whole story.
The only way to speed up this kind of query is by throwing hardware at the problem. Faster, or more disks, running on faster, or more, disk controllers feeding into faster, and, not or, more memory.
Are you sure this is the problem query?
"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 28, 2023 at 3:25 pm
There's not much optimization you can do for selecting all rows and columns from a table -- best optimization is to return fewer columns is you don't really need all.
The actual execution plan for delete is probably more useful -- that can probably be tuned.
What is the desired execution time needed for inserts? for deletes? What are the actual average and maximum execution times you are seeing for inserts? for deletes?
If this is truly high-volume/high TPS, SQL Server may not be the ideal platform for managing application locking.
February 28, 2023 at 3:35 pm
So, yeah, about what I expected. A clustered index scan and nothing else.
So, your #1 problem query is SELECT * from a table with two rows in it?
Dude, go home. You're winning the struggle. Everyone else here is dealing with much, much worse problems.
So, a query without a WHERE clause will only, ever, get a scan. No amount of indexing will change that. You're getting a scan. Since it's SELECT *, it's only ever going to scan the clustered index.
And that's it. That's the whole story.
The only way to speed up this kind of query is by throwing hardware at the problem. Faster, or more disks, running on faster, or more, disk controllers feeding into faster, and, not or, more memory.
Are you sure this is the problem query?
Yes. Its a small table. Question is , when there is 0 rows in the table, and when we say SELECT * FROM <Tname> , why SQL Server is taking 4 seconds . When I checked the STATISTICS IO ON output it is doing some ~465 logical reads. why? Later we see some ghost_version_record_count for that table. When there is no rows/no data in the table , why it is taking 4 secs? This is what the original problem which I am trying to understand.
February 28, 2023 at 3:43 pm
Yes. Its a small table. Question is , when there is 0 rows in the table, and when we say SELECT * FROM <Tname> , why SQL Server is taking 4 seconds . When I checked the STATISTICS IO ON output it is doing some ~465 logical reads. why? Later we see some ghost_version_record_count for that table. When there is no rows/no data in the table , why it is taking 4 secs? This is what the original problem which I am trying to understand.
As before, have you captured the blocked process report? Set yours to run for blocked queries longer than 3 seconds. You should see what's going on. It's more than likely resource contention. But no way to know from the info you've provided.
From the sounds of things, this is some kind of permanent but "temporary" table where data is constantly added and then deleted. It's going to form hot spots and have lots of reads because of all the addition and removal of rows. It's honestly the kind of table that should be a table variable from the sounds of things, but I get it, 3rd party. Also, I saw, what, four indexes on the table? Those are doing nothing if the queries are all SELECT * without a WHERE clause, but are adding to the overhead and requirements for inserting and deleting rows.
All of this is stuff you'll need to take to the vendor to fix. There's no magic we're going to be able to give you on this kind of thing.
"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 28, 2023 at 3:57 pm
vsamantha35 wrote:Yes. Its a small table. Question is , when there is 0 rows in the table, and when we say SELECT * FROM <Tname> , why SQL Server is taking 4 seconds . When I checked the STATISTICS IO ON output it is doing some ~465 logical reads. why? Later we see some ghost_version_record_count for that table. When there is no rows/no data in the table , why it is taking 4 secs? This is what the original problem which I am trying to understand.
As before, have you captured the blocked process report? Set yours to run for blocked queries longer than 3 seconds. You should see what's going on. It's more than likely resource contention. But no way to know from the info you've provided.
From the sounds of things, this is some kind of permanent but "temporary" table where data is constantly added and then deleted. It's going to form hot spots and have lots of reads because of all the addition and removal of rows. It's honestly the kind of table that should be a table variable from the sounds of things, but I get it, 3rd party. Also, I saw, what, four indexes on the table? Those are doing nothing if the queries are all SELECT * without a WHERE clause, but are adding to the overhead and requirements for inserting and deleting rows.
All of this is stuff you'll need to take to the vendor to fix. There's no magic we're going to be able to give you on this kind of thing.
Yes, exactly my point. it became a hot spot. There is a lot of contention on this table. This is not the exact query used within the app, however, app team had this question like , a simple select on a table with 0 or less rows would take that much time( 4 sec).
Vendor has left it to DBA's. As you pointed out, the 3 sec blocked process report might gives us some direction.
Just one last question, does those ghost_version_record_count is contributing to 4 sec elapse time? This is just for my understanding.
February 28, 2023 at 4:20 pm
It can, yes. Ghost cleanup can also block you.
The vendor can't leave this with you. You can't change the code, and the code needs to be changed. You also can't, usually, legally, change the structures (although, I've done it), and these structures look like they also must be changed (at least based on what you've shared). All of that is back on the vendor.
"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 28, 2023 at 5:11 pm
It can, yes. Ghost cleanup can also block you.
The vendor can't leave this with you. You can't change the code, and the code needs to be changed. You also can't, usually, legally, change the structures (although, I've done it), and these structures look like they also must be changed (at least based on what you've shared). All of that is back on the vendor.
Sir, Can you give some idea(s) to prove that this is kind of a "Hot Spot" contention scenario.
What metrics / method we can use to tell like these many transaction(s) are bombarding this table per min or 5 mins because of which it is not Scalable and vendor has to provide some kind of a fix or change the table design?
Thanks,
Sam
February 28, 2023 at 7:44 pm
Yes. Its a small table. Question is , when there is 0 rows in the table, and when we say SELECT * FROM <Tname> , why SQL Server is taking 4 seconds . When I checked the STATISTICS IO ON output it is doing some ~465 logical reads. why? Later we see some ghost_version_record_count for that table. When there is no rows/no data in the table , why it is taking 4 secs? This is what the original problem which I am trying to understand.
Lordy... another 3rd party software where the developers think they're smarter than the engine. 😀 Not you, Sam... I know you didn't create this.
The reason for the large number of reads may simply because of the way they're doing things and that left a pile of poo for ghost records, as has already been suggested... especially if it has such a high hit rate.
You asked about how to prove to the vendor that this table is a "hot spot". I'm thinking that you may already have the proof... you said that this table is suffering a huge number of page splits. Use whatever method you used to determine that and send the 3rd party the list of page splits you got in, say, 5 minutes. You could also setup a PerfMon chart to capture the blocking for a half hour span and send them that. If you have sp_WhoIsActive, you could set that up to capture some of this and, perhaps, direct the output to a table that you could also send so that they could see the code that's doing the blocking.
You might also want to setup either Extended Events or SQL Profiler (they'll have to pry that out of my cold, dead, hands before I quit using it 🙂 ) to capture Deadlock graphs that you could also send them in a file.
As far as the 4 second time goes and the related more-than 400 logic reads for a zero row return, you might want to try an ALTER TABLE REBUILD (provided that it's still a HEAP). In this case, I would probably NOT add a clustered index if it didn't have one before. Another reason why such a simple select may take so long is because it's simply waiting its turn on a very busy table.
As a bit of a sidebar, the 3rd party probably built this table and the locking methodology behind it in the name of "code portability"... that's almost never a good idea especially when it comes to performance. And, sorry... I know that statement doesn't help at all but had to say it out loud because you've just gotta wonder what the hell the 3rd party is thinking ... or if they actually thinking. 😀 They really need to learn how to do locking in a more frugal way, if that's what their goal was. Perhaps them switching to RCSI (Read Committed Snaphot Isolation) would be an easier win for them.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2023 at 7:50 pm
p.s. I also notice in one of your previous posts above that the table has 4 non-clustered indexes... that's likely where the page splits are coming from. Considering the very high usage and the extremely low row counts, my suggestion would be to disable (NOT DROP!!!) those 4 NCI's and see what happens. If there's no improvement, rebuild them to re-enable them. If disabling them DOES improve things, now you know the real issue. For low rowcounts, if disabling those indexes causes an extra slowdown, then you've gotta wonder why.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2023 at 12:59 pm
Everything Jeff said.
We've covered this in multiple posts. Capture query metrics, I'd go with batches & procedure calls, not individual statements at this point. Capture deadlocks (Extended Events, don't listen to Jeff on this single point, and he knows I love him, Profiler is dangerous). Capture blocking. Capture the page splits. Hand it all over to the vendor like the steaming pile of you-know-what that you're dealing with.
Document your pain and hand it over.
"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
March 1, 2023 at 5:00 pm
I don't understand why you would have indexes (apart from maybe a clustered one) on a table with only a few rows?
March 1, 2023 at 5:12 pm
I don't understand why you would have indexes (apart from maybe a clustered one) on a table with only a few rows?
Total agreement, but this one that Sam will have to take up with the vendor... along with all the other stuff.
"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
March 1, 2023 at 7:30 pm
vsamantha35 wrote:Yes. Its a small table. Question is , when there is 0 rows in the table, and when we say SELECT * FROM <Tname> , why SQL Server is taking 4 seconds . When I checked the STATISTICS IO ON output it is doing some ~465 logical reads. why? Later we see some ghost_version_record_count for that table. When there is no rows/no data in the table , why it is taking 4 secs? This is what the original problem which I am trying to understand.
Lordy... another 3rd party software where the developers think they're smarter than the engine. 😀 Not you, Sam... I know you didn't create this.
The reason for the large number of reads may simply because of the way they're doing things and that left a pile of poo for ghost records, as has already been suggested... especially if it has such a high hit rate.
You asked about how to prove to the vendor that this table is a "hot spot". I'm thinking that you may already have the proof... you said that this table is suffering a huge number of page splits. Use whatever method you used to determine that and send the 3rd party the list of page splits you got in, say, 5 minutes. You could also setup a PerfMon chart to capture the blocking for a half hour span and send them that. If you have sp_WhoIsActive, you could set that up to capture some of this and, perhaps, direct the output to a table that you could also send so that they could see the code that's doing the blocking.
You might also want to setup either Extended Events or SQL Profiler (they'll have to pry that out of my cold, dead, hands before I quit using it 🙂 ) to capture Deadlock graphs that you could also send them in a file.
As far as the 4 second time goes and the related more-than 400 logic reads for a zero row return, you might want to try an ALTER TABLE REBUILD (provided that it's still a HEAP). In this case, I would probably NOT add a clustered index if it didn't have one before. Another reason why such a simple select may take so long is because it's simply waiting its turn on a very busy table.
As a bit of a sidebar, the 3rd party probably built this table and the locking methodology behind it in the name of "code portability"... that's almost never a good idea especially when it comes to performance. And, sorry... I know that statement doesn't help at all but had to say it out loud because you've just gotta wonder what the hell the 3rd party is thinking ... or if they actually thinking. 😀 They really need to learn how to do locking in a more frugal way, if that's what their goal was. Perhaps them switching to RCSI (Read Committed Snaphot Isolation) would be an easier win for them.
RCSI is enabled for this database. However, writers are blocking writers. INSERTs and DELETE's,
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply