SQL Server – sysmaintplan_logdetail

Download PDF

The check for excessive sysmaintplan_logdetail has be added to the Database Health MonitorQuick Scan Report.

If you haven’t had a maintenance plan to clean up maintenance plan history, it can grow excessively over time. I recently worked on a SQL Server that had 6 years worth of maintenance plan history, and the msdb.dbo.sysmaintplan_logdetail table was approaching nearly 1gb in size. It would be a tough argument to claim that you really need a 6 year history of your maintenance plan history. If you really do, you might want to copy it off to another database outside of msdb.

The problem with this type of excessive growth is that it bloats out the size of msdb, which should be a pretty small database under almost any circumstances.

Here are some queries to examine the contents of the msdb.dbo.sysmaintplan_logdetail table.

SELECT TOP 100 *
FROM msdb.dbo.sysmaintplan_logdetail WITH (NOLOCK)


SELECT count(*)
FROM msdb.dbo.sysmaintplan_logdetail WITH (NOLOCK)


SELECT TOP 1 start_time, count(1) OVER() as numOlder
FROM msdb.dbo.sysmaintplan_logdetail WITH (NOLOCK)
WHERE start_time < GETDATE() - 365
ORDER BY start_time ASC

The following sproc sp_mainplan_delete_log can be used to clean up the excessive history. You could also use the maintenance plan task to clean up maintenance plan history.

msdb.dbo.sp_maintplan_delete_log @oldest_time='3/19/2016';

The recommendation is to clean out this table regularly. As it grows, it can add excess overhead when running maintenance plan jobs.

If you haven’t tried Database Health Monitor, now might be a good time to give it a try. Its a free application created by StedmanSolutions, LLC, and available at http://DatabaseHealth.com

Related Links:

Posted in Database Health Tagged with: , , , ,

SQL Server – Cost Threshold For Parallelism

Download PDF

The check for Cost Threshold for Parallelism check has be added to the Database Health MonitorQuick Scan Report.

The Cost Threshold For Parallelism setting in SQL Server controls the level at which the query optimizer decides to have a query be processed using parallelism, or multiple threads.

The default setting is 5 which may have made sense 15 years ago in SQL Server 2000 or older, but with more modern versions of SQL Server, with much faster processors, more memory, and all around major improvements, the default of 5 doesn’t make sense any more.

I usually set it to 50, then evaluate system performance and adjust it from there. Here is a SQL Script to set the cost threshold for parallelism to 50.

sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
sp_configure 'cost threshold for parallelism', 50;
GO
reconfigure;
GO

You may need to set this to something much higher than 50, this really depends on your overall SQL Server performance.

If you are adjusting Cost Threshold for Parallelism you may also want to take a look at adjusting the Max Degree of Parallelism at the same time.

If you haven’t tried Database Health Monitor, now might be a good time to give it a try. Its a free application created by StedmanSolutions, LLC, and available at http://DatabaseHealth.com

Related Links:

Posted in Database Health Tagged with: , , , ,

Database Health Monitor Quick Scan Report

Download PDF

With the updated release of Database Health Monitor yesterday, here is the updated list of documentation on the Quick Scan Report.  All of the following (plus a few more) are checked from the Database Health Monitor Quick Scan report.

The Quick Scan report checks some common problems that can cause grief on your SQL Server database, and reports on those, with detailed documentation on each of the checks.

 

 

Posted in Database Health Tagged with: , ,

SQL Server MAX DEGREE OF PARALLELISM

Download PDF

The check for MAX DEGREE OF PARALLELISM has be added to the Database Health MonitorQuick Scan Report.

The max degree of parallelism setting in SQL Server is used to control the maximum number parallel threads available to process your query. The default setting of 0 means use as many as possible. The problem with this default is that you may end up with way more parallel threads working on your query than you have processors available. When this happens you can end up with thrash as these multiple tasks are being swapped between your processors or cores.

