Steve Jones (@way0utwest), Grant Fritchey (@GFritchey), and I are often asked to predict trends in technology, especially at the end of the year. This year we were asked about the kinds of challenges and trends, especially for monitoring and DevOps, that will be seen for database professionals in the near future. Just like the answer … Read more
Keeping SQL Server instances patched can be a time-consuming task for DBAs. In this article, Alejandro Cobar explains how he created a service in Azure that anyone can use to retrieve the build information for SQL Server.… Read more
SQL Notebooks, based on Jupyter Notebooks, is one of the best things about Azure Data Studio. In this article, Edward Pollack explains how to get the most out of SQL Notebooks.… Read more
Complex integrity constraints are more challenging to enforce in Cassandra than they are in a relational database. In this article, Shel Burkow walks through four modelling examples in Cassandra involving constraints.… Read more
Every Database Administrator, developer, report writer, and anyone else who writes T-SQL to access SQL Server data, must understand how to read and interpret execution plans. My book leads you right from the basics of capturing plans, through how to interrupt them in their various forms, graphical or XML, and then how to use the … Read more
As new versions of SQL Server have been released, the Query Optimizer has undergone many improvements. Adaptive Query Processing, new in 2017, represents a new direction. This set of features provides some flexibility for varying join types, multi-statement table valued functions, and memory grants. In this article, Robert Sheldon explains the three features that make up Adaptive Query Processing … Read more
I have a great interest in hierarchy solutions, as I plan to write a book on implementing hierarchies in SQL Server in the next year or so (after I see what happens in SQL Server vNext after 2017). Something I didn’t include in my design testing when I built my presentation on hierarchies (available from … Read more
There are a few topics in SQL Server that I love academically, but are truly a pain to get right. The other is security, and it pales in comparison to how much trouble it is to program for truly safe concurrent access to a resource. The biggest issue: time. What makes it more difficult is … Read more
Blocking in SQL Server is normal behavior, but excessive blocking can lead to many complaints and is a symptom of poor performance. SQL Server contains a vast amount of information that can help when troubleshooting performance issues like blocking, but understanding and gathering that information when you need it is a lot of work. Darko Martinovic explains his solution for a custom blocking report.… Read more
Starting in SSMS version 17.3, we have an interesting new feature: Extended Events Profiler (XE Profiler). This new feature is in a very early stage, but it seems to be a first step to replace SQL Profiler, which is already deprecated. Two different session templates appear under the new ‘XE Profiler’ item in object explorer: ‘Standard’ … Read more
Query Store is becoming more and more important for server monitoring, specially in SQL Server 2017, which allows us to get historical information about wait stats. We can import query store information into power bi and create a very useful dashboard to monitor SQL Server. Query Store tables are system tables, due to that we … Read more
Several experts already wrote about CXPACKET before, there are plenty of articles about this subject on the internet, so, why one more? I think I can add my two cents to this subject with an example to emphasize an important sentence that several authors wrote, with small differences: “CXPACKET is not a problem by itself, … Read more
The working life of the DBA can be punctuated by surprises, but they aren't generally nice surprises. This is especially true if the DBA is not checking and monitoring the databases for obvious things such as database corruption, and disk space. However, the really scary surprises are less obvious and provide fewer warning signs. Brent Ozar gives six scary surprises that can be avoided by the shrewd DBA.… Read more
A few weeks ago I published an interesting article about how to use query store to identify parameterization problems. At that point I hadn’t played with the new SQL Server 2017. While later, when I did, I got surprised by the new ‘Queries with High Variation’ graph included in query store. This new graph can … Read more
Several database backup providers are now offering ‘Database Backup to the Cloud’, or ‘Backup As A Service’ (BaaS). There is little consensus within the industry as to what this actually means in terms of the service offered. ‘Cloud Backup’ can mean little more than copying database backups to Cloud ‘file lockers’ such as DropBox, but can include enterprise-wide solutions with central management, one-click restore, advanced compression over the network, aggregation of links to increase bandwidth, express dispatch of a hard drive with the data on request (e.g. AWS Import/Export), and minimization of storage requirements using deduplication. The main attraction of any of these ‘cloud’ services is that they provide offsite database backups. The more sophisticated solutions provide a way of meeting a company’s Disaster recovery requirements at a lower capital cost and in a way that includes outlying data in a company-wide backup regime. The simpler offerings aim to provide regular automatic offsite database backup where there was none before.… Read more
Without a doubt, Entity Framework is a quick and satisfactory way of producing a database-driven web application. As performance becomes more important, it does, however, require some knowledge of the traps that you need to avoid, and of the wrinkles that impact performance. Ben Emmett gives a practical guide.… Read more
Every time you access a relational database to make a query, you have an important decision to make: What is the appropriate isolation level for your query? If you get this wrong, the consequences can be serious. Deadlocks, Dirty reads, Non-repeatable reads, or poor performance. We're in luck, because Robert Sheldon once more answers those questions that are awkward to ask in public.… Read more
When a SQL Server instance deadlocks, it can be anything from minor irritation to something far more severe. In this article, Gail Shaw looks at how you can identify common types of deadlock, the difference between a deadlock and severe blocking, and how to avoid and fix the most common deadlock types.… Read more
The continuing drop in the price of memory has made fast in-memory OLTP increasingly viable. SQL Server 2014 allows you to migrate the most-used tables in an existing database to memory-optimised 'Hekaton' technology, but how you balance between disk tables and in-memory tables for optimum performance requires judgement and experiment. What is this technology, and how can you exploit it? Rob Garrison explains.… Read more