0

I have a table whose compound clustered index (int, DateTime) was 99% fragmented.

After defragmenting and making sure that statistics were updated, I still get the same response time when I run this query:

SELECT *
FROM myTable
WHERE myIntField = 1000 
AND myDateTimeField >= '2012-01-01' 
and myDateTimeField <= '2012-12-31 23:59:59.999'

Well, I see a small response time improvement (like 5-10%) but I was really expected to burst my queries after that index rebuild and stats update.

The estimated execution plan is:

  1. SELECT Cost: 0%
  2. Clustered Index Seek (Clustered)[MyTable].[IX_MyCompoundIndex] Cost: 100%

Is this because the index is a clustered index? Am I missing something?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Adi
  • 5,113
  • 6
  • 46
  • 59
  • 1
    How many rows are in the table, vs. how many rows match this query? How wide is the table? How long does it take? Where are you measuring this (Management Studio, your app) and how far away from the data source are you retrieving results? – Aaron Bertrand Sep 24 '12 at 14:46
  • Have you looked at the statistics? The set could be completely in memory helping explain what you are seeing here. – RubbleFord Sep 24 '12 at 14:49
  • What is the problem exactly? How many rows are returned? How long does the query take? – Martin Smith Sep 24 '12 at 14:50

2 Answers2

1

You should avoid SELECT * - probably even if you do need all of the columns in the table (which is rare).

Also, you are doing something very dangerous here. Did you know that your end range rounds up, so you may be including data from 2013-01-01 at midnight? Try:

AND myDateTimeColumn >= '20120101' 
AND myDateTimeColumn <  '20130101'

(This won't change performance, but it is easier to generate and is guaranteed to be accurate no matter what the underlying data type is.)

To eliminate network delays from your analysis of query time, you could consider SQL Sentry Plan Explorer - which allows you to generate an actual plan by running the query against the server, but discards the results, so that isn't an interfering factor.

Disclaimer: I work for SQL Sentry.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • And while you're at it - change the date string literals to the "safe" ISO-8601 format - `YYYYMMDD` without any dashes or slashes or anything! – marc_s Sep 24 '12 at 14:53
  • @Aaron Bertrand the code I put is only a sample, please don't mind about the way the select is written, it won't affect the performance I am looking for. – Adi Sep 24 '12 at 15:23
0

The execution time of the query is going to be spent reading enough pages of the index's btree to generate the result. Defragmenting the index will put adjacent rows together, reducing the number of pages that need to be read. It can also benefit from turning a largely random io pattern into a sequential one.

If your rows are wide and you don't get many rows per page you won't see much reduction in the number of rows.

If your index fill factor is low, you'll not get as many rows per page.

If your pages are in cache, you won't see any streaming v random IO benefit.

If you have spare CPU capacity on the machine, you may benefit from using page compression. This essentially trades more CPU for less IO.

Laurence
  • 10,896
  • 1
  • 25
  • 34