Performances suddenly dropping

Question

Post reply

Performances suddenly dropping

e.schreiner 59443

SSC Rookie

Points: 35
More actions
September 27, 2011 at 12:08 am

#248771

Hello,
We're running a .net 3.5 website, using SQL 2005 as database.
Since last week, the performances have become very slow and a page takes 10-15 seconds to load.
No change has been made to the code lately and the website was running smoothly before.
But I've noticed that if I change anything in the setting of SQL (memory allocation, autoclose on,...), the performances are good again for about 10 minutes. Then, it becomes slow again and I go back to the initial setting and it's ok for 10 minutes,... and so on.
Could you help me find out what is happening ?
Thank you

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

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

CoolCodeShare SSCrazy Points: 2584 More actions · Answer 1

Please refer these URLs.

http://msdn.microsoft.com/en-us/magazine/cc163854.aspx

http://msdn.microsoft.com/en-us/library/ff647813.aspx

Also I would suggest you for following things if something has changed last week at application level:

1. See how many connection strings are being created in your configuration file. Even if for the same value if there 2 keys are added, it would create 2 connections.

2. Use Data Reader for data retrievals. and Close connections immediately after the data is populated.

3. Run SQL profiler and identify the queries eating up more resources. [under test environment]

4. Try to call your data access layer in batches, for example you can send multiple records in a xml string to update or insert them in database with the help of xml types or OpenXml functions. [if something is hitting the db server in loops]

5. You can run PerMon to see the performance. Pls refer:http://msdn.microsoft.com/en-us/library/3xxk09t8(v=VS.80).aspx"> http://msdn.microsoft.com/en-us/library/3xxk09t8(v=VS.80).aspx [how your app is performing]

6. Implement Data/Object Caching for your master data or less changing data with appropriate expiration policy, Pls read from here :http://msdn.microsoft.com/en-us/library/system.web.caching.cache(v=VS.90).aspx [optimization step]

Best Regards,

Sudhir

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Assuming , and i think from what you have said its a big assumption, that this is a sqlserver issue, use server side tracing ( never run profiler against a live database, it can cause problems) to capture calls and durations to help find your poorly performing code.

Here's an article to get you started.

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

Clear Sky SQL
My Blog[/url]

e.schreiner 59443 SSC Rookie Points: 35 More actions · Answer 3

I will check all this.

Thank you very much to both of you

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

P.S. NEVER use autoclose or autoshrink on the busy db!

The simple-talk article will definitely help if the problem really comes from the server.

CoolCodeShare SSCrazy Points: 2584 More actions · Answer 5

Yes Auto Close and Auto Shrink are not good options.

Also please update us with your findings. I guess Trace thingy must helped you.

Best Regards,

Sudhir

e.schreiner 59443 SSC Rookie Points: 35 More actions · Answer 6

I've made some monitoring and found out the problem : missing indexes on two big tables.

Thank you all for your help.

MadAdmin SSChampion Points: 11261 More actions · Answer 7

Just to reiterate or beat a dead horse, is_auto_close_on = 1 is bad.

As soon as no connections to db, db closes.

When connection is opened, then db has to start and reallocate resources.

Never ever.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

ankur_libra Hall of Fame Points: 3999 More actions · Answer 8

Hi,

You can find the culprit by checking the WAITSTATS, when ever the slow response is felt on the server.

Use

DBCC SQLPERF(WAITSTATS)

or

Select * from sys.dm_os_wait_stats

Regards,

Ankur