CODEWITHSUNDEEP
sql-serverselectsqlperformance
Greg
Greg

Reputation: 648

Why is my SELECT query taking so long?

I have the following table:

CREATE TABLE [dbo].[Notifications](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Fk_institutionId] [int] NOT NULL,
[Fk_userId] [int] NOT NULL,
[Read] [bit] NOT NULL,
[CategoryId] [int] NOT NULL,
[Title] [nvarchar](150) NULL,
[NotificationText] [text] NULL,
[CreateDate] [datetime] NOT NULL,
[ReadDate] [datetime] NULL,
[DisplayDate] [datetime] NULL,
[ReadBy] [nvarchar](100) NULL,
CONSTRAINT [PK_Notifications] PRIMARY KEY CLUSTERED 
(
   [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

a simple select * from Notifications takes 30 seconds while there is only 800,000 rows in this table.

This actual situation is each user performs the query select * from notifications where DisplayDate is null and Fk_userId = [the user id] and this create a server error in my .NET web application saying something about a deadlock.

The execution plan for that query is quite simpleenter image description here

What am I doing wrong? Or why is it create a deadlock error?

Upvotes: 0

Views: 13038

Answers (3)

Martin Smith
Martin Smith

Reputation: 454020

You've asked about two different queries.

The more important one seems to be

SELECT *
FROM   notifications
WHERE  DisplayDate IS NULL
       AND Fk_userId = [the user id]

The execution plan shows that you are missing any useful index so are getting a parallel scan of the whole table.

If you provide an index then it should perform much better and be less deadlock prone simply by virtue of needing to read less data.

An example index for that query would be

CREATE INDEX IX_Fk_userId_Where_DisplayDate_Is_Null
  ON notifications(Fk_userId)
  INCLUDE (DisplayDate) 
  WHERE DisplayDate IS NULL;

Upvotes: 2

Luis Teijon
Luis Teijon

Reputation: 4909

It could be due to many reasons:

Maybe someone else is accessing to this table too and locked it. In that case, try this:

SELECT * FROM Notifications WITH(NOLOCK)

Sometimes the problem is related to missing indexes. I don't think it has to do with indexes since you have a primary key. However, you need to check the degree of fragmentation of that index and rebuild it if necessary.

Another key point could be the query optimization statistics. You can update them by doing this:

UPDATE STATISTICS Notifications;

As per you last update, you can use this query to deal with blocking:

-- Detect blocking (run multiple times)  (Query 62) (Detect Blocking)
SELECT t1.resource_type AS [lock type], DB_NAME(resource_database_id) AS [database],
t1.resource_associated_entity_id AS [blk object],t1.request_mode AS [lock req],  --- lock requested
t1.request_session_id AS [waiter sid], t2.wait_duration_ms AS [wait time],       -- spid of waiter  
(SELECT [text] FROM sys.dm_exec_requests AS r WITH (NOLOCK)                      -- get sql for waiter
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) 
WHERE r.session_id = t1.request_session_id) AS [waiter_batch],
(SELECT SUBSTRING(qt.[text],r.statement_start_offset/2, 
    (CASE WHEN r.statement_end_offset = -1 
    THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 
    ELSE r.statement_end_offset END - r.statement_start_offset)/2) 
FROM sys.dm_exec_requests AS r WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt
WHERE r.session_id = t1.request_session_id) AS [waiter_stmt],                   -- statement blocked
t2.blocking_session_id AS [blocker sid],                                        -- spid of blocker
(SELECT [text] FROM sys.sysprocesses AS p                                       -- get sql for blocker
CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle]) 
WHERE p.spid = t2.blocking_session_id) AS [blocker_stmt]
FROM sys.dm_tran_locks AS t1 WITH (NOLOCK)
INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK)
ON t1.lock_owner_address = t2.resource_address OPTION (RECOMPILE);

-- Helps troubleshoot blocking and deadlocking issues
-- The results will change from second to second on a busy system
-- You should run this query multiple times when you see signs of blocking

Upvotes: 0

Simon Woolf
Simon Woolf

Reputation: 603

You have a text field (NotificationText) in your table. "Text" in SQL Server is a type of BLOB (Binary Large OBject). These Field types are very difficult to optimise. You can use full text indexing but it gets complicated.

I would try a select excluding that field. Or, ask yourself if you really need to use Text. Would Nvarchar(8000) suffice?

I bet if you remove that Text field, or redefine it as an nvarchar, your query will suddenly start flying.

Upvotes: 0

Related Questions