Link to home
Start Free TrialLog in
Avatar of Zack
ZackFlag for Australia

asked on

Trouble analysing a database

Hi EE,

I have database that is having performance issues running certain queries so I thought I take a look at the fragmentation of the indexes using the following script from Paul Randall:

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() 
ORDER BY indexstats.avg_fragmentation_in_percent desc

Open in new window


The problem this query doesn't complete either even after 5 minutes. My query is why would this be occurring how do I resolve it?

Any assistance is appreciated.

Thank you.
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi Zack,

Can you share that query to me with the execution plan? Is that query blocked by some other spid?
Avatar of Zack

ASKER

Hi Pawan,

It's UAT instance so no other spid are running I have checked, please see the attached screenshot from the SQL trace profiler.

Thank you.
ShowplanXML-Part-1.PNG
ShowplanXML-Part-2.PNG
If possible can you please provide me your query which is slow?

Also Have you update the stats on the table and rebuild indexes ?
Avatar of Zack

ASKER

Hi Pawan,

The query I am concerned with currently is this one, which never completes see attached screenshot:

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() 
ORDER BY indexstats.avg_fragmentation_in_percent desc

Open in new window


Yes, I have rebuilt the indexes and updated the stats.

Thank you
Slowdb.PNG
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zack

ASKER

Hi Pawan,

'Size of DB' good point it's huge it's 429.659GB, I will give the query the next 10 minutes to execute.

Thank you.
Avatar of Zack

ASKER

Hi Pawan,

Okay after 15 minutes it completed, hmm I will run an overnight job to rebuild all the indexes, not just the ones the slow queries were calling.

Pawan do you know why the below query though would take 15 minutes to run?

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() 

Open in new window


Is the size of the database that is causing this, either way, I have got the query results so I can progress further from here.

Let me know if you have anything further to before I close this question.

Thank you.
SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zack

ASKER

Thank you for your assistance guys.