If your server has 4 cores, you could use the following script to set max degree of parallelism to match your 4 cores.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 4;
GO
RECONFIGURE WITH OVERRIDE;
GO

The recommendation is to set this to match the number of cores on your system, or the maximum number of cores in any NUMA node.

Setting this to 1 tells SQL Server to disable parallel processing, and is generally not recommended.

 

If you are adjusting Max Degree of Parallelism you may also want to take a look at adjusting the Cost Threshold for Parallelism at the same time.

If you haven’t tried Database Health Monitor, now might be a good time to give it a try. Its a free application created by StedmanSolutions, LLC, and available at http://DatabaseHealth.com

Related Links:

Posted in Database Health Tagged with: , , , , ,

Database Health Monitor – Version 2.3 Released

Download PDF

With over 7500 downloads worldwide to date, I have just released the next installment of the FREE Database Health Monitor application.

Here what one person had to say about it “Excellent – It’s the first tool I open every morning to see what is going on in my production environment! Thanks for the great work!”

Download today:

 

Database Health Version 2.3 Release Notes

Released March 20th, 2016.

It has been about 3 months since the last release, and I have added several new features, and many bug fixes.

New Features

  • Reducing the number of different database connections used by Database Health Monitor. Reusing existing connections where applicable.
  • Backup Report
    • Database restore script for the backups report now includes the “RESTORE HEADERONLY” option.
    • Updated the restore script to include a FILELISTONLY option to check on file locations before restoring.
    • Recent backups are now shown in green so it’s easy to see which databases have recent backups and which do not.
    • Added column to show the size of the backup.
  • CheckDB dialog: Added the instance name to the CheckDB dialog so that you can know which instance the CheckDB is being run against.

Read more ›

Posted in Database Health Tagged with: , , , , , , ,

Beta release 1.2 of SchemaDrift – the database schema comparison tool

Download PDF

Today I released another beta release of SchemaDrift.

Part of this release was some redesign of the main screen, as shown here.

SchemaDriftFeb282016

I had some great help from some of the beta testers, and there have been many bugs fixed and feature enhancements completed in the last week. Here is a list from the release notes:

Feb 28, 2016 Release Notes

Completed since last beta.

  • Fixed bug with extended properties on functions not being able to create on other database.
  • Fixed bug with extended properties on stored procedures not being able to create on other database.
  • Changed the directory name for objects with spaces for instance Stored Procedures to have the space, previously it had an underscore.
  • Sorted the database name dropdownlist by name, rather than by id.
  • Added a GO statement before each extended property, for reliability.
  • Main settings panel now sizes dynamically with the size of the window.
  • Various bug fixing comparing databases to file system / source control.
  • Enabled / Disabled controls while the diff is running to prevent starting another run, or to prevent changing params while running.
  • Connect dialog now remembers previously connected databases.

 

If you haven’t tried it out, give SchemaDrift a try, is a free tool for comparing database schemas.

See http://SchemaDrift.com for more details.

Posted in SchemaDrift Tagged with: , , ,

Sync your database with source control

Download PDF

SchemaDrift is a tool that I have created to compare the schema between two databases. It can also compare (and save) the schema to a directory in a file system. Point it to a directory in your source control system, and you are off and running with your database schema in source control.

SchemaDriffMainPage

 

 

Watch the video below to see how to save your database schema to source control, and to compare changes over time.

Syncing and comparing your database to source control is just one of the many things you can do with SchemaDrift.

 

Interested in trying out the beta, just fill out the beta form, and you will have access to SchemaDrift to start comparing your SQL databases today.

http://SchemaDrift.com

 

Posted in SchemaDrift Tagged with:

Recommendation

Download PDF

The video shown here is entirely unsolicited feedback from a client that I had the opportunity of working with recently on a SQL Server performance assessment.

Part of what I love about being in business for myself is being able to do what is needed to create happy customers.

Thanks for the feedback Patrick!

For this and other feedback on Stedman Solutions, visit the raving fans page at StedmanSolutions.com

