October 17, 2008 at 11:19 am
Hi,
I have a table with more than 200 Milion records,I have a Cluster Index on the primary key.When I try to run a simple query it takes a very long time.I checked the execution Plan,But it is using Clustered Index Seeking method.But it is running very slow.Previously this Query was running very fast
here is it's structure of table:
CREATE TABLE [dbo].[Table1](
[DateSince2000] [int] NOT NULL,
[Shape] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDateTime] [datetime] NOT NULL,
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ExID] [int] NOT NULL,
[Price] [float] NOT NULL,
[Size] [int] NOT NULL,
[Type] [tinyint] NOT NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[DateSince2000] ASC,
[shape] ASC,
[EventDateTime] ASC,
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
and the query is as fallows:
Select Top 1 EventDatetime
From Table1 with(index(PK_test))
WhereDateSince2000 = @DateSince2000
And Shape = 123
And eventDateTime <= '01/01/2008'
Order by EventDateTime desc
Does any body have any idea?
Thanks
October 17, 2008 at 11:51 am
arctica82 (10/17/2008)
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED(
[DateSince2000] ASC,
[shape] ASC,
[EventDateTime] ASC,
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
...
Select Top 1 EventDatetime
From Table1 with(index(PK_test))
WhereDateSince2000 = @DateSince2000
And Shape = 123
And eventDateTime <= '01/01/2008'
Order by EventDateTime desc
Looking at your primary key, how selective are each of the 4 columns? It looks like this will be doing a range scan, not a seek, since the third column has a <= comparison and the fourth column, ID, is not in the query.
October 17, 2008 at 12:21 pm
Are records added to the table in EventDateTime order? I mean, every time a new record is added to the table, is the EventDateTime larger than the previously entered value? If so, you will probably get some speed by changing the order of the key values in your Clustered index to have EventDateTime first in Descending order (to match the ORDER BY in your query). You'll have to think about how this impacts your other queries though - particularly if any of them only have DateSince2000 and Shape in their where clause and not EventDateTime (they will no longer use the index).
How does the fragmentation on the index look? If items are entered in EventDateTime order, it is probably pretty fragmented.
Has the distribution of DateSince2000 changed recently? I.E. gone from relatively unique to many records with the same value? If this is in a stored procedure, you need to check and see if the distribution varies within DateSince2000 (even if it hasn't changed recently), since the server will cache the query plan from the first execution. If there is only 1 record with the value "5" but 50 million records with the value "7", and the procedure is called for "7" the 1st time, it will decide to do a scan, cache that, and still do a scan when you call it later with "5".
I personally don't like using index hints (e.g. WITH(Index(PK_test))), and this might be a good example of why. You are forcing the server to use that index when another one might be faster. Put two copies of this query in SSMS, one with and one without the hint and then get an estimated query plan and see which is cheaper. Besides fixing the clustered index, you might find that it works faster with a different index.
October 17, 2008 at 2:28 pm
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[DateSince2000] ASC,
[shape] ASC,
[EventDateTime] ASC,
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
...
Select Top 1 EventDatetime
From Table1 with(index(PK_test))
Where DateSince2000 = @DateSince2000
And Shape = 123
And eventDateTime <= '01/01/2008'
Order by EventDateTime desc
Actually, I think if you are going to modify the Clustered Primary Key (based solely on this one query), I'd simply change it so that EventDateTime was DESC instead of ASC. The other two fields (DateSince2000 and Shape) are in the proper place base on the query, as they are equality tests, and would keep the EventDateTime values grouped together.
😎
October 17, 2008 at 10:12 pm
arctica82 (10/17/2008)
But it is running very slow.Previously this Query was running very fast
Do you do any database maintenance? Like defragging or rebuilding indexes and statistics?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2008 at 7:33 am
If it's doing a seek operation, but it's still slow, I'm inclined to look at index fragementation or out of date statistics. You do have automatic update of statistics enabled, right? You may need to do a manual update or even an update with a full scan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 20, 2008 at 11:51 am
I have done DBCC ShowConfig on that table and Scan Density it about 77% and I do not have any maintenance plan for rebuilding the index on this table.Should I do rebuilding the index on this table.Because I can not do any changes in Clustered Index.
October 20, 2008 at 12:19 pm
Considering the size of the table, yeah, I'd suggest a defrag. I wouldn't necessarily do a rebuild at with an index this size and this level of fragementation, but it's close.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 20, 2008 at 5:22 pm
Here is the result of DBCC ShowConfig,By considering Scan Density,Logical Fragmentation,Page Density, Should I do DBCC IndexDefrag or DBCC ReIndex?Cause Of the size of Database(which is pretty big) ,should I do DBCC ReIndex?
Table: 'Table1' (1842105603); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 78514854
- Extents Scanned..............................: 9912016
- Extent Switches..............................: 12685429
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 77.37% [9814357:12685430]
- Logical Scan Fragmentation ..................: 8.02%
- Extent Scan Fragmentation ...................: 4.34%
- Avg. Bytes Free per Page.....................: 138.2
- Avg. Page Density (full).....................: 98.29%
October 20, 2008 at 6:55 pm
I'd seriously consider doing some DB and disk maintenance... I don't let the Scan Density get below 90% before I do a rebuild, especially if the clustered index isn't chronological in nature. I don't believe I'd do a DBCC DBReIndex except at the start of a long, quiet period for the DB. In the meantime, you can do DBCC IndexDefrag pretty much anytime but you won't get the same benefit because it only shuffles the data in place... it doesn't resolve things like interlaced indexes.
You also need to get something like DiskKeeper to defrag the underlying files. It's well worth the investment because you can schedule "real" copies to run right after your index maintenance and it runs just fine while the server is "up".
--Jeff Moden
Change is inevitable... Change for the better is not.
October 21, 2008 at 10:38 am
1) are you absolutely certain it is doing a CI SEEK and not a CI SCAN??
2) I am with the others on index maintenance. I would also recommend update statistics
3) take a look at the actual execution plan and look at the estimated and actual row count coming out of the table.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply