SQL Server Performance Tuning in version 2.3

New in version 2.3 is the extended cached execution plan view. This screen has been redesigned to allow for drilling down from execution plans to their constituent operators and their details. This workflow exists with 1 task in mind – performance tuning.

This screenshot displays an average performance tuning workflow – execution plans are ordered by their execution time in the top pane. The plans operators are listed in the middle grid and then the operator details in the bottom pane:

sql server performance tuning workflow

performance tuning workflow

The green arrows indicate the drilling down from a list of execution planes to their operators and details of each.

The 4 tabs at the top of the screen let you start by selecting to see execution plans that are either the slowest, most recent, have expensive CPU or expensive IO/Disk cost.

The middle pane’s list of operators may be the biggest time saver on this page as the operators are ordered by estimated cost %. This is the same as SQL Management Studio’s operator cost but instead of being displayed in an unordered hierarchy tree view they are listed here by cost. No more having to follow graphical execution plan trees by the thickness of their line back to the offending leaf node:

execution plan operators

Execution plan operators ordered by cost

Each grid row when combined with the details pane at the bottom of the screen display the equivalent data to what Management Studio displays in it’s large tooltips.

Obviously taking corrective action based on the data presented above is not done for you but once you modify your schema or code to attempt to correct the problem you can just hit refresh at the top of the screen and see what difference your changes have made.

The new version of this screen also offers several other new features outside of the core performance tuning workflow. The T-SQL statement is fully available along with it’s XML execution plan and a button to automatically start sql management studio and load the execution plan into the graphical explorer.

Each operator’s XML execution plan can also be viewed by opening the ‘operator XML’ tab.

Other tools that miniDBA has to offer compliment this screen when it comes to performance tuning, such as the server and database level healthchecks which will notify you of problems in your T-SQL and the index useage screen to help with index design.

Version 2.1 Released (SQL Server mobile monitoring)

A lot of work has gone into the new Web Monitor website that comes bundled with miniDBA Server as well as hugely expanding out the feature list of miniDBA Desktop.

SQL Server Mobile Monitoring

This release offers the users the ability to consume their SQL Server monitoring data on any mobile device as well as use the rich miniDBA Desktop when on premises. Out of hours/on the move monitoring just got a lot easier for everyone that needs to know how their databases are performing no matter where they are.

SQL Server Web Monitor Mobile

Web Monitor on Mobile

SQL Server Web Monitor on tablet - SQL Server mobile

Web Monitor on Tablet

High level real-time performance data can be viewed in the Web Monitor application as well as all alerts. Full administration of miniDBA Server can be performed through the web UI – including configuring alerts, adding/removing servers, updating options and writing custom T-SQL queries for alerts.

Also the low level details available have been expanded, especially around memory details with a new memory dashboard that includes operating system memory usage.

Buffer physical IO, free list stalls, hard faults and memory grants pending are all new low level metrics that will help resolve memory issues.

SQL Server Memory Dashboard

SQL Server Memory Dashboard

Version 2.1 New Features

Desktop

  • Memory pressure property
  • Custom SQL alert configuration
  • Extra history graphs
  • Link to setup email from alerts page
  • Can filters dbs out of tree and summary pane
  • CPU scheduler history graphs
  • Memory Dashboard redesign and new metrics
  • IO dashboard reliablity bug fixes
  • TempDB log always visible on server dashboard
  • Enterprise node now has number of active alerts
  • DB summary screen has trans sec column
  • Server connectivity more reliable – UI components now always correct
  • client server conn dialog error better displayed
  • faster parallel loading of logs
  • T-SQL colour highlighting

Server

  • SQL Server mobile monitoring – Web Monitor introduced
  • Service starts with better default windows logon
  • License tool for server
  • Provides historic data – not just realtime

Misc

  • New alert: memory pressure
  • Loading historic data time reduced by 500%
  • log extra counters
  • much more efficient waits and full text requests
  • Logs total max size now actually deletes oldest file if necessary
  • Recording of deadlocks

New Feature: Export SQL Performance Data To Excel

The ability to extract performance data quickly and efficiently from the SQL Servers you are working with and share it with others in your organisation has been near the top of the requested features for quite a while. Well now it is here and the magic “Export Excel” button does exactly what you’d expect it to (as long as you have Excel installed on your workstation). If you don’t have Excel a csv is produced which can be sent to co-workers that do have Excel – or any other means to view csv data.

For every screen in miniDBA Desktop that has a data grid, the button is enabled and will export data from the active data grid into a new CSV file (you can choose where this is created on the options page). Excel will then open to show you the data.

The example below shows the expensive CPU queries datagrid – the export excel button is enabled (shown in red) as a datagrid is being displayed:

exportexcelbutton

The Excel file generated from the SQL data is given the name of the datagrid plus the date and time when generated so you can easily identify what the file is if it is in a folder of many generated files. The Excel file looks like the following:

excel data generated from sql server data

excel data generated from sql server data

Having the ability to generate a report such as this slowest sql queries and very quickly have an excel file you can share is a feature that will save teams time and effort. There are over 40 different data grids in miniDBA Desktop that can be exported to csv/Excel.

