November 1, 2012 at 10:07 am
We are having an issue where a simple select statement occasionally hangs one of our applications (99% of the time it works correctly). The statement is "SELECT * from table where foo = 'bar'".
It should normally only return one row and the table itself rarely contains more than 100 rows at a time.
Other applications that use this table are not affected and run normally.
We have a script that runs periodically that looks for blocked processes but nothing is reported.
November 1, 2012 at 10:57 am
Are you able to see what the wait is in SQL Activity Monitor in SSMS? That might give you a clue.
Are you sure it's not the application thats causing the delay?
November 1, 2012 at 12:15 pm
While I haven't completely ruled out the application as the cause, we have turned on some debugging code and the last line logged is the execution of the SQL select statement.
Because it happens randomly, I haven't been lucky enough to catch it in SQL Activity Monitor when it occurs. We have something monitoring the process to automatically restart the application when it hangs.
Are there any diagnostic queries that would be helpful? I could setup these to run before the restart check to get a snapshot.
Thanks. I appreciate all of the help.
November 1, 2012 at 12:28 pm
Are there any indexes on the table? If so you might consider rebuilding them. I've seen "corrupted" before, causing similar problems.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 1, 2012 at 12:46 pm
I like it... But before I rebuild the index is there any way to verify the integrity of the index? I would like to be able provide data to the powers that be that this was the state the index was in before and after the rebuild.
November 1, 2012 at 1:36 pm
You could try running DBCC CHECKTABLE and see if it shows anything. Or try looking at sys.dm_db_index_physical_stats and see if it needs to be rebuilt anyway based on fragmentation.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 1, 2012 at 7:39 pm
srphelan (11/1/2012)
We are having an issue where a simple select statement occasionally hangs one of our applications (99% of the time it works correctly). The statement is "SELECT * from table where foo = 'bar'".It should normally only return one row and the table itself rarely contains more than 100 rows at a time.
Other applications that use this table are not affected and run normally.
We have a script that runs periodically that looks for blocked processes but nothing is reported.
You need to consider the issue from application side. Or either it could be a network issue.
Start a trace and leave it until you hit this issue that will provide you the clue you need.
November 2, 2012 at 3:41 am
Here is a useful query for checking the fragmentation of your indexes...run it in the context of the database that holds your table...
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
CREATE TABLE #FragmentedIndexes(
DatabaseName SYSNAME
, SchemaName SYSNAME
, TableName SYSNAME
, IndexName SYSNAME
, [Fragmentation%] FLOAT)
INSERT INTO #FragmentedIndexes
SELECT
DB_NAME(DB_ID()) AS DatabaseName
, ss.name AS SchemaName
, OBJECT_NAME (s.object_id) AS TableName
, i.name AS IndexName
, s.avg_fragmentation_in_percent AS [Fragmentation%]
FROM sys.dm_db_index_physical_stats(null,NULL, NULL, NULL, 'SAMPLED') s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.schemas ss ON ss.[schema_id] = o.[schema_id]
WHERE s.database_id = DB_ID()
AND i.index_id != 0
AND s.record_count > 0
AND o.is_ms_shipped = 0
select * from #FragmentedIndexes order by 'fragmentation%' desc
drop table #FragmentedIndexes
I can't claim credit for the query, it comes from the book SQL Server DMVs In Action by Ian W Stirk, it's been very useful for me!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply