November 4, 2011 at 5:42 pm
since you have a choice to make your clustored index desc or asc, and is usually (at least in my case) the identity column in some columns, in others its not.
im tired of allways having to add order by recordid desc
plus i would think when reporting, and joining with records with dates that are recent, the highest recordid's are usually only involved in the join.
my question is would it be faster to make the clustored index(identity or not) desc or would it hurt the table since it has to insert the record at the beginning of the tables "spot" on the disk
?
November 7, 2011 at 5:49 am
Honestly, I doubt there's a single right answer to this. I don't know of anyone who, as a matter of routine, sets their identity values to be DESC. But if you are constantly and regularly putting DESC into queries in order to get the order one way (although, if you want an absolute order, you'll still need to use an order by, but you'll save three key strokes since ASC is assumed), you might want to test your system to see if it makes a difference.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 7, 2011 at 5:54 am
xgcmcbain (11/4/2011)
im tired of allways having to add order by recordid desc
Changing the clustered index to be a DESC index instead of the default of ASC isn't going to remove the need to write ORDER BY recordid DESC in your queries. No order by = no guarantee of order.
Faster? Maybe, but probably not. SQL's quite capable of doing a backward scan if it needs the index rows the other way around. To be honest, the only time I've sorted an index the other way around is when I'm trying to support things like
ORDER BY Col1 ASC, Col2 DESC
It's worth testing out, but I doubt you'll see radical performance improvements.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2011 at 8:59 am
GilaMonster (11/7/2011)
xgcmcbain (11/4/2011)
im tired of allways having to add order by recordid descChanging the clustered index to be a DESC index instead of the default of ASC isn't going to remove the need to write ORDER BY recordid DESC in your queries. No order by = no guarantee of order.
Faster? Maybe, but probably not. SQL's quite capable of doing a backward scan if it needs the index rows the other way around. To be honest, the only time I've sorted an index the other way around is when I'm trying to support things like
ORDER BY Col1 ASC, Col2 DESC
It's worth testing out, but I doubt you'll see radical performance improvements.
1) Very good point that many don't realize Gail - that there is NO order unless you explicitly specify in on the outer-most SELECT
2) There is a VERY IMPORTANT reason to make sure you get your clustered indexes in the 'optimal' order for your queries: if the optimizer has to do a backward-order scan THAT VOIDS THE USE OF PARALLELISM!! Thus your queries will be forced to a serial plan, which can have obvious DRAMATIC effects on performance.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 14, 2011 at 8:42 am
Not sure if I've understood your scenario, so here goes...
Have you got an ascending ID (e.g. an identity field or date column) and you are adding records in ascending order? But your queries routinely have ORDER BY recordID DESC to get just the last few?
Sorting your clustered index DESC may help select statements (to paraphrase many people on this site, test, test, test...) but it will cause serious fragmentation on this clustered index.
If your clustered index is in ASCending order, as you add rows SQL Server will happily add them on the last page until the page is full, then start a new page. If you are lucky it'll be nearby on the disk.
If your clustered index is in DESCending order, as you add rows SQL Server will happily add them on the first page until the page is full. It will then split the page, moving half the data onto a new page somewhere at the end and leave half on the first page. Then fill up the first page again, move half the data to the end and carry on. Without maintenance this could end up with the pages all about half full and pretty well completely fragmented.
Here's some code to demonstrate what I mean...I end up with 67 pages of data in 67 fragments
--Create two test tables
CREATE TABLE test_ASC (recordID INT IDENTITY(1,1), filler CHAR(500), PRIMARY KEY (recordID ASC))
CREATE TABLE test_DESC (recordID INT IDENTITY(1,1), filler CHAR(500), PRIMARY KEY (recordID DESC))
--Fill with data a row at a time
DECLARE @i INT
SET @i=0
WHILE @i<1000
BEGIN
INSERT test_ASC (filler) VALUES ('')
SET @i=@i+1
END
SET @i=0
WHILE @i<1000
BEGIN
INSERT test_DESC (filler) VALUES ('')
SET @i=@i+1
END
--Then have a look at the index info on the tables
SELECT c.name AS tableName, b.name AS indexName, a.avg_fragmentation_in_percent, a.fragment_count, a.avg_fragment_size_in_pages, a.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),NULL, NULL, NULL) AS a
INNER JOIN sys.indexes AS b ON a.object_id = b.object_id
AND a.index_id = b.index_id
INNER JOIN sys.objects AS c ON a.object_id = c.object_id
WHERE c.name IN ('test_ASC','test_DESC')
--Clean up
DROP TABLE test_ASC
DROP TABLE test_DESC
November 15, 2011 at 12:46 pm
paul.jones (11/14/2011)
Not sure if I've understood your scenario, so here goes...Have you got an ascending ID (e.g. an identity field or date column) and you are adding records in ascending order? But your queries routinely have ORDER BY recordID DESC to get just the last few?
Sorting your clustered index DESC may help select statements (to paraphrase many people on this site, test, test, test...) but it will cause serious fragmentation on this clustered index.
If your clustered index is in ASCending order, as you add rows SQL Server will happily add them on the last page until the page is full, then start a new page. If you are lucky it'll be nearby on the disk.
If your clustered index is in DESCending order, as you add rows SQL Server will happily add them on the first page until the page is full. It will then split the page, moving half the data onto a new page somewhere at the end and leave half on the first page. Then fill up the first page again, move half the data to the end and carry on. Without maintenance this could end up with the pages all about half full and pretty well completely fragmented.
Paul,
Yes, that would happen if you created the IDENTITY as INT (1, 1). However, if you wanted a descending order you would most likely create it as IDENTITY(1000000, -1) or something like that.
Changing your example of the descending table to:
CREATE TABLE test_DESC
(recordID INT IDENTITY(10000,-1), filler CHAR(500), PRIMARY KEY (recordID DESC))
and then running your code produces no fragmentation.
I think the point here is does it make sense to have everything descending in the first place.
Todd Fifield
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply