Table has no data but query takes 20 secs to complete
-
Hi All,
I have come across one issue today and I am looking for some suggestions.
select @@version
Microsoft SQL Server 2017 (RTM-CU23) (KB5000685) - 14.0.3381.3 (X64) Feb 9 2021 12:08:50 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)
Dev team came back to us and says, the table has no rows in it, but when I do a "select * from tname" it is taking anyway around 7-20 secs.WHY?
I tried to fetch the actual plan. its doing a clustered idx scan. Attaching the plan as well.
Then I turned on set statistics IO and TIME ON. To my suprise, its doing 55932, logical reads why when there is no data in the table?
set statistics IO ON
set statistics time on
use <db>
go
select * from <tname>
go
--output
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(0 rows affected)
Table <tname> Scan count 1, logical reads 55932, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Then I came across some new discussion came up of ghost records in the table.
Few things I want get clarified?
1) Why query taking more time to finish when there are 0 rows in the table?
2) Why it is showing logical reads = 29532? what does it indicate?
3) What are ghost rec's? why they exist and sql server is creating them? what type of questions we can ask back to dev team to avoid ghost records in a table? why they exist inside a table?
4) How to clean the ghost records in a specific table? do we need to do that as part of regular maintenance? any side-affects or downsides?
5) Does rebuilding clustered idx will remove ghost records? are there any implications?
6) How to avoid these ghost records inside a table?
Thanks,
Sam
- This topic was modified 1 year, 4 months ago by vsamantha35.
-
-- now I am seeing version_ghost_record_count as well. How to clean up these ? why they exist? and how do they affect the SELECT performance?
SELECT
ghost_record_count,
version_ghost_record_count,
record_count,
*
FROM sys.dm_db_index_physical_stats (DB_ID(N'db1'), OBJECT_ID(N'tname'), NULL, NULL , 'DETAILED');
GO
GO
SELECT
--[name] ,
[database_id] ,
[snapshot_isolation_state_desc] ,
[is_read_committed_snapshot_on] ,
[recovery_model] ,
[recovery_model_desc] ,
[log_reuse_wait_desc]
FROM sys.[databases]
where database_id = 11;
-
Hi,
please learn to post sql code, it is quite diffucult to read.
SELECT @@version
-- it is so simple! -
Hi,
I have provided the version details in my description.
Microsoft SQL Server 2017 (RTM-CU23) (KB5000685) - 14.0.3381.3 (X64) Feb 9 2021 12:08:50 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)
Basically, I am looking for below answers. I did some basic reading of ghost rec's online when deletes happen. they are marked for deletion but the rows are not removed on the data page on the disk. Why sql has to do this instead of deleting the data permanently from the page when it is a committed txn(per say).
1) Why query taking more time to finish when there are 0 rows/no data in the table?
2) What are ghost rec's? why they exist and sql server is creating them? what type of questions we can ask back to dev team to avoid ghost records in a table? why they exist inside a table?
3) How to avoid these ghost records inside a table?
4) How to clean the ghost records in a specific table? do we need to do that as part of regular maintenance? any side-affects or downsides?
5) Does rebuilding clustered idx will remove ghost records?
I am seeing every fragmentation every now and then on this table and also came to know the table is used kind of meta data structure to maintain locks for the application. There will lots of DMLS happening on the table though it is a small table but it is hit quite often.
-
Hi,
did you take a look on the execution plan of your query?
-
Is ghost cleanup turned off (i.e., do you have trace flag 661 enabled?
-
If there are 50k reads, there's some data somewhere. Without seeing the query, your structures, the execution plan, nothing but the version of SQL Server, which, sorry, communicates just almost nothing, I've got nothing but guesses. However, if there were literally zero rows, ghost rows or not, you would not see 50k reads. That's a fact.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Grant Fritchey wrote:
If there are 50k reads, there's some data somewhere. Without seeing the query, your structures, the execution plan, nothing but the version of SQL Server, which, sorry, communicates just almost nothing, I've got nothing but guesses. However, if there were literally zero rows, ghost rows or not, you would not see 50k reads. That's a fact.
Attaching the Actual execution plan and table structure.
Just to add , this db has RCSI isolation level turned on and it is in AG readable secondary as well.
We made it as readable secondary very recently and that's when we started seeing this issue.
I don't exactly know, how ghost records/ghost version records are slowing down the SELECT on a table with no rows.
set statistics IO ON
set statistics time on
use db
go
select * from c_xxxxxxx_lock
go(0 rows affected)
Table 'C_xxxxx_LOCK'. Scan count 1, logical reads 29532, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 6812 ms, elapsed time = 6962 ms.
-
If you want to post a query plan use something like:
https://www.brentozar.com/pastetheplan/
Would you download a unknown file from the internet?
-
You didn't indicate schema in your pseudocode. Did you use schema name in your query? (Recommend you always do to avoid ambiguity)
Any tables, views, or synonyms w/ the same name but a schema other than "dbo" (or your default schema if not "dbo")?
-
Ah, so RCSI and Availability Groups. That changes things.
So, yeah, what's happening is, because you have RCSI, it has to keep those version records. Normally, those get cleaned up, but, because you have AG, it has to wait for the AG to complete all its transactions. In the mean time, the beauty of RCSI is that you can read data in flight. So you're still getting a scan because that stuff is stored still, and all of it is being checked to see if there is data you can read. Makes sense.
Be sure you haven't accidently turned off cleanup as was suggested above, and then focus on why the AG is running so slow.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Also, the code didn't include the clustered index that is clearly there in the plan and the picture of the table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - ratbak wrote:
You didn't indicate schema in your pseudocode. Did you use schema name in your query? (Recommend you always do to avoid ambiguity) Any tables, views, or synonyms w/ the same name but a schema other than "dbo" (or your default schema if not "dbo")?
Good catch, but looking at the plan, it sure does look like he's scanning the version rows because the AG hasn't finished its transaction and everything has been cleaned up. I sure could be wrong (I am regularly), but I don't think so this time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Ken McKelvey wrote:
If you want to post a query plan use something like:
https://www.brentozar.com/pastetheplan/
Would you download a unknown file from the internet?
The trouble with that is that can go away and not leave any info for this thread in the future. I've no issue with a ZIP file. I do think it very odd that an SQL Server forum, like this one, doesn't allow that attachment of SQLPlans. 🙁
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
From the original results posted for sys.db_db_index_physical_stats, I think it's pretty clear that ghost rows are an issue. The fast inserts "feature" may also be an issue.
You first need to determine if Trace Flag 661 is enabled or disabled and let that be your guide for the plan to get better.
If it's enabled and has to stay that way, you're going to need to do the cleanup that ratbak talked about and then you may need to do index rebuilds.
If it's not enabled, you may have a serious problem with ghost rows. Try rebuilding the indexes involved. It that doesn't fix it, then you may need to call MS because something is seriously wrong.
Also, check for another trace flag (692) and see if it's enabled or disabled. If it's not enabled, the there's a "feature" called fast inserts that will allocate a new extent for every "qualified" insert... even if it's just one row. Trace flag turns that off. A "qualified" insert, in this case, is an insert that uses "insert bulk" (not to be confused with "bulk insert", where it also happens) from the front end.
In any case, thank you for all the information you posted and I think the course of action is clear... check the condition of the trace flags that have been spoken of, possible due the cleanup that was recommended, maybe rebuild the indexes. Another thought is, if the bloody tables have no rows that are valuable and never will in the future, consider getting rid of them?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply