Zack
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:
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.
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
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.
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
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 ?
Also Have you update the stats on the table and rebuild indexes ?
ASKER
Hi Pawan,
The query I am concerned with currently is this one, which never completes see attached screenshot:
Yes, I have rebuilt the indexes and updated the stats.
Thank you
Slowdb.PNG
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
Yes, I have rebuilt the indexes and updated the stats.
Thank you
Slowdb.PNG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
'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.
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?
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.
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()
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your assistance guys.
Can you share that query to me with the execution plan? Is that query blocked by some other spid?