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.
Read more »
This is a second article that is a continuation of the Microsoft SQL Server Migration Assistant (SSMA) v7.1 for Oracle. In my first article, I wrote an overview of the tool and how to install it. In this second article I will write about how to use it to migrate data and objects from an Oracle database to a SQL Server database.
Read more »
If you haven’t heard of dbatools, it’s an open source project out on GitHub dedicated to making life easier for Data Professionals. If you have an idea that you don’t see a command for, you can build it and contribute it to the project. Or you can ask if someone has time to write if for you.
Read more »
In this article, we will verify if the following statements are true or they are just myths:
SQL Server 2016 SP1 is released as announced by Microsoft. It comes with a bunch of new features and enhancements as a result of customer and community feedback. In this article, I will introduce you to these new features and enhancements.
Read more »
Since its initial release that was part of SQL Server 2005, Dynamic Management Views (DMV) changed – mostly for the better – the role of database administration (DBA) within SQL Server-based environments. They improved the efficiency of DBAs regarding database server monitoring, issue diagnoses, and subsequent performance optimisation. As a result of this positive reception, it has become customary that some of the highly anticipated items in every release of SQL Server includes discovering what new DMVs will be added. In this article I take you through my favourite top 5 DMVs to come out of SQL Server 2016.
Read more »
Late in October, I received an unusual request from the head of sales within one of my client sites. Sales sells three articles: bread, perfume, and Jaguar motor cars. Now the reader will note that one of these items is a staple and the other two are for those folks with considerable disposable income. Management within the firm had increased the salesmen’s bonuses for those folks that managed to sell perfume and/or Jaguars along with the standard loaves of bread. The summary report may be seen below showing the final bonus rate for each sales order booked during the month.
Read more »
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 Data Warehouse data files.
Read more »
How often are you working with multiple environments? For example, if you are a database administrator who is responsible for a production environment as well as another environment, it most likely that you will be working with both environments simultaneously. What is the probability that you will execute a script on production, which actually needs to be executed on the other environment? I would say it is high. To prove this point let me present you an example.
Read more »
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.
Read more »
Database deployments are critical tasks that can affect negative in on performance in production. In this article we’ll describe some performance related best practices for database deployments.
Read more »
SQLCMD Mode allows creating, testing, executing SQLCMD commands or scripts in SQL Server Management Studio directly in the query editor. This option is available since SQL Server 2005.
Read more »
Audit tables are used to track transactions for a particular table or tables. For every important transaction table, it’s important to create an audit table. Auditing can be helpful if you need to track who is Inserting/Updating and Deleting data on a sensitive table and/or view before and after data change information.
Read more »
SQL Server Migration Assistant (SSMA) is a free supported tool from Microsoft that simplifies and automates database migration processes (assessment analysis, necessary conversions, data migration and tests) from Microsoft Access, MySQL, Oracle, Sybase and DB2 to SQL Server, Azure SQL DB and SQL Server vNext (at the time I’m writing this article, SQL Server vNext stills in CTP1 version, so being a technical preview version it should not be used for production migrations).
Read more »
As many of you by now know, I am a fan of utilizing expressions within Reporting Services reports to add additional flexibility. Recently I received a client request to create a stacked bar report which in turn would provide access to the underlying data the made up the bars on the chart. My client sells two articles: “Whigs” and “Tories”. Many people like to “buy” them.The idea is to display the aggregated data as may be seen below (gray black and turquoise) and depending on which bar and color is selected( clicked upon), to drill down and to display the underlying detailed data (see below).
Read more »
I must have been one of the first people who abandoned BIDS as soon as SSDT was first introduced. Although I have never regretted that decision, I do sometimes feel that SSDT has introduced an unnecessary additional layer of troubleshooting package execution failures. Regardless of whether your SSIS packages are deployed using BIDS or SSDT, the common scheduling mechanism used to run those packages is usually the SQL Server Agent. This is where you are likely to encounter additional layers of troubleshooting SSDT-deployed packages. This is because unlike BIDS where package execution details were stored in the same database (msdb) as were the execution details of SQL Server Agent job, the advent of SSDT came with an introduction a new database – SSISDB – which is used to, amongst other things, store package execution details. Subsequently, in order to retrieve details of package execution, we ought to query the SSIDB database.
Read more »
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.
Read more »
With the introduction of Microsoft’s new In-Memory OLTP engine* (code name Hekaton) a new type of nonclustered index was introduced to help you search the in-memory tables based on a range of values. Although the name is similar it does differ from the one we have in the traditional disk tables.
Read more »
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 native add-in in Excel 2016).
In this article, the custom SSAS Tabular model database will be used (“AW Tabular”) as an external data source.
Read more »
Suppose you’ve been asked to run a query against some SQL Server database but you don’t know anything more than the name of a stored procedure that you need to call. If you are going to do anything with the result set, you need to know at least the names and types of the columns to expect. This is actually a problem faced by many applications, including SQL Server Integration Services (SSIS) and SQL Server Reporting Services (SSRS).
Read more »
With the introduction of Microsoft’s new In-Memory OLTP engine (code name Hekaton) the familiar B-Tree indexes were not always the optimal solution. The target of the Hekaton project was to achieve 100 (hundred) times faster OLTP processing, and to help this a new index was introduced – the hash index.
Read more »
In this article I’ll cover all aspects of a new SQL Server 2016 feature, Temporal Tables (System-Versioned), including:
Read more »
When creating a database, SQL Server maps this database with minimum two operating system files; the database data file MDF and the database log file LDF. Logically, the database data files are created under a collection set of files that simplifies the database administration, this logical file container is called the Filegroup. Database data files can come in two types, the Primary data files that contains metadata information for the database and pointers to the other database files in addition to the data, where each database should have only one Primary data file and the optional Secondary files to store data only. The basic storage unit in SQL Server is the Page, with each page size equal to 8KB. And each 8 pages with 64KB size called Extent.
Read more »
When customers used to ask for advice to solve some T-SQL Problem, they would show me their scripts, stored procedures and cursors. I saw horrible things doing that job, some things I do not want to remember, and even some I cannot tell you about 😉 In this article, we will talk about performance problems when using while loops in T-SQL. We will also talk about problems with UNIONsand finally the use of JOINS of two or more tables using the where clause (ANSI 89) instead of using INNER, LEFT or RIGHT JOINS using the from clause (ANSI 92).
Read more »
The need for this script came about when I took a new job as a DBA. One of my responsibilities was to make sure all databases were available after the maintenance window which ran during the weekend. Rather than log into each database server and check that the databases were online, I had a script do the work for me and shoot me an email.
Read more »© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy