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?

  • 2
    The index should be covering, it could be due to something else like estimates / blocking / .... Could you add the actual / estimated query plan to the question? You could use Pastetheplan to do this. – Randi Vertongen Apr 4 '19 at 18:11
  • 1
    Since SSMS can be slow to render results, I'd do a sanity check and add set statistics time on to the top of your query the next time you run it and check the actual query execution time - just to verify that it's the query that's slow and not simply rendering the results. – Scott Hodgin Apr 4 '19 at 18:18
  • 1
    @rgs, if your SQL server is in a remote machine, check your network also. It can be a network issue also – Biju jose Apr 5 '19 at 5:18

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)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.