How often you got to the point that for any reason you don’t have enough space on the specific drive to host a database? In case of database development or other tasks outside the production environment, this should not be a problem as a database can be eventually re-created, restored from a backup and set to be hosted on another location. By default, SQL Server stores database files in its installation folder, specifically in the Data folder:Read more »
When building reporting structures, we typically have the need to build fact and dimension tables to support the apps that will consume this data. Sometimes we need to generate large numbers of dimension tables to support application needs, such as in Tableau, Entity Framework, or Power BI.
Creating this schema by hand is time-consuming and error-prone. Automating it can be a way to improve predictability, maintainability, and save a ton of time in the process!Read more »
Whenever a query is run for the first time in SQL Server, it is compiled and a query plan is generated for the query. Every query requires a query plan before it is actually executed. This query plan is stored in SQL Server query plan cache. This way when that query is run again, SQL Server doesn’t need to create another query plan; rather it uses the cached query plan which improved database performance.
The duration that a query plan stays in the plan cache depends upon how often a query is executed. Query plans that are used more often, stay in the query plan cache for longer durations, and vice-versa.Read more »
Dynamic management views (DMVs) and dynamic management functions (DMFs) are system views and system functions that return metadata of the system state. On querying the related system objects, database administrators can understand the internals of SQL Server. It allows us to monitor the performance of the SQL Server instance, and diagnose issues with it.
SQL Server 2017 ships with a number of new and enhanced dynamic management views and dynamic management functions that will help DBAs monitor the health and performance of SQL Server instances. A few existing DMV’s such as sys.dm_os_sys_info and sys.dm_db_file_space_usage have been enhanced. Some have also been newly built and available only for SQL Server 2017.Read more »
I was training some Oracle DBAs in T-SQL and they asked me how to create arrays in SQL Server.
I told them that there were no arrays in SQL Server like the ones that we have in Oracle (varray). They were disappointed and asked me how was this problem handled.
Some developers asked me the same thing. Where are the arrays in SQL Server?Read more »
To SQL Server DBAs who are the shepherds of data in organizations, key GDPR questions, in general, center around whether data will need to be treated differently, safeguarded more etc. and specifically, as it relates to allowing production data to be used in testing.
That will be the focus of this article as we’ll work our way through the details of this regulation as well as various authoritative articles on the subject, to address this key question. Then we’ll look to ways and means to potentially ameliorate our findings to provide alternatives and workarounds if possible.Read more »
Good, clean and reliable data is important for every company, because well-analyzed data will open new possibilities to success. When we look at successful companies, we see that they’ve analyzed customers, sales, and finance data very well. At this point, a game changer enters: machine learning.
Machine learning is a type of artificial intelligence which analyzes data in order to predict future and discover hidden patterns. The output helps us make decisions. Machine learning is widely used in finance, healthcare and marketing.Read more »
This is the second article in Ola Hallengren’s SQL Server Maintenance Solution series. It will cover the jobs for database integrity, backup history cleanup and job history cleanup. The installation of Ola Hallengren’s Maintenance Solution is covered in the first article in the series: Ola Hallengren’s SQL Server Maintenance Solution – Installation and SQL Server Backup solutionRead more »
In the article How to plot a SQL Server 2017 graph database using SQL Server R, I highlighted the lack of built-in graph data visualisation as one major limitation of the SQL Server 2017 graph database feature. In the same article, I went on to suggest making use of SQL Server R as one workaround that could be utilised in order to successfully plot and visualise diagrams out of SQL Server 2017 graph database objects. However, whilst 3rd party graph database vendors such as Neo4j provide an interactive and hyperlinked graph diagrams that allows you to – amongst other things – easily drilldown and identify node-relationships as indicated in Figure 1, the graph plotted using SQL Server R is not very interactive in fact it is simply a static image file as shown in Figure 2.Read more »
Some of my previous articles on Python provided insight of the basics and the usage of Python in SQL Server 2017.
- Why would a SQL Server DBA be interested in Python
- An overview of Python vs PowerShell for SQL Server Database Administration
- Data Interpolation and Transformation using Python in SQL Server 2017
This article is an effort to collect all the missing pieces and try to showcase the importance of using Python programming in SQL Server.Read more »
One of the new features announced with SQL Server 2017 is support for the Python language. This is big! In SQL Server 2016, Microsoft announced support for the R language – an open source language ideally suited for statistical analysis and machine learning (ML). Recognizing that many data scientists use Python with ML libraries, the easy-to-learn-hard-to-forget language has now been added to the SQL Server ML suite.
There’s a big difference between R and Python though: R is a domain-specific language while Python is general purpose. That means that the full power of Python is available within SQL Server. This article leaves ML aside for the moment and explores a few of the other possibilities.Read more »
When a DBA is working with the SQL Server, he/she might sometimes say that the execution plan is like a black hole. As a matter of fact, though, you have to look at execution plans as weather forecasts.
In this article, I will be discussing various aspects of executions plans, types and common options.Read more »
A few years ago, one common business case I came across in my professional career that required modelling of data into a many-to-many entity relationship type was the representation of a consultants and their projects. Such a business case became a many-to-many entity relationship type because whilst each project can be undertaken by several consultants, consultants can in turn be involved in many different projects. When it came to storing such data in a relational database engine, it meant that we had to make use of bridging tables and also make use of several self-joins to successfully query the data.Read more »
The SQL Server Query Store is a relatively new feature introduced in SQL Server 2016. It is basically a SQL Server “flight recorder” or “black box”, capturing a history of executed queries, query runtime execution statistics, execution plans etc. against a specific database. This information helps in identifying performance problems caused by query plan changes and troubleshooting by quickly finding performance differences, even after SQL Server restart or upgrade. All data that SQL Server Query Store capture are stored on disk.Read more »
As all other database engines that require a GUI too to interact with, Microsoft built SQL Server Management Studio as a tool that provides an environment to manage, configure, monitor and administrate the SQL Server infrastructure for different kind of instances, that are installed locally at your machine, on a remote server with Windows or Linux operating systems or hosted in the cloud such as SQL Azure or Azure Data warehouse.
Recall that the SQL Server Management Studio consists of the Object Explorer, which is used to view and manage all objects within the SQL Server instance, the Template Explorer, that is used to deal with the scripting text files and the Solution Explorer, that is responsible for building the projects that contain list of T-SQL scripts in SQL Server.Read more »
Microsoft released the latest version of the SQL Server Management Studio 17.4 a few weeks back. This release brings in exciting new features and bug fixes to the SSMS IDE for SQL Developers and DBAs. In this article, we will look at a new inbuilt tool for identifying security vulnerabilities in your database. We will also understand the improvements made to the XEvent Profiler and ShowPlan features.Read more »
PowerShell has become the ultimate choice for many database administrators because of its efficient way of handling and managing automation in a simple, quick way. It’s built on .NET Framework and uses Object Models such as COM, ADSI, ADO, and WMI. PowerShell has replaced the traditional way of scripting that used many legacy scripting practices to monitor SQL instances.
I’ve been asked on several occasions about how to store the output of PowerShell WMI data into the SQL table. The question comes up so frequently that I decided to write this article.
When sending data within a system (such as a PowerShell object to a cmdlet), the process is straightforward. However, with non-native data interchange (for instance, WMI to SQL), the process can potentially get complicated. Due to this, many purists suggest sticking to simple interchange formats, such as CSV, JSON or in some cases, XML.Read more »
One of the main tasks of a SQL Server database administrator is performance tuning. Sometimes, though, coders or developers don’t always prioritize database performance or query optimization. Here is a typical scenario
- Imagine that developers create a new table and then insert some records in a test environment and test their queries to retrieve data from it
- The query executed successfully and does not exhibit any symptoms of performance problems
- The developer team release this table and query into production
- One day you take a telephone from your colleague and he says my report is very slow
- Bingo! In production, this table contains a lot of records and this is resulting in performance bottlenecks when querying it
The user’s read requests in SQL Server are managed and controlled by the SQL Server Relational Engine, that is responsible for determining the most optimized access method, such as index scan or table scan, to retrieve the requested data. These read requests are also optimized internally by the SQL Server Storage Engine, the buffer manager components specifically, that is responsible for determining the general read pattern to be performed.Read more »
Every data warehouse developer is likely to appreciate the significance of having surrogate keys as part of derived fields in your facts and dimension tables. Surrogate keys make it easy to define constraints, create and maintain indexes, as well as define relationships between tables. This is where the Identity property in SQL Server becomes very useful because it allows us to automatically generate and increment our surrogate key values in data warehouse tables. Unfortunately, the generating and incrementing of surrogate keys in versions of SQL Server prior to SQL Server 2017 was at times challenging and inconsistent by causing huge gaps between identity values. In this article, we take a look at one improvement made in SQL Server 2017 to reduce the creation of gaps between identity values.Read more »
A graph database is a type of database whose concept is based on nodes and edges.
Graph databases are based on graph theory (a graph is a diagram of points and lines connected to the points). Nodes represent data or entity and edges represent connections between nodes. Edges own properties that can be related to nodes. This capability allows us to show more complex and deep interactions between our data. Now, to explain this interaction we will show it in a simple diagramRead more »
In SSAS, when I offer Power BI, Reporting Services, PowerPivot or SharePoint to connect to SSAS, the business analysts look scared. On the other hand, if I talk about MS Excel, everybody seems so happy and comfortable with it.
Excel is still the most popular spreadsheet in the world even when there are a lot of free spreadsheets like OpenOffice and LibreOffice to download, in the BI world, Excel is still the most popular.Read more »
Configuration of any system plays a vital role in its working efficiently. However, configuring a system needs not necessarily be a daunting task. There are several tools that help with the process.
SQL Server on Linux provides a wide range of options for configuration, management and administration.
- SQL Server Management Studio
I am a DBA consultant and several times, in the past, I have been asked for a good tool to generate fast reports to get information about the company like total sales, sales per year, month, week etc
SSAS is a Business Intelligence (BI) technology that Microsoft bought from Panorama Software in 1996. After the acquisition, the technology was upgraded and evolved and now it is part of the tools that come with SQL Server. This tool is an extremely powerful tool to create analytical reports fast. It works with cubes that are designed to generate reports with optimal performance using aggregated information stored in the cube.Read more »
In the first part of this article, we will discuss about parallelism in the SQL Server Engine. Parallel processing is, simply put, dividing a big task into multiple processors. This model is meant to reduce processing time.
- SQL Server can execute queries in parallel
- SQL Server creates a path for every query. This path is execution plan
- The SQL Server query optimizer creates execution plans
- SQL Server query optimizer decides the most efficient way for create execution plan
Execution plans are the equivalent to highways and traffic signs of T-SQL queries. They tell us how a query is executed.Read more »