In this issue:

Tech News : General Interest

Microsoft News : General Interest

Hardware News

Blogs : Administration

Blogs : Analysis Services / BI

Blogs : Backup and Recovery

Blogs : Computing in the Cloud

Blogs : Database Design, Theory and Development

Blogs : Events

Blogs : Integration Services/ETL

Blogs : Performance and Tuning

Blogs : Performance Point

Blogs : PowerPivot

Blogs : Reporting Services

Blogs : Security and Auditing

Blogs : Service Broker / SOA

Blogs : Software Development

Blogs : SQL Server 2012

Blogs : T-SQL

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-02-11

SQL Doc logo Hate explaining your database in meetings?
SQL Doc quickly documents your entire database schema so that you can easily present it to others. "This tool is embarrassingly easy to use." David Hayden, DavidHayden.com. Download a free trial now.
SQL Monitor logo Monitor the data you care about the most
SQL Monitor's customizable monitoring and alerting keeps you up-to-date with SQL Server performance, wherever you are. Free trial.
SQL Developer Bundle logo 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.
Editorial - Rethinking Your Design

I've always been a fan of finding ways to be effective and efficient with my resources. I like creative, out-of-the-box solutions that attack a problem in a different way. This past week I ran across a post from Brent Ozar that captured some of my philosophy well. It was titled The Fastest Query is the One You Never Make and it's worth a few minutes of your time.

The post looks at caching and other creative ways of preventing queries against your database. In the vast majority of my career, the database has been a bottleneck that isn't easily scalable. Buying larger, more powerful hardware is difficult to accomplish in many companies, and it only solves the problem so long. Writing much more efficient T-SQL is great, but it can be hard to find, and keep, top-notch coders in lots of companies. Re-architecting the database can be difficult, if not impossible.

However I have found that the front end software is often much more malleable, especially when developers get the opportunity to avoid the database and implement some new technology. Caching is also the type of solution I've found many developers enjoy implementing. It's kind of cool and different, giving them a chance to a) learn, and b) do something unusual. These are both things that often get developers excited.

The lesson here is that the database is a limited resource, perhaps your most limited one. Finding ways to work with it more efficiently, and spread the load to other servers, or remove it, can make your application perform much better.

I'm hoping we can a database service, PaaS, Azure like software installation on our own racks of machines at some point, and we'll be able to scale the database as we need it.

» 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.


Tech News : General Interest

Amazon users can now track their cloud-based databases with texts, email - Users of Amazon Web Services' Relational Database Service (RDS) can now keep track of their databases with new notifications via email and SMS. Amazon's Simple Notification Service (SNS) will give administrators a heads-up when their databases are running low on storage, have shut down, or a backup has started or finished. More than 40 types of notifications are available, Amazon said in a blog post on Monday....(more)

Microsoft News : General Interest

Microsoft Announces New Pricing for Azure SQL Reporting Services - Microsoft announced earlier this month that they were changing the way Azure SQL Reporting was being charged....(more)

Microsoft in Leaders Quadrant of Gartner* Magic Quadrant for Data Warehouse Database Management Systems - Hello SQL Server community! My name is Eron Kelly and I recently took the role of General Manager of Product Marketing for the Data Platform at Microsoft. I’ve been at Microsoft for over 12 years in various product marketing roles on BizTalk, Exchange, Office 365 and Windows Azure. I’m excited that my first blog post in my new role is to announce Microsoft’s positioning as a Leader the Magic Quadrant for Data Warehouse Database Management Systems that was published on January 31, 2013. ...(more)

Hardware News

Surface Pro Benchmark Showcases Impressive Performance - When reading data, Micron SSD delivers speeds of 400 megabytes-per-second....(more)

Dell To Build 3 Petabyte Storage Infrastructure For JAIST - Dell announced that Japan Advanced Institute of Science and Technology (JAIST) has selected Dell Compellent arrays to provide high-performance and large capacity storage for its growing, private cloud environment that supports its students and researchers....(more)

Blogs : Administration

Executive Visibility for DBAs: Use SQL 2008 Row+Page Compression, or SQL 2005 (SP2+) vardecimal Conversion, to Reduce Space ($/GB SAN) & Logical Reads - One of the great features available since SQL 2008 is Row and/or Page Compression. However, even for those of you on SQL 2005 in production who might be there for a little while: there’s a decent feature you can take advantage of too that is very expensive to do in SQL 2008. Why? Well, Compression in SQL 2008/12 requires that Entreprise edition to be installed, so if you want to benefit from this mega space saver (like 1.3TB down to 650GB as I have seen, plus queries running at as little as 40% the time they took before) you will be happy with VARDECIMAL conversion in SQL 2005 (SP2+)....(more)

