A Fine Slice Of SQL Server
Jason Brimhall takes us through a bit of a mystery:
Take quick note of the port number I have circled in red. This doesn’t match the original query at all. In fact, it doesn’t come anywhere close to the actual port number. In addition, the port number shown here is a negative value. Obviously a negative port is not correct as TCP/IP ports only range from 0-65535. So what is happening here?
Read on for the answer.
Comments closedEmanuele Meazzo has a new years’s gift for us:
Welcome to 2020! I wanted to start this year by giving to all my fellow consultants another way to troubleshoot our beloved SQL Servers; I’ve already talked about diagnostic notebooks in the past, and now, since Azure Data Studio has implemented the feature, I wanted to group them into a Diagnostic Book.
As the name implies, a jupyter book is no other than a collection of notebooks (and markdown files) that groups everything in a coherent space, with an index and navigation options alike.
I think this sort of collection of notebooks (a, uh, note-book), if put together well, makes it easier to learn a new environment and understand key problems than a big Scripts.txt file or a folder full of scripts.
Comments closedJason Brimhall gives us a solution to track index changes:
In the previous article, I showed a quick and dirty method to try and capture the indexes that may have been just created. We all know that in the hectic life of the DBA, it is easy enough to forget the indexes created more than just a few hours in the past.
In that article, I mentioned that the solution provided was far from a complete solution to the entire problem. A bigger part of the picture involves audits to know when and by whom indexes were created, altered or dropped.
Keeping track of this kind of thing is important, particularly in an environment where performance suddenly changes even though the devs totally didn’t touch anything, promise.
Comments closedThe Oracle SQL Developer product has come a long way since it’s inception and much of that credit needs to go to the incredible team at Oracle, including those that are prevelant in the Oracle community, Like Jeff Smith, Kris Rice and Ashley Chen. Their willingness to listen to the Oracle community and turn their needs into features has been one of the critical reasons for the product success.
Although this product is more focused towards the developer, unlike the previous three, I want to point out a few areas that hopefully will convince you there are more similarities than differences.
The shortest version of this is “the same tools as exist on-prem” but if you don’t know that answer, Kellyn’s got you covered.
Comments closedMonica Rathbun points out some of the trace flags which are no longer important in SQL Server:
If you have ever attended one of my performance tuning sessions, you know I tend to talk about trace flags. Trace Flags can help fix performance issues and some are now defaulted in later SQL Server versions. In my opinion, when a trace flag’s behavior defaulted in a version, then you should potentially put them in place within environments that do not have them implemented. Below, are a few of these particular traces flag along with Microsoft’s definition of what each trace flag does, taken straight from MS documents. I have also included a brief commentary on each one. As with any change, you should be sure to thoroughly test before implementing these trace flags into any production environment.
Read the whole thing, especially because at least one of them is still optional and defaulted to off (but able to change at a different scope).
Comments closedJamie Wick has some advice for managing Azure on-premises data gateways:
When organizations become aware of the On-premises Data Gateway application, there can be an explosion in the number of gateways that are connected to the tenant. Some people will invariably install a ‘temporary’ gateway on their local computer while they investigate/develop a new PowerBI report, PowerApp or Azure Automation process. All of these ‘temporary’ gateway instances can create problems when the systems that use them are moved into production and remain connected to the ‘temporary’ gateways.
In a previous post, I wrote about how to install and use an On-premises Data Gateway to connect on-site data with Azure-based apps (PowerBI, PowerApps, etc). Now we’re going to look at how to curtail the sprawl of gateways that may be occurring and how to remove any existing gateways that are deemed to be unnecessary.
Sprawl happens, but Jamie shows you how to limit it.
Comments closedChanging a column from
inttobiginthas gotten a lot easier since I started working on SQL Server back at the turn of the century. SQL Server 2016 introduced the ability to doALTER TABLE...ALTER COLUMNas an online operation using theWITH (ONLINE=ON)syntax. This wonderful syntax now allows you to alter a column frominttobigintwithout causing major blocking. Theinttobigintconversion is one of the most popular data type changes I see–A developer inevitably creates the table thinking they will never have more than 2 billion rows… then some years or months later 2 billion becomes a reality.The DBA is left with the task of implementing that data type change, and now that it has almost 2 billion rows, it’s a well-established table and uptime during the change is a major consideration.
This is a great post from Andy. If you want to dig into the concept of near-zero downtime in more detail, I’ve got a series on the topic.
Comments closedAnnette Allen had a service pack installation go south due to missing MSIs:
I was recently doing a service pack, I’d run it on the entire test estate and half of the Production estate, I’d used Pinal Dave’s really useful AG check list and been really overcautious, I’d finished integrity checks on all databases, I’d backed everything up and even had a snapshot of the server completed.
When I clicked on the service pack I got the error “missing MSI” sorry I don’t have the screen dump or the full error message because at the time of trying to fix it I did’t think to take a copy
Read on to see what the root cause was and how Annette was able to fix this error.
Comments closedRecently I set up an Azure VM running SSRS for my students to host their database and report projects. My goal was to set up a custom domain name and use SSL. I ran into a few issues and a couple of wrong paths so I thought this would be helpful for others trying to accomplish the same. I’m not going to do a step-by-step walkthrough, but instead a checklist to help you troubleshoot
It’s not step-by-step, but it’s pretty close.
Comments closedONLINE operations in SQL Server were simple to understand for years — we got ONLINE index rebuilds in SQL Server 2005. That was it for a while. Then, things got more complicated: we got more types of indexes. We got ONLINE options for schema changes that don’t involve indexes. We got more options for managing things like blocking, because online operations are really only mostly online — generally there’s going to be at least a short period where an exclusive lock is needed to update metadata. We now have some RESUMABLE operations coming in, too, for those big operations that are tough to handle.
Along the way, I fell behind. Because these features have steadily come out over a period of time, my brain simply didn’t register them all, or possibly I missed seeing them amid other announcements.
It’s not a comprehensive list, but it’s a good starting point for understanding the options you have available.
Comments closed