A Fine Slice Of SQL Server
After many months of confusing myself with mental arithmetic, I decided to create a straightforward list based on Jonathan’s algorithm, in 4 GB increments, for my own reference. I would like to share this with you today.
You can bask in the glory that is my Max Server Memory Matrix athttps://bornsql.ca/memory/.
It’s a nice lookup table, so thanks to Randolph for putting that together.
Comments closedErik Darling ran into an issue with endpoint security while setting up mirroring:
This is the error text:
The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://ORACLEDB.darling.com:5022’. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
SUPER SLEUTH
Alright, that’s silly. I used the GUI. Instead of going to bed I’ll spend some time checking all my VM network settings. BRB.
I’m back. They were all correct. I could ping and telnet and set up linked servers and RDP. What in the name of Shub-Niggurath is going on with this thing?
These things always happen right before bed, right before the big meeting, right before lunch. They never happen on a slow Tuesday afternoon, it seems…
Comments closedDerik Hammer discusses spanning an Availability Group across multiple sites:
In the architecture above, replica A and B are in the primary data center while replica C and D are in the disaster recovery (DR) site. Like the previous architecture, the disks are displayed as local but the most important part is that they are physically separate. SANs are wonderful systems with a lot of redundancy but they can also be a single point of failure. Keep your Availability Group disks separate.
This is a nice architectural overview. Once the series is done, it looks like it’ll be a good resource to discuss high availability and disaster recovery with management and show the options and trade-offs.
Comments closedKenneth Fisher describes startup stored procedures:
sp_procoption is a system stored procedure that lets us change the options on a stored procedure and in this case set it to run on startup. Note: The account that is running SQL Server needs to have permissions to start the Agent service. (Or do whatever your startup stored procedure does.) You can have as many stored procedures running on startup as you want but remember the more you have the longer it’s going to take for your instance to start.
There are a few uses cases in which startup stored procedures can be useful, but my reservation about them is similar to my reservation about triggers: it’s not apparent to people that a startup stored procedure is in place, so if there is a problem with it, troubleshooting might be harder than normal without good documentation.
Comments closedAaron Bertrand looks at converting an identity integer into an identity bigint:
This is a very disruptive change to the structure of the table, obviously. (And an interesting side observation: the physical order of the columns, RowID and filler, have been flipped on the page.) Reserved space jumps from 136 KB to 264 KB, and average fragmentation bumps up modestly from 33.3% to 40%. This space does not get recovered by a rebuild, online or not, or a reorg, and – as we’ll see shortly – this is not because the table is too small to benefit.
Note: this is true even in the most recent builds of SQL Server 2016 – while more and more operations like this have been improved to become metadata-only operations in modern versions, this one hasn’t been fixed yet, though clearly it could be – again, especially in the case where the column is an IDENTITY column, which can’t be updated by definition.
Read the whole thing. The clustered key scenario (which will be later in the series) is a bit more interesting to me, as that would be a more common use case for identity values.
Comments closedSystem views are divided into categories that each serve a specific purpose. The most extensive category is the one that contains catalog views. Catalog views let you retrieve information about a wide range of system and database components—from table columns and data types to server-wide configurations.
Information schema views are similar to some of the catalog views in that they provide access to metadata that describes database objects such as tables, columns, domains, and check constraints. However, information schema views conform to the ANSI standard, whereas catalog views are specific to SQL Server.
In contrast to either of these types of views, dynamic management views return server state data that can be used to monitor and fine-tune a SQL Server instance and its databases. Like catalog views, dynamic management views are specific to SQL Server.
One of the best things the authors of SQL did was require that metadata management be in the same language: you write SQL code to query metadata the same as if it were normal data.
Comments closedAmy (SQLKitten) shows how to troubleshoot a common error for replication-enabled databases:
Based on the results from sp_helpreplicationdb , I now have confirmation that my database is at least enabled for replication. The next thing I need to do is turn off replication for this database with sp_removedbreplication.
Sometimes the short answer is the best one.
Comments closedKendra Little shows us how to fix a common partitioning issue:
Everything was fine, but we forgot to put a boundary point in for Jan 1, 2016… and now it’s well into January. All our data for January 2016 is in the partition with the December data.
The more time passes, the more data is going to go into that partition, and it’ll get bigger and more lopsided. If we’re switching out old partitions by month, eventually that’s not going to work. And partition elimination won’t work for anything after Dec 1, 2015, either!
Be sure to keep those empty partitions around, just in case .
Comments closedSteve Jones explains database snapshots:
I’ve rarely dealt with database snapshots, outside of lab experimentation. They didn’t exist when I did most of my DBA work, and since then we haven’t seen the need for them at SQLServerCentral, though, I may suggest we add them to our deployment process since we can quickly roll back if something breaks.
However, I created one recently for a test and realized that I’d forgotten the syntax. Unlike the quick “create database xx” syntax I often use, with a snapshot I need to be more specific.
Word of warning: don’t have more than one active snapshot of a single database. If you do, you’ll likely have major performance problems. My favorite use case for snapshots was building some semi-automated integration tests a few years back. I created a tool for devs to create snapshots, and then they could run all the tests they wanted and revert the snapshot afterward. There are some good uses in production environments as well.
Comments closedMichael J. Swart has an Extended Event and a query to help monitor deadlocks:
Guess what? Apparently I “reinvented the wheel”. The extended events session I created is equivalent to one that Jeremiah Peschka wrote two years ago in Finding Blocked Processes and Deadlocks using SQL Server Extended Events. The embarrassing thing is that in Jeremiah’s article, he references a tool I wrote. And the first comment was written by yours truly.
There are a bunch of ways to capture deadlock information. This is a good one.
Comments closed