Forums (Obsolete)

Member

fraser - 2/5/2013 3:18:54 PM

SQL Server restart fixes slow performance

We are currently developing a site using version 7. There are 4-5 people working on content /CMSDesk on a daily basis. After a few days, the performance of the server slows right down, so that it is almost unusable.

If we restart our SQL Server (2008 R2), performance improves greatly.

Could Kentico be leaving connections open (or orphaning connections)?

Is there a recommended configuration for SQL server? or our connection string?

Thanks for any help!

Certified Developer 12

yasser - 2/6/2013 5:07:00 AM

RE:SQL Server restart fixes slow performance

Hi Fraser,

We are a digital communications agency and have been using Kentico since v3. We have been using Kentico v7 since its release and have setup quite a few sites on v7 and have never encountered the behaviour you have described in your post. There can be lots of different reasons for the site slowdown.

- Are you running the Kentico website and SQL Server database on the same server?
- Is there low memory on the database server?
- Is there a memory leak in some custom code or 3rd part code you are using. You can run profiling tools to get this information.
- Have you got any custom code in your project?
- Are you using any data access library?
- Have you got custom queries or stored procedures being called from your site?
- Are processes blocking each other on SQL server?

All of these things (and more) can contribute to the behaviour you have described.

Hope this helps you identify the problem!

Yasser Abbasi from MMT Digital's K-Team
Kentico Certified Developer

Kentico Customer Success

kentico_martind2 - 2/6/2013 5:31:48 AM

RE:SQL Server restart fixes slow performance

Hello,

I would definitely recommend you to debug your site, in this case especially try SQL Queries Debugging.

Are there any messages in the Event log? Also you can try to install the latest updates for your server version.

Have you made any customizations? Are you using somewhere your custom code which is connectiong to the database? Custom SQL queries?

Best regards,
Martin Danko

Member

fraser - 2/6/2013 9:15:05 AM

RE:SQL Server restart fixes slow performance

- Are you running the Kentico website and SQL Server database on the same server?

Yes

- Is there low memory on the database server?

No, there's 4GB RAM, SQL is using 1GB and we still have 1GB free.

- Is there a memory leak in some custom code or 3rd part code you are using. You can run profiling tools to get this information.
- Have you got any custom code in your project?
- Are processes blocking each other on SQL server?
- Have you got custom queries or stored procedures being called from your site?

We are only using the Kentico ConnectionHelper to get our connections. I have verified all DataReaders are closed, and all connections are in "using" blocks.

It's nothing too complex, mostly just for reading data and not updating.

- Are you using any data access library?

No, just the built-in Kentico libraries.

----

I have updated to the latest patch for Kentico as well as have applied all Windows Updates.

There are no Errors/Warnings in the event log related to SQL queries or database.

I turned on SQL Query debugging and found that we are shipping ~1.7MB of data on each request, with ~70 queries.

Upon drilling down, we have quite a few (16) calls to this query: (cms.menuitem.selectversions)

Some are returning > 100KB

I am going to have to look into this further, any suggestions on where to start??

Kentico Customer Success

kentico_martind2 - 2/27/2013 2:18:24 AM

RE:SQL Server restart fixes slow performance

Hi,

The server configuration looks fine. But here are some hints:

1. Take a look at the webinar videos: Performance Mistakes and Building Highly Scalable Website and Full Client Cache

2. Check your web.config for:

<compilation debug=”false” ... />

3. Some sections in the Dev Guide:
Caching Options
Code Minification and Compression
File Management and Performance
Performance Optimization Tips

4. Try to monitor your server to find out when the high load is coming

5. Are you periodically recycling your application pool?

Best regards,
Martin Danko


Member	fraser - 2/28/2013 4:11:23 PM RE:SQL Server restart fixes slow performance Thanks very much for the useful information!