Easy way to create policies using SSMS

SSMS has an interesting feature that turns easy the creation of policies to check our servers compliance. Right-clicking an item inside object explorer window, you will find ‘Facet’ item in the context menu. Clicking on it will open the ‘View Facets’ window. In the ‘View Facets’ window, you can see all the facets related to … Read more

Monitoring SQL Server with Power BI

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

How much should you worry about CXPACKET?

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

Managing the Password of the Application’s User

How can you ensure that passwords conform to a policy, even if they are used for connecting applications to databases? Absolving them from the policy is a security risk and you don't want to trouble the users with password changes. Dennes Torres shows how to manage application users’ passwords in Entity Framework; so that no one, not even the database and IT administrators, knows the current password.… Read more

Thoughts about submitting a new Power BI Quick Measure

‘Quick Measures’ is a feature included in Power BI Desktop April update. It’s still in preview, so you need to enable this feature in order to use it. Inside Power BI Desktop, click ‘File’->‘Options and Settings’->‘Settings’->‘Preview Features’ and check the box beside ‘Quick Measures’, as illustrated in the image below.   Microsoft created an online gallery … Read more

Monitoring UnmatchedIndexes Warning

If you are using filtered indexes in SQL Server, it worthwhile monitoring the UnmatchedIndexes warning, it can give us very interesting insights. Filtered indexes and parameterized queries are terrible enemies. I explained a bit about parameterized queries in my article about how to identify them using query store, you can read it here. When a … Read more

Using SQL Server Query Hints with Entity Framework

Entity Framework (EF) is designed to work with a variety of data sources. Although this presents many advantages, there is a downside that many of the special features of a data source such as SQL Server are off-limits. Query Hints are an example: though often misused, they are occasionally important. Dennes Torres shows how you can use these in EF, using a command interceptor that will allow you to use any query hint with SQL Server.… Read more

Encrypting connection strings in web.config

Encrypting web.config elements is a good security feature. Web.Config elements can include passwords and important keys that we need to protect.Encrypting web.config elements is a good security feature. Web.Config elements can include passwords and important keys that we need to protect. There are several methods to encrypt the web.config: Using a command line statement Using … Read more

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

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