SQL Server – Query Execution Time Due to Lengthy Column Size

sql serversql-server-2012

When I am trying to execute below query in SSMS, the query is taking more time (more than 10 minutes) to execute due to lengthy column size (Column Name: Keywords, Column Size: nvarchar(4000)). Employee table is containing 6000 records. 

Select EmployeeId, EmployeeName, Designation, Keywords
From Employees
Where StatusFlag = 'L'

I have created below non clustered covering index for the above table. 

Create NonClustered Index NCI_Employees_StatusFlag On Employees(StatusFlag) Include (EmployeeId, EmployeeName, Designation, Keywords)

How to retrieve the data from the above table in a faster manner?

Best Answer

Even if all 6,000 rows are packed full of 4,000 Unicode characters (8,000 bytes), that's only 45 MB of data. There's just no way it should take 10 minutes to read that much, even with a cold cache.

You're likely getting blocked by something, as Randi Vertongen mentioned in the comments. The answer here isn't going to be indexing. Run sp_WhoIsActive while your query is executing to see what's going on. That should show you:

  • what the query is waiting on
  • if it's blocked (and what's blocking it)
Related Question