February 12, 2014 at 12:53 pm
When I right click on the index and select rebuild or reorganize, nothing happens. The indexes are still 90% fragmented. Can someone explain why the indexes are not being rebuilt?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
February 12, 2014 at 1:46 pm
It is likely there are not many pages in the index. Personally, I do not even consider re-indexing, until there are at least 100 pages in an object.
February 12, 2014 at 1:59 pm
What if there are 4600 pages?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
February 13, 2014 at 2:09 am
Maybe.
Is it a heap? Is there a shrink operation running at any time? Is Autoshrink on? How are you checking the fragmentation?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2014 at 7:29 am
I am sorry I should have been a little more clear on this. Users are having issues with page loading and it is taking them somewhat 30 seconds. For some reason I thought defragmenting Indexes would help. Now, I see some fragmentation on some of the indexes but the page size is less then 300 so defragmenting them won't help. What other steps I can take to troubleshoot the problem?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
February 13, 2014 at 7:31 am
I believe this is related to this post (same question)
http://www.sqlservercentral.com/Forums/Topic1541179-391-1.aspx
In this case, you need to find the problematic query and review its execution plan - can you pinpoint which query the page is calling?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 13, 2014 at 7:46 am
Yes, it is the same post and I have no idea which store proc or table its calling. I am still working on getting it from a developer.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
February 13, 2014 at 8:13 am
You could look for the query on the server to try and identify what table it was accessing.
For example, if you have Windows Server 2008 then you can use Resource Monitor from the Perf tab of Task Manager.
Or try this query to see some expensive i/o queries:
SELECT TOP 5 sqltxt.text AS 'SQL', qstats.total_logical_writes AS [Total Logical Writes],
qstats.total_logical_writes/DATEDIFF(second, qstats.creation_time, GetDate()) AS 'Logical Writes Per Second',
qstats.execution_count AS 'Execution Count',
qstats.total_worker_time AS [Total Worker Time],
qstats.total_worker_time/qstats.execution_count AS [Average Worker Time],
qstats.total_physical_reads AS [Total Physical Reads],
DATEDIFF(Hour, qstats.creation_time, GetDate()) AS 'TimeInCache in Hours',
qstats.total_physical_reads/qstats.execution_count AS 'Average Physical Reads',
db_name(sqltxt.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats AS qstats
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS sqltxt
WHERE sqltxt.dbid = db_id()
ORDER BY qstats.total_logical_writes DESC
Or you could try Activity Monitor for recent expensive queries.... (in SSMS right click on the server and select activity monitor)....
Regards,
Dave
February 13, 2014 at 8:46 am
dave hants (2/13/2014)
For example, if you have Windows Server 2008 then you can use Resource Monitor from the Perf tab of Task Manager.
Or try this query to see some expensive i/o queries:
SELECT TOP 5 sqltxt.text AS 'SQL', qstats.total_logical_writes AS [Total Logical Writes],
qstats.total_logical_writes/DATEDIFF(second, qstats.creation_time, GetDate()) AS 'Logical Writes Per Second',
qstats.execution_count AS 'Execution Count',
qstats.total_worker_time AS [Total Worker Time],
qstats.total_worker_time/qstats.execution_count AS [Average Worker Time],
qstats.total_physical_reads AS [Total Physical Reads],
DATEDIFF(Hour, qstats.creation_time, GetDate()) AS 'TimeInCache in Hours',
qstats.total_physical_reads/qstats.execution_count AS 'Average Physical Reads',
db_name(sqltxt.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats AS qstats
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS sqltxt
WHERE sqltxt.dbid = db_id()
ORDER BY qstats.total_logical_writes DESC
I ran it and 0 result.
How do I even know if it's a DB issue?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
February 13, 2014 at 8:53 am
I right click on the server and then activity Monitor. I am looking in Recent Expensive Queries. Which column do I actually have to look out for?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
February 13, 2014 at 8:58 am
Sort by duration/logical reads - right-click to view the execution plan
(and the script you ran prior, you need to run that on the database in question)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 13, 2014 at 9:02 am
The Max logical read I see is 3 and I did right click and opened up the execution plan but it says "clustered Index Seek cost 100% and that's it.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
February 13, 2014 at 10:13 am
New Born DBA (2/13/2014)
I am sorry I should have been a little more clear on this. Users are having issues with page loading and it is taking them somewhat 30 seconds. For some reason I thought defragmenting Indexes would help.
Probably not, it seldom results in large performance gains
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2014 at 10:15 am
New Born DBA (2/13/2014)
The Max logical read I see is 3 and I did right click and opened up the execution plan but it says "clustered Index Seek cost 100% and that's it.
3 logical reads is highly unlikely to be a problem.
You need to identify the problematic queries.https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2014 at 10:18 am
I know this is a mute point, but I personally wouldn't go looking for performance problems for a slow running web page without first being told what is being called from that page (i.e. procedure/code) - for all we know it may not even be a database issue, rather a web-application pool that needs to be recycled, or something new that was introduced into the web application, etc.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply