Database indexing - 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 2008 Forums

SQL Server Administration (2008)

Database indexing

Author

Topic

jh_sql
Starting Member

24 Posts

Posted - 2011-10-13 : 05:13:54

Hello,

Im wery new to database indexing and could use some help improving my database performance.

We upgraded database from sqlserver 2005 to sqlserver 2008. And some queries started to become much slower than they were in old db.

I have investigated the issue and have to ask that could this be from index defragmentation, from sqlserver management studion in primary key index properties it shows:

Page fullness: 76,31 %
Total fragmentation : 30,98 %

Average row size: 344
Depth: 3
Forwarded records: 0
Ghost rows: 0
Index type: CLUSTERED INDEX
Leaf-level rows: 413700
Maximum row size: 1869
Minimum row size: 281
Pages: 23260
Partition ID: 1
Version ghost rows: 0

Im also wery interested adding some additional indexing to this table, can anyone give link or maybe recommend some book to get more familiar with the subject.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-13 : 06:42:27

You should always rebuild all indexes and update statistics after moving or upgrading a database. Run this query to generate an index rebuild and update statistics script. Be sure to run it in off-peak hours though:

SELECT 'ALTER INDEX ALL ON ' + table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'

SELECT 'UPDATE STATISTICS ' + table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Kristen
Test

22859 Posts

Posted - 2011-10-13 : 07:03:03

Tasks you should perform on upgrade to SQL 2008 (applies also to SQL 2005)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230

jh_sql
Starting Member

24 Posts

Posted - 2011-10-18 : 05:19:17

Thanks for replies, redoing indexes and adding few nonclustered indexes worked wonders to speed of the database :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources