Data science in SQL Server: Data analysis and transformation – grouping and aggregating data I July 10, 2018 by Dejan Sarka 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 »
Main Concepts of SELECT operators in SQL Server execution plans July 9, 2018 by Esat Erkec 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 »
In-Memory Optimized database backup and restore in SQL Server July 6, 2018 by Prashanth Jayaram 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 »
Various techniques to audit SQL Server databases July 5, 2018 by Nesha Maric 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 »
How to link two SQL Server instances with Kerberos July 5, 2018 by Jefferson Elias Introduction Intended audience This document is intended for application developers, database administrators and system administrator who plan to create linked servers between instances of Microsoft SQL Server using Windows Authentication. Read more »
Sharpen your ax July 4, 2018 by Brian Lockwood 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 »
SQL Server Execution Plans overview July 4, 2018 by Ahmad Yaseen 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 »
Reading the transaction log in SQL Server – from hacks to solutions July 3, 2018 by Nesha Maric 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 »
Comprehensive Performance review template July 3, 2018 by Brian Lockwood 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 »
SQL Server – using lowest unit of measurement in T-SQL July 2, 2018 by Timothy Smith 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 »
Daily Deliverables – the art of eating the frog June 29, 2018 by Brian Lockwood “Each morning sees some task begin, Each evening sees it close; Something attempted, something done, Has earned a night’s repose” – Henry Wadsworth Longfellow Read more »
SQL Server indexes – series intro June 29, 2018 by Ahmad Yaseen 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 »
Retention successes and failures – combatting the Peter Principle for managers June 29, 2018 by Brian Lockwood 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 »
Hash partitions in SQL Server June 28, 2018 by Bhavesh Patel In SQL Server, when talking about table partitions, basically, SQL Server doesn’t directly support hash partitions. It has an own logically built function using persisted computed columns for distributing data across horizontal partitions called a Hash partition. Read more »
Rubber Balls vs Glass balls – a metaphor for task prioritization June 28, 2018 by Brian Lockwood 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 »
SQL Server Reporting Service Configuration Manager June 27, 2018 by Esat Erkec 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 »
Top 4 options for fixing Microsoft.AnalysisServices.AdomdClientUI.dll assembly error in SQL Server 2016 June 26, 2018 by Sifiso W. Ndlovu 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 »
Top 25 SQL interview questions and answers about indexes June 25, 2018 by Ahmad Yaseen Q1: What is the difference between a Heap table and a Clustered table? How can we identify if the table is a heap table? A 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 »
Database Filegroup(s) and Piecemeal restores in SQL Server June 22, 2018 by Prashanth Jayaram So far, we discussed many de-facto details about SQL Server database backup and restore. In this 15th article of the series, we are going to discuss, the file-group and piecemeal database backup and restore process. Read more »
SQL Server security considerations with open source tools June 21, 2018 by Timothy Smith 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 »
Contribute, contribute, contribute! June 20, 2018 by Craig Porteous A good friend of mine and co-speaker at SQLGrillen this year, Paul Broadwith (@Pauby), said this during a practice run of our presentation, not in a Steve Ballmer way thankfully. Although we were talking about open source projects at the time, the concept applies to blogging, organising and speaking at user groups or conferences, everything Community related really. Read more »
Query optimization techniques in SQL Server: tips and tricks June 19, 2018 by Ed Pollack Description Fixing bad queries and resolving performance problems can involve hours (or days) of research and testing. Sometimes we can quickly cut that time by identifying common design patterns that are indicative of poorly performing TSQL. Read more »
How to automatically create KPIs in SQL Server Reporting Services June 18, 2018 by Esat Erkec 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 »
How to implement error handling in SQL Server June 15, 2018 by Bojan Petrovic 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 »
SQL Server Database Backup and Restore reports June 14, 2018 by Prashanth Jayaram 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 »
