Sunday, 25 January 2015

Test and Monitor your SQL storage performance and some good practices for SharePoint.

In the last couple of weeks I've been busy with analyzing and optimising one of our customer SharePoint 2013 deployment. This is a huge topic to discuss, SharePoint itself is not a slow application. However, there might be countless reasons that can make your SharePoint work and feel slow. In my case the source of the performance issues was the SQL.
You should pay as much attention to your SQL when you plan and deploy your SharePoint solution, as other components. The SQL server is referred as  the storage tier for SharePoint. Except the binaries and the static application files SharePoint stores almost everything(94% to be accurate) in the SQL, after all SharePoint farm is just a Configuration database and all your user content.sites,webs are stored in content databases.
Now, I am not a SQL Server DBA nor a storage expert, but for me the storage that hosts you databases is one of the most important components for the performance of your SQL. If your RAM is enough for your workload,you have enough CPU power or you have followed all good practices for SQL that hosts SharePoint, but if your storage needs more time accessing and delivering certain data(latency) or the debit (IOPS) is not small enough or big enough the SQL will work slow, therefore your SharePoint will work and feel slow.
In this post I am going to give you links and short description to articles and tools(free/open source) you can use for testing and monitor your SQL storage at the planning phase or as it was in my case after the system was deployed.
I mentioned the good practices for SQL that hosts SharePoint. Well if you do not know what this practices are you can learn about them from the following resources:

    1. Tuning SQL Server 2012 for SharePoint 2013 - This are MVA videos hosted by Bill Baer(Senior Technical Product Manager) and Brian Alderman. In this demo-rich videos they will cover some of the best practices for integrating SQL Server 2012 and SharePoint 2013

    2. Maximizing SQL 2012 Performance for SharePoint 2013 Whitepaper - This is a great whitepaper for the good practices that can maximize your SQL Server performance for SharePoint. The author is Vlad Catrinescu (SharePoint MVP) that covers most of the points from the videos above, plus many additional.

I had to implement lots of this practices before going to investigate any storage bottlenecks.

Now I would like to bold on one thing that has became more important with the development of the Cloud services. Distribute your database files on as many disks as possible. Every cloud provider I've been working with limits the VHDs by IOPS. For example in Azure class A and D VMs you have 500 IOPS per disk. However with the different VM sizes you can attach different number of disks. With A4 for example you can attach up to 16 data disks and that will make 16x500 IOPS. So why not attach as many disk as possible if it will not affect your budget(most of the providers charge storage per GB)?

One of the first thing to check if you are experiencing SharePoint slowness and you are suspecting slow SQL is the "Developer Dashboard". There you can find the SQL execution time and many more useful stats for your request execution.

Developer Dashboard

You can find information how to enable and use the dashboard for SharePoint 2013 in following Article.

At the planning phase of your solution, before you have installed anything you can use various tools to test the performance of your storage, here are some of them:

     1. SQLIO - In this article from Brent Ozar you will find download link for the tool and how to use it. The tool is really straightforward and can show you basic performance information for your storage, IO/S, MBs/S and the latency from your output. In this article you will also find links for other tools like SQLIOSim that can simulate SQL Server OLTP and OLAP.

     2. DiskSpd - Now this is more robust tool for testing your storage. With this tool you can test local disks and SMB shares as well. This is a great article from Jose Barreto (member of the File Server team at Microsoft). There you will find everything about DiskSpd. The tool also comes with very comprehensive documentation. However this is a powerful tool  and you should not run it against storage with live data because it can destroy it.

Tools that you can use to monitor after system deployment:

     1. PAL  - This is a great universal tool for Performance Log Analysis. It is written by Clint Huffman (PFE at Microsoft). It is a tool not just for SQL, but for number of products. If you do not know how to interpret the performance logs for certain Microsoft product or you need aggregated performance data report , the PAL is just the tool for you. PAL is working with templates for the different products. If you do not know what PerfMon counters to capture you can export the PAL template to PerfMon template and then you can create regular Data Collector Set in the Performance Monitor MMC. You can see an example for PAL HTML report below.

PAL Report Sample


     2. SSMS Activity Monitor - This is a great tool that you can launch in the SQL Server Management Studio. Just open SSMS right click on the instance and open Activity Monitor. There you will find current stats for Overview, Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries. If you do not know how the Activity Monitor looks like you can see it below:

Activity Monitor

     3. Triage Wait Stats in SQL Server -  Here is another article from Brent Ozar where you can find an explanation of what Resource Waits are, a script that can capture stats for a period of time and references to other articles on the subject. For me the Waits are very important thing to look at when it comes to SQL performance in general.

     4. Capturing IO latencies for a period of time - This article is last but not least. It is written by Paul Randal from www.sqlskills.com. There you can find a script that for me is the ultimate tool to identify some storage bottlenecks. Because it is not mentioned in the article you can set the time period with changing WAITFOR DELAY. In the output you can see the average latency (Read/Write) per file Average Bytes for Read/Write operations and more. You can see example output below.



And here are the final notes. SharePoint is application where we cannot edit some DB schema for example. It is enterprise product, not some inhouse build. If you have followed above mentioned Best Practices, you have correctly scaled your deployment, you are sure that there is no customisation that can cause issues, but you suddenly receive performance hit most, probably it is caused by other systems that are more likely to change like storage,network, Windows etc.. And of course there is no magic number or silver bullet to fix all issues.
If you are just SharePoint Administrator and deep diving in SQL and storage is not your responsibility, notify and push the SQL and Storage guys to do their work and fix the issues. If you are not sure with above mentioned tools, test them in non-production environment first. As far as I understand T-SQL the above scripts do not touch any user database.

