Category: Administration

Monitoring Availability Groups can be tricky. The DMVs don’t update the log_send_rate and redo_rate all the time especially if nothing is happening so if you try to use those for calculations when monitoring you could false results worse yet pages in the middle of the night. In order to calculate the log_send_rate and redo_rate you need to capture the perfmon counters ‘Log Bytes Flushed/sec’, ‘Redone Bytes/sec’, and ‘Log Bytes Received/sec’ into temporary tables WAITFOR 1 second then capture them again. Below is query that captures this along side what you see in the DMV for when Microsoft might fix the issue.

Click through for a script as well as a Powershell cmdlet wrapper for running against a set of hosts in your Central Management Server.

Comments closed

Handling Rogue Queries In Spark

Published 2017-04-27 by Kevin Feasel

Alicja Luszczak, et al, introduce the Query Watchdog:

The previous query would cause problems on many different systems, regardless of whether you’re using Databricks or another data warehousing tool. Luckily, as an user of Databricks, this customer has a feature available that can help solve this problem called the Query Watchdog.
Note: Query Watchdog is available on clusters created with version 2.1-db3 and greater.
A Query Watchdog is a simple process that checks whether or not a given query is creating too many output rows for the number of input rows at a task level. We can set a property to control this and in this example we will use a ratio of 1000 (which is the default).

It looks like this is an all-or-nothing process, but a very interesting start.

Comments closed

Deploying Reports With Powershell

Published 2017-04-27 by Kevin Feasel

Jana Sattainathan has created a few Powershell functions to automate dealing with SQL Server Reporting Services report deployment:

In this post, I want to publish a few functions that I created around SSRS. They are related to and depend on each other.

Get-SSRS – Given the SSRS URI returns the WSDL endpoint
Get-SSRSReport – Returns one or more reports based on inputs
Get-SSRSSharedDataSource – Returns one or more shared data sources based on inputs
Get-SSRSReportDataSource – Returns the data source information on a report by report basis based on inputs
Set-SSRSReportDataSource – Sets the data source of a report to the given data source.
Install-SSRS – Deploys an SSRS report to a specific folder and also optionally sets the datasource for the deployed report

Very useful.

Comments closed

How Query Store And Plan Guides Interact

Published 2017-04-26 by Kevin Feasel

Grant Fritchey shows that query metadata gets a little weird when you have a plan guide trying to use one particular query and Query Store is forcing a different plan:

If we rerun the query and then take a look at the first operator in the execution plan, we can see that the Plan Guide is in use… and that the query hash has changed. It no longer matches the original query. Now it matches the query that included the query hint. This actually makes perfect sense. The Plan Guide is basically changing the query from the first example above, into the second.
Now, what happens when we toss in the Query Store

The query behavior is exactly what you want, but some of the metadata is no longer correct.

Comments closed

Killing SPIDs

Published 2017-04-26 by Kevin Feasel

Garland MacNeill is all out of bubble gum:

Recently came across a situation where reporting logins were interfering with nightly jobs due to blocking. After a number of attempts of trying to resolve the blocking, it was decided that a stored procedure that disabled the login and killed the user sessions was the most pragmatic solution. This is the code I came up with to resolve the issue.

Click through for the script. This is definitely a last-ditch option, but it’s good to have in your bag of tricks.

Comments closed

Finding Query Plan Regressions

Published 2017-04-25 by Kevin Feasel

Jovan Popovic shows how to find query plan regressions in SQL Server 2017:

In CTP2.0 version is added new system view sys.dm_db_tuning_recommendations that returns recommendations that you can apply to fix potential problems in your database. This view contains all identified potential performance issues in SQL queries that are caused by the SQL plan changes, and the correction scripts that you can apply. Every row in this view contains one recommendation that you can apply to fix the issue. Some of the information that are shown in this view are:

Id of the query, plan that caused regression, and the plan that that might be used instead of this plan.
Reason that describes what kind of regression is detected (e.g. CPU time for the query is changed from 17ms to 189ms)
T-SQL script that can be used to force the plan.
Information about the current plan, and previous plan that had better performance.

In the “surgical scalpel to chainsaw” range of query tuning options, this rates approximately guillotine. I think it’ll be a very useful tool for finding issues, but it wouldn’t be wise to start lopping off all the heads just because the optimizer tells you to. In this context, I imagine this DMV to be about as useful as the missing indexes DMV and for the same reasons.

Comments closed

Back Up Those System Databases

Published 2017-04-25 by Kevin Feasel

Arun Sirpal had gremlins infest his server, causing a service pack installation to go sideways:

I try to start the service but it fails. (I was desperate ok!)
Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 5846, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
To be honest, rebuilding master would be my last option.

Read the whole thing, and then double-check that you have good copies of master & msdb somewhere.

Comments closed

Diagnosing Database Restore Wait Times

Published 2017-04-24 by Kevin Feasel

Bob Ward notes that the “100 percent processed” message doesn’t mean everything is quite finished yet in a database restoration:

Notice the “100 percent…” message has detailed about “bytes processed”. Since my data is around 13Mb this tells me that the progress indicators are all about the data transferred step of RESTORE. Notice the time gap in the messages to “Waiting for Log zeroing…” and “Log Zeroing is complete”. That gap in time is around 2 minutes. Exactly the time it took between the the 100% Complete message in the SSMS Window and the final restore message.
From this evidence I can conclude that having a transaction log file size that takes a long time to initialize can be a possible cause of seeing the behavior of getting the 100% Complete Message quickly but the overall RESTORE takes longer to complete.

There’s a lot worth reading packed into this post, as you’d expect from Bob. Read the whole thing.

Comments closed

Resumable Online Index Rebuilds

Published 2017-04-24 by Kevin Feasel

Niko Neugebauer introduces a new feature in SQL Server 2017:

After multiple executions, the first process (Resumable Online Index Rebuild) on the average took 65.8 seconds, while the second one (a simple online) took only 60.8 seconds, representing 8% of the improvement of the overall performance. I can’t say if it looks acceptable to you or not, but for me this is something I will be definitely considering to be as an advantage for the cases where the resumable process is needed.
I decided to run a test on much bigger table, the lineitem which for 10GB TPCH database contains 60 Million Rows. My expectation here was to see if the percentage would stay the same or will jump to a whole new level (please make sure that you do execute the following script at least a couple of times, to get the real results and not the results of your disk-drive prefetching :)):

The big table example result was somewhat surprising. Niko is his normal, informative self, so definitely read the whole thing.

Comments closed

Upgrading SQL On Linux

Published 2017-04-21 by Kevin Feasel

Steve Jones has a post on upgrading SQL Server on Linux:

I’m cutting off part of the path, since I think it’s probably NDA. No worries, apparently the old location for me hasn’t been updated with new packages, which makes sense.
I decided to check the MS docs and see how a new user would get SSoL running? At the new docs.microsoft site, I found the Install SQL Server on Ubuntu doc.
Following the instructions, I updated the GPG keys and registered the repository with curl:
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list

My expectation is that upgrading SQL Server on Linux is going to be a lot less painful than upgrading on Windows.

Comments closed