Whether it be for sharing live performance issues such as blocking queries or you are performing an audit on your SQL Server and want to share failed logins or total wait statistics, being able to share the data easily will be valuable to you.

The files are by default created in your “My Documents” folder. This can be changed using the “View->Options” menu and selecting another folder.

 

New Feature: Azure SQL Database monitoring in miniDBA Desktop

Real Time Metrics

miniDBA 1.5.2 has introduced the ability to monitor and performance tune a Azure SQL Database. This new functionality puts in reach a tool for the new wave of application developers working on the Azure platform that have not had a viable development tool for Azure database performance until now.

Low level, real-time SQL performance data can be viewed in graphs and live updating data grids. Schema/index/table/wait statistics level data can be used to aid development and fixing of performance issues.

azure sql database server dashboard

Azure specific information (bandwidth, throttling, storage, cpu per hour) can be seen on the top level Azure server dashboard. The majority of the existing functionality has been included for Azure database server, which can be seen in the nodes below the server, including an Azure specific health check and logging. This new functionality has been added as part of miniDBA 1.x and does not effect the price off the application, existing users get it as a free upgrade.

SQL Alerts

Administrators also have the ability to monitor multiple Azure servers/databases with SQL database specific metrics and alerts.  List of alerts:

  • Database off-line
  • Blocking Processes
  • Deadlocks
  • Long Running Queries
  • No Connection
  • Maximum Connections
  • Azure Storage Mb
  • Cpu Per Hour
  • Wait Ms Per Sec
  • Bandwidth Ingress Mb Per Hour
  • Bandwidth Egress Mb Per Hour
  • Throttling

T-SQL Analysis

Identify worst performing statements, see causes of bad performance (CPU, IO, memory, locks), have your code health checked & recommendations made

Database Healthchecks

Each Azure SQL database can have a collection of health checks run against it with a single click:

  • MAXDOP used
  • Scalar functions
  • Wildcard used to begin search
  • Computed column not persisted
  • ExecSql not used
  • Forced Parameterization Enabled
  • Disabled Indexes
  • Triggers On User Tables
  • Auto Create Stats
  • Untrusted Check Constraints
  • Untrusted FK constraints
  • Unused Indexes
  • Auto Update Stats Enabled
  • Missing Indexes

…and more.

Historic Data Views

Sampled data is stored and can be viewed in the History Viewer: performance counters, waits & blocking statements are all available for performance analysis. When alerts are raised every recorded performance metric can be seen at that point in time.

New SQL Server health checks make you a SQL Server doctor in a single click

As of version 1.5, miniDBA now offers health check diagnostics of both server and databases. There is no need to grab your rubber gloves and stethoscope yet though as miniDBA will dive into the patient on your behalf and diagnose any configuration. performance & design issues that it finds.

Normally these type of issues are found only after something starts going wrong – this is your chance to get ahead of the game and save future time/money/pain.

You can get a flavour for the types of test performed in the following screenshots of server:

SQL Server health checks

SQL Server health checks

and database level health checks:

SQL Server Database Health Checks

SQL Server Database Health Checks

In these screenshots the yellow rows/health checks indicate a warning where as white rows indicate a status of ‘OK’.

These health checks are included in both Developer and Enterprise editions of miniDBA Desktop so you can access them while only paying for the cheaper version.

Pro-actively getting miniDBA to perform these checks will give you an immediate picture of existing issues. Don’t forget that miniDBA doesn’t require sa or even dbo admin rights to access all this information!

The checks are a goldmine of valuable information that will save you time and money straight away.

SQL Server health checks List

  • Potential OS Memory Swapping
  • Resource Governor Enabled
  • xp_cmdshell enabled
  • Plan Guides In Use
  • Is a Cluster Node
  • Query hints – sort order
  • Query hints – join
  • Slow reads
  • Slow writes
  • Suspect Pages
  • Active Trace Flags
  • TempDb only has 1 data file
  • User tables in master database
  • Server level triggers
  • Startup procedures
  • System databases on C drive
  • ..plus many more…

SQL Server Database Health Check List

  • MAXDOP used
  • Unpartitioned Large Tables
  • Scalar functions
  • Wildcard used to begin search
  • Computed column not persisted
  • ExecSql not used
  • Slow reads
  • Slow writes
  • Forced Parameterization Enabled
  • Disabled Indexes
  • Triggers On User Tables
  • No Clustered Index On Table
  • Collation different to server
  • Auto Create Stats
  • Involved In Replication
  • Untrusted Check Constraints
  • Untrusted FK constraints
  • Is Auto Close Disabled
  • Full Backups Within Last Month
  • File % Growth
  • Files On C Drive
  • Uneven file growth
  • Log Larger Than Data File
  • Multiple Log Files On A Drive
  • Auto Update Stats Enabled
  • Auto Shrink Disabled
  • Optimal page verification
  • Is data correlation on
  • Non Aligned Indexes
  • Snap shot source
  • Old Compatability Level
  • Procedures ‘with recompile’
  • Missing Indexes
  • Unused Indexes