August 3, 2014 at 6:00 am
Hi All,
I have just created 1 index in a large database ( 172 million rows ) in the testing server ...It took around 8 hours to complete which is very long time ...
Now I want to implement it in the production because it can speed up the query significantly.
the problem is I am worried that it will take a long time too in the production ( the database in the production has less rows which is 33 million rows ) but however it is still a big database.
I am using SQL standard so it would be impossible to Create index ONLINE..
Any solution please?
THanks so much !!
Cheers
August 3, 2014 at 11:40 am
8 hours seems extraordinarily extreme for 172M rows. It seems like there must be more to the story.
Are you changing the clustered index on the table? Does the table have other indexes?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 3, 2014 at 1:15 pm
Did you check the status of the index creation while in progress? Did you check for blocking?
Does the dev server have just a small amount of RAM or little cpu power?
Those things can affect the creation of the index.
Besides the 172million records, we know so little about your table. Things like total size of the table, data types, what the index was that you created. Those things can also impact the creation time.
Run the script from the following link and post the results for the table in question. Also post things like the table definition and index definition please.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 3, 2014 at 7:28 pm
ScottPletcher (8/3/2014)
8 hours seems extraordinarily extreme for 172M rows. It seems like there must be more to the story.Are you changing the clustered index on the table? Does the table have other indexes?
hmm I never change anything before ..
yes the table has 5 other indexes...
atm the page count of that index is 788.162 and other indexes also have high page count ...
is that because of the large table or because of high page split ?
August 3, 2014 at 7:35 pm
SQLRNNR (8/3/2014)
Did you check the status of the index creation while in progress? Did you check for blocking?Does the dev server have just a small amount of RAM or little cpu power?
Those things can affect the creation of the index.
Besides the 172million records, we know so little about your table. Things like total size of the table, data types, what the index was that you created. Those things can also impact the creation time.
Run the script from the following link and post the results for the table in question. Also post things like the table definition and index definition please.
the dev server has only 4 CPU with 8 GB of RAM
but the production has 24 CPU with 32 GB of RAM
How do i check the blocking when I run the index creation process?
Data for the table :
Index space : 32.067.563 MB
Row count : 178.432.356 MB
Data space : 10.905.453 MB
The index :
CREATE NONCLUSTERED INDEX [IX_D] ON [dbo].[D]
(
[TemplateId] ASC
)
INCLUDE ( [Value],
[Datestamp])
FYI :
Template id (int)
Value (varchar(2000)
datestamp (datetime )
Unfortunately when I run the script that u gave me ..I got many errors but i hope the table profile that I give you can give you more information
looking forward your response
thankss
August 3, 2014 at 7:53 pm
WhiteLotus (8/3/2014)
SQLRNNR (8/3/2014)
Did you check the status of the index creation while in progress? Did you check for blocking?Does the dev server have just a small amount of RAM or little cpu power?
Those things can affect the creation of the index.
Besides the 172million records, we know so little about your table. Things like total size of the table, data types, what the index was that you created. Those things can also impact the creation time.
Run the script from the following link and post the results for the table in question. Also post things like the table definition and index definition please.
the dev server has only 4 CPU with 8 GB of RAM
but the production has 24 CPU with 32 GB of RAM
How do i check the blocking when I run the index creation process?
Data for the table :
Index space : 32.067.563 MB
Row count : 178.432.356 MB
Data space : 10.905.453 MB
The index :
CREATE NONCLUSTERED INDEX [IX_D] ON [dbo].[D]
(
[TemplateId] ASC
)
INCLUDE ( [Value],
[Datestamp])
FYI :
Template id (int)
Value (varchar(2000)
datestamp (datetime )
Unfortunately when I run the script that u gave me ..I got many errors but i hope the table profile that I give you can give you more information
looking forward your response
thankss
It looks like you have a lot of off row data involved in that index creation. Looks to be a large index on a machine with little resources.
As per the other script, it looks like the update to the code engine converted > to gt and < to lt in the code. That would be the cause for the errors you are seeing. I am looking for a fix for that code prettifier to fix it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 3, 2014 at 8:10 pm
WhiteLotus (8/3/2014)
SQLRNNR (8/3/2014)
Did you check the status of the index creation while in progress? Did you check for blocking?Does the dev server have just a small amount of RAM or little cpu power?
Those things can affect the creation of the index.
Besides the 172million records, we know so little about your table. Things like total size of the table, data types, what the index was that you created. Those things can also impact the creation time.
Run the script from the following link and post the results for the table in question. Also post things like the table definition and index definition please.
Unfortunately when I run the script that u gave me ..I got many errors but i hope the table profile that I give you can give you more information
Script fixed and download option added.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 3, 2014 at 9:35 pm
SQLRNNR (8/3/2014)
WhiteLotus (8/3/2014)
SQLRNNR (8/3/2014)
Did you check the status of the index creation while in progress? Did you check for blocking?Does the dev server have just a small amount of RAM or little cpu power?
Those things can affect the creation of the index.
Besides the 172million records, we know so little about your table. Things like total size of the table, data types, what the index was that you created. Those things can also impact the creation time.
Run the script from the following link and post the results for the table in question. Also post things like the table definition and index definition please.
Unfortunately when I run the script that u gave me ..I got many errors but i hope the table profile that I give you can give you more information
Script fixed and download option added.
Oh thanks ..Thats kind of you .
It works now and here is the profile of that table :
ObjNameSchemaNameIsMsShippedNumRows ReservedPageMBDataSizeMBIndexSizeMBUnusedSpacedbsizeMBLogSizeMBTotalTableFreeSpaceDataFileFreeSpacePercentofDBPhysFilePercentofDBUsedSpace
Table AdboNO17845518410909.710937510907.10156232079.75000010.187500043430.001985.0011.5468750422.929687598.97960099.005900
Btw TotalTableFreeSpace is in GB ?
August 3, 2014 at 10:01 pm
Ok, that tells a tale of a much smaller table than what you were showing before.
I bet there was blocking, or memory thrashing, or slow io, or any number of issues that could crop up due to resource use while trying to create that index.
You can use sp_who2 as a quick means to identify blocking on a query such as creating an index.
The total free space (from that query) is in MB.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 3, 2014 at 10:42 pm
SQLRNNR (8/3/2014)
Ok, that tells a tale of a much smaller table than what you were showing before.I bet there was blocking, or memory thrashing, or slow io, or any number of issues that could crop up due to resource use while trying to create that index.
You can use sp_who2 as a quick means to identify blocking on a query such as creating an index.
The total free space (from that query) is in MB.
I am running the creation of index and running SPwho2 as well
I notice there is 1 record has a profile as below :
SPIDStatusLoginHostNameBlkByDBNameCommandCPUTimeDiskIOLastBatchProgramNameSPIDREQUESTID
172 SUSPENDED dgcLoginTST1-VW156 DB1 INSERT 152904108/04 14:31:57.Net SqlClient Data Provider 172 0
The database that I am creating the index is DB1 ..so it seems it is blocked by INSERT process ??
How we overcome that ?
thanks !
August 4, 2014 at 7:32 am
Tune the insert, get the insert to finish.
Wait until an outage window when nobody will be connecting to the database and do your index then
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 4, 2014 at 7:51 am
when creating the new index on a large table that already has some indexes, the new index could take a long time when tehre is alredy index with indexed columns in it that will be in a new index. in such case SQL Server use that index bookmarking to the clustered index taking from there the columns needed for the new, which do not exists in the index.
for example, table T is declared as T (ID int, A char, B char, C Char)
it has say, a clustered index CLIX_ID on ID, nonclustered IX_A on A
if You are creating the new index as INDEX IX_A_inc_B on A include(B), it scans IX_A, taking A from it, and bookmarks CLIX_ID to take B. The good way to create IX_A_inc_B faster is to drop IX_A first, then create IX_A_inc_B and then recreate IX_A
in this way first it would scan CLIX_ID when creating IX_A_inc_B and then IX_A_inc_B to create IX_A
in my tests i've got the execution time reduced from 3 hours to 10 minutes
August 5, 2014 at 12:19 am
let me clarify a bit what I was meaning
create table t(id int PRIMARY KEY CLUSTERED, a varchar(256) null, b varchar(256) null, c varchar(256) null)
insert t select 1,'a','b','c'
create index IX_a on t(a) (execution plan in attachment plan1.bmp)
create index IX_a_inc_b on t(a) include(b) (execution plan in attachment plan2.bmp) - index scan + key lookup is nightmare on big tables
to create IX_a_inc_b fast it is better to drop IX_a first
August 5, 2014 at 9:23 pm
Shurgenz (8/4/2014)
when creating the new index on a large table that already has some indexes, the new index could take a long time when tehre is alredy index with indexed columns in it that will be in a new index. in such case SQL Server use that index bookmarking to the clustered index taking from there the columns needed for the new, which do not exists in the index.for example, table T is declared as T (ID int, A char, B char, C Char)
it has say, a clustered index CLIX_ID on ID, nonclustered IX_A on A
if You are creating the new index as INDEX IX_A_inc_B on A include(B), it scans IX_A, taking A from it, and bookmarks CLIX_ID to take B. The good way to create IX_A_inc_B faster is to drop IX_A first, then create IX_A_inc_B and then recreate IX_A
in this way first it would scan CLIX_ID when creating IX_A_inc_B and then IX_A_inc_B to create IX_A
in my tests i've got the execution time reduced from 3 hours to 10 minutes
wowww what a great idea!!!
I tried mine ...dropping all the indexes first ... the duration for creating the index from 8 hours become 20 minutes
Thankssss 🙂
August 5, 2014 at 10:29 pm
actually, order the indexes creation script in a way to create the most covering indexes first. That will drammatically reduce the execution time. Unfortunatelly, MS did not allow the query hints for CREATE INDEX statement. From version to version the statement has the worst execution plan in some cases, one of which You've faced with.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply