SQL Server News for 2013-07-22

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…

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:

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.

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.

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)

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)

Blogs : Analysis Services / BI

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)

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 : 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)

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)

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

Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can change your profile or follow the instructions on the daily newsletter.