Simple Talk is now part of the Redgate Community hub - find out why

Using R to illustrate relationships from graph objects

My new article about Graph Database Objects was just published, it’s really a very good new feature. Another new feature we have since SQL Server 2016 is R language. We can use R language inside the database to analyze our data. The possibility to use both technologies together is very interesting. Using graph objects we … Read more

SQL Graph Objects in SQL Server 2017: the Good and the Bad

Graph databases are useful for certain types of database tasks that involve representing and traversing complex relationships between entities. These can be difficult to do in relational databases and even trickier to report on. Until now, we have had the choice of doing it awkwardly in SQL Server or having an ancillary database to tackle this type of task. SQL Server 2017 will be bringing graph capabilities to the product but will these features prove to be good enough to allow us to dispense with specialised Graph databases? Dennes Torres decided to find out.… Read more

SQL Server 2017: New ‘Queries with high variation’ Graph

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

New T-SQL Functions in SQL Server 2017

SQL Server 2017 brings us some new T-SQL functions. They are very simple to use, and can also help us to simplify our T-SQL code. I’ll be talking about them in this article. String_AGG This new function solves an old and very interesting problem: How can we concatenate the contents of a column from several records in … Read more

Visual Studio 2017 and Swagger: Building and Documenting Web APIs

The OpenAPI ‘Swagger’ Specification defines a protocol that allows applications to discover, and generate documentation of methods, parameters and models of REST APIs, This provides a way for any software to identify the features of a REST API. It does for REST APIs what WSDL (Web Service Description Language) did for web services. Visual Studio now provides it to support proxy creation for REST APIs, as Dennes Torres explains.… Read more

SQL 2017 new DMF: Managing VLOGs in our databases

SQL Server 2017 brings to us some new interesting DMV’s and DMF’s, one of them is sys.dm_db_log_info. This new DMF allows us to manage VLOGs in our databases. The log files are divided in smaller slices called virtual log files, or vlog. Operations such as log truncations happens over an entire vlog. For example, when … Read more

Filtering Errors in PowerBI

I just faced this problem when trying to analyze some information from google analytics in PowerBI. In my specific situation, I was trying to extract a number from some URL querystrings and filter the rows without the number. When I set the data type of the column to decimal, the rows without a number receive … Read more

Query Store and Parameterization Problems

The query store gives us a novel way of identifying those queries that are causing performance problems when they are parameterized by SQL Server for reuse. Although it is relatively simple to ensure that certain troublesome queries avoid the problem, it is laborious to identify these queries. Additionally, Query Store gives us the means to fix the problem for groups of queries by means of plan guides without changing the DDL at all. Dennes Torres explains the details… Read more

News for Differential Backup

SQL Server 2017 brings several improvements to us. Sometimes a simple new field in a DMV can turn possible interesting new functionalities. That’s what happens with differential backup in SQL Server 2017. The DMV sys.dm_db_file_space_usage has a new field: modified_extent_page_count . This new field tell us how many pages were changed since the last full backup. … Read more

How to allow nulls in unique fields

4
3
An interesting workaround using unique fields Unique fields in SQL Server are created using unique constraints or unique indexes, furthermore, each unique constraint uses a unique index. Regardless of using unique constraint or unique index, the field can accept null values, however the uniqueness will result in only accepting a single row with null value. The solution … Read more
4
3

Careful With Trustworthy Setting

0
2
Trustworthy database setting is an easy solution for some problems in the server, especially cross database access. However, this setting has also a security problem in some scenarios. The scenario that has a security break is the following: One or more databases are owned by a user with ‘sysadmin’ rights. If the databases were created … Read more
0
2

Creating schemas without DBO permission

0
7
One result of having the correct care with the access permissions in our databases is the need to avoid the DBO permission even to the users responsible for the database model. Using the database roles such as ddl_admin or even in more grainy ways it’s easy to achieve this goal. However, it’s more complex when … Read more
0
7

5 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

Identifying client timeouts

0
0
Long running queries often causes client timeout in our applications. One good solution to attack this problem is to identify the timeouts and optimize the queries causing the timeouts. Client timeouts or connection interruptions are identified as Attention events. We can capture the attention events using an Extended Events session. The script to create the … Read more
0
0

How to identify contention in TempDb

0
0
TempDb database is used to store all temp tables created for our applications. Whether explicit or implicit created, all temp tables activity is done in tempdb, creating a bottleneck. For this reason, it’s important to identify tempdb contention. Tempdb contention will appear in the form of latches. Latch is a synchronization object that protects internal … Read more
0
0

How to find blocked processes

0
0
Blocked processes are often a big problem to DBA’s. They are difficult to monitor. We receive desperate calls saying “Everything is slow!” and before we can do anything a second call “Oh, don’t worry, it’s everything ok again”. Most of times it happens because ill-behaved process that blocks other tasks for short periods. How can … Read more
0
0

Are the statistics being updated?

0
1
The auto-update statistics configuration updates statistics when the amount of row changes in the table achieves 20% of total rows. However, for tables with too many rows, this threshold is too high. For example, a table with 1 million rows will only update statistics after 200 thousands changes. That’s too much and can affect query … Read more
0
1

How to find CPU intensive queries

0
1
High CPU usage is a very common problem in SQL Servers. Any query with a bad query plan can steal CPU time and harm system response time. Fortunately we can find queries with high CPU time using sys.dm_exec_query_stats DMV. This DMV, created in SQL Server 2008, keeps performance statistics for cached query plans, allowing us … Read more
0
1

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue

Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue