Dejan Sarka

Data science in SQL Server: Data analysis and transformation – grouping and aggregating data I

July 10, 2018 by

Previously, in this Data science series, I already tacitly did quite a few aggregations over the whole dataset and aggregations over groups of data. Of course, the vast majority of the readers here is familiar with the GROUP BY clause in the T-SQL SELECT statement and with the basic aggregate functions. Therefore, in this article, I want to show some advanced aggregation options in T-SQL and grouping in aggregations of data in an R or a Python data frame.

Read more »
Esat Erkec

Main Concepts of SELECT operators in SQL Server execution plans

July 9, 2018 by

One of the main responsibilities of a database administrator is query tuning and troubleshooting query performance. In this context, SQL Server offers several tools to assist. But among them, query execution plans are essential for query optimization because they include all of the vital information about the query execution process. At the same time as it provides this valuable information “under the hood”, SQL Server creates a graphical description of the execution plan. Read more »

Prashanth Jayaram

In-Memory Optimized database backup and restore in SQL Server

July 6, 2018 by

A SQL Server backup and restore strategy is an essential process to safeguard and protect critical data. The vital role of any DBA is to minimize the risk of data loss and preserve data modifications at regular intervals of time. A well-planned and well-tested backup-and-restore strategy always help to protect the organization with no data loss, or at least a minimum, from the many varieties of system and database failure. As such, it is recommended to understand the importance and implication of the backup-and-restore strategy.

Read more »
Nesha Maric

Various techniques to audit SQL Server databases

July 5, 2018 by

SQL Server auditing has gone from a nice to have to a legal requirement, especially following new legislation like HIPAA and GDPR. Organizations are now tasked with auditing access to records, reporting suspicious and potentially malicious activity, forensically auditing data changes, as well are tracking login attempts, security changes and much more.

Read more »
Brian Lockwood

Sharpen your ax

July 4, 2018 by

This is a popular story which illustrates the advantages of not just plowing away at your work, but improving your knowledge, planning, skills and preparation first to achieve a better outcome in the end

Once upon a time there were two men who lived in the same forest and decided to have a contest chopping wood.  The first man was in good physical shape and very muscular.  The second man was in good shape but smaller in statute and wiry.  They would chop wood all day and at the end of the day compare to see who had chopped the most wood.  The first man laughed to himself that there was no way this wiry little man would beat him and so they began the contest.  Every 45 minutes the second smaller man would take a break and seems to just wonder off somewhere.  The first man laughed again to himself and said “Yep there’s no way this wiry little man is going to beat me.”  This happens several times during the day.  At the end of the day the two men compare their piles of chopped wood and unbelievably enough the wiry little man has chopped twice as much wood as the more physically fit man.  He says “I don’t understand.  First I’m twice your size and twice your strength!  On top of that every 45 minutes you rolled off and took a break or a nap or something.  You must have cheated!”  The smaller man says “I didn’t cheat.  It was easy to beat you because every 45 minutes when you thought I was taking a break, I was out back sharpening my ax.” 1

Read more »
Ahmad Yaseen

SQL Server Execution Plans overview

July 4, 2018 by

In this series of articles, we will navigate the SQL Server Execution Plan ocean, starting from defining the concept of the Execution Plans, walking through the types, components and operators of Execution Plans analyze execution plans and we’ll finish with how to save and administrate the Execution Plans.

When you submit a T-SQL query, you tell the SQL Server Engine what you want, but without specifying how to do it for you. Between submitting the T-SQL query to the SQL Server Database Engine and returning the query result to the end user, the SQL Server Engine will perform four internal query processing operations, to convert the query into a format that can be used by the SQL Server Storage Engine easily use to retrieve the requested data, using the processes assigned to the SQL Engine from the Operating System to work on the submitted query.

Read more »
Nesha Maric

Reading the transaction log in SQL Server – from hacks to solutions

July 3, 2018 by

The SQL Server transaction log is akin to a ‘Black box’ in an airliner. It contains all of the records of transactions made against a database. This information is a proverbial goldmine for database audits, recoveries etc but it was never meant to be exposed to end users let alone visualized in an easy to read manner nor used for DBA tasks. As such, utilizing this information can be a challenge, to say the least.

Read more »
Brian Lockwood

Comprehensive Performance review template

July 3, 2018 by

I looked around for a good performance review template for a long time but I finally just sat down and wrote my own. It is a 5 pt system with various categories that cover

  • hard skills – your skill at your core competency i.e testing
  • soft skills (quality, follower ship, communication, proactivity, intangibles)
  • Performance – how you are doing, productivity
  • Quality
  • Trend – getting better, worse or the same
Read more »
Timothy Smith

SQL Server – using lowest unit of measurement in T-SQL

July 2, 2018 by

A client recently discovered a discrepancy on one of our reports that showed an improvement in performance metrics but was inaccurate. Our reports came from a software tool, which showed the average performance throughout the day. It derived this number from periodic checks and the frequency changed, which affected our report. When we showed an improvement in the metrics on a report, the client showed us that the frequency change may have impacted this, not necessarily any improvement in performance. In situations where we’re measuring values and comparing them to other values, how can we prevent a change in measurement from impacting our reports? Read more »

Ahmad Yaseen

SQL Server indexes – series intro

June 29, 2018 by

Description

