Database Health Beta 9.3 Released Today

Today I had the pleasure of releasing Beta 9.3 of the Database Health Monitor. It has been a whole week since the last beta update, and here is the outline of the new features and bug fixes.

Download at DatabaseHealth.com

New Features

  • New report, Partitioned Tables. This new report shows all of the partitioned tables in a database. Double clicking for drill down shows the new Partitioned Tables Advisor that shows details on the number of partitions, rows in each partition and the range for each partition.
  • The entire color scheme has been updated, not so blue.
  • Added double click action on the one time use query report.
  • The duplicate indexes chart has been migrated over to the new report format and colors.

Bug Fixes

  • Performance improvements and bug fixes on the table size advisor. It is still a bit slow on really huge tables, but it works well on most.
  • Fixed the drilldown when double clicking the chart view on the disk space report.
  • Improved exception handling on the needs parameters report.
  • Increased query timeout on the index fragmentation report.
  • Improved the column layout and sizing on the following reports:
    • Missing Indexes Report
    • Unclustered Tables Report
  • Improved error handling on all background threads when attempting to connect to a database. Better handling when the connection fails.
  • Fixed a bug on the plan cache panel not linking to the right report.
  • The enter key now does the same thing as double clicking for drilldown on all grids in the application.

Other Notes

  • Based on improved crash reporting in beta 9.1 and 9.2, I was able to learn more about the common problem areas in the application. This lead to improved exception handling in many of the areas where a database connection is established, along with better error messages when a connection fails for some reason.

 

The Partitioned Tables Report was a new addition to the Database Health monitor with this release.

The partition tables report shows a list of all the partitioned tables in a database. As shown here, there is just one partitioned table in this test database.

PartitionedTables2

Double clicking on the table [dbo].[orders] brings up the Partitioned Table Advisor which shows the number of rows in each partition, the percentage of the table represented by that partition, and the range items for the partition. In this example the partitions are split over date ranges.

 

 

PartitionedTablesAdvisor

I plan to keep the updates coming for some time.  Every week I am learning something new to help make the product even stronger.

Download at DatabaseHealth.com

-Steve Stedman

Speaking at Vancouver DevTeach this week.

This week I will be attending and speaking at Vancouver DevTeach. This event taking place on December 2nd to 4th 2013 at the Vancouver Sheraton Wall Center Hotel. Monday and Tuesday I have morning presentations which leaves the rest of the day to attend a few other sessions.

DevTeach

Here are the sessions that I will be presenting.

Monday: Using FILESTREAM and FILETABLES in SQL Server

Configuring and Using FILESTREAM and FILETABLES in SQL Server.  Developers love to use SQL Server to store files, but this causes headaches for the DBA, finally a reasonable solution for file storage in SQL Server FILETABLES and FILESTREAM. SQL Server 2008 and 2012 add the new features of FILESTREAM and FILETABLES. Learn how to configure and manipulate files in your SQL Server with FILESTREAM, then learn how to do everything that FILESTREAM sounds like it should do with FILETABLES. With FILETABLES inserting is as easy as drag and drop.

Tuesday: SQL Server Performance for Developers

For the .NET programmer, Visual Basic programmer or PHP programmer, if you are accessing a SQL Server database there are some things you should know to performance tune your queries. Learn how to improve query performance with Indexes, how to properly use parameterized queries, using the query analyzer, and avoiding common SQL Server performance pitfalls.

Looking at the lineup of other speakers this looks like it will be a great few days of education. It is nice to attend a conference so close to home, no flights involved for me this time.

Index Overview – Clustered and Nonclustered

This is intended as a brief overview of indexing on SQL Server. Understanding and using indexes can lead to some major performance improvements.

Non-Clustered Indexes

Non-Clustered indexes are often times considered traditional indexing – contains pointers to the data. This is similar to the index at the back of a book. If you open a book to the index, then look up a term you are given the page numbers that the term is on. A non-clustered index is similar to this process, the non-clustered index contains a list of data, then finally it has a link to the actual location for that data similar to the page number in the back of a book.
The Non-Clustered index contain only the data specified in the index, and the primary key information to get to the rest of the data. Non-Clustered indexes do not change the base layout of the tables, they are a copy of the data, and use pointers to get to the data.
Non-Clustered indexes can be created on most data types including char(), varchar(), and uniqueidentifiers. Non-Clustered indexes can improve performance by adding multiple columns.

Clustered Indexes

A clustered index is an index that reorganizes the actual data on disk, causing the entire base table structure to change. Since the clustered index rearranges the base table, only one clustered index is allowed per table.
The term clustering refers to the act of adding a clustered index. Clustering can significantly increase the size of a table and the database if it is not used correctly.
When used correctly clustered indexes can dramatically increase performance.

Covered Indexes

A covered index, is not another type of index, it usually applies to a non-clustered index and the index is considered covering when the index contains everything that a query is looking for without accessing the base table.
Covered indexes can lead to major performance increases since the base table doesn’t need to be accessed.

Index Usage Terminology

Scan: An Index Scan accesses all the rows in the index.
Seek: An Index Seek uses selective rows in the index.

The Seek is much quicker than the scan.
The index scan is usually much quicker than a full table scan.

Additional Index Resources

Temp Table vs Table Variable vs CTE and the use of TEMPDB.

For more information on Common Table Expessions and performance, take a look at my book at Amazon.com: Common Table Expressions Joes 2 Pros®: A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs

There are many reasons that a Temp Table, Table Variable or Common Table Expression can be a good solution, but there is also a lot of confusion around these.  Much of that confusion is around the use of TempDB.

For the purpose of this article I am going to use my standard Departments table from my CTE presentation. It is just a simple database to work with. Also, this is running on my local SQL Server with no other users connected.


USE [Master];
set statistics io off;

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'cte_demo')
BEGIN
 ALTER DATABASE [cte_demo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 DROP DATABASE [cte_demo];
END
CREATE DATABASE [cte_demo];
GO

USE [cte_demo];
-- create a table to use for CTE query demo
CREATE TABLE [Departments] (
 id int, --would normally be an INT IDENTITY
 department VARCHAR (200),
 parent int
 );

-- insert top level departments
insert into [Departments] (id, department, parent) values (1, 'Camping', null);
insert into [Departments] (id, department, parent) values (2, 'Cycle', null);
insert into [Departments] (id, department, parent) values (3, 'Snowsports', null);
insert into [Departments] (id, department, parent) values (4, 'Fitness', null);

-- now some sub-departments for camping
insert into [Departments] (id, department, parent) values (5, 'Tents', 1);
insert into [Departments] (id, department, parent) values (6, 'Backpacks', 1);
insert into [Departments] (id, department, parent) values (7, 'Sleeping Bags', 1);
insert into [Departments] (id, department, parent) values (8, 'Cooking', 1);

-- now some sub-departments for cycle
insert into [Departments] (id, department, parent) values (9, 'Bikes', 2);
insert into [Departments] (id, department, parent) values (10, 'Helmets', 2);
insert into [Departments] (id, department, parent) values (11, 'Locks', 2);

-- now some sub-departments for snowsports
insert into [Departments] (id, department, parent) values (12, 'Ski', 3);
insert into [Departments] (id, department, parent) values (13, 'Snowboard', 3);
insert into [Departments] (id, department, parent) values (14, 'Snowshoe', 3);

-- now some sub-departments for fitness
insert into [Departments] (id, department, parent) values (15, 'Running', 4);
insert into [Departments] (id, department, parent) values (16, 'Swimming', 4);
insert into [Departments] (id, department, parent) values (17, 'Yoga', 4);

Then to compare the difference on the TempDB usage, I am going to use an under-documented function called fn_dblog() which shows you a list of the items that are written to the transaction log in different scenarios.  I will also combine this with the CHECKPOINT command to clear the contents of the log for TempDB.

To try out out, run the following on a TEST or DEVELOPMENT database.  Don’t run this on your production system.


USE TempDB;
GO

SELECT *
 FROM fn_dblog(null, null);

CHECKPOINT;

SELECT *
 FROM fn_dblog(null, null);

Then take a look at the output which will look something like this.

The first time you select from fn_dblog() you may be many rows.  In my example fn_dblog() returned 171 rows, you may have many more.  But after running CHECKPOINT you only get 3 rows, and those are the 3 rows that you will normally get after running CHECKPOINT.

Read more of this post

Now that Database Health Beta 2 is out, whats next…

Now that Beta 2 of the Database Health Project is out, and people are using it, I am going to focus my attention on 2 areas.

  1. Fixing any bugs that are reported in Beta 2 of the Database Health Project.
  2. Adding new features, specifically related to offline monitoring or history data.

Currently all of the reports in the database health project report on what SQL Server can tell you right now.  What happens when somebody reports that the server was slow at 4:00pm yesterday, how can you track that down. Well, the plan if all goes well with the next beta of the Database Health Project there will be a way to configure a database on a monitoring system that will check up on your SQL server occasionally and record any problems.  This will allow you to be able to find out what query or what wait type caused the system to be slow at some point when you weren’t watching the database.

This is going to be very cool!

So far I have the core tracking and monitoring features working, now I just need to work on the reporting so that it is quick and easy to understand the data.

For now, please try out Beta 2 of the Database Health Project, it is a free download.

-Steve Stedman

Finding the contents of the Plan Cache

I often get asked “how do I find the contents of the SQL Server Plan Cache?”   My first response is just use the Database Health Reports and you can get this through a nice user interface.  But if you still want to get at the Plan Cache contents, I use the following query.

SELECT UseCounts,
       RefCounts,
       CacheObjtype,
       ObjType,
       DB_NAME(dbid) as DatabaseName,
       SQL,
       sqlbytes
  FROM sys.syscacheobjects
 WHERE dbid = db_id();

Again, the Database Health Reportsgives you a much better UI presentation of the conents of the plan cache.

Enjoy the query and please try out the Database Health Reports.