Posted in Uncategorized Tagged with:

SchemaDrift – Database Schema Differences – My Weekend Project

Download PDF

This weekend I had some spare time, so I started coding on a new project on Friday night. This project is something that I intend to include in the next release of Database Health Monitor. THis project allows users to compare the database schema between two SQL Server databases.

As you read this, consider two questions that I will ask again at the end of the post, I would really love some feedback:

  1. Would you use this?
  2. Would you be interested in helping me test it out as an early release?

This was inspired by working with clients who need to compare the schema between different SQL Servers, either between multiple production servers with the same schema, or between test and production servers.

I know there are other tools out there that do this, but its not easy to convince a client to purchase 3rd party tools, so I am left not having a good tool to do this.

This is just a start, I still have a great deal of work to do, but its the foundation for me to build upon.

How it works:

  • Connect to a SQL Server and choose a database to compare (known as the source).
  • Connect to another SQL Server and choose another database to compare against (known as the destination).
  • Click the compare button.
  • The schema is then compared between the two servers (Tables, Sprocs, Functions, Users, Indexes, etc…)
  • Results are sorting into 4 categories.
    • Matched between the two servers.
    • Exist on both servers, but something is different.
    • Only on the Source Server.
    • Only on the Destination Server.
  • You can the browse the code behind those objects, and see the differences.

Database Schema Drift The Demo:

To start with we just run the program.

SchemaDrift Database Schema Comparison Diff

Next we click the connect button under the Source Server section. For the first server I am going to connect to a SQL Server 2008 R2 instance with SQL credentials.

Read more ›

Posted in Uncategorized Tagged with: , , , , ,

SSRS Report Usage Queries

Download PDF

This last week I had the opportunity to do some work with SSRS, determining some stats on report usage. I ended up digging up some queries that I wrote a couple years ago against the ReportServer database, and thought they would be worthwhile to share, so here they are.  These have been tested against SQL Server 2008, and 2008R2 databases.

I hope these can save you some time if you need to track down details on SSRS report usage.

There is one place where the text DOMAIN_NAME is referenced. That is intended to be replaced with your current domain.

Find out how many reports executions are in the log, and the oldest TimeStart

-- Written by Steve Stedman http://SteveStedman.com
SELECT COUNT(*),
       MIN(ExecutionLog.TimeStart)
FROM [ReportServer].[dbo].ExecutionLog(NOLOCK);

Just looking at what is in the log.

-- Written by Steve Stedman http://SteveStedman.com
SELECT TOP 100 c.Name,
               c.[Path],
               l.InstanceName,
               l.ReportID,
               l.UserName,
               l.RequestType,
               l.Format,
               l.Parameters,
               l.TimeStart,
               l.TimeEnd,
               l.TimeDataRetrieval,
               l.TimeProcessing,
               l.TimeRendering,
               l.Source,
               l.Status,
               l.ByteCount,
               l.[RowCount]
FROM [ReportServer].[dbo].[ExecutionLog](NOLOCK) AS l
INNER JOIN [ReportServer].[dbo].[Catalog](NOLOCK) AS c ON l.ReportID = C.ItemID
WHERE c.Type = 2 -- Only show reports 1=folder, 2=Report, 3=Resource, 4=Linked Report, 5=Data Source
ORDER BY l.TimeStart DESC;

List the reports with the number of executions and time last run:

Read more ›

Posted in SSRS Tagged with: , , , , , , ,

SQL Server Performance Tuning

Need help with SQL Server Performance Tuning, contact Steve at Stedman Solutions, LLC for help, or take a look at the performance tuning page at the SteveStedman.com blog. We can help with tuning a single query or with figuring out why your SQL Server is running slow. Free 30 minute consultation.

Newsletter Signup

Stay informed of Database Corruption Challenge events, and other things happening at SteveStedman.com with my newsletter.
Newsletter signup form.

Stedman Solutions

StedmanSolutionsRemoteSkilledDBA

Archives