Times out when SP/SQL CLR is called from silverlight application

Question

Times out when SP/SQL CLR is called from silverlight application

apeterin

SSC Enthusiast

Points: 156
More actions
March 12, 2012 at 4:01 am

#265693

Some strange behaviour in sql server 2008. This happens for past couple of weeks. Whenever our silverlight application is started in the begining of the day , all calls to stored procedure times out. Then we go to SQL management studio to check any errors are thrown but the same stored procedure will execute successfully in 58 seconds. If the same stored procedure is ran again , it runs less than a second. Now from the silverlight application if we call the same SP it will get the results and no time out for rest of day. This happens for any sp we call or direct sql we execute from code. The same will continue the next day. Environment details given below.
Client Environment : WIndows XP, IE 7 , running silverlight application
Server Environment : Windows server 2008 R2, SQL Server 2008, virtual server running in VMware vsphere 5.0
Any help is much appreciated.

Viewing 9 posts - 1 through 9 (of 9 total)

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

There's no generic solution to these kind of issues, so people will need a lot more detail in order to help. I'd recommend having a read through this:

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

and posting some more information about the specific procedures causing pain, along with DDL and scripts to create some table data - the actual execution plan is also invaluable.

What you're describing could happen for a number of reasons. Queries will run faster once they have a cached execution plan and their pages are in memory, but the crux of the issue is likely to just be that you need to optimise the SQL. Rather than running in 58 seconds when the pages are not in the buffer cache and 1 second when they are, a typical OLTP query that users are waiting for in real time should be running in milliseconds when cached and seconds at most when not. It all comes down to SARGability of the predicates and an optimised schema

apeterin SSC Enthusiast Points: 156 More actions · Answer 2

Thanks for the detailed reply. Herewith attaching the query execution plan. I can see i have missed lot of indexes, is there any other information/data i should look into from the execution plan ? Kindly help.

HowardW One Orange Chip Points: 29109 More actions · Answer 3

There's a lot of low hanging fruit here. Can you post the DDL for all the tables involved and preferably a few rows of sample data (doesn't need to be real data)

HowardW One Orange Chip Points: 29109 More actions · Answer 4

Also, peppering your code with NOLOCK is risky at best. I'd have a read through here:

http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

And consider whether you really need these and are prepared to accept the consequences in terms of data quality/accuracy.

HowardW One Orange Chip Points: 29109 More actions · Answer 5

Also, why are you wrapping RTRIM's around columns? That means that indexes can't be used to perform a seek. You really should be cleaning this data on input, but even if not, this should be unnecassary as trailing spaces are generally ignored:

CREATE TABLE #test (col1 VARCHAR(100))

INSERT INTO #test VALUES ('Photobox ')

SELECT * FROM #test WHERE col1='Photobox'

apeterin SSC Enthusiast Points: 156 More actions · Answer 6

Thanks for help. Please find the DDL for 4 tables and sample data. Please let me know if you need any more details.

HowardW One Orange Chip Points: 29109 More actions · Answer 7

Ok. I've had a brief look, but don't have time to do anything too in-depth today.

Some general points:

1) Uniqueidentifier makes a very poor choice for a clustered index as they are non-sequential (you'll end up with lots of fragmentation in the base tables). You should pick something that's usually in insert order - if you have a date field that's regularly used in filtering, then that would be a good candidate. If you don't need global uniqueness for these (e.g. you're not merging these with another dataset somewhere else etc.) then an integer identity column is a better choice than uniqueidentifier

2) Why don't you have foreign keys for these clearly related tables? I hope you've just omitted them from the sample data

3) Once foreign keys are sorted out, create non-clustered indexes on the foreign key columns, then view another execution plan before accepting any suggested indexes

4) If you really can't be bothered with any of that, the suggested index probably will speed everything up quite a bit, but you should have a hard look at the schema design here, it is ringing a lot of alarm bells.

apeterin SSC Enthusiast Points: 156 More actions · Answer 8

Thanks for your valuable comments and pointing us to right direction. Will consider all your points. Actually we are going to re-write our application. Could you please elaborate little bit on database design/schema changes which you mentioned at the end. What points to consider while doing database design for re-write.