Indexes??? - SQL Server Forums

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.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Indexes???

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources