March 8, 2016 at 8:21 am
Acct dept is complaining, credit card process is timing out.
I checked the DB side for blocking and found none....also checked the memory usage, and SQL is using what's allocated to it....thats pretty normal daily.
Not sure what else to check....I have a feeling this is network issue, since they experience similar yesterday.
Please give me hints what else I need to check from DB prespective to make sure that this is not DB timeout issue.
Regards,
SQLisAwe5oMe.
March 8, 2016 at 8:48 am
Fist thing, did you check the fragmentation of indexes? Are the statistics of those tables updated?
If the issue still exists, then we're going to see other aspects.
Igor Micev,My blog: www.igormicev.com
March 8, 2016 at 8:51 am
Igor Micev (3/8/2016)
Fist thing, did you check the fragmentation of indexes? Are the statistics of those tables updated?If the issue still exists, then we're going to see other aspects.
Yes, indexes and stats updates every sunday night.
Regards,
SQLisAwe5oMe.
March 8, 2016 at 8:56 am
SQLisAwE5OmE (3/8/2016)
Igor Micev (3/8/2016)
Fist thing, did you check the fragmentation of indexes? Are the statistics of those tables updated?If the issue still exists, then we're going to see other aspects.
Yes, indexes and stats updates every sunday night.
That doesn't mean that they are good. However, could you run this query and share the results of the tables that are used in queries/sps for the reports:
selectss.name [Schema],
object_name(ddips.object_id) [Table_name],
isnull(si.name,'') [Index_name],
si.index_id,
si.type_desc,
isnull(ddips.avg_fragmentation_in_percent,0) [Ext_frag],
ddips.page_count [Pages],
si.fill_factor,
isnull(ddips.avg_page_space_used_in_percent,0) [Page_fullness_pct]
from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'LIMITED') ddips
join sys.indexes si on ddips.index_id = si.index_id and ddips.object_id = si.object_id
join sys.tables st on ddips.object_id = st.object_id
join sys.schemas ss on st.schema_id = ss.schema_id
where ddips.index_level = 0 and si.index_id > 0 and st.[type] = N'U'
group byss.name, ddips.object_id, si.name, si.index_id, si.type_desc, avg_fragmentation_in_percent, ddips.page_count,
avg_page_space_used_in_percent,si.fill_factor
order by ddips.page_count desc
Igor Micev,My blog: www.igormicev.com
March 8, 2016 at 9:03 am
Igor Micev (3/8/2016)
SQLisAwE5OmE (3/8/2016)
Igor Micev (3/8/2016)
Fist thing, did you check the fragmentation of indexes? Are the statistics of those tables updated?If the issue still exists, then we're going to see other aspects.
Yes, indexes and stats updates every sunday night.
That doesn't mean that they are good. However, could you run this query and share the results of the tables that are used in queries/sps for the reports:
selectss.name [Schema],object_name(ddips.object_id) [Table_name],
isnull(si.name,'') [Index_name],
si.index_id,
si.type_desc,
isnull(ddips.avg_fragmentation_in_percent,0) [Ext_frag],
ddips.page_count [Pages],
si.fill_factor,
isnull(ddips.avg_page_space_used_in_percent,0) [Page_fullness_pct]
from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'LIMITED') ddips
join sys.indexes si on ddips.index_id = si.index_id and ddips.object_id = si.object_id
join sys.tables st on ddips.object_id = st.object_id
join sys.schemas ss on st.schema_id = ss.schema_id
where ddips.index_level = 0 and si.index_id > 0 and st.[type] = N'U'
group byss.name, ddips.object_id, si.name, si.index_id, si.type_desc, avg_fragmentation_in_percent, ddips.page_count,
avg_page_space_used_in_percent,si.fill_factor
order by ddips.page_count desc
Igor, thanks for assisting....attached is the query results.
Regards,
SQLisAwe5oMe.
March 8, 2016 at 9:20 am
SQLisAwE5OmE (3/8/2016)
Igor Micev (3/8/2016)
SQLisAwE5OmE (3/8/2016)
Igor Micev (3/8/2016)
Fist thing, did you check the fragmentation of indexes? Are the statistics of those tables updated?If the issue still exists, then we're going to see other aspects.
Yes, indexes and stats updates every sunday night.
That doesn't mean that they are good. However, could you run this query and share the results of the tables that are used in queries/sps for the reports:
selectss.name [Schema],object_name(ddips.object_id) [Table_name],
isnull(si.name,'') [Index_name],
si.index_id,
si.type_desc,
isnull(ddips.avg_fragmentation_in_percent,0) [Ext_frag],
ddips.page_count [Pages],
si.fill_factor,
isnull(ddips.avg_page_space_used_in_percent,0) [Page_fullness_pct]
from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'LIMITED') ddips
join sys.indexes si on ddips.index_id = si.index_id and ddips.object_id = si.object_id
join sys.tables st on ddips.object_id = st.object_id
join sys.schemas ss on st.schema_id = ss.schema_id
where ddips.index_level = 0 and si.index_id > 0 and st.[type] = N'U'
group byss.name, ddips.object_id, si.name, si.index_id, si.type_desc, avg_fragmentation_in_percent, ddips.page_count,
avg_page_space_used_in_percent,si.fill_factor
order by ddips.page_count desc
Igor, thanks for assisting....attached is the query results.
There is high fragmentation in some tables, e.g. Activity, some indexes are 40,50,60,70% fragmented. What are the tables used by the SPs?
Next step is if you can REORGANIZE followed by update of the statistics, or REBUILD online the indexes. Check this https://msdn.microsoft.com/en-us/library/ms189858.aspx
You can find many scripts that are rebuilding/reorganizing indexes. Be careful if you're on production. If you're on Standard edition you should not rebuild with offline option, but reorganize.
Check this https://ola.hallengren.com/
Share here if the executions improved, otherwise other matters are in question...
Igor Micev,My blog: www.igormicev.com
March 9, 2016 at 3:21 am
Personally I'd start with the basics, do you know what stored procedures \ sql code is being executed by the application that is timing out? Most applications have a default timeout period set and for the sake of argument its usually 30 seconds although can be configurable. The stored procedure \ query is most likely taking longer than the maximum threshold of the timeout setting in the application.
So first of all identify the procedure \ query that is being executed, you have a few options to do this:
1. define and execute a server side trace during the period the issue occurs, be careful as this can generate huge trace files depending on system activity so set your filters accordingly.
2. define and execute an extended event session to capture stored procedure calls \ queries with long duration
3. Look at the DMV stats for executed procedures, this may or may not help depending on when the last time the server was restarted.
4. If its a current issue you may even be able to identify the code being executed using activity monitor, sp_who2 or sp_callwhoisative.
Once you have the stored procedure \ query identified you can look at the execution plan by executing the query \procedure and having include actual plan enabled in SSMS. Obviously only do this if the code executed only selects data and does not insert \ delete \ update data. This will help identify how long the procedure takes to execute and the most costly elements of the plan. Alternatively you can look at the cached plans for the stored procedure \ query to get execution plans for the code previously executed.
Now that you have the plan you should be able to interrogate the plan and see if the problem comes down to a bad execution plan due to statistics out of date or fragmented indexes, poor code, something else.
Rebuilding indexes and updating stats when certain thresholds are met is good maintenance, but without specifically looking at what is executing on the server at the time of the issue you'll never get to the root cause.
MCITP SQL 2005, MCSA SQL 2012
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply