2 question about index - 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 2005 Forums

SQL Server Administration (2005)

2 question about index

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-09-05 : 12:28:20

1.if i create and drop index how is it impact of my preforamce?
(i need to check the best index to the table,and i do not have
development server)
2.indexes need pk on table?(is it make better of performace?)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-05 : 14:33:23

1. Are you using Enterprise Edition? If so, you can use the ONLINE=ON option.
2. All tables should have a primary key, except perhaps staging tables for imports. SQL Server automatically gives us an index when the PK is added.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-05 : 14:34:09

By the way, install developer edition on your client machine and test there. Do NOT do this testing on production. You could seriously do some damage if you do it on production. And if you are going to do it in production, do it during a maintenance window.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-09-05 : 15:22:13

tkizer

i use standard edition,so what i need to do?

our system is BI (so wht i have PK)

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-09-05 : 16:18:35

When you create the index, the system will be affected due to the increased IO needed to read the data and build the index. The build of the index will not block any users or other processes.

When you drop the index, you could block users - or the drop will be blocked because the index has to be locked before it can be dropped. This could cause all kinds of issues with your system - and really should be performed during a maintenance window.

Take Tara's advice - get a copy of the developers edition ($50 US, or less), install on your workstation and test it there first.

Jeff

P.S. Enterprise Edition would not make any difference here, since you are not looking at rebuilding indexes online/offline.

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-09-06 : 02:16:06

create and drop index impact on fragmentation?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-06 : 03:48:19

Same as rebuilding the index

--
Gail Shaw
SQL Server MVP

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-09-06 : 05:22:57

how to solve fragmentation?
how i know if i have fragmentation?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-06 : 06:29:07

http://www.google.com/search?q=index+fragmentation+%22sql+server%22

--
Gail Shaw
SQL Server MVP

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-09-07 : 14:14:23

quote:

2.indexes need pk on table?(is it make better of performace?)

A PK is also an index. Indexes (both PKs and other indexes) slow down a little Inserts, Updates and Deletes. But speed up Selects a lot.

Indexes do not require a PK on the table. FKs (Foreign Keys) require a PK on the table.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-09-07 : 15:24:56

quote:

how i know if i have fragmentation?

SELECT top 500
  (select OBJECT_NAME(i.object_id) from sys.indexes i 
   where i.object_id = phystat.object_id AND i.index_id = phystat.index_id ) Table_Name,
  (select i.name from sys.indexes i 
   where i.object_id = phystat.object_id AND i.index_id = phystat.index_id ) Index_Name,
  phystat.avg_fragmentation_in_percent, * 
FROM
  sys.dm_db_index_physical_stats(14, NULL, NULL, NULL, Default) phystat
where 
  Page_count > 1000 
order by
  fragment_count desc

The 14 in this case is the Database ID. You should also run the Query in this Database.
I have 'where Page_Count > 1000' to ignore the smaller tables.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-09-07 : 15:28:09

quote:
Originally posted by inbs

how to solve fragmentation?

Re-build it likely better than drop/create.

http://www.mssqltips.com/tip.asp?tip=1791

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources