index issue

Question

index issue

rpatil22

Ten Centuries

Points: 1371
More actions
April 9, 2010 at 7:09 am

#233651

Are the followings indexes are correct for rows 110k?
Table contain following Columns
employeeId - uniqueindentifier ( primary key )
machine - nvarchar(100)
firstname - nvarchar(100)
lastname - nvarchar(100)
startdate - datetime
finishdate - datetime
location - int
dept - nvarchar(100)
active - bit
Indexes
PK-Unique NonClustered - employeeId
IX1_clustered - machine
IX2_non clustered - startdate

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

rpatil22 (4/9/2010)
Are the followings indexes are correct for rows 110k?
Table contain following Columns
employeeId - uniqueindentifier ( primary key )
machine - nvarchar(100)
firstname - nvarchar(100)
lastname - nvarchar(100)
startdate - datetime
finishdate - datetime
location - int
dept - nvarchar(100)
active - bit
Indexes
PK-Unique NonClustered - employeeId
IX1_clustered - machine
IX2_non clustered - startdate

Your question was not clear? Please give me more info.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

rpatil22 (4/9/2010)
Are the followings indexes are correct for rows 110k?
Table contain following Columns
employeeId - uniqueindentifier ( primary key )
machine - nvarchar(100)
firstname - nvarchar(100)
lastname - nvarchar(100)
startdate - datetime
finishdate - datetime
location - int
dept - nvarchar(100)
active - bit
Indexes
PK-Unique NonClustered - employeeId
IX1_clustered - machine
IX2_non clustered - startdate

Nobody can answer that question without knowing what queries are hitting that table. What indexes are used or unused at the moment... how much dml versus reads, etc.

What problem are you trying to fix with this question?

joeroshan SSChampion Points: 10377 More actions · Answer 3

Use dm_db_index_usage_stats view to get some idea regarding this. Note: this view is flushed on each restart

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

rpatil22 Ten Centuries Points: 1371 More actions · Answer 4

Queries

Update

SET firstname = 'XYZ' ,

lastname = 'ABC' ,

finishdate = GETDATE() ,

location = 10 ,

dept = ' PRODUCTION',

active =1

WHERE machine = 'ABCSSSS'

AND startdate < GETDATE()

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 5

rpatil22 (4/9/2010)
Queries
Update
SET firstname = 'XYZ' ,
lastname = 'ABC' ,
finishdate = GETDATE() ,
location = 10 ,
dept = ' PRODUCTION',
active =1
WHERE machine = 'ABCSSSS'
AND startdate < GETDATE()

How long the update is running ?

what's the time duration?

Did you see the execution plan ?

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

rpatil22 (4/9/2010)
WHERE machine = 'ABCSSSS'

For this query it will show Clustered index update in Exce. plan but performance wise we cant say anything.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)