SQL Server 2008 Query

Under SQL 2008, how to

- check internal & external index fragment ? I use the script below but it seems that it takes a long time to run ..
ref: http://gallery.technet.microsoft.com/scriptcenter/Check-SQL-Server-a-a5758043

- how to identify dead lock in SQL 2008 ? How do I know whether a dead lock occur in production environment ? Will the SQL server slow down the performance ?

- how can I identify a long running sql, procedure or jobs in SQL 2008 server ?
AXISHKAsked:
Who is Participating?
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

I suggest you look up
http://technet.microsoft.com/en-us/library/ms188917.aspx, and dig into the dmv
dm_db_index_physical_stats a little more.

Regards
  David
0
 
David ToddSenior DBACommented:
Hi,

These are three separate questions.

1. The script
How long does it take to run? What size is the database in question?

Regards
  David
0
 
David ToddSenior DBACommented:
PS It is extent fragmentation, not external ...

An extent is a related group of 8 pages.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
AXISHKAuthor Commented:
Wait for 1/2 hour but it doesn't return the result...

Is the query correct ? Is there some example on using the sys.dm_db_index_physical_stats ? eg. how to interpret the value and result ?

I will separate the #2 and 3rd question into separate thread. Tks
0
 
David ToddSenior DBACommented:
Hi,

Try on a smaller database first then.

Or if you follow the link I gave, you can specify the indexes of immediate interest.

Regards
  David
0
 
AXISHKAuthor Commented:
Tks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.