Determining What Happens When You Force an Execution Plan via the Query Store
When you force a query plan via the Query Store, you will need to track what happens: Sometimes the request to force a plan will fail, and you will want to know when and why. There are several ways of getting feedback, ranging from the built-in reports to using extended events. Enrico explains the details.… Read more
Using Power BI Desktop to Visualize SQL Server Metadata
You can easily use PowerBI Desktop to show graphically how your database is growing, which tables are taking the most space, how various parts of SQL Server is consuming memory, its use of indexes and so on. Sure, you can create graphs in SSMS, but with PowerBI, you can create reports that you can then publish to others, and which allow drill-down. It is a great way to get familiar with PowerBI Desktop as well. Rob Sheldon shows how simple it is to do.… Read more
SQL Server Database Provisioning
Database provisioning for development work isn't always easy. The better that development teams meet business demands for rapid delivery and high quality, the more complex become the requirements for the work of development and testing. More databases are required for testing and development, and they need to be more rapidly kept current. Data and loading needs to match more closely what is in production. Grant Fritchey explains.… Read more
Retrieving SQL Server Query Execution Plans
Execution plans explain all you need to know about query performance, and how to fine-tune. Sure, you can see them in SSMS, but what if you need to drill into to the important details? What about using DMVs, Extended Events or SET statements to get at the execution plans? To get the best use of execution plans you need to be able to get right information from the right plan at the right time. Robert Sheldon explains how.… Read moreRepresenting Hierarchical Data for Mere Mortals
Why is it that we use XML, but with so little enthusiasm when it does so much, and is so feature-rich? Phil Factor argues that there are better ways of doing it, more complete than JSON, but easier to read than XML. To try to convince you, he gives a set of flying demos, using PowerShell and his PSYaml module, to illustrate how YAML can let you work faster, and more accurately.… Read more
Azure SQL Data Warehouse: Explaining the Architecture Through System Views
The architecture of Azure SQL Data Warehouse isn't easy to explain briefly, but if you have some useful queries that access the management and catalog views, and diagrams that show how they relate together, you can very quickly get a feel for what is going on under the hood. By using and extending these queries that use these views, you can check on a variety waits, blocking, status, table distribution and data movement in ASDW.… Read more
In-Memory OLTP: Row Visibility in SQL Server’s MVCC
SQL Server's In-memory OLTP is fast, due to its multi-valued concurrency control (MVCC). MVCC avoids the need for locks by arranging for each user connected to the database to see a snapshot of the rows of the tables at a point in time, No changes made by the user will be seen by other users of the database until the changes have been completed and committed. It is conceptually simple but does the user always see the correct version of a row under all circumstances? Shel Burkow explains.… Read more
SQL Server for Linux
Although SQL Server for Linux removes the concern that adopting SQL Server forces you to also adopt the Windows platform, it could also provide a useful alternative platform, and a more obvious alternative to Oracle. There are, however, several obvious concerns as to how such a product could ever achieve parity with the existing Windows-based product. Microsoft have made an interesting move with several ramifications, as Robert Sheldon explains.… Read moreQuestions About Azure SQL Database You Were Too Shy to Ask
You've heard of Azure SQL Database, you've nodded wisely in discussions and you might even have tried it out on some sample data. However, you have some nagging basic questions that would seem slightly ridiculous to ask in public. For all those questions that you're too shy to ask, Murilo gives a reassuring answer. … Read more
SQL Server Access Control: The Basics
No technology yet invented can in any way allow us to neglect the task of ensuring the security of the database by controlling access. Security must be applied in depth, and the database is designed provide a system that will thwart even the most determined external attack. If it seems a bit complicated at first, that is no longer an excuse now that Rob Sheldon has provided this simple guide for getting started. … Read more3 Built-In Technologies for Centralizing SQL Server Administration
DBAs can be more effective in managing their workload by centralizing their procedures. There are several features of SQL server that can be harnessed to this end: PowerShell is only part of the solution: there is also Central Management Server, Master /Target Agent and the Remote Server Administration Tools. It's time to work out your objectives and pick the most suitable technologies to meet them. … Read more5 Monitoring Queries for SQL Server
Every DBA squirrels away favourite queries for monitoring SQL Server. Nowadays many of these are too complex to keep in your head. Dennes describes how he uses T-SQL queries for solving problems, whether it involves fixing the problems of missing indexes, preventing unrestrained autogrowth, avoiding index fragmentation, checking whether jobs have failed or avoiding memory stress conditions. … Read more
The PoSh DBA: Accessing SQL Server from PowerShell
The worst part for a DBA of getting started with PowerShell is often just figuring out the best way of working with SQL Server. The most suitable approach to accessing SQL Server depends on the sort of task you need to produce a script for. Laerte Junior aims at a simple guide to the most common approaches and when to use them.… Read more
The Enterprise DBA Mindset as a Practical Problem-solving Approach
In order to keep the demands of the job under control, any DBA needs to automate as many as possible of the suitable tasks that their role demands. What makes a task suitable? How do you judge whether it is worthwhile? Can we take a 'managed', consistent, decision? Joshua Feierman explains the practicalities with a real example.… Read moreIn-Memory OLTP – Row Structure and Indexes
There are several decisions to be made when designing indexes for Memory-optimized tables in In-Memory OLTP, and to make the best choice, it pays to understand something about the nature and usage of memory-optimised indexes. Murilo Miranda continues his series by explaining how such an index is unlike a conventional index… Read moreIn-Memory OLTP – Understanding Memory-Optimized Tables
How do you get started with In-memory OLTP? Murilo Miranda first advises on the setup of the server and the other prerequisites, and, after explaining about the two alternative types of memory-optimised table and their stucture, then provides sufficient explanation to enable you to create your first Memory-optimised tab… Read moreSQL Server Security Audit Basics
SQL Server Server Audit has grown in functionality over the years but it can be tricky to maintain and use because it lacks centralization and analysis tools. It can do a fast and lightweight audit of many different activities including DML and DDL at both Instance and Database Levels - even the work of the DBAs. How do you check logins and permissions? How do you script an enterprise-wide audit solution? How can you hope to analyse the log data you get? Feodor gets you started.… Read more
The SQL Server 2016 Query Store: Analyzing Query Store Performance
There are some obvious advantages to having the Query Store, but what is the performance impact that it is likely to have on a busy OLTP database server? It is early days, of course and until we get more experience we have to rely on Microsoft's estimate of a performance impact of 3-5% on average. However, this will depend on a number of factors such as usage an the way it is configured. Enrico explores some of these factors in order to give a clearer picture of what you should expect.… Read moreThe SQL Server 2016 Query Store: Forcing Execution Plans using the Query Store
The SQL Server 2016 Query Store can give you valuable performance insights by providing several new ways of troubleshooting queries, studying their plans, exploring their context settings, and checking their performance metrics. However, it can also directly affect the performance of queries by forcing Execution Plans for specific queries.… Read moreJoin Simple Talk
Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.
Sign up