May 31, 2011 at 7:31 am
Hi folks, I need a bit of advice. We are having an issue with a query which is a very simple 2 table join that until two days ago took under 2 seconds to return the results and was perfoming a clustered index seek on both tables involved.
All of a sudden that has jumped to 8 hours :w00t: Sorry I cannot post the code company policy.
When looking at the plan, it is now performing a non clustered index scan on different indicies on both tables but I have no idea why as none of the columns covered by the non clustered indexs are used anywhere in the statement.
My suspicion is out of date statistics due to index fragmentation making the query engine choose a different plan.
We have got around the issue by forcing the query to use hints and specifying the clustered indexes which resolved the problem for now.
Now to get to my point, when querying the DMV sys.dm_db_index_physical_stats to find the fragmentation details with the following query we can't get the results as after letting the query execute for an hour we killed it as we were staring to see contention on resources. Out of hours is not an option as its a 24-7 system. In addition this behaviour does not happen in our performance, dev or QA environments which have almost identicle data sets.
SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
*
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'Detailed') WITH(nolock) AS IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE object_name(IPS.object_id) = 'tblXXXXXXXXXXX
AND ST.is_ms_shipped = 0
Can anyone suggest a better alternative to get the fragmentation figures as this DMV is very slow in returning results.
MCITP SQL 2005, MCSA SQL 2012
May 31, 2011 at 7:46 am
Yeah, don't use detailed.
As per Books Online
The LIMITED mode is the fastest mode and scans the smallest number of pages. For an index, only the parent-level pages (that is, the pages above the leaf level) are scanned. For a heap, only the associated PFS and IAM pages are examined; the data pages of the heap are not scanned. In SQL Server 2005, all pages of a heap are scanned in LIMITED mode.
The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap. If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED.
The DETAILED mode scans all pages and returns all statistics.
The modes are progressively slower from LIMITED to DETAILED, because more work is performed in each mode. To quickly gauge the size or fragmentation level of a table or index, use the LIMITED mode. It is the fastest and will not return a row for each nonleaf level in the IN_ROW_DATA allocation unit of the index.
As for your performance problem, did you try updating statistics before resorting to hints?
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
May 31, 2011 at 7:53 am
Gail, thanks for the help, as soon as you said "detailed", I thought bugger, I never noticied that. Must be having an off day.
As for the stats we don't have a maintenance window available for a few days to update them. We can change the select as read activities are ok to change on an adhoc basis.
Thanks again.
MCITP SQL 2005, MCSA SQL 2012
May 31, 2011 at 7:57 am
Why do you need a maintenance window to update statistics?
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
May 31, 2011 at 8:05 am
Company policy deems this as maintenance that has to be performed during a maintenance window.
However it looks like is definetly a fragmentation issue as now that I have re-ran as limited, I can see that the clustered index is 46% fragmented.
MCITP SQL 2005, MCSA SQL 2012
May 31, 2011 at 8:06 am
GilaMonster (5/31/2011)
Why do you need a maintenance window to update statistics?
Peeking my interest... why could you need maintenant window to update the stats?
May 31, 2011 at 8:09 am
RTaylor2208 (5/31/2011)
Company policy deems this as maintenance that has to be performed during a maintenance window.However it looks like is definetly a fragmentation issue as now that I have re-ran as limited, I can see that the clustered index is 46% fragmented.
Half shot in the dark... could this be your issue?
Any way you can lower the fill factor a little bit on that table since you can't seem to go the whole week? without maintaining that index?
May 31, 2011 at 8:11 am
RTaylor2208 (5/31/2011)
Company policy deems this as maintenance that has to be performed during a maintenance window.
Hmmm... do the people that wrote the company policy know that SQL automatically updates statistics on a regular basis during normal operation?
They take no locks, the can't block queries. Only possible problem is if your IO subsystem is overloaded and can't keep up with the load with stats updates running. But if it can't, it'll be getting that problem whenever autostats kicks in.
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
May 31, 2011 at 8:18 am
This particular index is excluded from index maintenance due to the transaction log growth during the process. We use replication and log shipping on this database to provide reporting data to other servers \ databases and we also recieve data from other servers for this OLTP system.
For this reason we cant change the recovery model as there is not enough storage for the transaction log backups as we also have a 3 day retention policy for the transaction logs.
The tables involved are huge just over 2 TB between them. We are working out some options for these tables to archive data making this situation more managable.
Changing the fill factor may be an option if we can get an opportunity to rebuild the index.
MCITP SQL 2005, MCSA SQL 2012
May 31, 2011 at 8:22 am
RTaylor2208 (5/31/2011)
This particular index is excluded from index maintenance due to the transaction log growth during the process.The tables involved are huge just over 2 TB between them. We are working out some options for these tables to archive data making this situation more managable.
Changing the fill factor may be an option if we can get an opportunity to rebuild the index.
So fill factor is not really the issue here. How long has it been since it was last maintained?
May 31, 2011 at 8:39 am
It has been months as mentioned before the t-log growth is preventing the rebuild.
this table is only queried once per day to produce some operational stats and as long as the results came back in under 15 minutes it was not a problem. When it jumped to 8 hours that became a problem.
An application update was applied 2 weeks ago which is now adding and removing circa 1,500,000 rows daily from the table.
It looks like we just reached the tipping point with the fragmentation last night where the query plan changed.
MCITP SQL 2005, MCSA SQL 2012
May 31, 2011 at 8:43 am
I'd still try the update stats trick first. Fragmentation might slow the query down but I've never read that it changes the execution plan... maybe Gail has more info on that matter.
It seems like a very likely candidate for the link both Gail and I posted.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply