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.390DM2LocationNULL[DateTimeInserted][DID]48339384434302014-04-30 00:02:00.940NULL30685.116185009395.1800NULLNULL00[DM2].[dbo].[Location]375672386339383
2014-04-30 08:37:19.390DM2 Location[DID][SentDate][LID], [JID], [Latitude], [Longitude], [Ignition], [Speed], [Course], [Odometer], [PacketTypeID], [PacketStatusID], [DateTimeInserted], [DisplayIcon], [RESETFlag], [LastAckTime], [CellVoltage], [CellChargeVoltage], [GSMStrength], [GSMCID], [GSMLacID], [CoulombCounter], [SourceVoltage]48211032723130302014-04-30 08:18:30.490NULL3.1263729958005593.4500NULLNULL00[DM2].[dbo].[Location]3756723862110326
79DM2 Location_PKLocation12166456225LID NULL5681059650464500791017587392014-04-30 08:37:15.4402014-04-30 00:02:00.9402014-04-30 08:37:19.3402014-04-30 08:37:19.3471PRIMARY480700NULL2014-04-26 00:54:22.787NULLNULL
79DM2_dta_index_Location_100_375672386__K2D_K7_1_3_4_5_6_8_9_10_11_12_13_14_15_16Location172166456225JID, SpeedCourse, DateTimeInserted, DID, DisplayIcon, Ignition, LastAckTime, Latitude, LID, Longitude, Odometer, PacketStatusID, PacketTypeID, RESETFlag, SentDate17193336001017587392014-04-30 08:37:19.283NULLNULL2014-04-30 08:37:19.3471PRIMARY480000NULLNULLNULLNULL
79DM2 _dta_index_Location_5_103671417__K2_K1_K3_4_5_6_7_8_9_10_11_12_13_14Location22166456225JID, LID, DID, Course, DateTimeInserted, DisplayIcon, Ignition, Latitude, Longitude, Odometer, PacketStatusID, PacketTypeID, SentDate, Speed50709411001017587392014-04-30 08:37:19.340NULLNULL2014-04-30 08:37:19.3471PRIMARY480000NULLNULLNULLNULL
79DM2 _dta_index_Location_5_103671417__K3_K1Location32166456225DID, LID NULL29265001017587222014-04-30 08:36:54.340NULLNULL2014-04-30 08:37:19.3471PRIMARY480000NULLNULLNULLNULL
79DM2 _dta_index_Location_did_sent_status_locid_invalidsLocation152166456225DID, SentDate, PacketStatusID, LID NULL11839657001017587222014-04-30 08:37:19.307NULLNULL2014-04-30 08:37:19.3471PRIMARY480000NULLNULLNULLNULL
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.941267673874211032723530702014-04-30 09:48:22.680NULL3.1264128343777193.4500NULLNULL002110327211032621103269375672386[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.02415035339384434302014-04-30 00:02:00.940NULL30685.116185009395.1800NULLNULL003393843393833393839375672386NULL[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