SQL Collation and related performance impact, viewing collation in query plans - It has been a while since there has been activity on this blog, we as a team will be trying to post weekly going forwards so as to share what we do on a regular basis – happy reading!...(more)

SQL Server 2012 Diagnostic Information Queries (Feb 2013) - Here is the February 2013 version of my SQL Server 2012 Diagnostic Information Queries, with some minor tweaks and improvements to several of the existing queries. There is also one new query that looks for missing indexes for all databases on the instance....(more)

SQL Server 2008 R2 Diagnostic Information Queries (Feb 2013) - Here is the February 2013 version of my SQL Server 2008 R2 Diagnostic Information Queries, with some minor tweaks and improvements to several of the existing queries. I have decided to split off this into a separate version just for SQL Server 2008 R2. There are also two new queries that I have added this month. ...(more)

DBCC WRITEPAGE: an introduction - SQL Server’s undocumented commands, options, and trace flags are sometimes carefully-guarded secrets. I’ve been a major secret-keeper of lots of these, having written/re-written a bunch of the undocumented DBCC commands. I’ve been assiduously avoiding writing this blog post for 14 years but now my hand has been forced....(more)

Tracking page splits using the transaction log - Whenever I’m teaching about index fragmentation I get asked how to track page splits proactively. This can be useful to discover fragmentation occurring in indexes you didn’t know had fragmentation problems, without running the sys.dm_db_index_physical_stats DMV (see here for how that works) against all the indexes in your databases. Today this came up multiple times, both in class and in email, so it’s time to bubble this blog post up to the top of the list....(more)

Blogs : Analysis Services / BI

SSAS How-To: Create Dynamic Time Calculations To Reuse Over Any Measure - Rather than making individual time calculations for each measure in an SSAS cube (i.e. YTD, Prior YTD, YTD Variances, etc), one can use a more dynamic approach to time calcs applicable to all measures and save a great amount of dev time and overhead in the process....(more)

MDX #25 – Slicer or Sub-Cube? - Every developer with SQL background knows how the WHERE clause works. You put some condition in the WHERE clause, such as TheDate = Yesterday (pseudo code), and it will only return data for yesterday....(more)

SSAS: Clearing the MDX Script for a session in SSMS - Sometimes when troubleshooting performance issues you may want to comment out the MDX Script in your cube in order to quickly isolate whether the MDX Script is a significant contributor to the issue. So if you can reproduce your issue in your dev environment you can open up your project in BIDS / SSDT, comment out the script and re-deploy. But what happens if you can't reproduce the issue against dev. You may not have the same server specs, or the same data volumes or you may even have design changes in dev that have not yet been deployed that would cloud the issue....(more)

Blogs : Backup and Recovery

How to break a high end de-duplication backup device - I have been working with a client recently who purchased a nice top of the line de-duplication backup device in order to synchronize backups between two data centers. This device is very nice in how it will de-duplicate data to reduce storage needs and also compress the data. It will then synchronize the data to a secondary device hundreds of miles away....(more)

Fix: Error: 1505 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name and the index name - Here is another example where the error messages are very clear but often developers get confused with the message. I think the reason for the confusion is the word “Key” used in the error message. After I explained this to a developer who sent me the error he realize that it is about how we all interpret a same statement. ...(more)

Backup and Restore Database Using Command Prompt – SQLCMD - Backup and Restore is one of the core tasks for DBAs. They often do this task more times than they would have ideally loved to do so. One thing I noticed in my career that every successful DBA knows how to automate their tasks and spend their time either playing games on a computer or learning something new!...(more)

Blogs : Computing in the Cloud

How Does the Cloud Change a Systems Administrator's Job? - The systems administrator shares some similarity with the database administrator, in that it's rare to find a single job description that fits all people in that role. There are some basic similarities among various organizations, so I'll use those as a starting point....(more)

Blogs : Database Design, Theory and Development