In this series, we will dive deeply in the SQL Server Indexing field, starting from the surface by understanding the internal structure of the SQL Server tables and indexes, going deeper by describing the guidelines and best practices that we can follow to design the most efficient index and what operations can be performed on the created indexes. Having these knowledges about the SQL Server indexes, we have all the tools that help us in testing the lower part of the ocean and dive deeper with the two main types of the SQL Server Indexes; the Clustered and Non-Clustered, and the other types of indexes that can be customized to serve us improving your environment. After that, the adventure becomes more interesting when learning how to use this knowledge to tune the performance of our queries and touch the bottom of the ocean. In our way back to the surface, and before celebrating our achievements, we will collect statistical information about these indexes and use this information to maintain the indexes to take benefits from it continuously and gain the best application performance.

Read more »
Brian Lockwood

Retention successes and failures – combatting the Peter Principle for managers

June 29, 2018 by

No matter what your position, unless perhaps you work for the post office, typically you are required to do more to earn a promotion, not just do your job long longer. The trouble is that not everyone can or will do more. Some lack the motivation, some the potential and others endurance to continually progress. For whatever reason, even if there are growth slots available, not everyone will move up and instead some will ultimately hit their career ceiling aka plateau. This concept is often described at the Peter Principle

Read more »
Brian Lockwood

Rubber Balls vs Glass balls – a metaphor for task prioritization

June 28, 2018 by

When I was in the Army we all got a 2 page hard copy with a visual called “Rubber balls vs glass balls” as an analogy for balancing the many day to day priorities of being an Officer. It was a compelling article that I’ve referred back to many times in life. I’ve spent the last couple years looking for it on the web, but with no luck here is my attempt to recreate it from scratch, by memory.

Read more »
Esat Erkec

SQL Server Reporting Service Configuration Manager

June 27, 2018 by

After the native installation of SQL Server Reporting Service, we may need to customize some settings of Report Server. SQL Server Reporting Service provides a tool which is named as such; Report Server Configuration Manager. This tool helps us to customize SQL Server Reporting Service parameters and configurations. With Report Server Configuration Manager, we can change and customize various parameters of SQL Server Reporting Service. We can find the below settings in Report Server Configuration Manager:

Read more »
Sifiso W. Ndlovu

Top 4 options for fixing Microsoft.AnalysisServices.AdomdClientUI.dll assembly error in SQL Server 2016

June 26, 2018 by

I’ve always been in favor of an orthodox strategy when it comes to applying SQL Server updates which often goes like:

  • Instead of installing SQL Server Cumulative Updates, wait for release of service packs
  • When a service pack is released, install it in phases starting from the non-production environment (i.e. DEV, UAT) to eventually roll it out on production
Read more »
Ahmad Yaseen

Top 25 SQL interview questions and answers about indexes

June 25, 2018 by

Q1: What is the difference between a Heap table and a Clustered table? How can we identify if the table is a heap table?

Heap table is a table in which, the data rows are not stored in any particular order within each data page. In addition, there is no particular order to control the data page sequence, that is not linked in a linked list. This is due to the fact that the heap table contains no clustered index.

Read more »
Timothy Smith

SQL Server security considerations with open source tools

June 21, 2018 by

As our company has grown, we’ve recently added developers to our team who want to use open source tools (open source languages and libraries). In the past, we built and used our own custom libraries, but our new developers to prefer to use open source libraries or add new languages that require new libraries. We’re concerned that the use of open source libraries may not be secure and may introduce new inputs and outputs in our system that we don’t fully understand. What should we consider when we think about allowing open source software, tools or languages in our environment from the standpoint of security? Read more »

Esat Erkec

How to automatically create KPIs in SQL Server Reporting Services

June 18, 2018 by

Key Performance Indicator (KPI)

A Key Performance Indicator aka KPI is a metric which objectively measures the numeric equivalent of goals or targets of a company, team or an organization. Any individual or a community which has a numerical target can measure success of their goal with key performance indicators.

In all business roles and levels, if you manage a team you most likely have to set at least one key performance indicator and try influence this metric to show a positive result or trend. KPIs should be both logical and achievable. If not, team motivation and morale may decrease as a result of loss of self-confidence. For this reason, we have to be careful and logical when we set key performance indicators. At the same time, we have to check KPIs at regular intervals to determine if they remain valid. We can illustrate a typical KPI life cycle below.

Read more »
Bojan Petrovic

How to implement error handling in SQL Server

June 15, 2018 by

Error handling overview

Error handling in SQL Server give us control over Transact-SQL code. For example when things go wrong we get a chance to do something about it and possibly make it right again. SQL Server error handling can be as simple as just logging that something happened or it could be us trying to fix an error. It can even be translating the error in SQL language because we all know how technical SQL Server error messages could get making no sense and hard to understand. Luckily we have a chance to translate those messages into something more meaningful to pass on to the users, developers, etc.

Read more »
Prashanth Jayaram

SQL Server Database Backup and Restore reports

June 14, 2018 by

In the previous articles, we discussed several ways of taking backup and testing the restore process to ensure the integrity of the backup file.

In this article, we’re going to discuss the importance of the database backup reporting. This kind of reporting will be designed to eliminate any unnecessary risk that may arise with the safety and security of information. This is the reason, the high-level report; Daily Health Check report will be generated and sent to the SME or to the DBA responsible for IT Infra management.

Read more »