A Fine Slice Of SQL Server
Now we are ready to attach the database using the TSQL below. For this demo, I used Management Studio from my Laptop, to connect to SQL Server.
In the TSQL we need to use the FOR ATTACH_REBUILD_LOG argument as we have no log file to attach. It will create a 1MB log file in the default log file directory.
It’s better to restore a full backup, but there’s more than one way to connect a database.
Comments closedWe have a client who has no idea how or when Common Criteria was enabled on their production system. All they know is that performance has been slowly degrading. After collecting performance data, we found that there were high LCK_M_SCH_M waits which is a schema modification lock that prevents access to a table while a DDL operation occurs. We also found blocked process records where a LOGIN_STATS table in the master database was waiting a lot. This table is used to hold login statistics. When there are a lot of logins and outs there can be contention in this table.
When you enable Common Criteria compliance, something called Residual Information Protection (RIP) is enabled. RIP is an additional security measure for memory and it makes it so that in memory a specific bit pattern must be present before memory can be reallocated(overwritten) to a new resource or login. So with lots of logins and outs, there is a performance hit in memory because overwriting the memory allocation has to be done.
It’s worth reading the whole thing.
Comments closedCody Konior looks at a couple errors in full-text indexing:
The first error message holds the key. It’s not enough to just enable the service, you need to run that command also (which will also start the service for you as well as doing something internally) or restart SQL Server after the service is enabled.
Here’s where we go to the next level. You now know that full-text indexing can have a problem on startup that isn’t visible externally but surely this could never happen to you, right? But I guarantee you that if you have 500 servers many of them currently have this issue. When a developer or application support person encounters it that’s going to take half an hour or an hour of investigation, then logging incidents, then passing it on to you, for another half hour or hour of investigation, and then more time, and more time, and then doing a root cause analysis, and then sharing it with your team, and then…
Wouldn’t it be so much easier to just write a validation test for this now so that if this ever occurs you can just proactively go fix it? Well you can!
It’s a good read.
Comments closedTracy Boggiano has a set of Perfmon counters she uses to monitor Availability Groups:
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 closedAlicja 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 closedIn 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 closedIf 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 closedGarland 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 closedJovan 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 closedArun 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