Data Partitioning - Scaling-Out (Part 3: Factors to consider) - Of course, there are plenty other ways to scale-out the system in addition to horizontal partitioning and data sharding. Although in every case we have the same set of factors we need to consider before making the final decision of separating the data between the physical servers. Let’s talk about them today....(more)

Contingency Tables in SQL Server 2012 - Contingency tables are used to examine the relationship between subjects' scores on two qualitative or categorical variables. They show the actual and expected distribution of cases in a cross-tabulated (pivoted) format for the two variables. Here is an example of the actual and expected distribution of cases over the Gender column (on rows) and the MaritalStatus column (on columns) of the dbo.vTargetMail view from the AdvetureWorksDW2012 demo database:...(more)

Blogs : Events

Announcing SQL Saturday 197 Omaha Pre-Cons! - Here is a flyer produced by someone on the team - he says it all very well:...(more)

First DAX Advanced Workshop in London, May 2013 #dax #tabular #ssas - Are you working with SSAS Tabular? Are you an experienced PowerPivot user? In both cases, you should be aware that there is only one skill that is important for PowerPivot and SSAS Tabular, and it is the DAX language. I and Alberto have been using DAX since 2010, wrote several books containing several chapters about DAX and we know that there is still much to do. We have plans to publish more content online (more on this in a few months…) but we realized that the number of companies building tabular models is increasing every day. The common issues we see are about design, calculation, queries and performance. All of them are related to DAX, and we understand that learning DAX requires mentoring and practice (if only we had that 3 years ago…)....(more)

SQLIntersection: your new SQL Server conference - This is a quick post to announce that we’ve designed a cool new SQL Server conference, where we control all aspects of the show (including the speakers, the sessions, the workshops, the format, the schedule, and everything else) to make sure you get the best possible conference experience and the best possible return on your time and budget investment....(more)

Big Challenges in Data Modeling - Feb 28, 213 with Karen Lopez. We invite you to join us in this monthly DATAVERSITY webinar series, “Big Challenges with Data Modeling” hosted by Karen Lopez. Join Karen and two or more expert panelists each month to discuss their experiences in breaking through these specific data modeling challenges. Hear from experts in the field on how and where they came across these challenges and what resolution they found. Join them in the end for the Q&A portion to ask your own questions on the challenge topic of the month....(more)

Mastering the Art of Conversation and Making Business Contacts - Feb 21st, in New York City at 6:30pm. Talk! Talk! Talk. Everyone's doing it but are you getting the results you want? Do your words have power and impact or do they leave people wondering, "What was he (or she) talking about?" Whether you are chatting in small groups or one-on-one at a networking event, IT conference or after-hours party, your ability to master the art of conversation will help you find and connect with business contacts....(more)

Blogs : Integration Services/ETL

BimlScript: Incremental Load Design Pattern - Are you using Biml yet? Why not?! Business Intelligence Markup Language (Biml) speeds and simplifies SSIS development and improves code quality. There's a good reason that sounds like a win/win - it is a win/win! Best of all, BidsHelper supports Biml and it is free. BimlScript.com contains a collection of sample, functional Biml snippets and code that demonstrate all kinds of cool functionality....(more)

Blogs : Performance and Tuning

Getting Started with Extended Events in SQL Server 2012 - Extended Events provide a way of unintrusively monitoring what's going on in a SQL Server instance. Unlike SQL Server Profiler and SQL Trace, it has little performance impact. Now, in SQL Server 2012 SSMS, it is relatively easy to use, as Robert Sheldon shows....(more)

SQL Monitor - Using the performance data smartly - How does a Database Consultant use a tool such as SQL Monitor? The seasoned expert doesn't just dive into DMVs before considering baseline information, application stress and the interrelation of different factors over time, to understand what's changed. It is the art of putting performance data in context....(more)

The Fastest Query is the One You Never Make - When someone brings me a query to tune, I start with a few questions: How long does it take to run now?  I’m looking for a time at first, and later as I progress through tuning, I’ll measure it more scientifically with logical reads and CPU cycles....(more)

Something Smells: Parameter Sniffing - Parameter sniffing is an aspect of SQL Server that is typically viewed as a bad thing. The simple fact is that SQL Server uses parameter sniffing all the time, that’s how SQL Server works. It is only those times when parameter sniffing results in performance issues that people (naturally) complain. ...(more)

Don’t change value of that parameter - Parameter sniffing is a well known among SQL User community. But I have seen variations of this frequently that need a bit creative handling and solution may not that straight forward....(more)

