In this issue:

Vendors/3rd Party Products

Tech News : General Interest

Press Releases

Blogs : Administration

Blogs : Analysis Services / BI

Blogs : Career

Blogs : Data Mining

Blogs : DBA Tools

Blogs : Hardware

Blogs : NOSQL

Blogs : Performance and Tuning

Blogs : Professional Development

Blogs : Security and Auditing

Blogs : Software Development

Blogs : SQL Server 2008 R2

Blogs : SQL Server 2012

Blogs : SQL Server 2014

Articles : Administration

Articles : Reporting Services

Database Weekly - www.databaseweekly.com

The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

SQL Server News for 2013-07-22

SQL DBA Bundle Top 5 Hard-earned Lessons of a DBA
‘10 Tips for Efficient Disaster Recovery’ by Steve Jones. Prepare for any future disaster by reading Steve’s tips today.
SQL Source Control Get your SQL Server database under version control now!
Version control is standard for applications, but databases haven’t caught up. So how can you bring database development up to speed? Why should you start? Find out…
SQL Backup presents Transaction Log Management Free eBook! SQL Server Transaction Log Management
Find out how understanding how log files work makes all the difference in a crisis. Then try SQL Backup Pro to put the tips into practice. Download your free resources now.
Editorial - What Counts for a DBA: Baselines

One morning, you wake up and feel funny. You can't quite put your finger on it, but something isn't quite right. What now? You check your symptoms over the next few days; do you feel the same, better, worse? If better, then great, it was some temporal issue, perhaps caused by an allergic reaction to some suspiciously spicy chicken. If the same or worse then you go to the doctor for some health advice, but armed with some data to share, and having ruled out certain possible causes. Whether you realize it or not, in comparing how you feel one day to the next, you have taken baseline measurements. In much the same way, a DBA uses baselines to gauge the gauge health of their database servers. Of course, while SQL Server is very willing to share data regarding its health and activities, it has no idea of the difference between good and bad.

Over time, experienced DBAs develop "mental" baselines with which they can gauge the health of their servers almost as easily as their own body. They accumulate knowledge of the daily, natural state of each part of their database system, and so know instinctively when one of their databases "feels funny". Equally, they know when an "issue" is just a passing tremor. They see their SQL Server with all of its four CPU cores running close 100% and don't panic. Why? It's 5PM and the end-of-day reports are running, which are very CPU intensive. Equally, they know when they need to respond in earnest.

Nevertheless, no DBA can retain mental baselines for every characteristic of their systems, so we need to collect physical baselines too. In my experience, surprisingly few DBAs do this. Part of the problem is that SQL Server provides a lot of instrumentation. If you look, you will find an almost overwhelming amount of data regarding user activity on your SQL Server instances, and use and abuse of the available CPU, I/O and memory. It seems like a huge task even to work out which data you need to collect, let alone start collecting it on a regular basis, managing its storage over time, and performing detailed comparative analysis.

Without baselines, though, it is very difficult to pinpoint what ails a server, just by looking at a single snapshot of the data, or to spot retrospectively what caused the problem by examining aggregated data for the server, collected over many months.

It isn't as hard as you think to get started. You've probably already established some troubleshooting queries of the type SELECT Value FROM SomeSystemTable. Capturing a set of baseline values for such a query can be as easy as changing it as follows:

INSERT into BaseLine.SomeSystemTable (value, captureTime) SELECT Value, SYSDATETIME() FROM SomeSystemTable;

Of course, there are monitoring tools that will collect and manage this baseline data for you, automatically, and allow you to perform comparison of metrics over different periods. However, to get started, and to prove to yourself the value of baselines, stick something similar to the above query into an agent job, running every hour or so, and you are on your way with no excuses! Then, the next time you investigate a slow server, and see x open transactions, y users logged in, and z rows added per hour in the Orders table, compare to your baselines and see immediately what if anything changed.

Louis Davidson (Guest Editor)

» Join the debate, and respond to today's editorial on the forums


The Weekly News

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.


Vendors/3rd Party Products

Codeplex: Script SQL Server Configuration - Server level objects (linked servers, audits, etc.) are scripted in one file per object type. This makes DR easier and reviewing history possible. Database level objects (tables, functions, etc.) are scripted in one file per object. This makes reviewing object history easy and DR possible. The database backup is the primary DR mechanism for databases....(more)

Codeplex: SalarDbCodeGenerator - SalarDbCodeGenerator is a database first, code generator. It uses customizable patterns to generate different codes and models for applications. The generator engine is mostly optimized for C# but it can be used to generate any other programming language code...(more)

SQL Monitor Custom Metric: Top Buffer Cache Object - This metric measures the amount of memory used in the buffer cache by the largest object (based on the number of pages). It checks the sys.dm_os_buffer_descriptors to identify the object, and returns the relative percentage used. You should use this metric if you want to monitor what is in the buffer area, or if you are having performance-related disk read problems....(more)

Tech News : General Interest

RSS: let's not do the same mistakes - There are so many ways to consume feeds: Email subscriptions have been massively pushed by Wordpress for example, RSS recipes on IFTTT also provide innovative alternatives. Did you know that Flipboard also relies heavily on RSS to show you content in a beautiful magazine format? These are non-google-reader-like experiences: let’s get more of these to make the ecosystem stronger and more resilient....(more)

Press Releases

SQL Server Expert Thomas LaRock Elected PASS President - Thomas LaRock will serve as president for two years, beginning January 1, 2014. He is joined by a notable executive committee, six at-large directors, four founding representatives and appointed advisors. ...(more)

Blogs : Administration

Microsoft® SQL Server® 2012 Service Pack 1 (SP1) Master Data Services Add-in For Microsoft® Excel® - The Master Data Services Add-in for Excel gives multiple users the ability to update master data in a familiar tool without compromising the data’s integrity in Master Data Services...(more)

SQL Server Standards Support Documentation - The SQL Server standards support documentation provides detailed support information for certain standards that are implemented in Microsoft SQL Server....(more)

Locking in Microsoft SQL Server (Part 18) – Key lookup deadlock - There is a special case of the deadlock called key lookup deadlock. This deadlock can happen when multiple sessions are reading and updating the same rows simultaneously. Let us look at the example....(more)

Introducing the sp_Blitz™ application - sp_Blitz™ provides a summary of potential issues with a single SQL Server. The only problem is that it’s not an easily digestible report. A few weeks ago, we released a Windows application of sp_blitz™...(more)

Monitoring SQL Server 2012 AlwaysOn Availability Groups Worker Thread Consumption - To see what potential CPU problems could arise with 40+ availability groups, and a multiple of that of availability group databases, we can look at HADR worker thread contention. SQL Server lends us a handful of ways to monitor and troubleshoot this, including XE events. ...(more)

Blogs : Analysis Services / BI

Announcing the SQL Server Central Stairway to Biml! - I believe Business Intelligence Markup Language (Biml) is the future of business intelligence....(more)

Why You Need a Data Warehouse - For a company to be successful in the future, they must make good decisions. And to make good decisions requires all relevant data to be taking into consideration. And the best source for that data is a well-designed data warehouse....(more)

Why You Should Never Trust a Data Scientist - The wonderful thing about being a data scientist is that I get all of the credibility of genuine science, with none of the irritating peer review or reproducibility worries...(more)

Data Science and the Semmelweis Reflex - Ignaz Semmelweis was ignored and essentially driven mad (he spent his final years in an asylum) by his colleagues’ refusal to accept the truth. It’s likely that your experiences with sharing the insights provided by data science within your organization will follow a similar trajectory ...(more)

Blogs : Career

My Advice About Growing as an IT and Data Professional - I've experienced an unexpected surge in requests for mentoring and advice from friends and colleagues about career growth. ...(more)

Blogs : Data Mining

Microsoft SQL Server Data Portability Documentation - The SQL Server data portability documentation explains various mechanisms by which user-created data in SQL Server can be extracted for use in other software products. These mechanisms include import/export functionality, documented APIs, industry standard formats, or documented data structures/file formats....(more)

Blogs : DBA Tools

Microsoft Finally Allows Use of Remote Desktop Services (RDS) on Windows Azure Virtual Machines - Microsoft has officially changed Windows Azure licensing terms (PUR) to allow the use of Remote Desktop Services (RDS) on Windows Azure Virtual Machines. ...(more)

Blogs : Hardware

Using TPC-E OLTP Benchmark Scores to Compare Processors - When customers are looking to upgrade their database servers to new hardware, a new operating system, and a new version of SQL Server, part of this process is a comparison of the estimated TPC-E score of the existing system compared to the estimated TPC-E score on the new system...(more)

