Kategoriarkiv: Performance

Announcing web based SQL Max Memory calculator

sql-max-logo

Today I want to announce a tiny web application for calculating max memory in the SQL Server to improve the overall performance in you SharePoint environment:

SQL Max Memory Calculator.

One of the important actions to improve the performance in SharePoint is fine tuning of the SQL Databases. Regarding the databases one of the crucial settings is the Max Memory Setting. You can read more about database optimizations for SharePoint in a whitepaper written by SharePoint MVP Vlad Catrinescu. The document is available on the SharePoint Community Web Site:

Vlad Catrinescu also created a windows application for calculating the max memory for SQL which can be downloaded from codeplex (GPL v2):

This windows application has inspired me and my colleagues to create the web based application SQL Max. The goal of the SQL Max web app is to take Vlad’s idea a step further and make it available directly in your browser, on desktop at work, or on the go in your mobile. This mobile web app will also be available for offline access. Simply put, why should you dowload a zip file, extract and run the .exe file? Perhaps you cannot run executable files due restrictions, or perhaps you are not running Windows at all.

How it works

The web application is completely javascript-based. It is open source (MIT license) and the code is freely available at github:

The most important part of the solution is the formula. The original formula (C#) is provided by Vlad Catrinescu is as follows:

SQL Max Memory = TotalPhyMem – (NumOfSQLThreads * ThreadStackSize) – (1GB * CEILING(NumOfCores/4)) – OS Reserved
NumOfSQLThreads = 256 + (NumOfProcessors*- 4) * 8 (* If NumOfProcessors > 4, else 0)
ThreadStackSize = 2MB on x64 or 4 MB on 64-bit (IA64)
OS Reserved = 20% of total ram for under if system has 15GB. 12.5% for over 20GB

When you’ve filled in the values in the form, you’ll see the max memory in the blue box above:

sql-max-001

How to apply this setting in the SQL Server

To apply the max memory follow these steps, the instructions come originally from technet:

  1. In Object Explorer, right-click a server and selectProperties.
  2. Click the Memory node.
  3. Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.Use the default settings to allow SQL Server to change its memory requirements dynamically based on available system resources. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 megabytes (MB). The minimum amount of memory you can specify for max server memory is 16 MB.

Contributors

Improving the web performance of an intranet

overloaded-sharepoint-in-browser

All the “small” app parts, web parts, delegate controls, user controls, and other “packages” that “must” be delivered to the users on every page load of the Start Page of your Intranet.

Recently we made an investment to improve the performance of our intranet. We made many changes in different layers: SQL, Network, Browser upgrade and code. Here I want to tell about what code changes we did to improve the web browser performance. Please leave feedback if you find it useful. You can also share your suggestions. We measured the performance before our code changes and after them. We had amazing results. Unfortunately I can not share any numbers, but we improved the Time to First Byte, time to load event firing in the browser, memory consumption in the clients and, perhaps, the most important, we improved the perceived performance of the Intranet, the way how users experience the speed and UI responsiveness. To do this I got many ideas of my project colleagues and branch colleagues. Here is the list of changes we’ve implemented:

– Execute code on demand

Consider this scenario: on a page users can click on a button to download a vcard. Aggregating user information is a costly operation that requires getting data from the User Profile Service, getting the profile image from SharePoint. Don’t ever do this operation on page load. Move the code to the “onclick” action. In other words, work when it is needed. It is like cooking a lot of food, when you are not hungry. It is nothing new, unfortunately there were a couple of the “eager code” places.

– Cache results, investigate what parts can be cached and how fresh data needs to be

All data on your page doesn’t need to be fetched on every page load. In our project We listed all the “parts” of the start page and other often visited pages and went to the business and asked them to evaluate how fresh data should be. Some parts should be as fresh as possible (no cache), whereas it would be tolerated that the information could be dirty (cache up to one week or more).

– Reference javascript and css files from one location

Do you have jQuery in your SharePoint Intranet. How many copies do you have? What we did in our Intranet was that we partially implemented the CDN concept. Even though we don’t distribute our resource files geographically, we have 1-to-1 relation between a file and its absolute URL. In the whole intranet, we have only one jQuery url and only one our intranet.core.js url. We did by creating a dedicated CDN site collection. This alone makes a big difference. To evolve the idea we could provision resources outside SharePoint to remove the authorization overhead. We could also distribute it geographically by having files closer to the end users.

– Unify your framework and your dependencies

In our projects we had a couple of SharePoint-hosted apps developed by different teams. We had different approaches and different framework that solved almost the same problem: AngularJS and KnockoutJS. Eventhough apps are independent pieces of software, they were used within the same page (as app parts). It was too much http traffic. We agreed on Developer Guidelines and chose one framework.

– Do not hide controls on the page with CSS, remove them

On the start page in our intranet we didn’t show the left navigation, but it was still rendered in code behind. Instead hiding it with CSS, we just removed it by an empty ContenPlaceHolder in our Start Page Layout:

– Optimize jQuery Selectors

We reviewed all the jQuery code and improved the selectors. Optimizing the selectors will improve the overall performance in the browser, especially in older browsers. The worst example is using text selectors, like this one:

It will sink your IE8 browser.

– Minify javascript and CSS files

Minifying resource files like javascript and css is not hard. My recommendation is to use Web Essentials plugin in Visual Studio. Alternatively you can use the SharePoint Assets Minifier.

– Use the weakest selectors in CSS and in LESS

In our project we are using LESS. With LESS it is easier to write  readable CSS code. But be aware of the output. Do not make the selectors too strong: Use the weakest CSS Selectors. The weakest selectors will make it easier to maintain the CSS and it will minimize the amount of KB the server needs to send to your users’ browsers.

– Ensure javascript and CSS files are cached

JavaScript and CSS files should be cached. You should also avoid 304 responses where the Server answers “Not Modified”, because this has an impact on the performance. Configure the Blob Cache and put your resources into the Style Library.

– Remove all app parts from SharePoint-hosted Apps from the start page

There can be exceptions, but we encountered that client web parts (app parts) from SharePoint-hosted apps had a huge impact on the performance. The combination of a couple of app parts on often visited pages (like the start page on the intranet) led to long page load times. These are the reasons why you should not have SharePoint Hosted App Parts on your start page:

  1. App Parts are iframes. They are loaded simultaneously if you add client web parts (app parts) in a usual way. They hold up the whole page. Users cannot interact with the intranet page until all the content in all app parts has been loaded. This can be partially improved if you introduce a delay in the app part loading, by developing an own engine. See my blog post where I mention such a concept: AppLoader Concept for SharePoint apps.
  2. The content from a SharePoint AppWeb is not fully cached. If you examine the http traffic from the apps you’ll see a lot of 304 responses, meaning the browser requests when the server answers that there is no newer version. This has an impact on the performance. See more in Alik Levin’s blog: ASP.NET Performance: Get Rid of HTTP 401 and HTTP 304. In a SharePoint-hosted app you don’t have any control what so ever to adjust the the cache settings. This is not the case in the Provider-hosted apps.
  3. SharePoint-hosted apps can only use javascript. The code is executed on the client. Older browsers like IE9 or IE8 render the pages slower. The Start Page that is slower than the rest of the Intranet is not something that will engage your users.
  4. App Parts are iframes that do not know about their dimensions. App parts often need to update the height and the width  of the parent iframe. This causes irritating flickering. Perhaps OK on some pages, but I’d say totally unacceptable on the start page of your brand new intranet..

What did we do instead of App Parts on the Start Page? We converted them into Script Editor Web Parts, the app parts were only one-time parts, they only were used on the start page.

Do not get me wrong. What we did was not abandoning apps as a model, we just removed wrong apps, apps that cannot be reused, the SharePoint-hosted apps that had big performance issues. I am looking forward creating right apps, that are written with performance, reusability, scalability and good design in mind.

 

The CDN concept in SharePoint

How many instances of jquery are there in your SharePoint farm?

Have you more than two (jquery and jquery-ui), then you have too much. You can save much place and performance by using Content Delivery Network (CDN) links for the resources like javascript, css, fonts and icons. Consider those Content Delivery Networks:

CDN for custom resources

But can we benefit from this CDN concept for our custom resources? I think so, if your farm has ten thousands of site collections, and you deploy javascript files, css files in the Style Library, it would be great to eliminate resource duplicates. So my thougt is to deploy resources to one place. It could be:

  • An external web application static.contoso.com like many web applications do
  • A dedicated site collection for resources cdn.contoso.com with Anonymous access
The CDN concept in Office 365

Have you noticed that Office 365 uses cdn links for almost all SharePoint javascript files that traditionaly were referenced from the _layouts folder

cdn-001

With this I want to raise a question. What do you think about the CDN concept within SharePoint? Have you used it? Have you plans to have it?