A Fine Slice Of SQL Server
One common issue that bugs me is where databases have been moved from one instance to another, usually through backup and restore, and the files haven’t been moved as part of the restore so they get recreated in the data\log folders for the old instance.
This has caused me various problems, e.g. working out which instance is hammering the disk or using up all the space.
Click through for the script.
Comments closedArun Sirpal noticed a problem when he tried to copy an Azure SQL Database:
Now, I was looking at the following code.
CREATE DATABASE CodeDBP1 AS COPY OF CodeDB ( SERVICE_OBJECTIVE = 'P1' ) ;You would think this is okay? I did, especially with the fact that it parsed and was executing. I was thinking a copy of the CodeDB database will be created as a premium P1 database regardless of what the source database service tier was. This source database is 0.5GB in size under the basic tier and 40 minutes later the copy was still executing. It just didn’t seem right.
Click through for the solution. If this is going to be normal behavior, I’d really like to see an error message.
Comments closedJana Sattainathan has a procedure which makes it easier to read the error logs in SQL Server:
xp_ReadErrorLog has some limitations
- Reads only the specified error log whose ArchiveNumber is specified
- Shows only the rows with matching string (not adjacent context info rows)
The first bullet is obvious in that we cannot read ALL the logs to look for something more holistic and meaningful with all the information we have. This MSSQLTips post does describe a method to loop through.
Click through for Jana’s stored procedure code.
Comments closedRobert Cain has part one of a two-part series on creating VMs in Azure using Powershell:
Creating a virtual machine is great, but it won’t be of much use unless it can communicate outside of itself. That’s where virtual networking comes in. To setup a virtual network, often abbreviated vnet, you need to accomplish three things. First is the creation of the virtual network itself. After the network is created, you need to define a security group for it. In essence, the security group defines a firewall. In the process of creating it, the PSAzure module automatically creates firewall rules that allow HTTP and RDP (Remote Desktop Protocol) traffic through the firewall. There are functions in PSAzure to create security groups at a lower level, allowing one to create alternate rules. This example will demonstrate the most common options.
The final step is to create a virtual NIC, or Network Interface Card. The NIC will form the bridge between the virtual network and the virtual machine, much like a physical network card allows a physical computer to connect to a real network. First off, a few variables are assigned. These will hold names for the security group, network and subnet names. The network addresses for the main network and subnet are also placed into into variables. Finally, a name is assigned to the NIC.
Check it out, especially if you build a lot of VMs in Azure.
Comments closedI don’t need to validate SQL Server installations on a regular basis. When the need arises, my preference is to run the SQL Server features discovery report. Further, I prefer to run it from the command line. After looking up command line parameters one too many times, I decided to script it out.
It turns out the script commands are a little more complicated than I realized: there is a different setup.exe file for each version of SQL Server installed. I ended up making two script versions: a DOS batch file with hard-coded paths, and a PowerShell script that’s more robust. Check them out and let me know what you think. (Keep scrolling down for a report sample image.)
I’m not sure I’ve ever run that report, but now I know how to do it from Powershell.
Comments closedTracy Boggiano continues her series on configuring Telegraf:
The solution for this is fairly simple now that you have setup Part 1 of this series. You can download the Windows conf file for Telegraf from my presentation. Below are the important pieces of the file. The main part of the OUTPUT PLUGINS being to place the data in the InfluxDB database. The data will be housed in the same database as our SQL performance metrics. Next, you can collect any Windows Performance Counters you want and group them into a “Measurement”. I’m using the dashboard that is on the Grafana website along with the performance metrics they have set up to be collected.
Click through for Tracy’s setup script to get an idea of which Perfmon counters she’s tracking.
Comments closedHamish Watson shows how easy it is to synchronize SQL authenticated logins using dbatools:
When building new servers the most important thing after restoring and securing the database is syncing up the users. This is especially important for Availability Groups as SQL Authenticated users required the SIDS to be the same.
In the past I had some very long winded code that would do the sync – it was a mixture of TSQL and PowerShell. It worked but you know – it was cumbersome.
Read on to see how life gets easier with dbatools.
Comments closedJoe Obbish walks us through a scenario with automatic soft-NUMA leading to poor performance:
Consider a server with soft-NUMA nodes of 8 schedulers with
MAXDOP 8. The first parallel query will be sent to numa node 0. The number of active workers matches the number of schedulers exactly so each active worker is assigned to a different scheduler in the NUMA node. The second parallel query will be sent to NUMA node 1. The third parallel query will be sent to NUMA node 2, and so on. Execution of serial queries or creation of sessions does not matter. That advances a counter that’s separate from the “global enumerator” used for parallel query scheduler placement. As far as I can tell the scheduler assigned to execution context 0 does not affect the scheduling of the parallel worker threads, although it can certainly affect parallel query performance.The scenario described above doesn’t sound so bad. It can work well if the parallel queries take roughly about the same amount of time to complete and query
MAXDOPmatches the number of schedulers per soft-NUMA node. Problems can emerge when at least one of those is not true. With the spread selection type it’s possible that the amount of work already assigned to schedulers has no effect on parallel query scheduler placement. Let that sink in. You could have 100 serial queries all assigned to schedulers in numa node 0 but SQL Server may send a parallel query to that NUMA node. It depends on the position of the “global enumerator” as opposed to current work on the server.
Joe offers up some alternatives if you find yourself dealing with this issue. Definitely a must-read.
Comments closedKenneth Fisher wants to delete a lot of rows:
I recently had the task of deleting a bit over a billion rows from a table. Now I could have done just this:
DELETE FROM tablename WHERE createdate >= '1/1/2017'But I have a few problems here. The table has no index on createdate, potentially causing problems with tempdb (the sort on createdate). Although in this case tempdb is pretty large because of some large batch work done at various times. I’m also going to be deleting > billion rows of ~6 billion which is probably going to fill up the log of the database (which fortunately isn’t in use yet) and end up rolling back my delete anyway. Even if I don’t fill up the log, I’m still going to bloat it pretty badly (autogrowth). And last, and anything but least, this is on a production server. Even if this database was on its own drive (meaning growth of the log can’t cause a problem with any other databases) that tempdb thing (let alone other resource usage) is going to be an issue.
Read on to see how to delete in batches. My pattern is to have an explicit transaction within the WHILE loop, opening and closing for each deletion operation. That has worked pretty well in the past when deleting large numbers of rows from a table. It might also make sense to put a temporary filtered index on the table, dropping it afterward.
Comments closedTracy Boggiano has a top 5 list of tools she uses on a day-to-day basis:
This T-SQL Tuesday is brought to us by Jens Vestergaard (b | t), and we are asked to share our favorite SQL Server tools. Hint Profiler will not be on the list. But where do you start there are so many tools out there. In alphabetical order here are my top 5 tools because I can’t pick which one is better than other.
Click through to see Tracy’s top 5 list.
Comments closed