Blogs : NOSQL

Data Science Laboratory System - Key/Value Pair Systems - Though the Key/Value pair paradigm is common to almost every computer language, there is no clear agreement yet for the definition of a Key/Value Pair database. However, Key/Value pair databases are valuable for special applications where speed of writing data is more important than searching and general versatility. It is certainly worth experimenting with in a data science lab....(more)

World’s smallest No SQL Database: ACID & Transactions - Even for a first attempt, World’s Smallest No SQL Database is actually pretty good. We got 3 of the 4 of ACID. The DB is Atomic, since change will either go in or be rejected, Consistent, you can’t see changes half way and Isolated, one change can’t modify/view another....(more)

Blogs : Performance and Tuning

Aggregates and Partitioning - The changes in the internal representation of partitioned tables between SQL Server 2005 and SQL Server 2008 resulted in improved query plans and performance in the majority of cases (especially when parallel execution is involved). Unfortunately, the same changes caused some things that worked well in SQL Server 2005 to suddenly not work so well in SQL Server 2008 and later. ...(more)

Blogs : Professional Development

You Need This One Skill to Succeed in IT - All of the best IT professionals I have worked with have excellent problem solving skills...(more)

Blogs : Security and Auditing

Your website has never been hacked! (except for all the times that it has) - Absence of evidence is not evidence of absence. Not knowing you’ve had a security incident is not the same as not having had a security incident and there are some interesting precedents that illustrate this rather well....(more)

Understanding the SQL Server Symmetric Encryption Algorithms - We'll specify two types of algorithms here. The first are the symmetric encryption algorithms considered broken either because computing power has caught up with them or there's a flaw that can be exploited. The second are algorithms which SQL Server implements in a weakened or incorrect way. ...(more)

Blogs : Software Development

Quick and Dirty PowerShell SQL Server Load Test - This PowerShell script that can be used to simulate running multiple clients against a SQL Server, for simple performance or hardware testing....(more)

July, the 31 Days of SQL Server DMO’s – Day 16 (sys.dm_sql_referenced_entities) - The sys.dm_sql_referenced_entities Dynamic Management Function returns a result set of all objects that are being referenced as part of the definition of the object that you reference within the function argument section. ...(more)

July, the 31 Days of SQL Server DMOs – Day 18 (sys.dm_io_virtual_file_stats) - The sys.dm_io_virtual_file_stats Dynamic Management Function is used to return IO statistic information about each of your database files on your server. ...(more)

July, the 31 Days of SQL Server DMOs – Day 17 (sys.dm_sql_referencing_entities) - The sys.dm_sql_referencing_entities Dynamic Management Function is very similar to the function sys.dm_sql_referenced_entities, except that it displays all objects that are REFERENCING the object that you specify, rather than those being REFERENCED....(more)

Blogs : SQL Server 2008 R2

SQL Server 2008 SP3 Cumulative Update #12 released! - Microsoft has released the Cumulative Update #12 for SQL Server 2008 SP3: http://support.microsoft.com/kb/2863205/en-us After you have applied the CU12, the version number of SQL Server 2008 SP3 will be 10.00.5844.00....(more)

Blogs : SQL Server 2012

SQL Server 2012 Service Pack 1 Cumulative Update #5 is available! - The SQL Server team has released CU #5 for Service Pack 1. KB article: KB #2861107 Build # is 11.0.3373. This build has 30 fixes by my count....(more)

Blogs : SQL Server 2014

Transaction dependencies and speculative reads with memory-optimized tables - According to the whitepaper about “High-Performance Concurrency Control Mechanisms for Main-Memory Databases”, there are two implementations of the multiversion storage engine in SQL Server 2014 CTP1, one using optimistic concurrency with no locking and one using locking....(more)

Articles : Administration

Different Ways to Find SQL Server Object Dependencies - The latest dynamic management views (sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities) and catalog view (sys.sql_expression_dependencies) can be used by Developers as well as by Database Administrator to dig into their databases and learn/document different types of dependencies....(more)

Articles : Reporting Services

Using Microsoft Excel to Retrieve SSAS Tabular Data - Although it is well-known how to create a tabular database in PowerPivot, it is less obvious that there are several useful options for retrieving SSAS tabular data into Excel. This provides an easy way of manipulating, visualizing and analyzing the data without needing to know the details of SSAS and the tabular model....(more)


Administrative