May 11, 2011 at 11:12 am
Ninja,
You also might want to consider how data is entered for the table. If there are VARCHAR type columns that are initially empty and then get filled in later this can also cause page splits due to the expansion on the column widths.
Todd Fifield
May 12, 2011 at 9:01 am
tfifield (5/11/2011)
Ninja,You also might want to consider how data is entered for the table. If there are VARCHAR type columns that are initially empty and then get filled in later this can also cause page splits due to the expansion on the column widths.
Todd Fifield
That is a GREAT point Todd that sooo many people miss.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 12, 2011 at 10:52 am
TheSQLGuru (5/12/2011)
tfifield (5/11/2011)
Ninja,You also might want to consider how data is entered for the table. If there are VARCHAR type columns that are initially empty and then get filled in later this can also cause page splits due to the expansion on the column widths.
Todd Fifield
That is a GREAT point Todd that sooo many people miss.
Kevin,
I'm glad I can contribute something here. I once had to tune customer table where the clustered index was an Identity column. No-one could figure out why it was always so fragmented. The application inserted a skeleton record and marketing people would later call and get the full information, which bloated the VARCHAR columns and caused page splits. Simply lowering the fill factor from 85 to around 45 fixed the problem. There was some unused space in the table but performance shot up like a rocket!
Todd Fifield
May 13, 2011 at 9:49 am
SwayneBell (5/10/2011)
Hi Ninja;By now, I'm sure that you've looked it up in BOL, but it's worth mentioning for others who read this thread; a constraint if you're implementing this function is that it "can only be used with DEFAULT constraints on table columns of type uniqueidentifier" (from BOL).
e.g. CREATE TABLE myTable (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID())
So, using it is contingent on our ability to alter the tables in a COTS application. We ran into a similar issue but were fortunate to be able to solve it by changing the clustered index in the way Gail suggested.
There is a cheat/workaround the default constraint IF for some reason you want the GUID generated in another session rather as a default value in the table. It goes a bit like this:
create procedure generateSequentialID
as
create table #tempGUID (myGUID uniqueidentifier default newsequentialid())
insert into #tempGUID default values
select myGUID from #tempGUID order by myGUID asc
go
create table tbl_GUID
(
rownum int identity(1,1),
myguid uniqueidentifier
)
insert into tbl_GUID(myguid)
exec generateSequentialID
select * from tbl_GUID
Yeah it's cheeky but sometimes you need to. 😀
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
May 13, 2011 at 9:55 am
:hehe:
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply