Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
NguyenL71
Posting Yak Master
228 Posts |
 Posted - 2014-07-08 : 14:01:59
|
I have some performance issue on one of the table and I would recommend the newly create indexes and
drop duplicates indexes below. Is it safe to remove --4, 5, 6, 9, 10. I was weight between INSERT and
SELECT but turn out INSERT was hit more than SELECT. I like to get your input on newly create indexes below.
-- SQL2012
Thank you in advance.
-- Recommending
ALTER TABLE dbo.tblEventTracking
ADD CONSTRAINT XPKtblEventTracking PRIMARY KEY CLUSTERED ([Object_ID]);
CREATE UNIQUE INDEX unci_tblEventTracking ON dbo.tblEventTracking ( eventDate, eventID, VLU_ID );
CREATE NONCLUSTERED INDEX IE1tblEventTracking ON dbo.tblEventTracking ( VEHICLE_ID, Account_ID );
CREATE NONCLUSTERED INDEX IE2tblEventTracking ON dbo.tblEventTracking ( VEHICLE_NAME, DEG_LONGITUDE, DEG_LATITUDE );
CREATE NONCLUSTERED INDEX IE3tblEventTracking ON dbo.tblEventTracking (SQLSystemDate );
GO
-- Problems.
-- Current indexes on a table EventTracking
---------------------------------------------------------- ------------------------------------------------------- ------------------------------
--1 AK1_Tracking_eventID_VluID_eventDate nonclustered, unique, unique key located on PRIMARY eventID, VLU_ID, eventDate
--2 idx_SQLSystemDate nonclustered located on PRIMARY SQLSystemDate(-)
--3 idx_tblEventTracking_ETLCovering nonclustered located on PRIMARY VEHICLE_ID, eventDate, Account_ID
--4 D idx_Tracking_EventDate nonclustered located on PRIMARY eventDate
--5 D idx_Tracking_EventID nonclustered located on PRIMARY eventID
--6 D idx_Tracking_Vehicle_ID nonclustered located on PRIMARY VEHICLE_ID
--7 idx_Tracking_Vehicle_Name nonclustered located on PRIMARY VEHICLE_NAME, eventDate, DEG_LONGITUDE, DEG_LATITUDE
--8 PK__tblEventTracking__7211DF33 clustered, unique, primary key located on PRIMARY Object_ID
--9 D XIE_eventDate_VehicleID_tblEventTracking nonclustered located on PRIMARY eventDate, VEHICLE_ID
--10 D XIE7tblEventTracking nonclustered located on PRIMARY eventID, VLU_ID |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-08 : 14:06:00
|
Please show us the DDL for the current indexes. I can't read your output.
Ideally we need to see the table definition, poor performing queries, execution plans and stats io output.
Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/ |
 |
|
|
NguyenL71
Posting Yak Master
228 Posts |
Posted - 2014-07-08 : 14:09:39
|
Sorry, here is the output from sp_helpindex
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------
AK1_Tracking_eventID_VluID_eventDate nonclustered, unique, unique key located on PRIMARY eventID, VLU_ID, eventDate
idx_SQLSystemDate nonclustered located on PRIMARY SQLSystemDate(-)
idx_tblEventTracking_ETLCovering nonclustered located on PRIMARY VEHICLE_ID, eventDate, Account_ID
idx_Tracking_EventDate nonclustered located on PRIMARY eventDate
idx_Tracking_EventID nonclustered located on PRIMARY eventID
idx_Tracking_Vehicle_ID nonclustered located on PRIMARY VEHICLE_ID
idx_Tracking_Vehicle_Name nonclustered located on PRIMARY VEHICLE_NAME, eventDate, DEG_LONGITUDE, DEG_LATITUDE
PK__tblEventTracking__7211DF33 clustered, unique, primary key located on PRIMARY Object_ID
XIE_eventDate_VehicleID_tblEventTracking nonclustered located on PRIMARY eventDate, VEHICLE_ID
XIE7tblEventTracking nonclustered located on PRIMARY eventID, VLU_ID
quote:
Originally posted by tkizer
Please show us the DDL for the current indexes. I can't read your output.
Ideally we need to see the table definition, poor performing queries, execution plans and stats io output.
Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-08 : 14:27:42
|
The output of sp_helpindex isn't helpful to me unfortunately. I need the DDL.
Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/ |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-07-08 : 15:07:46
|
| Other than foreign keys (which you haven't shown); we would need to know the types of queries you are running against that table. Those queries will determine the index(es) that would be helpful. In the absence of those queries, we can't begin to tell you want indexes you will need. |
|
|
|
|
|
|
|
|
Indexes???