SSMS – Proactive Management Tools in a Single Console

How to keep up data platform predictable performance?

Is it possible to have a built-in capabilities that help make performance more consistent and predictable?

No doubt to say an exponential growth within server consolidations and multi-tenant data platforms managed within on-premise and cloud environments, organisations (in specific to DBAs) need a way to ensure predictable performance can be provided across workloads and multi-applications layer. This is not just an expectation to keep up, but managing a resource allocation is complex and a pro-active actions are necessary. Since 2008 version days certain tools and features in SQL Server can help us to manage the show in real time with minimal impact on these multi-faceted data workloads.

Not just with shiny GUI tools that we have from SQL Server, recent addition of Column-store index technology is match-able for data warehouse data-sets to provide enhanced scalability for typical data warehousing type queries.  As we are talking about tools let us track back into new capabilities that will help us to perform proactive management and performance loss troubleshooting with built-in tools in SQL Server Management Studio (SSMS). Being a DBA you don’t need any introduction to the tools such as Data Collector, Policy-Based Management, SQL Server Profiler, Multi-Server Management and Resource Governor.

To give better understanding here goes highlighted information about these tools (source: SQL Server 2012 Performance Whitepaper):

Data Collector

The Data Collector in SQL Server 2012 provides a central point for data collection across your database servers and applications. The Data Collector is a system that can collect data from any T-SQL query, like DMVs, Windows Performance Counters, and the SQL Trace, and it bundles any selection of these items into a Collection Set, which can be configured to meet your requirements.

Policy-Based Management

Policy-Based Management enables you to define and enforce common, enterprise-wide policies for the SQL Server instances you manage throughout the organization. With this capability, you can create policies for the management of entities on a server, such as the instance of SQL Server, databases, or other SQL Server objects. Policy-based management can help to enable a number of useful scenarios, such as imposing the surface area configuration settings of one database instance to another, creating and enforcing naming convention policy, and supporting scalability by exporting and importing policies or by applying policies to a server group.

SQL Server Profiler

SQL Server Profiler gives you the ability to monitor and capture server events for real-time diagnosis. It does this by showing how SQL Server resolves queries internally, which enables you to see exactly what Transact-SQL statements or Multi-Dimensional Expressions are submitted to the server, and then how the server accesses the database or cube to return result sets. You can configure SQL Server Profiler to monitor only the events of interest to you, and you can filter traces based on the information you want, so that only a subset of the event data is collected. This is helpful when traces become too large. Monitoring too many events adds overhead to the server and the monitoring process, and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

Database Engine Tuning Advisor

SQL Server Database Engine Tuning Advisor (DTA) helps to improve query processing by selecting and creating an optimal set of indexes, indexed views, and partitions without requiring an expert understanding of the structure of the database. Database Engine Tuning Advisor analyzes a workload and the physical implementation of one or more databases, and it can recommend adding, removing, or modifying physical design structures in your databases. The advisor can also recommend what statistics should be collected to back up physical design structures, such as clustered indexes, non-clustered indexes, indexed views, and partitioning.

Multi-Server Management

SQL Server 2012 includes multi-server management capabilities that improve insight into resource utilization to help identify potential issues and make proactive changes before problems occur. SQL Server Management Studio provides dashboard and drilldown utilization views of instances and applications, and it enables quick set up and instance enrollment into a central control point. Policy evaluation and trending analysis of instances and applications enable you to gain the insight needed to ensure that resources are fully optimized.

Resource Governor

With the increasing trend toward server consolidation and multi-tenant environments found in private and public cloud environments, organizations need a way to ensure consistent performance across workloads and applications that share the same server. Managing resource allocation in these scenarios can be complex.

Dynamic Management Views in Resource Governor enable you to monitor and manage CPU and memory utilization in multi-tenant environments—and even provide information and controls that can be helpful in charge back scenarios. Resource limits can also be reconfigured in real time with minimal impact on workloads that are executing.

Consistent Performance for Multiple Workloads In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested based on the limits that you specify. This helps to ensure consistent performance for concurrent and mixed workloads.

Increased Resource Pools

A resource pool in the Resource Governor represents the physical resources of the server, and each resource pool can contain one or more workload groups. In SQL Server 2012, Resource Governor has increased the number of resource pools to 64, benefiting multi-tenant environments where resources need to be managed across a large number of distinct workload groups.

Enabling a Hard Cap on Resource Usage

In SQL Server 2012, Resource Governor enables you to create a hard cap on CPU resource usage. This is an important capability because it enables you to not only assign and monitor resources, but also to manage and enforce restrictions on resource usage. Affinitized Resource Pools Resource Governor enables you to affinitize your resource pools for CPU schedulers, groups of schedulers, and NUMA nodes.