A Fine Slice Of SQL Server
Ryan Adams wraps up a video series on setting up a SQL Server lab environment:
You are going to create a SQL Server Failover Cluster Instance in Part 4 of our series on how to build a SQL Cluster Lab. The FCI will only be installed on Node1 and Node2. FCIs require shared storage so you will make your domain controller an iSCSI target. Last you will create your FCI using the iSCSI drives you presented to the cluster.
Click through for links to the entire series.
Comments closedMilos Radivojevic is excited about OPTIMIZE_FOR_SEQUENTIAL_KEY:
The results show that the feature should be used for tables, where latch convoys already happen or are about to happen. In a not-so-heavy workloads, it brings a small overhead. You can see that inserts with 100 parallel sessions are 16% slower for a table with the optimized sequential key. However, when you hit the convoy, the feature definitely helps.
This graf is critical: if you don’t have high enough concurrency on the table, insertion can be a little slower than otherwise, so don’t go setting this for every table.
Comments closedJohn McCormack has three things new DBAs should learn first:
Help, the SQL Server is on fire and my really important process is not working. Why is the server so slow?
This scenario will happen to you. Invariably, the person (often a manager) will stand over you and expect you to knock out one or two lines of t-sql wizardry to get things running along smoothly again. First of all, I should say that in a perfect world, you will ask them to raise a ticket and you will work on it according to its priority against your other tasks. Then you can let them know what the issue was and what can be done to prevent it happening again. But we rarely work in a perfect world. In this scenario, you need one primary line of t-sql to get started.
John’s three points are a really good starting point.
2 CommentsAaron Bertrand looks at a couple things you can configure on installation with SQL Server 2019:
One of the long-standing defaults in SQL Server has been the maximum degree of parallelism (MAXDOP), which has always been 0, meaning use (up to) all cores when the engine believes that will help. For many OLTP workloads, 0 is not the optimal setting, and you may want to use a different number depending on the behavior of your workload. I don’t want to belabor the thought process here, but this will be based on settings like the number of cores exposed to SQL Server, whether they are divided into NUMA nodes, and if there are other instances, applications, or services running on the same Windows Server. Microsoft’s guidelines are published here.
Aaron shows us how to set MAXDOP as well as min and max server memory for our new instance. It’s nice to see these types of additions to the setup process—that makes it a bit more likely that the DBA who installs instances only occasionally doesn’t forget to set these afterward.
Comments closedThis morning when working with one of my SQL Server servers, I went looking for an error log from last August. After finding that I was only able to go back six days, I checked the directory that stored SQL Server’s error logs, and noticed a gap in the logs from October to last March.
When I checked the configuration for the error logs in SQL Server Management Studio, I saw that the setting was configured at the default setting of 6 error logs for history.
Read on to understand why this happened.
Comments closedRyan Adams continues a series on building a SQL Cluster lab:
You are going to create a Windows Cluster in Part 2 of our series on how to build a SQL Cluster Lab. You will run cluster validation followed by creating the Windows Server Failover Cluster. In addition, you configure the Cluster Networks and a File Share Witness.
Read on for some basics and a video containing more info.
Comments closedNiko Neugebauer takes a detailed look at an exciting 2019 feature:
Anyone who has ever seen/done tuning bigger Hardware would instantly be interested, since the CPU is clearly going 100% during the processing, showing APPARENT better focus on the process and hopefully better performance. Since the granularity of the Task Manager for both cases is the same, you can easily notice that overall spent significantly less time churning the same workload and that is very true – we have spent just 11.777 Seconds on the average!
The main benefit to this is an environment where you’re creating and destroying a lot of temp tables concurrently. If you are in that situation, you can realize significant performance improvements. But Niko does have a warning at the end.
Comments closedAdrian Buckman has a new stored procedure:
Sometimes you just want to get a quick row count for a specific table – other times you may want to see a list of tables order by size or row count or perhaps you are monitoring table row counts, whatever it is the chances are you have a script or various scripts stashed away and maybe like me you end up re writing parts of them to suit your requirements.
I got bored of that game so I decided to write a stored proc to make things easier.
Read on to see the procedure in action and then check it out in the Undercover Toolbox.
Comments closedBefore we start to build a SQL Cluster Lab, let’s look at the desired result. You will build a 3-node cluster replicating an environment that has two data centers. As a result, the first two nodes will reside in data center 1 and the third node in data center 2. We are creating this architecture because it is the most common architecture I see for Availability Groups. It is multi-subnet and can solve for both HA and DR.
You will notice the domain controller in the middle. That piece is certainly not representative of a production environment. However, we are using it in our lab for several different functions and being a router is one of them.
Part 1 is the only part which is currently up, but this looks like it will be a good one. Go buy a couple more sticks of RAM for your PC and get reading.
Comments closedNiko Neugebauer points out a small but interesting addition to SQL Server 2019:
Database Snapshots can serve for so many great purposes, such as Quick Restores and DWH/BI/Reporting reading operations, between others.
In-Memory is still the feature that I love a lot, and while the most active Programming Feature has not seen a lot (or enough) development, because frankly we are back to “Chicken & Egg, Who was first” kind of problem, I see little but important developments and most importantly feature integration. The original implementation in Sql Server 2014 and further improvements in Sql Server 2016 & Sql Server 2017 have improved the programming surface to being useful member of the toolkit, but some of the current limitations are dreading for a number of people and projects.
Well, it seems that Microsoft has been silently working on the improvements and one of them is the support for the Database Snapshots of the In-Memory File Groups in the Sql Server 2019.
I haven’t used database snapshots in a while, but I was fond of them for testing purposes and even wrote an ugly WinForms app to let devs manage them at a prior company.
Click through for Niko’s demonstration as well as a limitation with this.
Comments closed