A Fine Slice Of SQL Server
Before we begin, if you are running Windows 7 or if you do not have the Active Directory module installed, please do so first by downloading and installing “Remote Server Administration Tools”. I personally cannot live without this module.
Let us call the new employee as NEW_USER and the existing employee as OLD_USER. Here is how to use PowerShell to do the comparison.
The retrieval and comparison code is pretty easy; click through to see.
Comments closedDavid Klee notes that vSphere 6.5 might modify vNUMA settings on you:
I appreciate their attempt to improve performance, but this presents a challenge for performance-oriented DBAs in many ways. It now changes expected behavior from the basic configuration without prompting or notifying the administrators in any way that this is happening. It also means that if I have a host cluster with a mixed server CPU topology, I could now have NUMA misalignments if a VM vMotions to another physical server that contains a different CPU configuration, which is sure to cause a performance problem.
Worse yet is that if I were to restart this VM on this new host, the hypervisor could automatically change the vNUMA configuration at boot time based on the new host hardware.
I now have a change in vNUMA inside SQL Server. My MaxDOP settings could now be wrong. I now have a change in expected query behavior.
Definitely read the whole thing if you’re using vmWare.
Comments closedInstantly it is apparent that the most resource intensive query was the same query across both workload tests and note that the query hash is consistent too. It is also apparent that this query performs worse under the new cardinality estimator model version 120. To investigate and understand why this particular query behaves differently under the different cardinality estimators we’ll need to look at the actual query and the execution plans.
Looking at the information in #TempCEStats and the execution plans, the problematic query below belongs to the SLEV stored procedure.
There’s also a discussion of Query Store in there, but it’s important to understand how to figure this out even if you’re on 2014 and don’t have access to Query Store.
Comments closedDaniel Hutmacher has released a new tool:
I designed my sp_ctrl3 procedure from the ground up to be a development tool that I can install on any dev environment that I use regularly. I wanted a detailed overview of a specific database object as well as copy-paste-friendly T-SQL code. For instance,
No “length” column on an int column
Proper scale/precision values on datatypes
“NULL” or “NOT NULL” instead of “yes” or “no”
Identity column syntax
“nvarchar(50)” instead of “nvarchar”, “100”
Column defaults
Complete index definitions
GRANT/DENY permission statements
The object_id of the object in sys.objects
If you use sp_help a lot, this looks like a good supplement.
Comments closedDave Bermingham shows how to configure automatic shutdown of Azure VMs:
If you are like me, I try to make my Azure MSDN subscription credits stretch the entire month. I’m typically just building labs to try out new features or to demonstrate SQL Server Failover Clusters in Azure. A lot of the time I am testing some pretty large instance sizes with plenty of premium storage. As you can imagine, you can burn through $150 pretty quick with a few GS5 instances running.
I try to be mindful and shutdown or destroy instances once I am done with them, but occasionally I’ll get pulled away for other business, only to log in the next day and see my credit has expired because I forgot to turn off the VMs.
Click through for details, including a warning about storage.
Comments closedTim Spann has produced a guide for HDFS shell commands:
There are number of commands that you may need to use for administrating your cluster if you are one of the administrators for your cluster. If you are running your own personal cluster or Sandbox, these are also good to know and try. Do Not Try These In Production if you are not the owner and fully understand the dire consequences of these actions. These commands will be affecting the entire Hadoop cluster distributed file system. You can shutdown data nodes, add quotas to directories for various users and other administrative features.
Many of the commands in this list should be familiar if you know much about Linux or Unix administration, but there are some Hadoop-specific commands as well, like moveFromLocal and moveToLocal.
Comments closedKenneth Fisher is on a mission:
HAL0001 randomly stopped you from making DDL changes.
HAL0002 stopped you from using NOLOCK in code.
HAL0003 will not let you touch a given table (DiscoveryOne as it happens) and will disable your login and kill your connection if you try.
He’s got a ways to go, but I applaud his long-term vision.
Comments closedDavid Alcock sounds an important note:
All of that is very good however a word of warning has been issued from the MSSQL Tiger Team if you are using SP1 on the “lower” version:”you might see some unforeseen errors or databases might even be left in suspect state after uninstallation of SQL Server 2016 SP1. Even worse would be if the system databases are using new features for example, partitioned table in master database, it can lead to SQL Server instance unable to start after uninstalling SQL Server 2016 SP1″.
This makes sense: if you’re using new functionality and try to revert back to a version without that functionality available, there could be an issue. David links to a test script you can use to see if your database is using any new features.
Comments closedKendra Little discusses the locks entry in sp_configure:
Each lock uses 96 bytes of memory. On the instance in question, 25,000 locks = 2,400,000 bytes.
That’s only 2.3 MB of memory devoted to locks. Even though 25K sounds like a lot, the memory footprint for that is pretty darn small.
I checked back with our questioner, and their instance has 32GB of memory. That’s a pretty small amount in the grand scheme of things (as of SQL Server 2014, Standard Edition can use up to 128GB of memory for the Buffer Pool), but 2.3 MB isn’t anything to worry about, percentage wise.
Read on for advice if you’re seeing your SQL Server instance take a very large number of locks.
Comments closedAaron Nelson has a post on using Powershell to visualize contents in the buffer pool:
On Monday Chrissy LeMaire & I Did a session called “SQL Server Cmdlets and Community Involvement” for the PowerShell 10 Year Anniversary all-day event on Channel9 on MSDN. If you jump to the 18 minutes 30 second mark of that video you’ll see me showing how to look at the Buffer Pool of your SQL Server instance, first with the Out-GridView cmdlet, then I used a function from PowerShell MVP Boe Prox ( b | t ) called Out-SquarifiedTreeMap like so:
Read on to get a link to the code.
Comments closed