A Fine Slice Of SQL Server
Jen McCown has a script to compare column names between tables to find case inconsistencies:
I’m reviewing the code for the upcoming Minion CheckDB, and one of the things we’re checking for is case consistency in column names. For example, if Table1 has a column named Col1, and Table2 has COL1, well that’s no good.
But, how do we easily find those mismatches on a system that’s not case sensitive? Easy: collations.
Click through for the script.
Comments closedKenneth Fisher looks at various error log messages to show how to fix different login failures:
Error Displayed
Login failed for user ‘Kenneth_Test’. (Microsoft SQL Server, Error: 18456)How do I fix it
Simple enough. Change the password. However, there are a few warnings here. If you change the password and it’s being used by someone (for example this is an application id) you may be breaking the application. If this is production, that could be a no-no. Just in case I like to back up the password hash before changing it.
I’ve also gone back to the well several times over the years with this blog post showing what the various error states mean when you get error 18456.
Comments closedDerik Hammer shows how to use a pair of virtual network gateways to connect two Azure regions:
Refer to Microsoft documentation for the difference between gateway types, VPN types, and SKUs. All of them will work but with different costs and benefits. For this demonstration, I am sticking with a basic VPN.
Check it out; this is a step-by-step guide and Derik makes it easy.
Comments closedLonny Niederstadt has a half-baked idea on dead connections:
In SQL Server 2005, a valuable addition was made. SQL Server would use a “Keep Alive” value specified specifically per instance, rather than the KeepAliveTime specified by the Windows registry. It defaulted to 30000 ms/30 seconds.
The blog post below explains this new addition, and mentions that the interval for SQL Server will be a fixed 1000 ms/1 second, regardless of the KeepAliveInterval specified in the Windows registry. At the time SQL Server 2005 was introduced, TCPMaxDataRetransmissions from the Windows registries still controlled the maximum number of probes.
Read on for more.
Comments closedJoey D’Antoni tests how large database files are after running DBCC CLONEDATABASE:
One of the recent feature introductions to SQL Server is dbcc clonedatabase, a feature that lets you create a “data-less” clone of you database. All of the statistics and objects come into your cloned database, however none of the data does. This is perfect for development or performance tuning exercises, where you want all the metadata, but do not want the security risk of dealing with production data.
Recently I had the opportunity to use clonedatabase on a very large database. I was concerned about the size of the data files and how this would impact space on my volumes. Books Online is fairly clear, but I wanted to see for myself.
Click through for the answer.
Comments closedThis pattern works in SQL Server 2014 and higher. And it even works in Standard Edition of 2014.
Some folks will see the word ‘Switch’ in this pattern and assume the pattern that I’m suggesting is Enterprise Edition only for versions before SQL Server 2016 SP1.
However, oddly enough, you can use partition switching even in Standard Edition, as long as the tables only have one partition.
And all rowstore tables have at least one partition! That happens automagically when you create a table.
Read the whole thing.
Comments closedAllen White shares his list of interesting perfmon counters:
Paging File(_Total)\% Usage When Windows runs out of memory it takes large chunks of memory and swaps it out to disk, to the Paging File. Unfortunately, the slowest operation in all computing is writing to disk, regardless of the physical media involved, so swapping memory to disk is naturally going to slow down the performance of your system. Keeping an eye on this counter will help you know when you are encountering memory issues, and you can then take action to resolve the conflicts.
There are dozens of interesting counters you could use, but I appreciate that Allen stuck with 15.
Comments closedJohn White says, spoilers, you can’t:
The bottom line of all this is that even if you use Office 365, and you think that you have disabled Groups in your tenant, the chances are that you could be in for a surprise. If any of these dependent services are in use, the chances are that you already have several created.
Groups are the bedrock of all new features in Office 365 moving forward – it is therefore a good idea that your organization understand them as soon as possible. Their inevitability is also another strong argument for paying close attention to them. If you are currently discussing whether or not they should be used, I would strongly encourage you to shifting that discussion to how they should best be used.
Read the whole thing if you’re getting into Office 365.
Update: John has a clarification, showing that it is possible, but it’s not trivial.
Comments closedThomas Rushton provides a script to set max memory on a SQL Server instance:
The thing to do, ideally, is to configure the maximum server memory when you build the server; however, sometimes you walk into a place where there are many servers where this hasn’t been done, or are otherwise looking for a quick way to determine what the setting should be. Jonathan Kehayias of SQLSkills blogged about a sensible SQL Server Maximum memory calculation (in response to a post elsewhere about a really dodgy memory config advisor, but I’m not going to link to that…)
What I’ve done below is codify that knowledge into a nice friendly T-SQL query that you can run, below. It makes use of the sys.dm_os_sys_info DMV to get the memory physically in the server; that DMV, though, has changed form between SQL 2008R2 and SQL 2012, the new version reporting physical_memory_kb whereas the previous version had physical_memory_in_bytes. Hence a bit of dynamic SQL nastiness at the start of the query.
Click through for the script, but make sure to tweak it for your environmental peculiarities.
Comments closedLori Brown documents the hints available with SQL Server 2016 SP1’s USE HINT syntax:
USE HINT ( ‘hint_name‘ ) Provides one or more additional hints to the query processor as specified by a hint name inside single quotation marks. Hint names are case-insensitive. USE HINT can be utilized without having to be a member of the sysadmin server role.
It’s an interesting list.
Comments closed