0

My SQL Server query is something like 

Select * 
from mytable 
where mycolum = X

The table only has about 8000 records, and this query used to run extremely fast.

Now all of a sudden it's running super slow. Any thoughts? Here's what I've tried

  • Updating the statistics for that table
  • Shrinking the database
  • Updating stats for the entire database via sp_updatestats
  • Restarting the db server

None of these are working.

Any ideas or things I could try?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ricky
  • 2,850
  • 5
  • 28
  • 41
  • 1
    Can you share the query plan? – DavidG Sep 30 '14 at 16:30
  • Some other query in progress? What does the server do? CPU / Disk capacity exceeded? – Pleun Sep 30 '14 at 16:32
  • are you querying the database locally? – Jake Sep 30 '14 at 16:32
  • Consult [How do I Obtain a Query Execution Plan](http://stackoverflow.com/questions/7359702/). – Dour High Arch Sep 30 '14 at 16:32
  • 1
    Build an index on `mycolum`. – Gordon Linoff Sep 30 '14 at 16:34
  • If your query was running fast and now is running slow, either your data changed which could alter the statistics (and therefore the execution plan) or more likely something environmental is occurring. Check your Db server, network, client PC, etc.. to narrow down possible issues. –  Sep 30 '14 at 16:42
  • 3
    define "super slow" minutes ? Hours ? And prior ? How many recs now ? always 8000 ? How many columns ? There are binary columns ? Where are the db local or network ? What kind of type are mycolumn ? – Max Sep 30 '14 at 16:46

1 Answers1

0

You should create an index for the column. If you are using a production DB that needs to remain active during the building of the index then you want to use ONLINE = ON in my sample below.

CREATE INDEX mytable_indx1 ON dbo.mytable (mycolum) WITH (ONLINE = ON);
jhilden
  • 12,207
  • 5
  • 53
  • 76