I am hoping this was useful for you.

Sunday, 18 January 2015

Unable to Update/Uninstall/Repair SharePoint binaries. Find out if files are missing from 'C:\Windows\Installer'

I guess that some of the things I am going to tell you in this post are well known for some of you that has been involved in Windows platform maintenance in recent years.
However this can happen, it can happen in your SharePoint environment, can happen in Production and you should be prepared for it.
So, last week I was helping a colleague of mine that has an issue in one of our customers production SharePoint 2013 servers. The farm has several language packs installed, but for one of the servers it was showing that this language packs are not installed. There was no chance this server to be missing binaries. We checked the "Product Version Job" timer job. This job by default runs every night on each SharePoint server and it should check the installed products and versions. If there is server where some binaries are not installed or they are lower version you will see some red colored alerts like the one below. However, it is possible that this job is unable to get the product data, because it runs under the Farm account identity and it needs local administrator privileges to retrieve the product data. In this case you will see event log warnings like the one described in this WiKi, you can put the account in the local admin group, restart the Timer Service and run the job, but this is not recommended. You can run the job with different identity if you log in to the server with proper account and run the command Get-SPProduct -Local and you will be able to update the data for the products installed on the server, this can help if PSConfig is blocked because of "missing products".

Missing Language Pack

Unfortunately this did not helped us in this case. We decided to run PSConfig on the server in question and oh surprise it was blocked by missing products on the server.
Logically we came up to the idea to Uninstall/Repair and reinstall the language packs.
However we were unable to do so from control panel nor by running the installation packages.
This was really strange, then we figure out that there might be some files missing from 'C:\Windows\Installer'.
This folder stores the Windows Installer Cache. The folder is hidden by default and it contains some very important files for every software that was deployed on the system using Windows Installer technology and this are all products and updates that are coming from Microsoft. The files are unique for every machine and they are not interchangeable. If you are not aware for the importance of this files it is possible to think that this are some temporary files that can be deleted. 
You will be seriously wrong! If there is missing files from this folder it will block the Update/Uninstall/Repair of the product  and your server is in "out of support" state. The guidance from Microsoft for such cases is very general. You need Machine Rebuild.
Apparently something has happened on our server. The support and maintenance of the Windows platform for this customer is delegated to a different company, so it is not possible someone from us to delete/remove/rename some of the files in that folder.
This situation is described in KB2667628, there you will find guidance for a very nice Microsoft Support Diagnostics tool called Windows Installer Cache Diagnostic. Run the tool on the server and it will generate a CAB file that contains nice reports on the installer cache. If there are no missing files you will see this:

No Windows Installer Cache Issue

If you have missing files you will see something like this for every missing files. Notice the bolded table row Action Required, it says "This .msp appears to be missing. Machine needs to be rebuilt."

Missing files from Windows Installer Cache

With this reports you have a strong weapon against the platform support teams :). 
However you will need to rebuild this server. I am not sure if restoring the files from backup will be supported. If you have not experienced such issues I think that it is good idea to run the diagnostic tool to check all your servers, it is fairly fast and simple.

Tuesday, 13 January 2015

Change the URL of existing SharePoint List/Library[Tip]

Let's say that a site admin has created a ShatePoint library (or a list) with name CrappyOldUrl. Then the user decides that this is not an appropriate name for his library. The user can easily change the Name of the library from the Library Settings and changes it to Nice New Library. However the url of the Library is still /CrappyOldUrl/ and he/she calls you for help to change the url to something like http://<yoursite>/Nice New Library/.

Crappy Library Url

There are many articles that will tell you to use SharePoint Designer, Save the library as template and recreate it or something else. But you are lazy SharePoint administrator, for you here is a way to do this in PowerShell.

$web = Get-SPWeb http://portal-2k8r2.ilabs.l/ ## the Url of the Web
$list = $web.Lists["Nice New Library"] ##Get by current Library Name
$list.RootFolder.MoveTo("/Nice New Library") ## Change the Url(relative to the Web)


And here is the result. Be careful because the Name of the library may be changed as well if it is not the same as the new url.

New Library Url

In this case we are renaming the Root Folder of the library the original URL of the library in this example is http(s)://<WebUrl>/<LibraryRootFolder>.
However if you are renaming List it is likely that the URL of your list is http(s)://<WebUrl>/Lists/<LibraryRootFolder> in this case if you do not want to put the list in the Root folder and keep the .../Lists/... The code will be as follows:


$web = Get-SPWeb http://portal-2k8r2.ilabs.l/ ## the Url of the Web
$list = $web.Lists["Nice New List"] ##Get by current List Name
$list.RootFolder.MoveTo("/List/Nice New List") ## Change the Url


You can see current RootFolder with something like this:


$web = Get-SPWeb http://portal-2k8r2.ilabs.l/ ## the Url of the Web
$list = $web.Lists["Nice New List"] ##Get by current List Name
$list.RootFolder

See the value of property ServerRelativeUrl.

[*UPDATE*]: Check out a Video Tutorial of this solution HERE