Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


166 days to create index


166 days to create index

Author
Message
scott_lotus
scott_lotus
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 395
I have a table containing 2,163,568,622 rows hosted on SQL server Standard 2008.

Attempted to create a new index:

--------------------------------------------------------------------------------------------------------------------------------------------------
Using TEMPDB = ON this ran for 2 hours 8 mins and only read 27m rows.
run time 02:08:12.174   
rows 27,293,752
Web applications reported periodic timeout messages.
Cancelled.
--------------------------------------------------------------------------------------------------------------------------------------------------
Tried again with TEMPDB = OFF. Ran for 4 hours 34 mins and read 53m rows.
run time 04:35:09.826   
row 53,166,762   
Web application reported no timeout errors.
Cancelled.
--------------------------------------------------------------------------------------------------------------------------------------------------

TempDB disk space is 500GB , the table I am reading is larger but the resulting index size is smaller.
13m record reads per hours / 2,163,568,622 rows = 166 days to create this index !
5 other indexes on this table display about 70% fragmentation. Will try rebuilding these first. Previous rebuilds on existing indexes took approx 1 - 2 hours only.

Enterprise edition is not an option.

Index was suggested via tuning advisor and the dev team concur.


Thanks for ideas.
Scott


CREATE NONCLUSTERED INDEX [_dta_index_L_9_375672386__K3_K2_K10_1_6_7_9_11_12] ON [dbo].[L]
(
[DID] ASC,
[JID] ASC,
[Date] ASC
)
INCLUDE ( [LID],
[Ig],
[Sp],
[Od],
[TID],
[SID]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]


CREATE TABLE [dbo].[L](
   [LID] [int] IDENTITY(1,1) NOT NULL,
   [JID] [int] NULL,
   [DID] [int] NOT NULL,
   [Lat] [decimal](20, 10) NULL,
   [Long] [decimal](20, 10) NULL,
   [Ig] [bit] NULL,
   [Sp] [decimal](4, 1) NULL,
   [Co] [decimal](4, 1) NULL,
   [Od] [decimal](8, 2) NULL,
   [Date] [datetime] NULL,
   [TID] [int] NULL,
   [SID] [int] NULL,
   [In] [datetime] NULL,
   [Icon] [int] NULL,
   [Re] [bit] NULL,
   [Lst] [int] NULL,
   [Cel] [decimal](13, 8) NULL,
   [Ch] [decimal](13, 8) NULL,
   [St] [int] NULL,
   [Ce] [int] NULL,
   [La] [int] NULL,
   [Co] [int] NULL,
   [So] [decimal](13, 8) NULL,
CONSTRAINT [L_PK] PRIMARY KEY CLUSTERED
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17535 Visits: 32252
First, please, for clarity's sake, rename that index. Anytime I open a server and see _dta at the start of an index, I almost want to cry.

I would suggest you capture the wait statistics on the process as it's running in order to understand where your slow-down is coming from. I'd think it's either in memory or possibly waiting on I/O within tempdb, but understanding why and where is going to be a big help.

How much memory does the system have? Is tempdb and your database on the same disk? What's the clustered index on? I can see that you have one, but not the definition.

But, at the end of the day, you'll probably have to throw hardware at this problem. You're trying to sort a very large data set. You need memory and lots of fast disks to get it done.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
scott_lotus
scott_lotus
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 395
Thanks for the reply.

"First, please, for clarity's sake, rename that index. Anytime I open a server and see _dta at the start of an index, I almost want to cry."
Can do - sorry.

"I would suggest you capture the wait statistics on the process as it's running in order to understand where your slow-down is coming from."
I captured the following dueing index creation using SP_WHO3.

---------------------------------------------------------------------------------
dd hh ss ss mss: 00 02:08:12.174
sql_text: <?query -- insert [dbo].[L] select * from [dbo].[L] option (maxdop 1) --?>
reads: 27,293,752
writes: 662
wait info: NULL
blocking session: NULL
TempDB = on.
---------------------------------------------------------------------------------
dd hh ss ss mss: 00 02:11:41.440
sql_text: <?query -- insert [dbo].[L] select * from [dbo].[L] option (maxdop 1)--?>
reads: 27,905,369
writes: 675
wait info: (26544ms)PREEMPTIVE_OS_WRITEFILEGATHER
blocking session :NULL
TempDB = on.
---------------------------------------------------------------------------------
dd hh ss ss mss: 00 00:01:29.580
sql_test: <?query -- insert [dbo].[L] select * from [dbo].[L] option (maxdop 1) --?>
reads: 524,440
writes: 5
wait info: (33ms)PAGEIOLATCH_SHBigGrinM2:1(*)
blocking session: NULL
TempDB = off.
-----------------------------------------------------------------------------------
dd hh ss ss mss:00 04:35:09.826
sql_test: <?query -- insert [dbo].[L] select * from [dbo].[L] option (maxdop 1) --?>
reads: 53,166,762
writes: 4,084
wait info: NULL
blocking session: NULL
TempDB = off.
----------------------------------------------------------------------------------

"I'd think it's either in memory or possibly waiting on I/O within tempdb, but understanding why and where is going to be a big help."
I use Quest Spotlight to monitor blocking and locking performance.
The "WAIT STATISICS" tab is currently showing 75% MISC WAITS.
I will check it again during the index creation and publish the results.
Anything specific to look for ?

"How much memory does the system have? "
96GB


"Is tempdb and your database on the same disk?"
m: data 2.18tb (raid10)
n: logs 553gb (raid1)
t: tempdb 558gb (raid1)


"What's the clustered index on? I can see that you have one, but not the definition."
Clustered index on Primary Key LID (ASC).
NAME: LID
SORT: ASC
DATA TYPE: INT
Size: 4
Idenity: YES
Allow nulls: NO




ALTER TABLE [dbo].[L] ADD CONSTRAINT [L_PK] PRIMARY KEY CLUSTERED
(
   [LID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO




"But, at the end of the day, you'll probably have to throw hardware at this problem. You're trying to sort a very large data set. You need memory and lots of fast disks to get it done. "

15x600GB (SAS) disks in a NexSan SaysBoy. (1 hot spare).

Disk performance during daily operations is great.
SQL performance during daily operation is great.
- 18% CPU utilisation
- 64Gb of 96gb used
- cache hit rate is 82%.
- no deadlocking
- low number of blocked processes
- lock waits 2-5 seconds on Object Locks

Maintenance on a large single table:
- can rebuild in 1 - 2 hours but some writes will timeout.
- can delete 3000 rows per min before causing blocking problems (2.8m inserts per day in this table - another table has more but can truncate).
- cannot create new index tbl.L.

We are very heavy write and low read in general during normal operations.

Thanks for any advice.
Scott
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17535 Visits: 32252
scott_lotus (4/29/2014)
I use Quest Spotlight to monitor blocking and locking performance.
The "WAIT STATISICS" tab is currently showing 75% MISC WAITS.
I will check it again during the index creation and publish the results.
Anything specific to look for ?


Just what the actual waits are. You already have two that are interesting. PREEMPTIVE_OS_WRITEFILEGATHER is an indication of file growth occurring and your system is waiting on it. So, if you're growing files during this process, it'll certainly slow you down. You'd want to grow those files ahead of time so it's not waiting. You're also seeing PAGEIOLATCH_SH which is flat out an indication you're waiting on the disk for some other process to clear. But those waits seem to be associated with an INSERT query, not your index build. You need to see what the index build itself is waiting on.


ALTER TABLE [dbo].[L] ADD CONSTRAINT [L_PK] PRIMARY KEY CLUSTERED
(
   [LID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Tiny thing, unlikely to help overall, but since your PK is on LID, you don't need to have that in the INCLUDE statement. SQL Server will just ignore it anyway since the key for the PK is automatically stored with the nonclustered index.


15x600GB (SAS) disks in a NexSan SaysBoy. (1 hot spare).

Disk performance during daily operations is great.
SQL performance during daily operation is great.
- 18% CPU utilisation
- 64Gb of 96gb used
- cache hit rate is 82%.
- no deadlocking
- low number of blocked processes
- lock waits 2-5 seconds on Object Locks

Maintenance on a large single table:
- can rebuild in 1 - 2 hours but some writes will timeout.
- can delete 3000 rows per min before causing blocking problems (2.8m inserts per day in this table - another table has more but can truncate).
- cannot create new index tbl.L.

We are very heavy write and low read in general during normal operations.

Thanks for any advice.
Scott


You have a reasonably hefty system. I'd think it could handle what you're adding to it, but you need to identify where the bottleneck is first, then understand why it's a bottleneck. Early, and incomplete, indications are that it's disk related, but you need to get more accurate information about the specific blocking occurring in your index creation script.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3937 Visits: 6663
What indexes does SQL report are missing? What is the usage of existing indexes on that table?

There's a reasonable chance that the table should be clustered by [DID] rather than [LID], but obviously not enough info yet to decide that. And would need to know the insert pattern of [DID] column.

First, please run the queries below, after changing the db name and table name if needed, and post the results:


--!! chg to your db name
USE [<your_db_name>]

SET DEADLOCK_PRIORITY LOW --probably irrelevent, but just in case

DECLARE @list_missing_indexes bit
DECLARE @table_name_pattern sysname

SET @list_missing_indexes = 1 --1=list missing index(es); 0=don't.
--!! put your table name/name pattern here
SET @table_name_pattern = 'L'

PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

--SELECT create_date FROM sys.databases WITH (NOLOCK) WHERE name = N'tempdb'

IF @list_missing_indexes = 1
BEGIN
SELECT
GETDATE() AS capture_date,
DB_NAME(mid.database_id) AS Db_Name,
OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
mid.equality_columns, mid.inequality_columns, mid.included_columns,
ca1.sql_up_days AS days_in_use,
migs.*,
mid.statement, mid.object_id, mid.index_handle
FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)
CROSS APPLY (
SELECT DATEDIFF(DAY, create_date, GETDATE()) AS sql_up_days FROM sys.databases WHERE name = 'tempdb'
) AS ca1
LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON
mig.index_handle = mid.index_handle
LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ON
migs.group_handle = mig.index_group_handle
--order by
--DB_NAME, Table_Name, equality_columns
WHERE
1 = 1
AND mid.database_id = DB_ID()
AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern
ORDER BY
--avg_total_user_cost * (user_seeks + user_scans) DESC,
Db_Name, Table_Name, equality_columns, inequality_columns
END --IF

-- list index usage stats (seeks, scans, etc.)
SELECT
ius2.row_num, DB_NAME() AS db_name,
i.name AS index_name,
OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name,
i.index_id, --ius.user_seeks + ius.user_scans AS total_reads,
dps.row_count,
SUBSTRING(key_cols, 3, 8000) AS key_cols, SUBSTRING(nonkey_cols, 3, 8000) AS nonkey_cols,
ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates,
ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update,
fk.Reference_Count AS fk_ref_count,
FILEGROUP_NAME(i.data_space_id) AS filegroup_name,
ca1.sql_up_days AS days_in_use,
ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,
ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update
FROM sys.indexes i WITH (NOLOCK)
INNER JOIN sys.objects o WITH (NOLOCK) ON
o.object_id = i.object_id
CROSS APPLY (
SELECT DATEDIFF(DAY, create_date, GETDATE()) AS sql_up_days FROM sys.databases WHERE name = 'tempdb'
) AS ca1
OUTER APPLY (
SELECT
', ' + COL_NAME(object_id, ic.column_id)
FROM sys.index_columns ic
WHERE
ic.key_ordinal > 0 AND
ic.object_id = i.object_id AND
ic.index_id = i.index_id
ORDER BY
ic.key_ordinal
FOR XML PATH('')
) AS key_cols (key_cols)
OUTER APPLY (
SELECT
', ' + COL_NAME(object_id, ic.column_id)
FROM sys.index_columns ic
WHERE
ic.key_ordinal = 0 AND
ic.object_id = i.object_id AND
ic.index_id = i.index_id
ORDER BY
COL_NAME(object_id, ic.column_id)
FOR XML PATH('')
) AS nonkey_cols (nonkey_cols)
LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
dps.object_id = i.object_id AND
dps.index_id = i.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius WITH (NOLOCK) ON
ius.database_id = DB_ID() AND
ius.object_id = i.object_id AND
ius.index_id = i.index_id
LEFT OUTER JOIN (
SELECT
database_id, object_id, MAX(user_scans) AS user_scans,
ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans
FROM sys.dm_db_index_usage_stats WITH (NOLOCK)
WHERE
database_id = DB_ID()
--AND index_id > 0
GROUP BY
database_id, object_id
) AS ius2 ON
ius2.database_id = DB_ID() AND
ius2.object_id = i.object_id
LEFT OUTER JOIN (
SELECT
referenced_object_id, COUNT(*) AS Reference_Count
FROM sys.foreign_keys
WHERE
is_disabled = 0
GROUP BY
referenced_object_id
) AS fk ON
fk.referenced_object_id = i.object_id
WHERE
i.object_id > 100 AND
i.is_hypothetical = 0 AND
i.type IN (0, 1, 2) AND
o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND
(
o.name LIKE @table_name_pattern AND
o.name NOT LIKE 'dtprop%' AND
o.name NOT LIKE 'filestream[_]' AND
o.name NOT LIKE 'MSpeer%' AND
o.name NOT LIKE 'MSpub%' AND
--o.name NOT LIKE 'queue[_]%' AND
o.name NOT LIKE 'sys%'
)
--AND OBJECT_NAME(i.object_id, DB_ID()) IN ('tbl1', 'tbl2', 'tbl3')
ORDER BY
--row_count DESC,
--ius.user_scans DESC,
--ius2.row_num, --user_scans+user_seeks
-- list clustered index first, if any, then other index(es)
db_name, table_name, CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END, index_name

PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)




SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3254 Visits: 11771
Have you tried using the ONLINE = ON option in your index creation statement?
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3937 Visits: 6663
Michael Valentine Jones (4/29/2014)
Have you tried using the ONLINE = ON option in your index creation statement?


I believe OP said that Enterprise Edition was not an option.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44940 Visits: 39859
As Grant said, it looks like the system might be waiting on file growth. Make an educated guess on how big the Leaf Level of the index is going to be and grow your database at least that much.

Also, if you're in the FULL recovery model, your log file is going to grow a lot on this one. Make a backup just before you're ready to try another rebuild and the set the server to the BULK LOGGED recovery model. Your point-in-time backups will take the hit of not being able to recover to a point in time because, in the BULK LOGGED mode, CREATE INDEX is minimally logged.

Also, what kind of table is this? Is it an "audit" or "order detail" type of table by any chance? Do you have a "Date_Created" column on this table and, regardless of type, is it "temporal" and the old rows are mostly static?

As for the new index, everyone on your team realizes that NCIs are a duplication of data that includes all keys, includes, and the PK columns, right? You sure you REALLY want to make an index that turns out to be 9 columns wide (PK includes in the INCLUDEs)? Are you sure that key lookups are going to hurt that bad? Are you sure that your backups can handle the extra load and that you actually have the time to do the backups each night. Are you really, really sure that you want another index and set of stats to maintain on this relatively large table?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Andy sql
Andy sql
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 1301
Maybe not relevant, but.... the column [LID] has datatype [int] with IDENTITY(1,1).
The table contains 2,163,568,622 rows.
Max size of an integer is 2,147,483,647.
I guess it's possible that negative numbers have been used, but otherwise I see a problem!
scott_lotus
scott_lotus
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 395
Thank you again for the reply , very helpful.

Grant Fritchey (4/29/2014)

Just what the actual waits are. You already have two that are interesting. PREEMPTIVE_OS_WRITEFILEGATHER is an indication of file growth occurring and your system is waiting on it. So, if you're growing files during this process, it'll certainly slow you down. You'd want to grow those files ahead of time so it's not waiting. You're also seeing PAGEIOLATCH_SH which is flat out an indication you're waiting on the disk for some other process to clear. But those waits seem to be associated with an INSERT query, not your index build. You need to see what the index build itself is waiting on.


Ok will grow the MDF.




ScottPletcher (4/29/2014)
First, please run the queries below, after changing the db name and table name if needed, and post the results:


Results are as follows (unmasked names - apologies for masking before - hope this formatting is legible):


2014-04-30 08:37:19.390   DM2   Location   NULL   [DateTimeInserted]   [DID]   48   339384   43   43   0   2014-04-30 00:02:00.940   NULL   30685.1161850093   95.18   0   0   NULL   NULL   0   0   [DM2].[dbo].[Location]   375672386   339383

2014-04-30 08:37:19.390   DM2 Location   [DID]   [SentDate]   [LID], [JID], [Latitude], [Longitude], [Ignition], [Speed], [Course], [Odometer], [PacketTypeID], [PacketStatusID], [DateTimeInserted], [DisplayIcon], [RESETFlag], [LastAckTime], [CellVoltage], [CellChargeVoltage], [GSMStrength], [GSMCID], [GSMLacID], [CoulombCounter], [SourceVoltage]   48   2110327   231   303   0   2014-04-30 08:18:30.490   NULL   3.12637299580055   93.45   0   0   NULL   NULL   0   0   [DM2].[dbo].[Location]   375672386   2110326

79   DM2 Location_PK   Location   1   2166456225   LID NULL   56810596   50   46450079   101758739   2014-04-30 08:37:15.440   2014-04-30 00:02:00.940   2014-04-30 08:37:19.340   2014-04-30 08:37:19.347   1   PRIMARY   48   0   7   0   0   NULL   2014-04-26 00:54:22.787   NULL   NULL

79   DM2   _dta_index_Location_100_375672386__K2D_K7_1_3_4_5_6_8_9_10_11_12_13_14_15_16   Location   17   2166456225   JID, Speed   Course, DateTimeInserted, DID, DisplayIcon, Ignition, LastAckTime, Latitude, LID, Longitude, Odometer, PacketStatusID, PacketTypeID, RESETFlag, SentDate   17193336   0   0   101758739   2014-04-30 08:37:19.283   NULL   NULL   2014-04-30 08:37:19.347   1   PRIMARY   48   0   0   0   0   NULL   NULL   NULL   NULL

79   DM2 _dta_index_Location_5_103671417__K2_K1_K3_4_5_6_7_8_9_10_11_12_13_14   Location   2   2166456225   JID, LID, DID, Course, DateTimeInserted, DisplayIcon, Ignition, Latitude, Longitude, Odometer, PacketStatusID, PacketTypeID, SentDate, Speed   50709411   0   0   101758739   2014-04-30 08:37:19.340   NULL   NULL   2014-04-30 08:37:19.347   1   PRIMARY   48   0   0   0   0   NULL   NULL   NULL   NULL

79   DM2 _dta_index_Location_5_103671417__K3_K1   Location   3   2166456225   DID, LID NULL   29265   0   0   101758722   2014-04-30 08:36:54.340   NULL   NULL   2014-04-30 08:37:19.347   1   PRIMARY   48   0   0   0   0   NULL   NULL   NULL   NULL

79   DM2 _dta_index_Location_did_sent_status_locid_invalids   Location   15   2166456225   DID, SentDate, PacketStatusID, LID NULL   11839657   0   0   101758722   2014-04-30 08:37:19.307   NULL   NULL   2014-04-30 08:37:19.347   1   PRIMARY   48   0   0   0   0   NULL   NULL   NULL   NULL






I used this statement found in this forum which reports same results:


select * from
(select user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage,
migs.* from sys.dm_db_missing_index_group_stats migs) as migs_adv
inner join sys.dm_db_missing_index_groups as mig on migs_adv.group_handle=
mig.index_group_handle
inner join sys.dm_db_missing_index_details as mid on mig.index_handle=mid.index_handle
where statement like '%location%'
order by migs_adv.index_advantage




896.941267673874   2110327   235   307   0   2014-04-30 09:48:22.680   NULL   3.12641283437771   93.45   0   0   NULL   NULL   0   0   2110327   2110326   2110326   9   375672386   [DID]   [SentDate]   [LID], [JID], [Latitude], [Longitude], [Ignition], [Speed], [Course], [Odometer], [PacketTypeID], [PacketStatusID], [DateTimeInserted], [DisplayIcon], [RESETFlag], [LastAckTime], [CellVoltage], [CellChargeVoltage], [GSMStrength], [GSMCID], [GSMLacID], [CoulombCounter], [SourceVoltage]   [DM2].[dbo].[Location]

1255862.02415035   339384   43   43   0   2014-04-30 00:02:00.940   NULL   30685.1161850093   95.18   0   0   NULL   NULL   0   0   339384   339383   339383   9   375672386   NULL   [DateTimeInserted]   [DID]   [DM2].[dbo].[Location]




Jeff Moden (4/29/2014)
As Grant said, it looks like the system might be waiting on file growth. Make an educated guess on how big the Leaf Level of the index is going to be and grow your database at least that much.


Good idea.




USE DM2
GO
ALTER DATABASE DM2
MODIFY FILE
(NAME = DM2,
SIZE = 1375GB)
GO
-- current size 1350004736KB




Jeff Moden (4/29/2014)
Also, if you're in the FULL recovery model ...

In SIMPLE MODE.


Jeff Moden (4/29/2014)
Also, what kind of table is this? Is it an "audit" or "order detail" type of table by any chance? Do you have a "Date_Created" column on this table and, regardless of type, is it "temporal" and the old rows are mostly static?


Logs location data , approx 2.5m rows per day.
6 hours a day we execute the following in a bid to reduce the table size.



delete top (3000) from location where -- DID is unallocated



This has been added fairly recently, causing index fragmentation.


Jeff Moden (4/29/2014)
As for the new index, everyone on your team realizes that NCIs are a duplication of data that includes all keys, includes, and the PK columns, right? You sure you REALLY want to make an index that turns out to be 9 columns wide (PK includes in the INCLUDEs)? Are you sure that key lookups are going to hurt that bad? Are you sure that your backups can handle the extra load and that you actually have the time to do the backups each night. Are you really, really sure that you want another index and set of stats to maintain on this relatively large table?


All good questions. Yes we understand the space considerations when adding NCIs. We have an overnight service gathering reporting data. This service timed out on a specific report (location table) after 20 mins if concurrent instances of this reports are running (WCF service appears to allow 5 max) but execute successfully one at a time. The service cursors which i believe is inefficient.

OPTION (MAXDOP 1) has been set to no effect. NOLOCK (which i understand is not best practice) has been tested to no effect (not blocking issues). Query appears to SCAN. Tunning Advisor recommended the index listed above. Independently the dev team came to the same conclusion.

Before attempting to add the index its clearer as a result of this post we should investigate:
- growing the mdf manually.
- rebuild existing indexes.
- amend the report service to allow administrative control over the number of concurrent executions and set to 1 for testing.
- investigate the removal of the cursor / making the query more efficient.
- add a statistics job to table location (disabled some time ago) , ensure this is run often
- analyse the query in tuning advisor again

If the index still needs to be added then:
- grow the MDF to the appropriate size.
- min number of columns


Thank you for taking the time to respond.
Scott
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search