Monday 27 November 2017

Improve the performance of SharePoint 2013 web application

  • Blob Cache has been set for 24 hours
Parameter needs to be set is “max-age” under blob tag in web.config file of the application
  • Http Response header removal to enable caching of the web application
1. Param Expire 0
  • CSS Registration under “<Sharepoint:CSSRegistration” tag which helps to cache the css in SharePoint 2013
  • Cache Profiles, Site Collection Object Cache, Site Output Cache, Page layout cache
  • Trouble Shooting
1. Under “Safe Mode” callstack =”True” but could impact the performance of the application
2. Under “Compilation” tag in “web.config” setting Debug=”True” will impact and disable the caching  of the application on “Client Side” and “Server Side”
3. Set Connection string under connection settings in web.config file using “Secure Connection string” command for more search on Google
  • Set Default time zone of the application under SharePoint 2013 otherwise it will be running timer jobs as per the server time zone
1. For e.g.: if server is in USA and Client is in India the timer job and Default time zone is not set to the client country time zone then the timer jobs will be referencing the server timings of the hosted country and which will run at any time perhaps the Indian Market hours which could impact the performance of the application and can be faced by the end users complaining of slowness of the application.
  • Secure Store Service
1. To share the credentials to developers without providing the confidential username and password by proving the secure store service key to the developer will do the job. The key will authenticate the BDC application to communication with external databases with the auto generate key by SharePoint 2013 under secure store service
  • List View threshold should not exceed the limit of 5000, however 30 million records can be saved in the list
  • Search Query v/s CAML query
1. Search Query is used to fetch large number of data
2. CAML query cannot be used to fetch large no of data
3. Search Query: For an instance we have to crawl 3 columns from list which will create 3 search properties to query data using managed metadata service via using search query
  • Below Path to create search query crawl
1. CA->Managed service application->Search Service application->Search Schema
  • App pools recycle introduced in SharePoint 2013 it was not present in SharePoint 2007
1. Http.sys kernel mode driver accepts the request from client browser
2. W3wp creates queue as per app pool, which will have two queues Input and Output queue.
3. Whenever an app pool is recycled the old w3wp will stop taking client request and the new request will be diverted to the newly created w3wp. If old w3wp consists of any request and all the request gets completed under the declared Shutdown time limit in IIS will forcibly close the old w3wp. The only drawback of app pool recycle is whenever the request switches to new w3wp the whole process of caching starts from the beginning which slowdowns the process of load site on client end and users observes a slowness while loading the website.
  • HTTP 1.1 SP 2013
1. 1.0-> 4 concurrent connection to minimize the overhead of response
2. 1.1-> Keep Alive (gives response faster)
3. 2.0-> used by Google
  • Health Usage and data collection service consumes more resource which impacts the server   performance under CA
  • Optimization of web part fetching large list
1. Where clause have to be assigned on Indexed column of the list
2. Having Index column and where clause assigned to the same column in the list, however it’s still affecting the performance while loading the web part then check the threshold limit of list that it has crossed 5000.
3. CAML query without where clause

Unable to restore database "..because it is in use by this session."

Restore failed for server restore cannot process database because it is in use by this session

Your login to the server cannot have the database as its default. Go to Security > Logins > your login user. Right click to get properties and where it says Default, put the database back to "Master". It doesn't take you out of the other db, just takes it out of that connection for the restore. I'd routinely restored but must have one day changed the default thinking it wouldn't hurt anything. Now I know it hurts restores to that db if I log onto the server as that connection with that default! Hope this helps clear it up.




Reference : Unable to restore database "..because it is in use by this session."

Getting exclusive access to a SQL Server database for restore

Overview
When restoring a database, one of the things you need to do is ensure that you have exclusive access to the database.  If any other users are in the database the restore will fail.

Explanation
When trying to do a restore, if any other user is in the database you will see these types of error messages:

T-SQL

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


SSMS



Getting Exclusive Access

To get exclusive access, all other connections need to be dropped or the database that they are in needs to be changed so they are not using the database you are trying to restore.  You can use sp_who2 or SSMS to see what connections are using the database you are trying to restore.

Using KILL
One option to get exclusive access is to use the KILL command to kill each connection that is using the database., but be aware of what connections you are killing and the rollback issues that may need to occur.  See this tip for more information on how to do this.

Using ALTER DATABASE 
Another option is to put the database in single user mode and then do the restore.  This also does a rollback depending on the option you use, but will do all connections at once.  See this tip for more information on how to do this.

Use Master
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE DatabaseName FROM DISK = 'C:\DB Backups\Database.bak'
GO

Link: Getting exclusive access to a SQL Server database for restore