Configuration

Get Certificate Details for SQL Encryption In-Transit Using PowerShell

In my opinion, all customers should be encrypting connections to their SQL Servers to secure data in-transit, but that’s a discussion for another day. Today, I wanted to discuss how you can use PowerShell to lookup details on the certificate being used for that encryption. For this example, we have three SQL Server instances each configured slightly different for encrypted communication. Using the SQL Server Configuration Manager, we can see the first instance is configured to use a certificate named “deathstar.govlab.corp (SHA256)”. The FORCE ENCRYPTION option is set to YES to make it mandatory for all connections to use this...

Continue reading...

Finding the NTFS Allocation Unit Size for Mount Points

The 64Kb Allocation Unit size is one of the easiest SQL Server best practice items to complete during your new builds, but from time to time, I still see this one overlooked. And I know this topic has been beat to death over the years, so we are not going to cover the why you should format volumes using 64Kb Allocation Size. We are just going to cover how to check the current value. In our setup, we have a server with 4 disks attached; two are mounted with drive letters (M: and S:) and two are mounted as folders...

Continue reading...

SQL Server has encountered % occurence(s) of cachestore flush for the % cachestore (part of plan cache) due to some database maintenance or reconfigure operations

Have you seen this type of an informational message in your SQL errorlog before?  If so, then I hope the timestamp associated with it was not during the middle of your peak processing time.  This is a good indication that someone has been tinkering with some of the SQL configuration settings. SQL Server offers a lot of wonderful configuration options; many of which can be changed dynamically using sp_configure without the need of restarting the SQL Server service.  The downside to the dynamic changes is sometimes they can have a detrimental effect on performance, and that’s what this “informational” message is tell...

Continue reading...

Which SQL Instance is Killing Your CPU?

You have 5 instances running.  How do you know which one is killing your CPU? As you can see from Windows Task Manager, you have several instances of SQL installed and one of them is using nearly half of the CPU.  To figure this out, you’ll need to know the PID (Process ID) number.   To view the PID number in Windows Task Manager, from the menu select View, then Select Columns, and then check the box for PID.  For our example, it’s Process ID 1212 that’s using nearly 50% of our CPU. So how do you correlate that PID...

Continue reading...

How to Make SQL Server Listen on Multiple Ports

Have you ever needed an instance of SQL Server to listen on multiple TCP/IP port numbers but didn’t know how to implement it?  Well here’s a quick step-by-step guide that will work for SQL Server 2005, 2008, and 2012. Open SQL Server Configuration Manager. On the left hand side, expand SQL Server Network Configuration. Select your instance name. On the right hand side, right click on TCP/IP and select Properties. Click the IP Addresses tab. Scroll down to the IPALL section. Make sure the TCP Dynamic Ports field is empty. In the TCP Port field, enter each port number separated...

Continue reading...