Blogs : Performance Point

PerformancePoint 2013 Dependencies for Analysis Services - Besides the usual hassle configuring PerformancePoint, including insufficient permissions to databases and service accounts, version 2013 requires the SQL Server 2008 R2 drivers. This is surprising considering that SharePoint 2013 shipped after SQL Server 2012....(more)

PerformancePoint Filter – Save as my defaults permissions - It has been a while since I did some blogs, so time to get going again. What better way to start than with a PerformancePoint one, right? This is a post that I have been meaning to do, particularly after I responded to a post about it in the PPS M & A forums here – PerformancePoint Filter – Save as default permissions. I had come across this particular issue at a few client sites as well. The ‘Save as my defaults’ option for filters was a new capability that was added with SharePoint 2010 SP1 and I posted a blog on the new features added to PerformancePoint with SP1 here PerformancePoint 2010 Cascading & Apply Filters – SP1 Features....(more)

Blogs : PowerPivot

Hurricane Sandy Mash-Up: Hive, SQL Server, PowerPivot & Power View - For my #SQLPASS Summit 2012 talk SQLCAT: Big Data – All Abuzz About Hive (slides available to all | recording available to PASS Summit 2012 attendees) I showed a mash-up of Hive, SQL Server, and Excel data that had been imported to PowerPivot and then displayed via Power View in Excel 2013 (using the new SharePoint-free self-service option). PowerPivot brings together the new world of unstructured data from Hadoop with structured data from more traditional relational and multi-dimensional sources to gain new business insights and break down data silos. We were able to take very recent data from Hurricane Sandy, which occurred the week before the PASS Summit, and quickly build a report to pinpoint some initial areas of interest. The report provides a sample foundation for exploring to find additional insights. If you need more background on Big Data, Hadoop, and Hive please see my previous blogs and talks....(more)

Blogs : Reporting Services

SSRS How-To: Simple Method to Pass MDX Multi-Select Parameters to MDX Datasets - I recently ran across a poorly covered topic in regards SSRS development using MDX parameters and datasets. After some trail and error and visiting a number of online resources, I found the approach below to be the most straight forward....(more)

Blogs : Security and Auditing

How Safe Is Your Data From Theft? - his Friday will mark the 41st anniversary of the day that Jerry Neal Schneider became a household name....(more)

Blogs : Service Broker / SOA

How to enable and disable a queue using SMO - The SMO object model for SQL Server ServiceQueue does allow one to enable or disable a queue, but the property that modifies the queue status is not intuitive, it is IsEnqueueEnabled:...(more)

Blogs : Software Development

Database Deployment: The Bits - Versioning - Although databases have no inherent way of recording their version numbers, SQL Server provides the means of doing so, and much more besides. This is a great advantage to anyone faced with the task of deploying databases without errors....(more)

Blogs : SQL Server 2012

SQL Server 2012 Developer Training Kit BOM - This wiki provides a comprehensive list of all of the SQL Server 2012 Developer Training Kit Content. All content has been tested to work with the following technology releases:...(more)

Blogs : T-SQL

Fear Not the Mighty TRIM() Function! - Jason Strate wrote up an excellent blog post dealing with the people asking for SQL Server to have a TRIM() function in addition to the LTRIM() and RTRIM() function and the possibility of performance issues creeping up. You can check out his post below:...(more)

Incorrect Results with Indexed Views - Summary: If you use MERGE, indexed views and foreign keys, your queries can return incorrect results. ...(more)

Obtaining rowcounts when using Composable DML [T-SQL] - In my August 2009 blog post Exploring Composable DML I introduced a new feature in SQL Server 2008 called Composable DML and also outlined one of its limitations; namely that data from the OUTPUT cannot be aggregated prior to insertion. Composable DML does have some useful scenarios however and one of those is in capturing and storing values that are replaced by an UPDATE (which I have talked about before in Using Composable DML to maintain entity history). Here’s the basic premise:...(more)

UNION, UNION ALL and ORDER BY - At every client I've been to, there have always been questions about UNION and UNION ALL. They may return the same results or they may not. It all depends on the data. If you don't have any duplicates, you'll get the same number of rows returned whether you use UNION or UNION ALL. If you have duplicates, UNION will return fewer rows than UNION ALL. No matter who your database vendor is, a UNION statement follows the rules of set theory. ...(more)


Administrative