SQL Server Table Simple Select Query taking too long

sql-server database sql-server-2012 database-performance database-administration

11,565

Solution 1

Try this,

select count(*) from Personas with (nolock)

Note:

if this query returns results then we can identify that your table is locked in some other session. You have to identify the session which blocks your table. Either you can kill that session or commit the transaction in it.

Solution 2

For me disconnecting the SQl database and reconnecting it back helped to solve the problem.

The problem was while updating a table (few transactions have been triggered/ they need commit). So i just needed to commit the transactions and close it

11,565

Jemru

Updated on June 04, 2022

Comments

Jemru almost 2 years
I have a simple table in MS SQL Server.
```
CREATE TABLE [dbo].[Personas] (
[Name] varchar(250) NOT NULL,
[ID] varchar(250) NOT NULL,
[IntranetID] varchar(250) NULL,
[eMail] varchar(250) NULL,
[Number] varchar(50) NULL,
[ModifDate] datetime NULL)
ON [PRIMARY];
```
After truncating and inserting data again to this table, I'm unable to do a simple select query or a simple count query. The process tasks keeps running without ending. However I can query successfully on other tables except this one.
```
select * from Personas;
select count(*) from Personas;
```
How can I know the reason why this is happening and how can I fix it? There are no error message on the queries so I don't know what are the errors. How can I know if the table was corrupted?

I'm not expert in SQL Server administration but I tried to execute below. I'm not sure if this is the reason, and how to fix this if it is.
```
DBCC SQLPERF(logspace)

Result:
Log Size (MB) = 19.6
Log Space Used (%) = 78.79
Status = 0
```
Please kindly help. Thank you in advance.
- Tom Chantler almost 9 years
  
  Do you have any open transactions? Try SELECT @@TRANCOUNT. It should be 0. If it's not, that could cause the select statement to hang indefinitely.
- Jemru almost 9 years
  
  Hi Tom, I tried it just now, the result is "0". Other tables are okay, except this one. Thanks.
- John Bell almost 9 years
  
  How many rows are in the table? Truncating a table can cause performance issues because the query planner is not updated, and indexes may become fragmented.
- Jemru almost 9 years
  
  Hi JohnnyBell, using toad table properties I can see that it have 16,000 rows. How can I know if my table is now corrupted? What can I do to try to fix this? Thanks.
- ughai almost 9 years
  
  Try running SELECT session_id,status FROM sys.dm_exec_sessions WHERE open_transaction_count > 0
- ughai almost 9 years
  
  @TomChantler - @@TRANCOUNT will only give you if the current session has any open transaction. for blocking we will have to check all sessions
- Jemru almost 9 years
  
  Hi Ughai, error in sql: Invalid column name 'open_transaction_count'. Thanks.
- Tom Chantler almost 9 years
  
  @@TRANCOUNT gives the current transaction count for the current connection, not the current session, but yeah, your command should work for SQL 2012 and newer.
- Abdul Rehman Sayed almost 9 years
  
  create a primary key on the table
- ughai almost 9 years
  
  you can use them interchangeably , but if another connection has an open transaction, it will not show up using @@TRANCOUNT in the current connection
- ughai almost 9 years
  
  @Jemru - thats weird. Try to execute your select in one query window and in another query window run EXEC sp_who2 and look for a SPID value in BlkBy column
- Jemru almost 9 years
  
  Hi Ughai. I executed EXEC sp_who2. There are Status = Sleeping. Command = Awaiting Command, CPU Time = 47929, BlkBy = "." Thank you.