A Fine Slice Of SQL Server
For this example, let’s assume that we have a physical server with 512 Gigs of RAM and two physical NUMA nodes (and two CPU sockets). We have a VM running in that machine which has a low CPU requirement, but a large working set. Because of this we have 4 cores and 360 Gigs of RAM presented to the VM.
The answer is not trivial, making this an interesting question.
Comments closedJames Anderson had a database which would drop into In Recovery mode a few times throughout the day:
The database in question wasn’t stuck in recovery, it would slip in and out of the status throughout the day. Normally, I would only ever expect to see a database in recovery during a restore or after a service restart. Once recovery is complete, I would not expect to see the database slip into ‘in recovery’ again. I especially wouldn’t expect a database to keep slipping in and out of recovery.
The answer is a true head-slapper. Whose head, I’ll leave up to you…
Comments closedSlava Murygin uses spatial data types to graph CPU utilization on a SQL Server instance:
That diagram provides you about 260 last minutes of Server CPU usage and measured in percents.
As you can see my SQL Server is mostly doing nothing and only during that blog-post writing in the last half and hour or so it is heavily running test scripts to over-utilize my CPU, but it still barely goes more than 60% of CPU (Blue line).
The Red line represents all other processes besides of SQL Server and you can tell if anything else from outside is impacting your performance.
Combined with Glenn Berry’s diagnostic queries, you could generate some quick analytics. I’d still use R for anything more than slightly complicated, but this is great for those environments in which you don’t have good alternative tooling.
Comments closedPerry Skountrianos talks about Windows Containers and running SQL Server in one:
We are currently working on testing and publishing SQL Server Container Images that could speed up the process of getting started with SQL Server in Windows Containers significantly. Stay tuned for an update!
Windows getting into the Docker world is interesting.
Comments closedI have a new script available to reorganize columnstore indexes:
Note that this script requires SQL Server 2016 (or later) because the database engine team made some great changes to columnstore indexes, allowing us to use REORGANIZE to clear out deleted rows and compact row groups together, as well as its previous job of marking open delta stores as available for compression.
The code is available as a Gist for now, at least until I decide what to do with it. Comments are welcome, especially if I’m missing a major reorganize condition.
As mentioned, comments are welcome.
Comments closedJason Brimhall digs into an error where page-level locking is disabled:
You receive the error message similar to the following:
Msg 2552, Level 16, State 1, Line 1 The index “blah” (partition 1) on table “blah_blah_blah” cannot be reorganized because page level locking is disabled
Immediately, you start double-checking yourself and verifying that it worked the previous night. You even go so far as to confirm that the same index was previously reorganized. How is it possible that it is failing now on this index. What has changed? Has something changed?
There’s an interesting troubleshooting story, but the important message is about setting up a good set of Extended Events so that you can troubleshoot these types of problems.
Comments closedIn the code below, the first thing we do is enable Ad Hoc Distributed Queries so we can try out the OPENROWSET method. The advantage to this method is not having a linked server and being able to call it directly out of TSQL. Once we have that enabled we write our query and you’ll notice that we are essentially doing 2 queries. The first query is the LDAP query inside the OPENROWSET function. Once those results are returned we are using another query to get what we want from the result set. Here is where I want you to stop and think about things. If my LDAP query pulls back 50 attributes, or “columns” in SQL terms, and I tell it I only want 10 of them, what did I just do? I brought back a ton of extra data over the wire for no reason because I’m not planning to use it. What we should see here is that the columns on both SELECT statements are the same. They do not, however, have to be in the same order. The reason for that is because LDAP does not guarantee to return results in the same order every time. The attribute or “column” order in your first SELECT statement determines the order of your final result set. This gives you the opportunity to alias anything if you need to.
You can query LDAP using SELECT statements, but the syntax isn’t T-SQL, so in my case, it was a bit frustrating getting the data I wanted out of Active Directory because I was used to T-SQL niceties. Nevertheless, this is a good way of pulling down AD data.
Comments closedJust recently a reply was made to the Connect item, highlighting the fact, that the current values of the Data/Log/Temp and Backup Directories – meaning the currently configured values – is exposed through the Server.ServerProperties collection. According to the answer, only public property values are exposed.
Using PowerShell, we can now retrieve the desired information from any given instance of Analysis Services. Doing so would look something like this:
It’s good to know that this information is available via Powershell.
Comments closedJon Morisi shows how to use the default trace:
Often times while troubleshooting an issue, you’ll want more details than what you can find in the application log or SQL Log. In the background, SQL Server runs a default trace which includes a lot of items to help with troubleshooting including (but not limited to) errors, warnings, and audit data. I often run the following script as a quick way to find additional details for “ERROR” items from the default trace.
Jon notes that the default trace has been put on the deprecation list, so keep that in mind if you do use it.
Comments closedSo why would you want to do this? Well lets say for example you have a table in a database where the clustered index has become corrupted. Let’s further say that no one mentioned this to you for .. say a year. (No judging!) So your only option at this point might be to use the REPAIR_ALLOW_DATA_LOSS of DBCC CHECKDB. But when you are done how much data has actually been lost? Can you get any of it back?
If you’ve lived a good life and are very lucky, you might recover all data this way. Otherwise, it’s a good idea to run CHECKDB more frequently and check those backups regularly as well.
Comments closed