Fragmentation

Question

Post reply

Fragmentation

yentha.valla

SSC Enthusiast

Points: 147
More actions
April 27, 2011 at 12:53 am

#145499

HI,
I ran dbcc showcontig .. i found in some of the table logical fragmentation is more than 60 & 90 percents .. how i can reduce that ...
DBCC INDEXDEFRAG
(
{ database_name | database_id | 0 }
, { table_name | table_id | view_name | view_id }
[ , { index_name | index_id } [ , { partition_number | 0 } ] ]
)
[ WITH NO_INFOMSGS ]
how i can het above details .....
any one has sql query to get all the above info together ...
Thanks
Valla ...

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

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

Patrick_Fiedler SSCommitted Points: 1904 More actions · Answer 1

Hi,

you can reduce this with at least reorganizing these indexes.

You can write a sql script with a cursor over all indexes to update and reorganize these indexes.

Unfortunably I have currently no script for such a thing.

[font="Arial"]Kind regards,

Patrick Fiedler
Consultant

Fiedler SQL Consulting

www.fiedler-sql-consulting.de
[/font]

yentha.valla SSC Enthusiast Points: 147 More actions · Answer 2

HI,

Thanks for prompt reply ..

MA new to Sql Server .. Now i hv to do Performance tuning on Database .. i was working as Oracle DBA .. in our co we hv a sql DBA also .. now i got a cance to work on this .. as one of DBA left for emergency ..

all user are saying Application is very slow ..

What are the things i hv to check ..

Thanks

Valla

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Hi , performance tuning is a big big area, first off you need to identify which sql statements/procedures are running 'slow'. Personally, i find that making the assumption that the SQLServer is slow is wrong as there are many other layers between the data and the user, but since this is a sql forum :)...

Your best start to identifying the poorly performing items are

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

and

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Clear Sky SQL
My Blog[/url]

rsingh.sql SSC Eights! Points: 874 More actions · Answer 4

My 2 Cents,

Before you defragment (rebuild or reorganize) those indexes make sure to look at the number of pages. Rebuilding a small index would not give you great performance benefits. To determine number of fragmented pages you can query sys.dm_db_index_physical_stats DMV.

Follow this link:

http://msdn.microsoft.com/en-us/library/ms188917.aspx

For large tables this will take a lot of time so, determine ideal off peak time for querying this DMV

and for rebuilding indexes follow this link

http://msdn.microsoft.com/en-us/library/ms181671(v=SQL.90).aspx

ricer Right there with Babe Points: 722 More actions · Answer 5

In my opinion you should work with your SQL Server DBA since performance tuning is not an easy topic to cover without having some experience with SQL Server.

Will C

MCITP, 2008 Database Admin, Developer

-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer

Nadrek SSC-Insane Points: 20039 More actions · Answer 6

May I suggest looking at http://ola.hallengren.com/[/url]? Ola has some fairly advanced, easy to use scripts for many aspects of maintenance, including index maintenance, backup, and integrity checking.

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 7

yentha.valla (4/27/2011)
HI,
Thanks for prompt reply ..
MA new to Sql Server .. Now i hv to do Performance tuning on Database .. i was working as Oracle DBA .. in our co we hv a sql DBA also .. now i got a cance to work on this .. as one of DBA left for emergency ..
all user are saying Application is very slow ..
What are the things i hv to check ..
Thanks
Valla

This is the short answer. I can't help but quote Jeff Moen's usual reply: "It depends." It depends on what is causing your server to be slow to respond to requests for service.

Other posters have said it but I'll say it again: Performance tuning a database server is a large subject and there are no simple answers because there is so much that you must know about your server and it's application environment to do it properly. The execution of performance tuning is a process, not an event.

That being said, I encourage you to fall back on your knowledge of Oracle databases and servers. When you had to performance tune your Oracle databases and servers, what did you focus on? It's pretty much the same for all relational database management systems. For example:

> eliminate table scans where possible.

> make certain search columns are properly indexed.

> determine where the stress on your server resources is. Is it your disks? RAM? NIC? CPU? (The Windows Performance Monitor software can help you evaluate your server.) Seek to reduce the stress on your most stressed resource.

> and, more.

I could go on for a long list but I'm sure you get the idea. Focus on the aspects of performance tuning that you already know how to do.

To do that using SQL Server, you will have to learn how to use the product, especially SQL Server Management Studio. I encourage you to open SQL Server Management Studio and left and right click on everything in its interface to locate your resources. This process will naturally lead you toward solutions if you fundamentally understand how to tune a database and a database server.

LC