1

I have a table that stores less than 3 registers, but the queries on it are very slow.

This table has only a single Primary Key with 0% fragmentation.

> DBCC SHOWCONTIG scanning 'MYTABLE' table... TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7930.0
- Avg. Page Density (full).....................: 2.03%
  • 2
    What do you call 'very slow' ? What query do you use ? – DeadEye Jun 22 '15 at 13:10
  • 2
    Does this table get frequent updates that would cause blocking? – Martin Smith Jun 22 '15 at 13:14
  • slow is more than 6sec to a select. typically they run on it delete, update, select – Diego Flores Jun 22 '15 at 13:22
2

If multiple threads are issuing the delete/update/select statements concurrently, then blocking is the most likely explanation. You could use SQL Profiler and filter the trace on TextData Like '%MYTABLE%' to find all the statements accessing the table.

https://stackoverflow.com/questions/4076516/how-to-profile-for-one-table-in-sql-server

However use caution with SQL Profiler in a production environment, it can impact overall server performance.

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.