A Fine Slice Of SQL Server
If you frequently connect to many different SQL Server as I do, you are probably used to the Server Manager loading slowly when you log in with Remote Desktop.
The Server Manager has a bad reputation for taking up lots of CPU over time and possibly even bogging down a SQL Server when left open for days on end.
To prevent this from automatically loading you can do the following to quickly disable it for your user session, and your future user sessions.
Read on for the steps.
Comments closedLori Brown has a script which lists installed SQL Server instances:
It is pretty common that I frequently am asked to take over a server with SQL on it sight unseen. Many times shops many not be fully aware if additional instances are or have been installed on their server. Since I am working on a set of scripts that we can use for discovery on new systems, I thought it would be handy to have a query that would give me a list of SQL instances with the version that are installed.
I found that this kind of information can be found in the registry which is accessible via a couple of undocumented extended procedures. Let me give the disclaimer right here…. Undocumented procedures from Microsoft are not supported and should not be used for regular production work. This is because they can change or be removed at any time. Now that I have stated that, the other obvious statement is that undocumented procedures can be pretty handy for some things, just use them with caution.
Click through for the script.
Comments closedBob Ward has a post on automatic tuning in SQL Server 2017:
One of the key points I’ve been making to our customers about SQL Server on Linux is that the core database engine is the same as on Windows. Since Automatic Tuning is built into the core database engine, this feature works exactly the same on SQL Server on Linux.
As you watched my demo of Automatic Tuning for SQL Server on Windows, I used the Windows Performance Monitor to show the workload performance when automatic tuning corrects a query plan regression problem. But what about Linux? Windows Performance Monitor will not work with that platform.
Turns out I have some smart people working with me at Microsoft. I got help from Pedro Lopes from our Tiger Team and the engineering team who built SQL Operations Studio to show automatic tuning using that tool.
Pedro helped me build scripts that would query the DMV, sys.dm_os_performance_counters, which runs on SQL Server on Linux as it does on Windows (because it is built into the core engine). These scripts store results from this DMV in a temp table, and then query the temp table.
The folks at SQL Operations Studio showed me how to collect the result set from the temp table in a manner that would allow me to show the data in a Time Series chart in the tool. Kind of like a static perfmon chart of SQL Server counters.
With a bonus shout out to Tracy Boggiano.
Comments closedOn Linux, we don’t have yet named instances, so all commands are executed against default instance. SQL Server can be managed using thesystemctl command.
To check the current state of SQL Server instance you can run this command:
sudo systemctl status mssql-server
Click through for more.
Comments closedYou may (or may not) have a requirement to setup a linked server to Azure SQL Database from a locally installed SQL Server. One reason could be to pull down some reports from an Azure SQL Database to a local file share. Whatever your reason is hopefully you will find this blog post useful because I ran into some complications on the way.
This is what your linked server creation screens in SSMS (SQL Server Management Studio) should look like.
Take advantage of Arun’s hard-earned experience and read his post.
Comments closedAdrian Buckman shows us how to re-send an e-mail that was sent through DB Mail:
Sometimes emails from SQL Server go missing, especially when you share an inbox with colleagues. On most occasions it doesn’t always matter as the job that generated the email can simply be re ran to produce the email once again, but what about those emails that contain time specific information? we cannot simple just kick off the job again as it may be coded to evaluate right now and not provide us with the information that was sent 2 hours ago.
I was faced with exactly this issue the other day – i needed the information from the email that was sent at that given time so I wrote a bit of code to get the job done then decided that if I tidy this code up I could make it reusable so here is what I come up with:
Check out Adrian’s helpful script.
Comments closedNed Otter announces a new tool for troubleshooting memory-optimized databases:
Instance level evaluates the following:
the version/edition of SQL server
SQL Server ‘max memory’ setting
memory clerks
XTP memory consumers, aggregated
XTP memory consumers, detailed
the value of the committed_target_kb column from sys.dm_os_sys_info
whether or not instance-level collection of execution statistics has been enabled for all natively compiled stored procedures (because this can kill their performance….)
when running Enterprise, if there are any resource groups defined, and which memory-optimized databases are bound to them
XTP and buffer pool memory allocations, because In-Memory OLTP can affect on-disk workloads
summary of memory used by XTP
There’s a lot of useful information you can get out of this procedure. Click through for the full documentation.
Comments closedKenneth Fisher has a script to take information out of the error log and put it into a table:
This is one of my favorite scripts. It pulls all of the data from the error log and dumps it into a temp table. I did one version here but it was pretty specific to I/O errors. I also gave some homework to find/build a script like it. So if you didn’t do the homework here is a nice little (more generic) script that I personally find extraordinarily handy. There are two parameters at the top that will restrict the data pulled in, and a query against #LogInfo at the bottom that filters out backupand logon entries since they tend to get in the way of what I’m looking for. That said, if you want to create a report on logons (or backups) you’ll want to modify the query. I do leave #LogInfo open and the end so you can run multiple queries against it.
Click through for the script.
Comments closedDave Mason shows how to configure alerts to fire when tempdb is low on disk space:
Naturally, the job runs on a predefined schedule. But how frequently should we check disk/available space for [tempdb]? The temporary nature of [tempdb] makes this a difficult question: objects within aren’t saved from one session of SQL Server to another, and evidence to explain runaway growth or loss of available space may be gone before an assessment can be made. Whatever schedule I decide on, I’ll always wonder if it’s frequent enough (or too frequent).
It’s tempting to “over-schedule” a job’s frequency, perhaps as much as every X seconds. Asking SQL Server “Are we out of disk space?” over and over again doesn’t make a lot of sense to me, though. It reminds me of Bart and Lisa asking Homer “Are we there yet?”until he snaps. Ideally, instead of asking, I want SQL Server to *tell me* when disk/available space is running low.
Read on to see how Dave does this.
Comments closedBrent Ozar has a few tips to see if you have too much memory:
Sounds impossible, right? The saying goes that you can never be too rich or too thin or have too much memory.
However, there is one good indication that your SQL Server is probably overprovisioned, and to explain it, I need to cover 3 metrics.
1. Max Server Memory is set at the instance level: right-click on your SQL Server name in SSMS, click Properties, Memory, and it’s “Maximum server memory.” This is how much memory you’re willing to let the engine use. (The rocket surgeons in the audience are desperate for the chance to raise their hands to point out different things that are or aren’t included in max memory – hold that thought. That’s a different blog post.)
If you have too much memory, I’d happily borrow a cup of RAM.
Comments closed