Using SQL Server Resource Governor To Optimize SharePoint 2013 Performance

When first introduced, SQL Server did not quite had the number of value added features that it boasts of today. Some of the features that have been added include Query Editor T-SQL debugger, schemes for alternative keyboard shortcuts, Query Editor Snippets, advisor for Database Recovery, GUI for Extended Events, Query Editor IntelliSense, and so on. Resource Governor, which was a new addition to SQL Server 2008 provided internal mechanism for management along with control of resource consumption through processes and connections. To control management of resources, previously we had to resort to Windows System Resource Manager but Resource Governor has made the job easier.

Resource pools, classifications along with workload groups comprise of the Governor. The server's physical resources are collected by the pools and then they can be used for workload group assignment. Groups of connections that are classification based are known as workload groups and they belong to a single pool which is limited by the intake of physical resources that it can perform. This results in exertion of a holistic control over the impact level of user connection on server.

With the enhancements made to Resource Governor you will be able to govern performance effectively, especially in case of machines that have many CPU cores. The following are the enhancements that have been made:

• A new option called CAP_CPU_PERCENT for pools that will allow means for setting limits on usage of CPU resources, providing greater predictability.
• Another option called AFFINITY meant for resource pools that will allow mechanism for affinitization of resources to NUMA nodes as well as schedulers, providing better isolation of resources of CPU.
• Support for almost 64 resource pools which is far more than the 20 which was previously supported so that larger machines can be better partitioned between increasing workloads.

The Governor can be implemented very easily. First, enable it by right clicking and selecting 'enable'. This will create two default resource pools along with two default groups of workload. The workload groups are assigned to the resource pools; so configure them properly and the feature of the Governor will start functioning.

The performance of SharePoint 2013 can be optimized or improved by using Resource Governor. Search Database can be optimized in a number of ways. Amongst them, the one with the use of Resource Givernor is the best. When search crawling takes place, apart from the crawled web servers which are hammered, a lot of resources of SQL Server are used. If there is only a single SQL Server, it will affect your databases and you surely do not want your users to get annoyed if they have to bear with slow SharePoint farm during their work. Using the feature will ensure limited RAM or CPU usage of Search databases during average working hours.

The feature can also be configured so that search databases' resource usage may be limited during working hours, but not during non-working hours. Executing the code in SQL Server Management Studio will give the desired results. Resource Pools need to be created for this, one for off-work hours and another for under-work hours. Next two Workloads Groups must be created and then associated with the Pool that corresponds with it. Next, Classifier function has to be created so that the feature chooses the correct Workload Group. The function may be adjusted according to your needs as well as requirements. Verify the set up.

For the best results the entire configuration of the Pool along with Classifier logic should be tested as well as verified so that they work optimally within the environment. One thing to remember while working with the Governor is that it can work only with Database Engine; support is not provided for processes like Reporting Services, Integration Services or Analysis Services.

You can hire developers from top database development company in India who can help you build your projects within allocated budget and time schedules.

We provide SQL server application development services. If you would like to know more about the expertise of our SQL server developers, please get in touch with us at Mindfire Solutions.

Different Versions of SSRS and What Fits Better for Businesses

SQL Server Reporting Services or SSRS comes with great many features to meet the reporting needs of complex businesses. So far as creation of reports is concerned, SSRS is a full-fledged report engine. The reports can be generated against any data source which consists of OLE DB, ODBC data source or any managed code provider. This implies that data can easily be retrieved by a SQL database developer or by a user versed with SSRS from Oracle, SQL Server, Access, Essbase, Analysis Services or lots of other databases. The data can be brought forth in a number of ways.

Taking feedback from SSRS 2005, Microsoft enhanced SSRS 2008 with new Chart as well as Gauge controls, along with a brand new Tablix control, that is in fact a blend of the Matrix and the Table controls from the prior release. Collectively, these fresh presentation formats offer a completely new experience that is also something innovative. Additional enhancements consist of new formats of output presentation, including Excel and Word, as well as straightaway assimilation with SharePoint. Before discussing the different version of SSRS, let us have a look at the main features of SSRS.

Prime features of SSRS

• Data Retrieval from managed providers, ODBC and OLE DB connections
• Display of Data in a number of modes, including charts, free form and tabular
• Export to a number of formats, such as PDF, HTML, CSV, XML, Word reports, TIFF and Excel
• Summarization and Aggregation of Data
• Add in report navigation
• Creation of ad-hoc reports and saving the same to the server
• Embedding images, graphics as well as external content
• Integration with SharePoint
• Offering a SOAP or Simple Object Access Protocol API (application programming interface) along with pluggable architecture
• Generation of on-demand reports and subscription-based reports
• Facilitating management and storage of custom reports by users, generated with Report Builder 2.0 of SSRS.
• URL-oriented access of report
• Availability of Gauge as well as Chart controls for the display of KPI data

As is obvious, SSRS offers an all-inclusive list of innovative features. Again, another striking aspect of SSRS is its extensible nature. This kind of extensibility empowers developers to make use of SSRS in a variety of ways, from tailor-made reporting solutions to embedded reports.

Versions of SQL Server Reporting Services

As far as SSRS is concerned, there are four versions or editions, which tend to mirror those of Visual Studio and SQL Server. These versions incorporate free starter editions as well as full-fledged Enterprise versions. Let us have a quick overview of the different editions:

• Express: This edition provides a lightweight version of SSRS for those developers who intend to understand the procedures of using SSRS.
• Workgroup: This version is meant for use within small branch offices or departmental organizations. In case there is any need, up gradation of the Workgroup Edition to the Enterprise or Standard Editions can be done.
• Standard: The edition is utilized in medium to small sized enterprises or within a single server ambience. The Standard Edition renders supports to all the available features of SSRS, with the exception of extremely specialized data-oriented subscriptions and Report Builder driven infinite drill down.
• Enterprise: The Enterprise edition is highly suitable for large organizations which work with complicated reporting needs and databases. This version is completely functional and renders support to scale-out functionality across the web farm.
• Developer: Generally speaking, the Developer version is almost similar to the Enterprise edition, but possesses distinct licensing requirements to facilitate the development of enterprise applications by people. In fact, this edition is licensed with regard to each developer within non-production or development environments.

Thus we have seen the prime features of SSRS and understood how the different editions suit organizational purposes. Each version has its own specialties that attend to particular business needs.

We provide SQL server database development services. If you would like to know more about the expertise of our SQL server developers, please visit Mindfire Solutions.