A Fine Slice Of SQL Server
Chad Callihan lost a transaction log file in a boating accident:
What if you’re moving a database to a new server by detaching and re-attaching database files and someone (not you of course) loses the log file? What if an old database needs to be brought online but the person coming to you only has an mdf file? Can you still attach the database in these scenarios? Let’s find out.
Read on for the answers.
Comments closedDavid Klee creates some files:
Do you have quite large MDF files on your database? By large, I mean hundreds of gigabytes (or larger). Have you ever noticed that your SQL Server disk stall metrics for these data files are much higher than the storage latency metrics exhibited on the underlying operating system layer? It could be that your SQL Server data files are being hammered too hard and you don’t have enough data files to help the SQL Server storage engine distribute the load. We do this for tempdb, right? Why don’t we do this enough for our user databases as well? It’s easy for a brand-new database from day zero, but what about existing databases that have grown out of control with a single data file attached? Let me show you how to adjust this for existing databases without an outage!
Check it out. This is a part of database administration I’d never really thought much about, so it often ended up being a blind spot for me.
Comments closedRobert Sheldon performs some level-setting:
SQL Server includes a set of configurable options known as trace flags. You can use trace flags to set server characteristics and control different types of operations. SQL Server offers a wide range of trace flags that let you modify the platform’s default behavior to meet specific requirements. Trace flags can help you when performing such tasks as testing stored procedures, diagnosing performance issues, or debugging complex computer systems. Microsoft Support might also recommend using certain trace flags to address behavior that’s impacting specific workloads. This article explains how to enable SQL Server trace flags.
Click through for the article.
Comments closedAndy Mallon says it’s time to give up a couple of procedures:
We recently ran into some performance problems with our login sync, which is based on
sp_hexadecimalandsp_help_revlogin, the documented & recommended approach by Microsoft.I’ve been installing & using these two procedures since I started working with SQL Server, back at the turn of the century. In the nearly two decades since, I’ve blindly installed & used these procedures, first on SQL Server 2000, and then on every version since… just because that’s the way I’ve always done it. But our recent performance problems made me rethink that, and dive in to take a look at the two procedures to see if I could do better, which made me realize, OHBOY! WE CAN DO BETTER!!
Read on to understand how.
Comments closedSolomon Rutzky tries Betteridge’s Law of Headlines:
Over time I’ve used a variety of SQL Server versions with a variety of instance-level collations. This has lead me to conclude that
sysnamehad to be all lower-case in some earlier versions if the instance-level collation was either binary or case-sensitive. However, it was not clear exactly which versions and what scenarios truly affected the behavior ofsysnamename resolution, so I played it safe and continued to always specify that particular data type in all lower-case, even leaving comments in scripts that it must remain as all lower-case (just in case someone goes through and tries to make it upper-case to be consistent with the other data types).But then, while researching another topic, I recently found the following in some old documentation ( Breaking Changes to Database Engine Features in SQL Server 2005 ):
Read on for the results of Solomon’s archaeological expedition.
Comments closedChad Callihan explains the importance of a feature:
Anytime you run a query, SQL Server needs to build an execution plan to use as directions for best executing that query. These execution plans can be stored in your plan cache to be reused in the future if that same query is ran. Instead of resources going into rebuilding the plan each time, SQL Server can use the same plan as the previous execution. This is great for queries that run over and over. On the other hand, what if you have a large number of queries that will run once but never again? Plans never to be reused are taking up valuable space in plan cache. If this looks like your workload, consider enabling the Optimize for Ad hoc Workloads feature.
Click through to learn more about the feature. I haven’t seen many (any?) cases where Optimize for Ad Hoc Workloads doesn’t help at least a little on net.
Comments closedKenneth Fisher lays out preferences between two upgrade paths:
Most discussions like this start with the fact that an in-place upgrade is far easier but riskier if anything goes wrong. Side-by-side is more work and you run the risk of forgetting something. But if something goes wrong you just move back to the old instance.
I thought about something that isn’t usually part of the discussion this week while working on a side-by-side migration.
Read on for Kenneth’s insight. My general preference is side-by-side updates on a new server, as that helps get rid of operating system bit rot as well.
Comments closedI’ve used the Ola Hallengren Maintenance Solution across various SQL Server environments . I was recently asked by a colleague about how adaptable they are to the AWS RDS SQL Server environment.
I checked the Ola Hallengren FAQ and there is a comment :
Read on to learn the details.
Comments closedKiana Bergsma announces a new tool:
Save yourself Time and Money with Steve Stedman’s new database comparison tool. Following in its cousin’s (Database Health Monitor) footsteps, SchemaDrift is FREE! It is currently in Beta phase but it is free to download for personal or business use.
We only ask that you give us feedback. Let us know what you like and dislike. How can we make this product even better and we’ll send you emails on update releases. Comment down below or message us through our website.
Click through for a download link, as well as a FAQ in video form.
Comments closedRobert Sheldon gives us a primer on hyperconverged infrastructure:
A growing number of organizations have deployed hyperconverged infrastructure (HCI) systems in an effort to simplify IT operations, better utilize resources, and lower costs. They might house the systems in their own data centers, colocation facilities, edge environments, or office closets. Regardless of the location, many of the organizations are running SQL Server on their HCI systems, often alongside other applications. Although it means deploying SQL Server to a virtualized environment, such a practice has become fairly common, especially with the advent of the cloud. This article covers hyperconvergence, another option for SQL Server.
Despite how common hyperconvergence has become, some IT teams might still not be familiar with HCI or are familiar with HCI but have not deployed SQL Server to an HCI platform. In either case, they might now be considering HCI for SQL Server and need to better understand what this looks like before deciding on new infrastructure. Although HCI can make it easier to provide a platform for SQL Server, decision-makers should know what they’re getting into before going down this route.
Click through to learn more about hyperconverged infrastructure and where it can help (or hurt).
Comments closed