SQL 2008 R2 and custom application built with C# very slow query times causing errors and timeouts

Question

SQL 2008 R2 and custom application built with C# very slow query times causing errors and timeouts

Banks1850

Old Hand

Points: 309
More actions
December 27, 2013 at 7:38 am

#302736

Hello SQL Guru's
I have a big problem with my production servers. Here's the layout, maybe someone can suggest something as I'm pretty much at my limit of knowledge.
I've been a SQL DBA for a while (over 10 years), and have never had this type of issue before. I've diagnosed as far as I know with no answers.
We have a C# application that waits for client connections then does a backup operation of client files and transfers them to our server, the application uses a local SQL 2008 R2 enterprise instance (64 bit) to keep track of these clients and their files. We own the Code for the Application and have engineers who code for it. We recently released a new version and since the release we are seeing a particular issue with regards to the SQL Database it connects to.
At seemingly random points in time, queries that would normally take milliseconds (in the range of 5 to 50 ms) will begin taking seconds and eventually minutes to complete. This generates a serious queue in the SQL server and pushes the CPU usage up to (or near depending on the queue size) 100% (eventually causing the clients to begin to error). These are not small servers, and the volume of transactions normally never pushes the CPU past 20 to 25% during spikes. We have plenty of RAM (128 GB), Dual 8 core CPU (16 logical) and nothing else running other than the application (which takes very little resources) and the webserver (IIS7), this is on a Windows 2K8 R2 x64 server.
I've put every sql server counter on it I can think of as well as the normal windows counters for SQL server, and nothing out of the ordinary pops up (I've baselined these machines). I've run the SQL BPA against it and it comes back clean, it has the service packs for SQL 2K8 R2 on it as well as being up to date with windows patches.
A couple of things I notice.
1) When this happens, the client log usually shows some kind of a connection issue with a timeout and reconnect, it's not an exact correlation as these reconnects happen quite often as part of normal behavior, it's just that they are happening on all clients that fail because of this issue.
2) When I restart the application services WITHOUT restarting sql, the queue drops off but then comes back the same way it did before (taking minutes to query instead of milliseconds)
3) When I restart the application AND sql server the queue goes away and the machine again behaves normally.
I've been trying everything I can think of to troubleshoot this but I have no more avenues.
I'm thinking there may be some kind of a leak or connection hang with the connection between the application and the AppDomain that is removed only when the AppDomain is unloaded and reloaded during a sql restart but I have no idea how to prove this.
Caveats:
1) the Engineering boss doesn't want to work on this with me until I "PROVE" it's a problem with the code, so I am kind of out on a limb on this one.
2) I cannot reproduce this issue on dev and/or QA devices, since I don't know how it's happening, everything I try at this point has to be on a production machine experiencing the issue (which sucks but at this point, it's happening almost every day so...)
So,
-Anyone have any thoughts? On either what the issue could be or how I can move forward diagnosing the issue? I am not a .Net developer so I have no idea how to debug the application.
-Does anyone know what counters to add to perfmon to see if the application is holding on to connections or has errors in it's connections to SQL?
I'm at the end of my rope here and need help. More than happy to provide additional data if necessary. Just ask.
thanks,
-Banks

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if it works quickly for a while, and then becomes slow, it sounds like out of date statistics might be the first thing to look at.

stale statistics can easily slow down existing queries.

identify the tables being used, and run the command UPDATE STATISTICS ON [TableName] WITH FULL SCAN;(for each of the critical tables)

you'll probably see an instant performance difference.

I've created scheduled jobs that update stats much more often (multiple times per day) to resolve the issues of stale stats on tables that get inserted/updated/deleted a lot.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Banks1850 Old Hand Points: 309 More actions · Answer 2

statistics are updated daily and automatically for all tables right after the index maintenance is performed.

and there are none that are listed as out of date when I check.

Lowell SSC Guru Points: 323518 More actions · Answer 3

Banks1850 (12/27/2013)
statistics are updated daily and automatically for all tables right after the index maintenance is performed.
and there are none that are listed as out of date when I check.

I'm speaking from experience here: once a day is sometimes not enough for frequently updated tables.

stats get auto updated if 20% +500 rows of data are inserted/updated/deleted.

20% on a MillionBillion row table is a huge amount and probably won't happen before the next stats scheduled job, and it only takes a fraction(ie half a percent to 1%) for the stats to throw of existing execution plans out of whack.

especially since you mentioned the issue goes away when you bounce the service, because new execution plans are built that now take into consideration the current stats, I'm more than sure that your issue is stale statistics.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 4

When sql server is showing the signs of slowness have you checked to see if you run the same sql on query analyzer, how much time time it takes?

Also look for is there any locking/blocking going n in the sql server.

I would suggest to run Adam Machanic's sp_who_is_active when sql server is slow and see what is running at that point and start with looking at the execution plan of those SQL's.

By looking at the execution plan of slow queries, you will know what is actually causing slowness.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Lowell (12/27/2013)
especially since you mentioned the issue goes away when you bounce the service, because new execution plans are built that now take into consideration the current stats, I'm more than sure that your issue is stale statistics.

Except that a service bounce doesn't update stats, so the plans would be recreated using the same stale stats from before the restart and the queries would still be slow.

Could be a form of parameter sniffing, though we're all shooting in the dark without more information.

Banks, could you identify a couple of queries that have this problem? Post the queries along with slow and fast execution plans (actual plans, not estimated so don't pull the plans from cache). Also the table definitions and index definitions for those queries.

What are the wait types that you see for the queries while they're running longer than usual?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Banks1850 Old Hand Points: 309 More actions · Answer 6

I'm seeing the queries using sp_whoisactive already.

that's how I'm able to see the queries slowing down.

I've checked for locks (nothing unusual, these queries are not being blocked)

there's no latch waits (or nothing out of the ordinary)

I've run traces and looked for any unusual queries happening during this issue, nothing that doesn't happen during the normal execution times.

I've loaded just about ever performance counter that is even peripherally associated with Windows and SQL server and found nothing that would scream "problem"

I've checked locks, laches, memory statistics including PLE and Buffer Cache, transactions, transaction times, connections, compilations, batch requests/sec, recompilations, total memory, index statistics, index fragmentation levels, query plan cache statistics. All look normal. The only avenue I haven't really looked at is the AppDomain because I know very little about it.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Can you post the queries that are slow (just one or two to start), the slow and fast execution plans (actual plans) along with table and index definitions?

What wait types do the slow-running queries accrue? If they're running at the time you look, what's their last wait type?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Banks1850 Old Hand Points: 309 More actions · Answer 8

Gail,

Thanks for the reply, I'll grab all that info and post it.

the query itself is this:

SELECT TOP 1 ufi.UserFileInfoId

FROM userfileinfo ufi WHERE EXISTS

(

SELECT vfi.VersionFileInfoId FROM VersionFileInfo vfi

WHERE ufi.BackupSetId = vfi.BackupSetId

AND ufi.VersionFileLocalNameId = vfi.VersionFileLocalNameId

AND ufi.UserFileInfoId BETWEEN vfi.FirstUserFileInfoId AND vfi.LastUserFileInfoId

)

ORDER BY ufi.UserFileInfoId DESC

and the table structures being accessed are as follows

[VersionFileInfo](

[VersionFileInfoId] [bigint] IDENTITY(1,1) NOT NULL,

[BackupSetId] [bigint] NOT NULL,

[VersionFileLocalNameId] [bigint] NOT NULL,

[BlockNumber] [bigint] NOT NULL,

[FirstUserFileInfoId] [bigint] NOT NULL,

[LastUserFileInfoId] [bigint] NOT NULL,

[FileInfoId] [bigint] NOT NULL,

[UserId] [bigint] NOT NULL,

[ModifiedDate] [bigint] NOT NULL,

[FileSize] [int] NOT NULL,

[CompressedFileSize] [int] NOT NULL,

[FileNameCRC] [bigint] NOT NULL,

CONSTRAINT [PK_VersionFileInfoId] PRIMARY KEY NONCLUSTERED

(

[VersionFileInfoId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [VFI_ixc_BackupSetId]

) ON [VFI_ixc_BackupSetId]

[UserFileInfo](

[UserFileInfoId] [bigint] IDENTITY(1,1) NOT NULL,

[UserId] [bigint] NOT NULL,

[BackupSetId] [bigint] NOT NULL,

[VersionFileLocalNameId] [bigint] NOT NULL,

[FirstVersionId] [bigint] NOT NULL,

[LastVersionId] [bigint] NOT NULL,

[LatestVersionId] [bigint] NOT NULL,

[IsFinished] [bit] NOT NULL,

[FileAttr] [bigint] NOT NULL,

[SpecialFileId] [bigint] NOT NULL,

[ModifiedDate] [bigint] NOT NULL,

[CreatedDate] [bigint] NOT NULL,

[ACL] [varbinary](max) NULL,

[FileNameCRC] [bigint] NOT NULL,

CONSTRAINT [PK_UserFileInfoId] PRIMARY KEY NONCLUSTERED

(

[UserFileInfoId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [UFI_ixc_BackupSetId]

) ON [UFI_ixc_BackupSetId]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Still need indexes and both execution plans, fast and slow (actual plans please)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 10

If you see slow sql in SQL server, it means there is something off in SQL. As Gail suggested it may be very well parameter sniffing issue. But again without looking at the execution plan of slow SQL, nothing can be said for sure.

Again, have you tried executing slow sql in query analyzer and see if they are slow there too?

Banks1850 Old Hand Points: 309 More actions · Answer 11

GilaMonster (12/27/2013)
Can you post the queries that are slow (just one or two to start), the slow and fast execution plans (actual plans) along with table and index definitions?
What wait types do the slow-running queries accrue? If they're running at the time you look, what's their last wait type?

Unfortunately I didn't write down the wait types, but here's the execution plan for the query when it's working correctly

<?xml version="1.0" encoding="utf-16"?>

<Batch>

</OutputList>

</RunTimeInformation>

</ScalarOperator>

</TopExpression>

</OutputList>

</RunTimeInformation>

</OuterReferences>

</OutputList>

</RunTimeInformation>

</OuterReferences>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</RunTimeInformation>

</RangeColumns>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</EndRange>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</Top>

</RelOp>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

For anyone interested, plan converted and fixed so that it will actually load into SSMS

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Banks1850 Old Hand Points: 309 More actions · Answer 13

here's the indexes for the VersionFileInfo table (the first table)

CREATE CLUSTERED INDEX [ixc_BackupSetId] ON [dbo].[VersionFileInfo]

(

[BackupSetId] ASC

)

CREATE NONCLUSTERED INDEX [ix_Cleanup] ON [dbo].[VersionFileInfo]

(

[BackupSetId] ASC,

[VersionFileInfoId] ASC

)

INCLUDE ( [VersionFileLocalNameId],

[FirstUserFileInfoId],

[LastUserFileInfoId])

CREATE NONCLUSTERED INDEX [ix_FileInfoId] ON [dbo].[VersionFileInfo]

(

[FileInfoId] ASC

)

INCLUDE ( [BlockNumber],

[FileSize],

[CompressedFileSize])

CREATE NONCLUSTERED INDEX [ix_FileNameCRC] ON [dbo].[VersionFileInfo]

(

[FileNameCRC] ASC

)

CREATE NONCLUSTERED INDEX [ix_JoinColumns] ON [dbo].[VersionFileInfo]

(

[BackupSetId] ASC,

[VersionFileLocalNameId] ASC,

[FirstUserFileInfoId] ASC,

[BlockNumber] ASC

)

INCLUDE ( [VersionFileInfoId],

[LastUserFileInfoId],

[FileInfoId])

CREATE NONCLUSTERED INDEX [ix_ModifiedDate] ON [dbo].[VersionFileInfo]

(

[ModifiedDate] ASC

)

CREATE NONCLUSTERED INDEX [ix_VersionFileLocalNameId] ON [dbo].[VersionFileInfo]

(

[VersionFileLocalNameId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [VFI_ix_VersionFileLocalNameID]

GO

ALTER TABLE [dbo].[VersionFileInfo] ADD CONSTRAINT [PK_VersionFileInfoId] PRIMARY KEY NONCLUSTERED

(

[VersionFileInfoId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [VFI_ixc_BackupSetId]

GO

Banks1850 Old Hand Points: 309 More actions · Answer 14

and here are the indexes for the 2nd table

CREATE CLUSTERED INDEX [ixc_BackupSetId] ON [dbo].[UserFileInfo]

(

[BackupSetId] ASC

)

CREATE NONCLUSTERED INDEX [ix_BackupSetAndVersion] ON [dbo].[UserFileInfo]

(

[BackupSetId] ASC,

[FirstVersionId] ASC

)

INCLUDE ( [LastVersionId],

[VersionFileLocalNameId],

[UserFileInfoId],

[IsFinished])

CREATE NONCLUSTERED INDEX [ix_FileNameCRC] ON [dbo].[UserFileInfo]

(

[FileNameCRC] ASC

)

CREATE NONCLUSTERED INDEX [ix_JoinColumns] ON [dbo].[UserFileInfo]

(

[BackupSetId] ASC,

[VersionFileLocalNameId] ASC,

[FirstVersionId] ASC

)

INCLUDE ( [UserFileInfoId],

[ModifiedDate],

[LastVersionId],

[IsFinished])

CREATE NONCLUSTERED INDEX [ix_ModifiedDate] ON [dbo].[UserFileInfo]

(

[ModifiedDate] DESC

)

CREATE NONCLUSTERED INDEX [ix_UserId] ON [dbo].[UserFileInfo]

(

[UserId] DESC

)

CREATE NONCLUSTERED INDEX [ix_VersionFileLocalNameId] ON [dbo].[UserFileInfo]

(

[VersionFileLocalNameId] ASC

)

ALTER TABLE [dbo].[UserFileInfo] ADD CONSTRAINT [PK_UserFileInfoId] PRIMARY KEY NONCLUSTERED

(

[UserFileInfoId] ASC

)