A Fine Slice Of SQL Server
For my specific issue I was attempting to debug why the SQLDriverConnectW errored with TCP 10054 because it was failing the pre-login handshake. For that I first needed to understand and capture the flow of the handshake activities and this is where SNITrace is helpful.
Read on to see what it does and how it works, as well as some nice Wireshark screen shots.
Comments closedGrant Fritchey is learning about PostgreSQL:
One of the first things I worry about when I’m looking at a new system is the status of the backups. I don’t see anything in PostgreSQL that will lead me to a different conclusion. However, I didn’t realize just how big a can of worms I would open by pursuing backups and restores.
Instead of talking immediately about backups, let’s talk about restores.
This is a bit different from what we’re used to in SQL Server, so definitely check it out if you are looking at Postgres development or administration.
Comments closedDavid Fowler is looking for lead blockers:
By far the most common cause for poor performance that I see is blocking. Unless you’ve got some monitoring in place it’s near impossible to identify the cause of any historical blocking incidents. Luckily, if you’re doing as I do and running the First Responder Kit regularly into tables, you will have a history of running statements thanks to sp_BlitzWho. That data will contain the SPIDs of any blocking processes.
Now, if you’ve ever had to look at that data and tried to figure out what the blocking chain is then you’ll know that it can be a total headache.
But David has a solution to find those fullbacks and pulling guards.
Comments closedPaul Randal answers a question about caching of compressed values in SQL Server:
In the previous Curious Case post, I explained how to monitor how well page compression is working. That prompted a reader to ask me how to monitor the hit rate of lookups in the cache of decompressed column values. She was very surprised by my answer…
Read on for Paul’s answer. I’d say that even with the information Paul discloses, I don’t think I’ve ever seen page compression be a bad idea in any system I’ve worked with. There are specific tables in which it’s a bad idea but never have I seen a situation in which I could not use page compression at all due to a performance impact.
Comments closedGrant Fritchey wants queryable data:
We’ve always been able to look at statistics with DBCC SHOW_STATISTICS. You can even tell SHOW_STATISTICS to only give you the properties, STAT_HEADER, or histogram, HISTOGRAM. However, it’s always come back in a format that you can’t easily consume in T-SQL. From SQL Server 2012 to everything else, you can simply query sys.dm_db_stats_properties to get that same header information, but in a consumable fashion.
Read on for a quick post showing a couple of things you can do with the DMF.
Comments closedChad Callihan wants to troubleshoot some queries:
We have Extended Events and Profiler in SQL Server for tracking database activity. What if we want to track queries in MySQL? Let’s take a look at a few methods to do just that.
Read on to see what options are available in MySQL. If you’re coming at MySQL from a SQL Server background, you won’t have exactly the same tools available to you.
Comments closedGrant Fritchey tests a restore plan:
I recently wrote an article about PostgreSQL restores (and by extension, backups) over on Simple-Talk. The restore process within PostgreSQL, without 3rd party involvement, can be a little tricky. However, when you are using a Platform as a Service offering, like Azure Database for PostgreSQL, things get quite a bit easier. Let’s explore this just a little.
Read the whole thing if you’re thinking about PostgreSQL or Azure Database for PostgreSQL.
Comments closedJeff Iannucci raises job awareness:
If you’ve ever had to implement Change Data Capture (CDC) for a database in an Availability Group, then you know that the CDC jobs don’t really consider the Availability Group. The capture and cleanup jobs created are set up as if the database exists only on a single instance.
And that’s a problem, because I would guess quite lot of databases are in Availability Groups. Maybe even some of yours. If you have this issue, I’ve put together a step-by-step solution in this post.
Click through for the Microsoft way and the Jeff way.
Comments closedDavid Alcock shows us the button not to press:
A while ago I presented a session which covered transformation rules that are used by the query optimiser to produce our execution plans. I’m not feeling in the mood for relational algebra this morning so instead I’ll introduce a command that can cause mayhem on an instance SQL Server: DBCC RULEOFF.
DBCC RULEOFF is an undocumented command, that alone makes me want to use it but in order to cover one’s backside please don’t do any of the following in any environment apart from a disposable sandbox that only you use for weird and wonderful experiments in SQL Server, because we are going to break it…
Read on for something you can but should not do.
Comments closedAndrea Allred has a script for us:
This week, I had a co-worker that was stuck. They no longer use SSMS and needed to rename a database. They asked if I had a script and so I wrote one. Here it is:
Read on for that script.
Comments closed