My team and I were recently tasked with refactoring older data marts, particularly those that were created with SQL Server 2008 in mind. As we all know, SQL Server has undergone significant changes since the release of SQL Server 2008. One of those changes relates to the introduction of columnstore as an alternative to the […]
The SQL Server Query Processing Engine is the most complex and sophisticated part of SQL Server. This engine has many responsibilities, but one of the most important parts is to design the query execution plan and execute it in a best and most efficient possible manner.
In this article, we’ll discuss some tips and best practices regarding the design of OLAP cubes in Analysis Services Multidimensional (SSAS). Most tips – if not all – are applicable for SSAS 2008 to 2016 (and later most likely). Since Analysis Services Tabular – the in-memory columnstore OLAP database from Microsoft – is a completely […]
SQL Server Transparent Data Encryption, also known as TDE, is a “data at rest” encryption mechanism that is introduced in SQL Server 2008 as an Enterprise Edition feature. TDE is used to perform a real-time I/O encryption for the SQL Server database data, log, backup and snapshot physical files, rather than encrypting the data itself, […]
Microsoft SQL Server has many important features when it comes to file structures. Generally speaking, DBAs often ignore file structures and growth configurations. More specifically, the SQL Server Log files are often left at default values and never touched, even when they begin to cause problems caused. These Log files are important and critical part […]
In a software development life cycle, Code Review plays an integral role in improving the product quality. Having a Code Review Checklist is indispensable since it ensures that the best practices are followed and reviews are performed consistently. It is essential for developers to be aware of the coding guidelines while working on their code […]
Introduction In the 90s and at the beginning of the 21 century, Linux and Windows were decidedly working at cross purposes However, starting on 2009, Microsoft started to contribute with code in the Linux Kernel. On 2013, Azure supported Virtual Machines in Ubuntu and later came the support to .NET in Linux, Visual Studio for […]
Introduction This is the second article in a continuing series on the many system tables, views, procedures and functions available in SQL Server. In the first part of this series, Discovering SQL server instance information using system views, you learned how to discover many attributes of a SQL Server instance you have been given access […]
As you may already have figured out, the default settings in SQL Server are not always the best. Such is the case when you are working with new user databases; usually you get a single data (*.mdf) and transaction log (*.ldf) file. The data file resides within the PRIMARY file group; the only one we […]
Introduction Out of the box, SQL Server comes with a substantial and – release by release – ever-growing set of system tables, views, stored procedures and functions. There’s a good chance you’ve never directly used more than a handful of them. That’s certainly the case with me! This is the first article in a series […]
When you run a query in SQL Server, the SQL Server Query Optimizer will draw the road map for that query, specifying the optimal way to execute it, which is called the query execution plan. Generating the execution plan will take few milliseconds from the CPU cycles, which is negligible for one query or small […]
Introduction To improve performance, it is common for DBAs to search in each aspect except analyzing storage subsystem performance even though in many times, issues are, in fact, caused by poor storage subsystem performance. Therefore, I want to give you some tools and recommendation that you can use it to prevent your storage subsystem from […]
Introduction Importing and exporting CSV files is a common task to DBAs from time to time.
With this article, we continue part 1 of common best practices to optimize the performance of Integration Services packages. As mentioned in the previous article “Integration Services (SSIS) Performance Best Practices – Data Flow Optimization“, it’s not an exhaustive list of all possible performance improvements for SSIS packages. It merely represents a set of best […]
In this article, we’ll present a couple of common best practices regarding the performance of Integration Services (SSIS) packages. These are general guidelines for package design and development which guarantee a good performance in most use cases. However, there are always exceptional cases and outliers. The mantra of “measure twice, cut once” also applies here. […]
Often we install SQL Server on clients or we get to clients where they have pre-installed SQL Server Instances. As a DBA we have the Primary responsibility of keeping the Database Up and Running. This responsibility leads to optimizing, performance tuning and many other stuff related to database’s internal objects. While keeping the other aspects […]
Troubleshooting SQL Server is all about gathering the right evidence. Ordinarily we utilise a variety of different methods and analyse their output to look for specific areas where we would focus our diagnostic efforts. We could for example, use the results of various DMVs to look at wait statistic and resource information to help us […]
Description Keeping track of our SQL Server Agent jobs is a very effective way to control scheduling, failures, and understand when undesired conditions may be manifesting themselves. This is a brief journey towards a solution that provides us far greater insight into our jobs, schedules, and executions than we can glean from SQL Server by […]
A SQL Server execution plan is the most efficient and least cost road map that is generated by the Query Optimizer’s algorithms calculations to execute the submitted T-SQL query. Execution plans are used by the database administrators to troubleshoot the performance of poorly performing queries to isolate the part of the query that is at […]
Introduction The main goal of many organizations, today, is reducing costs while maintaining the highest degree of stability and efficiency. To this end, we should think out of the box about how we can help to achieve this as DBAs. The approaches include:
Introduction Have you ever written up a complex query using Common Table Expressions (CTEs) only to be disappointed by the performance? Have you been blaming the CTE? This article looks at that problem to show that it is a little deeper than a particular syntax choice and offers some tips on how to improve performance.
Introduction and Overview Transparent Data Encryption (TDE) was introduced in SQL Server 2008. Its main purpose was to protect data by encrypting the physical files, both the data (mdf) and log (ldf) files (as opposed to the actual data stored within the database). Transparent Data Encryption Encrypts SQL Server, Azure SQL Databases, and Azure SQL […]
When it comes to monitoring of SQL Server Analysis Services (SSAS) performance, as it relates to the database engine, there are several categories that should be mentioned and compared.
Introduction Consolidating SQL Server databases and instances is a practice which, under certain criteria, allows organizations to benefit as they can reduce licensing costs, and if properly designed, hardware costs.
Prerequisites for using query results from any Analysis Services model database in Excel through Power Pivot add-in are SQL Server 2014 or higher with installed Analysis Services Tabular instance (Multidimensional is installed by default), deployed Multidimensional or Tabular model database (for data source) and Power Pivot add-in for Excel 2010 or higher (Power Pivot is […]
© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy