Showing posts with label DBA. Show all posts
Showing posts with label DBA. Show all posts

Wednesday, February 11, 2015

The two words a DBA or Dev loves to hear, "It's Slow..."

Brent Ozar - “It’s Slow” Is Not A Metric

When I start a SQL Server Critical Care with a client, the first thing I ask is, “What is your #1 pain point? Where does it hurt?” The answer I hear most often is, “The server is too slow”.

I’m a runner. If I was able to run a 5K race at an 8:30 per mile pace, I would think I was the fastest runner on earth. The world record for a 5K by a woman is at a 4:34 per mile pace. She would think an 8:30 pace was…slow.

Slow is relative.

Slow is not a metric.


In order to effectively measure our systems, we need metrics.This is why baselines for our systems are so essential.

Getting Started with Baselines ...

Tools for Baselines ...

Have metrics, not feelings

“Slow” is relative. “Slow” is not a metric. You never know when someone is having a bad day and everything feels slow to him, or he needs to rush out the door early because his kid is sick, or she is getting ready for vacation and time has slowed down in her world. Measure and track. Numbers don’t lie.

Brent says: You get what you measure. The instant you start putting a graph on your cubicle wall, people start paying attention.

Kendra says: User complaints per day is also a metric! Find out what parts of the application people are complaining about and what their experience is specifically. As you work through issues, “Numbers of complaints solved” is a really useful metric for your next performance review.

I swear, Brent and team have someone slipping them information from my day job [Yes, I'm looking at you... ;] We've recently been having some "It's Slow" and conversations and "Prove It" benchmark tests so this topic is nice and timely.

It's also funny just how long us Dev's and DBA's have been having this conversation. I remember fielding "It's slow" questions around my VB1 app running on Windows 3.1 (where we had to use a floppy disk to do our daily information transfers... lol...). The answer is still the same today, "define slow"...

Wednesday, December 10, 2014

"SQL Server Execution Plans" free eBook from Red Gate

Red Gate - SQL Server Execution Plans

Improve the performance of your .NET code – even if SQL Server is slowing you down

Download your free copy of SQL Server Execution Plans, the only in-depth look at how to improve your query performance through careful design of execution plans.

Then, isolate the problem that's causing pain in your own application, with a 14-day free trial of new ANTS Performance Profiler 9, which now includes a detailed view of SQL Server execution plans.

Narrow down the problem to your .NET code or SQL Server database in a single profiling session. Get line-level timings for slow code, and full execution plans for your queries, with expensive or slow operations highlighted for you automatically.

1. Free eBook: SQL Server Execution Plans

  • Free 321 page eBook that covers execution plan basics, how to interpret plans, complex queries, and how to optimize execution plans.
  • Includes 169 SQL code samples & 173 illustrations.
  • Written by SQL Server MVP Grant Fritchey, the SQL Server community's leading authority on execution plans.
  • Yours free – normally $29.99

2. Free trial: root out .NET & SQL Server performance problems with ANTS Performance Profiler


image ..."

NOTE: This is dated September 2012, but was also modified June 2014...


I thought I had blogged about this, but didn't so I might as well now! I mean, who doesn't need 322 pages on SQL Server Execution Plans?

Liked how the eBook download already included the example files...


Thursday, October 30, 2014

The Brent Ozar New All-in-One Download Pack (and discount on their commercial stuff secret)

Brent Ozar - Announcing Our New All-in-One Download Pack

We give away a lot of stuff – scripts, setup checklists, e-books, posters, you name it.

But we kept hearing a theme from folks: “Wow, I’ve seen one of your tools before, but I had no idea there were so many others!” In order to get everything, they had to go all over the place in our site.

To fix that, we’ve got a new easy button: our free SQL Server download pack. Now when you get anything, you’ll get everything in a single zip file, plus get email notifications whenever there’s a new version.

Enjoy, and hope we make your job suck just a little less.


[GD: POST Leached in FULL... Come on, it was just a few sentences!]

All of the awesome free Brent Ozar and team downloads in one? Yeah, that's cool.

The discount secret? You have to subscribe to their RSS Feed to see... :)

Thursday, September 25, 2014

Adventure Works, 2014

Jimmy May, Aspiring Geek: SQL Server Performance, Best Practices, & Productivity - AdventureWorks 2014 Sample Databases Are Now Available

Recently, SQL Community feedback from twitter prompted me to look in vain for SQL Server 2014 versions of the AdventureWorks sample databases we’ve all grown to know & love.

I searched Codeplex, then used the bing & even the google in an effort to locate them, yet all I could find were samples on different sites highlighting specific technologies, an incomplete collection inconsistent with the experience we users had learned to expect.  I began pinging internally & learned that an update to AdventureWorks wasn’t even on the road map.

Fortunately, SQL Marketing manager Luis Daniel Soto Maldonado (t) lent a sympathetic ear & got the update ball rolling; his direct report Darmodi Komo recently announced the release of the shiny new sample databases for OLTP, DW, Tabular, and Multidimensional models to supplement the extant In-Memory OLTP sample DB.

What Success Looks Like

In my correspondence with the team, here’s how I defined success:

1. Sample AdventureWorks DBs hosted on Codeplex showcasing SQL Server 2014’s latest-&-greatest features, including:

  • In-Memory OLTP (aka Hekaton)
  • Clustered Columnstore
  • Online Operations
  • Resource Governor IO

2. Where it makes sense to do so, consolidate the DBs (e.g., showcasing Columnstore likely involves a separate DW DB)

3. Documentation to support experimenting with these features

As Microsoft Senior SDE Bonnie Feinberg (b) stated, “I think it would be great to see an AdventureWorks for SQL 2014.  It would be super helpful for third-party book authors and trainers.  It also provides a common way to share examples in blog posts and forum discussions, for example.”


Adventure Works 2014 Sample Databases

Adventure Works 2014 sample databases are an upgrade from the 2012 version. To learn how to install the databases, see Readme for Adventure Works 2014 Sample Databases.docx.


Having training, sample data that is safe to demo and use is always, always, always nice...


Related Past Post XRef:
Community AdventureWorks on Azure one year later, where you, the community, not only helped keep it going BUT also donated GBP351.49 to War Child charity
Community Driven Read-Only AdventureWorks2012 now available on SQL Azure
103 SQL Server 2005 Samples and AdventureWorks Sample Databases Download

Tuesday, September 16, 2014

Does your SQL Smell? The free "119 SQL Code Smells" eBook might help you sniff it out (and fix it)

simple talk - 119 SQL Code Smells

Written with advice, help or contributions from over 25 SQL professionals
Download the free PDF: from Red Gate

Once you've done a number of SQL code-reviews, you'll be able to identify signs in the code that indicate all might not be well. These 'code smells' are coding styles that, while not bugs, suggest design problems with the code. In this PDF, Phil's put together 119 of those code smells so you can see what to avoid and why.

Kent Beck and Massimo Arnoldi seem to have coined the term 'CodeSmell' in the 'Once And Only Once' page of, where Kent also said that code ‘wants to be simple’. Kent Beck and Martin Fowler expand on the issue of code challenges in their essay ‘Bad Smells in Code’, published as Chapter 3 of the book ‘Refactoring: Improving the Design of Existing Code’ (ISBN 978-0201485677).

Although there are generic code smells, SQL has its own particular habits that will alert the programmer to the need to refactor code...




One can be a bit defensive about SQL code smells. I will cheerfully write very long stored procedures, even though they are frowned upon. I’ll even use dynamic SQL on occasion. You should use code smells only as an aid. It is fine to ‘sign them off’ as being inappropriate in certain circumstances. In fact, whole classes of code smells may be irrelevant for a particular database. The use of proprietary SQL, for example, is only a code smell if there is a chance that the database will be ported to another RDBMS. The use of dynamic SQL is a risk only with certain security models. Ultimately, you should rely on your own judgment. As the saying goes, a code smell is a hint of possible bad practice to a pragmatist, but a sure sign of bad practice to a purist.

In describing all these 119 code-smells in a booklet, I’ve been very constrained on space to describe each code smell. Some code smells would require a whole article to explain them properly. Fortunately, SQL Server Central and Simple-Talk have, between them, published material on almost all these code smells, so if you get interested, please explore these essential archives of information.


nuff said...

Wednesday, September 10, 2014

Visualizing Database Schema changes, with a little help from the Google Charts API

Maria Zakourdaev - Using Google Charts API to Visualize Schema Changes

Last week I have worked on the new email report using Google Charts and liked it so much that decided to share it here with anyone who finds it useful.

I have a Schema Changes Audit table which is being maintained by the DDL Trigger. The relevant record is added to this table every time anyone changes objects on the server




I just thought this something interesting, kind of cool and different. I'd have never thought to use the Google Charts API (or other charting API) like this...

Wednesday, September 03, 2014

Using Brent Ozar's magic SQL steps to query and find unanswered StackExchange questions

Brent Ozar Unlimited - Finding Unanswered StackExchange Questions with SQL

You love Q&A sites like and, but sometimes it’s hard to find interesting questions that need to be answered. So many people just sit around hitting refresh, knocking out the new incoming questions as soon as they come in. What’s a database person to do?

Use the power of the SQL. lets you run real T-SQL queries against a recently restored copy of the StackExchange databases. Here’s my super-secret 3-step process to find questions that I have a shot at answering.

Step 1. Find out how old the restored database is....


Step 2. Find questions everybody’s talking about....



Step 3. Find questions that people keep looking at....



Why web query when you can just SQL your way through StackExchange? I don't know about you, but I often dream in SQL (no lie.. sigh), so this approach to StackExchange struck a cord for me. Now, if only I was actually smart enough to provide good answers... :O


Related Past Post XRef:
SELECT * FROM StackExchange. There's the easy way and the hard, yet much more data fun, way...
Stacks and stacks of data - Your copy of the Stack Overflow’s (and family) public data is a download away

The Stack Family (StackOverflow, SuperUser, etc) gets OData’d via Stack Exchange Data Explorer
Build something awesome with the new StackExchange v2 API and win something awesome...
Stacking up the Open Source Projects, Stack Exchange is...

Monday, August 18, 2014

New SQL Server 2014 Virtual Labs (aka Hands On Labs) available... 18, no... 20!

A Story of BI, BIG Data and SQL Server in Canada - 18 Free SQL Server 2014, Azure and BI virtual labs


Do you want to learn about SQL Server 2014, Azure SQL Databases, Corporate BI, and Power BI without the need of installing and configuring the installation bits?

We’ve just released 18 (eighteen) new virtual labs based on the integrated demo Virtual Machines with full step-by-step instructions.

All that you need to start using these online labs is the Internet access and browser (IE, Chrome, and FireFox)*



SQL Server 2014 has been out for a little bit now, time for you to start wrapping your head around its new features... And there's nothing to install, just learn...



Tuesday, July 29, 2014

"Killer Free [Free as in free from nag's, calls, whines, free] SQL Tools" from ApexSQL. Includes ApexSQL [Auto] Complete, ApexSQL Refactor and ApexSQL Search

Dirk Strauss - ApexSQL Provides Excellent SQL Tools For Free

"ApexSQL has a few free tools on their site. Before I carry on, I was in no way compensated for this blog post at all. Now that is out of the way, check out ApexSQL tools. I was amazed by the feature richness of these tools. I would be amiss if I didn’t share this with the developer community out there. If you are a developer using SSMS, you definitely must download their free tools and give them a try.


These tools are:

  • ApexSQL Complete
    • SSMS and VS integration
    • SQL syntax checking
    • SQL object descriptions
    • SQL code completion
    • SQL code visualization
    • Snippet management
  • ApexSQL Refactor
    • SQL parameter management
    • SQL formatting
    • Consistent code layout
    • Database object refactoring
    • Batch formatting
    • One-to-many relationship replacement
  • ApexSQL Search
    • Smart renaming
    • Text search
    • Database object search
    • Easy SSMS tab navigation
    • SQL code cleaning
    • Graphical dependencies


ApexSQL - Killer Free SQL Tools



Why choose ApexSQL free tools?

Great software

We strive to ensure that even though these tools are Free, we still strive for a "Best of Class" product, including, performance, usability, and quality in its respective product class. If you think we can improve our Free tools please contact us to let us know how!

Feature rich

Our free tools implement all the features you need for FREE, including SQL formatting, Database object and Text search, Smart renaming, SSMS and Visual Studio integration, SQL refactoring, and much more. These are features that you would actually have to pay for … but don’t have to!

Continuous development

Just like all of our other tools, we strive to release updates to our Free SQL tools regularly, fix problems, improve performance, and add features. We look to maintain host integration with new versions of SSMS and Visual Studio when they come out. See What's next, to check out our product roadmap

Killer support

Don’t think that because these tools are free that they are unsupported! We still offer Full support via bundle subscriptions including email, phone, and WebEx for all of our Free tools. Even without a subscription to a bundle we are happy to answer questions via our forum and attempt to address any and all issues quickly

Yes, these tools are really FREE

  • No crippleware, time bombs, or bait and switch
  • No annoying embedded ads or nag screens
  • No additional costs to upgrade to newer versions of SQL Server e.g. 2012, 2014
  • No professional version or non-free features you must pay for
  • No mandatory re-installs
  • No requirement to install additional, non-free software
  • No phone calls from sales people


I'd recently seen, and have been meaning to blog about, ApexSQL Complete being free, but I didn't know about the other tools being available. Kudo's to Dirk for making it really clear what was free and how "free" they were!

Monday, April 28, 2014

Free (Email Address-ware) "Performance Tuning with SQL Server Dynamic Management Views" eBook from Red Gate

Red Gate - Performance Tuning with SQL Server Dynamic Management Views

"Dynamic Management Views (DMVs) reveal the hidden activity of your database sessions and transactions – and this book is packed full of practical advice on getting to grips with them.

Download your free copy of Performance Tuning with SQL Server Dynamic Management Views by Tim Ford and Louis Davidson to demystify DMVs. The eBook provides you with the core techniques and scripts to monitor your query execution, index usage, session and transaction activity, disk IO, and more.

You can track down and fix even more SQL Server problems with the SQL DBA Bundle. You’ll get web-based monitoring and six more tools to support your core SQL Server administration tasks, so pick up a free trial and put your new knowledge into practice.

1. Free eBook: Performance Tuning with SQL Server Dynamic Management Views

  • Root out the queries that are causing memory or CPU pressure on your system
  • Investigate caching, and query plan reuse
  • Identify index usage patterns
  • Track fragmentation in clustered indexes and heaps

2. Free trial of the SQL DBA Bundle

  • Real-time performance data and alerts
  • Built with advice from SQL Server experts and MVPs
  • Compress SQL Backups by up to 95%
  • The industry-standard schema and data
    comparison tools



With the advent of the Dynamic Management Objects (DMOs) in SQL Server 2005, Microsoft vastly expanded the range and depth of metadata that could be exposed regarding the connections, sessions, transactions, statements, and processes that are, or have been, executing against a database instance. These DMOs provide insight into the resultant workload generated on the server, how it is distributed, where the pressure points are, and so on, and are a significant and valuable addition to the DBA's troubleshooting armory.


In short, if you look hard enough, you will find an almost overwhelming amount of data regarding user activity on your SQL Server instances, and the CPU, I/O, and memory use and abuse that results. The focus of this book is squarely on core engine activity, and troubleshooting. We'll describe the most important columns that these DMOs return, and provide a core set of scripts that can be saved and adapted for your own environment, to shine some light on previously dark corners of SQL Server performance optimization. In the process, we'll cover about a third of the total number of available DMOs, spanning 6 of the available 19 categories of DMO, outlined below.


Full time DBA, or accidental, this free 337 eBook is one that's going to be hard to beat. Normally these run 60+ bucks, so get it... get it now (or later, it should be around for a while... ;)

(via Tatworth - Free eBook: Performance Tuning with SQL Server Dynamic Management Views)


Related Past Post XRef:
“The SQL Server DMV Start Pack” (eBook and T-SQL) from Red Gate and more Dynamic Management Views (DMV) stuff

Friday, April 18, 2014

Data Editing in SQL Server Data Tools just got a little cooler (you can filter and sort now...)

Deborah's Developer MindScape - SSDT Data Editor Now Has Sorting and Filtering!

The SQL Server Data Tools (SSDT) provide access to many SQL Server features from within Visual Studio. One of those features, available from the SQL Server Object Explorer, is the visual Data Editor.

While the Data Editor has always been great for inserting, updating, and viewing data, it did not support any sorting or filtering … until now!



  • For an introduction to SSDT and the SQL Server Object Explorer, see this post.
  • For an introduction to the SSDT Data Editor, see this post.

The March 2014 release of SSDT added support for SQL Server 2014 databases. But it ALSO provided new features in VS 2012 and VS 2013 for sorting and filtering the data in the Data Editor!

If you are using VS 2012, you can use the update option to get this update (SQL | Check for Updates).

If you are using VS 2013, the update should appear in the Notification window when you click the notification flag:



While I don't suggest this as a main stream means to edit your data, but some times it's just so much easier to fire this up and edit knock out your quick updates. This also helps you avoid the "oh crap, I used the wrong WHERE in my Query Window update" game (not that I've ever done that, updating every row in a table, without a BEGIN TRAN... nope, not me! :/  )

Thursday, April 17, 2014

SQL Server 2014 Secret Killer New Feature, Cardinality Estimator (okay, it's not Secret, but it seems pretty killer...)

Jimmy May's Blog - MSDN Whitepaper: More Cowbell—Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

Hot off the presses is this new MSDN white paper:

Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

One of the gems introduced in SQL Server 2014 is the Cardinality Estimator (CE)—new! improved! & now with more cowbell.  I'm thrilled to be a Technical Reviewer for a superb MSDN white paper authored by my friend, buddy, & pal Joe Sack (b|t). It's exciting & humbling to see my name among such an array of Contributors & Reviewers—including several former colleagues from Azure CAT (formerly SQL CAT b|t).

What’s a CE?

As described on the Cardinality Estimation (SQL Server) page:

Cardinality estimates are a prediction of the number of rows in the query result. The query optimizer uses these estimates to choose a plan for executing the query. The quality of the query plan has a direct impact on improving query performance.

Why a New CE?

The pre-existing CE is more than a decade old.  Both OLTP & DW workloads have changed—& databases are bigger by far than they used to be.  Often, cardinality changes spawned disparate plans (in one prototype, over 78 different plans were generated by the former CE).  Plainly & simply—the CE needed more cowbell.

What’s New?

During SQL14 TAP, SQL Engineer Kate Smith provided a heads up.  Highlights included:


Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

Summary: SQL Server 2014 introduces the first major redesign of the SQL Server Query Optimizer cardinality estimation process since version 7.0.  The goal for the redesign was to improve accuracy, consistency and supportability of key areas within the cardinality estimation process, ultimately affecting average query execution plan quality and associated workload performance.  This paper provides an overview of the primary changes made to the cardinality estimator functionality by the Microsoft query processor team, covering how to enable and disable the new cardinality estimator behavior, and showing how to troubleshoot plan-quality regressions if and when they occur.

Authors: Joseph Sack (

Contributers: Yi Fang (Microsoft), Vassilis Papadimos (Microsoft)

Technical Reviewers: Barbara Kess (Microsoft), Jack Li (Microsoft), Jimmy May (Microsoft), Sanjay Mishra (Microsoft), Shep Sheppard (Microsoft), Mike Weiner (Microsoft), Paul White (SQL Kiwi Limited)



The SQL Server Query Optimizer’s purpose is to find an efficient physical execution plan that fulfills a query request. It attempts this by assigning estimated costs to various query execution plan alternatives and then choosing the plan alternative with the lowest estimated cost. One key factor for determining operator cost is the estimation of rows that will be processed for each operator within a query execution plan. This row estimation process is commonly referred to as cardinality estimation. SQL Server 2014 marks the first, significant redesign of the SQL Server Query Optimizer cardinality estimation component since version SQL Server 7.0.

The SQL Server query optimization process seeks the most efficient processing strategy for executing queries across a wide variety of workloads. Achieving predictable query performance across online transaction processing (OLTP), relational data warehousing, and hybrid database schemas is inherently difficult. While many workloads will benefit from the new cardinality estimator changes, in some cases, workload performance may degrade without a specific tuning effort.

In this paper, we will discuss the fundamentals of the SQL Server 2014 cardinality estimator changes. We will provide details on activating and deactivating the new cardinality estimator. We will also provide troubleshooting guidance for scenarios where query performance degrades as a direct result of cardinality estimate issues.



SQL Server 2014 marks the first significant redesign of the SQL Server Query Optimizer cardinality estimation process since version 7.0. Use of the new CE can result in an overall improvement in average query performance for a wide range of application workloads. The new CE also provides diagnostic output for use in troubleshooting cardinality estimate issues. As described in this paper, some workloads may encounter degraded performance with the new CE. We recommend that you thoroughly test existing applications before migrating. When using the new CE, users can leverage trace flags to use the legacy model for queries that regress. This allows you to still benefit from queries that improved under the new model.

I've been hearing a number of good things about SQL Server 2014's new Cardinality Estimator, for example, The SQL 2014 Cardinality Estimator Eats Bad TSQL for Breakfast. If you've been hearing the same thing, or are just interested, you should check out this 43 page doc.

Wednesday, April 16, 2014

On SQL Server 2005, 2008, 2008 R2, 2012? Here's what you get when you jump to SQL Server 2014

A Story of BI, BIG Data and SQL Server in Canada - SQL Server 2014. Benefits of upgrading from SQL Server 2005, 2008, 2008R2 and 2012.


Are you running a previous version of SQL Server and want to know what new SQL Server capabilities you will be able to use by upgrading to the latest version?

Please find below the list of new features introduced since SQL Server 2005, 2008, 2008R2 and SQL Server 2012 (it will be a very long blog post!).



I thought this a great roll-up of features added to SQL Server in the last decade. Prefect if you're a version or two (or three) behind.

Tuesday, April 08, 2014

Providing your users some DBaaS (that's Database as a Service)

Microsoft Downloads - Database as a Service Reference Architecture Guide: SQL Server 2014

Explains how to build an infrastructure for hosting Microsoft SQL Server Database as a Service by using the features of SQL Server 2014 and Hyper-V virtual machines with Microsoft System Center 2012.

Version: April 2014

Date Published: 4/7/2014

Database Hosting Reference Architecture Guide_SQL_2014.docx, 302 KB

This guide explains how to build an infrastructure for hosting Microsoft SQL Server Database as a Service (DBaaS). By using the features of SQL Server 2014 and Hyper-V virtual machines with Microsoft System Center 2012, a hosting service provider can start with very small tenant databases and scale to meet the needs of the largest and busiest SQL Server applications. This reference architecture includes information about hardware, software, system design, and component configuration.





1 Overview

This guide to building the infrastructure for hosting Microsoft® SQL Server® Database as a Service (DBaaS) is not limited to a particular type of hardware. By using the features of SQL Server 2014 and Hyper-V® virtual machines with Microsoft System Center 2012, a hosting service provider can start with very small tenant databases and scale out or scale up to meet the needs of the largest and busiest SQL Server applications. This reference architecture includes hardware, software, system design, and component configuration.

2 Hosted Services

Database as a Service, for the purposes of this reference architecture, is a multitenant offering with isolation at the SQL Server database level. Many tenants can share an instance of SQL Server 2014 Enterprise Edition, each tenant with its own database. SQL Servers are hosted on Hyper-V virtual machines running Windows Server® 2012 R2 or using Windows® Core services. Hyper-V virtual machines are managed and monitored by System Center 2012 R2 Virtual Machine Manager and Operations Manager.

A hosted service provider (HSP), the intended audience for this guide, may offer a single server or many hundreds or thousands of servers. Servers may be in the same data center or distributed across data centers for load balancing and disaster recovery.

As an HSP, you make individual SQL Server databases available to the tenant with an agreed-upon maximum size and an agreed-upon amount of resources available. You are responsible for maintaining the SQL Server instance, the virtual host, and the physical host compute, network, and storage infrastructure.

You can secure a single instance of SQL Server easily. When you use the Partially Contained Database and Contained Users features of SQL Server 2014, a database can be made into a secure environment. Users cannot access other databases or the metadata about tenant databases.

You can also use the SQL Server 2014 Enterprise Resource Governor to prevent a single user or a single tenant from using too much of the available resources, and resource usage can be balanced among tenants.

In a SQL Server 2014 DBaaS environment, each tenant is responsible for the data in his SQL Server database. Tenants create the database architecture consisting of objects that store data and application code to maintain and search data and return results to clients. Within the database, a tenant database administrator (DBA) can set permissions enabling subsets of tenant users and groups to carry out these tasks.

The architecture of the database and how well the tenant optimizes code that performs searches will have a direct impact on performance and resource usage. You may wish to offer services to help tenants to optimize design and code to improve response times and minimize resource usage.

As the HSP, you are responsible for Windows and SQL Server maintenance and your standard agreement to provide hosted SQL Server services should define your maintenance windows. You may patch SQL Server and Windows on the virtual and physical hosts, migrate the database to a new virtual machine host when resource requirements require it, and migrate the virtual machine to a new physical host during routine maintenance.

You may also perform certain database-level services on behalf of the tenant, such as running scheduled jobs and backups. Scheduled jobs can run common maintenance tasks like Update Statistics or large scale data modification and aggregation, such as month-end processing. You may also make tools, such as an API or a self-service control panel, available to the tenant to manage such jobs while restricting the tenant’s access to only the data she needs.

You can automate provisioning services by using management APIs and offer features like self-service provisioning to your customers. Such services reduce operational expenses for both you and your tenants.

You can monitor usage at a very detailed level with tools like Microsoft System Center Operations Manager and the SQL Server Management Pack. Tenants can be billed according to very broad guidelines or for only what they use.

Customers will see lower capital expenditures and total cost of ownership when they consolidate database servers, reduce the proliferation of on-premises applications, and share the cost of administrative expertise with other tenants. Tenants can take advantage of advanced solutions without having to buy and administer an entire enterprise solution.

By leasing SQL Server DBaaS, the tenant can pay for only those resources required for an application with the option to scale up in the future. There is no need to over-provision for what might or might not happen in the future, because when more capacity is needed, the hardware will be available.


While targeted at hosted service provider, I still think this has value for large organizations or those dealing with SQL Server Sprawl... SQL Server Enterprise isn't cheap, so ensuring that you're using it to its maximum is important for everyone. IT groups like to say they are "service providers." Doing something like this makes that a real statement, without them giving away the keys to the store...

Monday, April 07, 2014

Free eBook of the Day: "Introducing Microsoft SQL Server 2014 - Technical Overview" [Keyword: "Overview"]

Microsoft Press - Free ebook: Introducing Microsoft SQL Server 2014


We’re pleased to announce our next free ebook – Introducing Microsoft SQL Server 2014 – by Ross Mistry and Stacia Misner.


Microsoft SQL Server 2014 is the next generation of Microsoft’s information platform, with new features that deliver faster performance, expand capabilities in the cloud, and provide powerful business insights. In this book, we explain how SQL Server 2014 incorporates in-memory technology to boost performance in online transactional processing (OLTP) and data-warehouse solutions. We also describe how it eases the transition from on-premises solutions to the cloud with added support for hybrid environments. SQL Server 2014 continues to include components that support analysis, although no major new features for business intelligence were included in this release. However, several advances of note have been made in related technologies such as Microsoft Excel 2013, Power BI for Office 365, HDInsight, and PolyBase, and we describe these advances in this book as well.

Who should read this book?

This book is for anyone who has an interest in SQL Server 2014 and wants to understand its capabilities. Many new improvements have been made to SQL Server 2014, but in a book of this size we cannot cover every improvement in its entirety—or cover every feature that distinguishes SQL Server from other databases or SQL Server 2014 from previous versions. Consequently, we assume that you have some familiarity with SQL Server already. You might be a database administrator (DBA), an application developer, a business intelligence solution architect, a power user, or a technical decision maker. Regardless of your role, we hope that you can use this book to discover the features in SQL Server 2014 that are most beneficial to you. [GD: Post Leached in Full]

You heard that SQL Server 2014 became available for download last week, April 1st (no fooling), right? And now that it's available, you're ready to really invest a little time and learn more about it? This free ebook is for you then...

Get it from the Microsoft Virtual Academy eBook Shelf


I'm cheating a little, but here are the direct download links...

Here are some snaps from the PDF;



Who should read this book?
This book is for anyone who has an interest in SQL Server 2014 and wants to understand its capabilities. Many new improvements have been made to SQL Server 2014, but in a book of this size we cannot cover every improvement in its entirety—or cover every feature that distinguishes SQL Server from other databases or SQL Server 2014 from previous versions. Consequently, we assume that you have some familiarity with SQL Server already. You might be a database administrator (DBA), an application developer, a business intelligence solution architect, a power user, or a technical decision maker. Regardless of your role, we hope that you can use this book to discover the features in SQL Server 2014 that are most beneficial to you.

We assume that you have at least a minimal understanding of SQL Server from both a database administrator’s perspective and a business-intelligence perspective, including a general understanding of Microsoft Excel, which is often used with SQL Server. In addition, having a basic understanding of Windows Azure is helpful for getting the most from the topics associated with private, public, and hybrid-cloud solutions.

Who should not read this book
As mentioned earlier, the purpose of this book is to provide readers with a high-level preview of the capabilities and features of SQL Server 2014. This book is not intended to be a step-by-step, comprehensive guide.

Related Past Post XRef:
Six videos toward helping you upgrade your inner DBA to SQL Server 2014'ness
SQL Server 2014 RTM's, available for download on the worse possible day (no fooling)?
"SQL Server 2014 Developer Training Kit"
"Microsoft SQL Server 2014 CTP1 Product Guide" - One new SQL Server, 12 PDF's...
"How the heck is Hekaton part of SQL Server 2014..." More on SQL Server 2014's In-Memory Tech
Can you Hekaton? Intro to the SQL Server 2014 Analysis, Migration and Reporting tool...
Playing with SQL Server 2014 (and VS2013) the Azure VM way
Dev:"But it's new and shinny! Let's upgrade!" DBA:"Over my..." - Preparing to upgrade your SQL Server
TechEd NA 2013 Day 1 Announcement Round-up - VS 2013, TFS 2013, InRelease, SQL 2014, Server 2012 R2, BizTalk Services, Azure-in-a-box and even more Azure...

Friday, March 28, 2014

Search SQL scripts simply with the SQL Scripts SSMS plugin

simple talk - Tony Davis - D.R.Y. with SQL Scripts

Developers strive to write well-tested, reusable code with well-defined interfaces so that when they need to update the functionality, they need do so in one place only. It is the principle of ‘Don’t Repeat Yourself’ (D.R.Y.).

However, it is common for developers to be poor at applying D.R.Y. to their own past work. When it comes time to implement some complex new routine, a faint bell rings in their mind…didn’t I write something similar for that CRM project? What year was that…? If a brute-force search through their chaotic script archive doesn’t unearth it, within a few minutes, they then roll up sleeves, crack knuckles and set about writing it again from scratch, convinced they will do a better job of it this time, anyway: And, after all, it’s fun.

However, what if you really don’t have time to write the code from scratch, or need some pointers to get started? You might trawl a few of your favorite blogs, or find something on Stack Overflow. After all, many developers and DBAs blog all sorts of snippets and scripts, suggesting hopefully that they may be useful to others, but admitting that their main motivation is to know where to come the next time they need it themselves!

Another option, when in need of SQL code, is to search a public script archive such as the one on This always used to be a tricky operation. If, for example, we wanted a string-splitting function, we’d need to type into Google something like ‘ list split‘. Now it is so much easier, with the addition of a small SSMS plug-in called SQL Scripts.



In addition, you can search the whole archive directly from within SSMS, as well as add scripts to your briefcase and to the archive as a whole.


If you’re an occasional or frequent miner of the SSC script archive, we’d love you to try out SQL Scripts and let us know what you think. How much might it help you reuse the code of others, as well as find and reuse your own?

We just don't see SSMS plugins often enough. This one looks interesting and pretty useful for both the DBA and SQL Dev...

Thursday, March 20, 2014

Six videos toward helping you upgrade your inner DBA to SQL Server 2014'ness

Channel 9 - Updating your Database Management Skills to SQL Server 2014

Do you manage online transaction processing (OLTP) database workloads? Want to learn about the new and enhanced capabilities in SQL Server 2014 to help you do so? Watch this course to learn about enhancements and capabilities new to SQL Server and the Microsoft data platform since the release of SQL Server 2008.


Since the RTM bits are not yet available you can get a jump on your friends and co-workers and start upgrading your DBA brain cells to support (or sell to your bosses) SQL Server 2014. As I've said, this is a pretty awesome release and there's a great deal to grok. The sooner you start...


Related Past Post XRef:
SQL Server 2014 RTM's, available for download on the worse possible day (no fooling)?
"SQL Server 2014 Developer Training Kit"
"Microsoft SQL Server 2014 CTP1 Product Guide" - One new SQL Server, 12 PDF's...
"How the heck is Hekaton part of SQL Server 2014..." More on SQL Server 2014's In-Memory Tech
Can you Hekaton? Intro to the SQL Server 2014 Analysis, Migration and Reporting tool...
Playing with SQL Server 2014 (and VS2013) the Azure VM way
Dev:"But it's new and shinny! Let's upgrade!" DBA:"Over my..." - Preparing to upgrade your SQL Server
TechEd NA 2013 Day 1 Announcement Round-up - VS 2013, TFS 2013, InRelease, SQL 2014, Server 2012 R2, BizTalk Services, Azure-in-a-box and even more Azure...

Wednesday, March 19, 2014

SQL Server 2014 RTM's, available for download on the worse possible day (no fooling)?

The Official Microsoft Blog - SQL Server 2014 released to manufacturers, will be generally available April 1

Today I am very happy to announce SQL Server 2014 has been released to manufacturing and will be generally available on April 1.

SQL Server 2014 is the culmination of thousands of hours of hard work from Microsoft engineers and thousands of hours of testing and input from our preview customers. The result is an important component of Microsoft’s overall cloud-first data platform. The platform delivers breakthrough performance, accelerated insights through tools everyone uses and the ability to scale globally on-premises and in the cloud – letting our customers get the most from their data.

This release of SQL Server is significant in that, in addition to delivering key hybrid scenarios, it rounds out our journey to embrace in-memory technology. Several years back, we began exploring the changing hardware landscape – memory being one of the key areas of focus. Today, our in-memory technology spans the core workloads in the data platform: business intelligence as part of Analysis Services, Excel and Power BI for Office 365; complex event processing with StreamInsight; in-memory columnstore in SQL Server and our data warehousing product; and now with SQL Server 2014 – in-memory transaction processing.


Windows Azure and SQL Server provide a continuum of capability and flexibility in deployment options for our customers – on-premises and in the cloud. Windows Azure also supports an important data workload – Windows Azure HDInsight, our Apache Hadoop-based solution in the cloud. Today, we’re announcing the general availability of Hadoop 2.2 support in Windows Azure HDInsight, which has been updated to take full advantage of the latest Hadoop 2.2 platform, including support for YARN and Stinger Phase 2.

There has never been a more exciting time in the database and broader data platform industry. In an era where nearly everything will become digitized, today we’re delivering the data platform that will help our customers digitize their business. Customers can download SQL Server 2014 on April 1 or register today to be notified once the release is available. Customers can also learn more about the release and our data platform strategy at our “Accelerate your Insights” event on April 15, where I will be joined by Microsoft CEO Satya Nadella and COO Kevin Turner. In addition, some of our customers deriving value from our data platform today will also be in attendance. I encourage you save the date and tune in for the event.


<rant>April Fools Day? Really? You couldn't release it a day earlier or later? Think what a Build bullet point this would have made during the Day One Keynote on the 2nd? But noooo... This will now and forever be known as the SQL Server April Fools edition.

Yeah, yeah, I know we're all tired of the April Fools crap, but ... really? really?

Don't get me wrong, I think this is one of the more existing versions of SQL Server in a while and there's some awesome tech in it. Congrats to the team, just do me a favor and smack the marketing wonk who pick this date...</rant>

Friday, March 14, 2014

ERwin gets a new home at Embarcadero

PCWorld - Embarcadero moves into data modeling with CA ERwin buy

Embarcadero Technologies is acquiring the ERwin data modeling software and associated personnel from CA Technologies, giving the vendor of software development tools an instant and formable presence in the growing field of data architecture.

The purchase makes data architecture the largest part of Embarcadero’s business, Embarcadero CEO Wayne Williams said in a statement.


To date, Embarcadero has been most well known for its software and database development tools. The company’s first product, released in 1993, was a cross-platform SQL IDE (integrated development environment) called Rapid SQL. It also maintains the Delphi language and toolbase, as well as C++Builder IDE, both acquired from Borland Software in 2007




I've used ERwin in a number of past lives and found it pretty awesome. But the price was always a pain, and when it went to CA <snarky> aka the place where products go to die </snarky> well...

Embarcadero <snarky> aka the Island of Orphan Products, </snarky> seems to treat the new products it acquires with much more respect. Here's to hoping that they give ERwin some TLC. I'm looking forward to seeing a new and improved ERwin (that hopefully I don't have to take a 2nd out on the house to buy... ;)

Thursday, February 27, 2014

Making Relativity relatively faster... Partition it baby, (sometimes)

Brent Ozar - How to Use Partitioning to Make kCura Relativity Faster

kCura Relativity is an e-discovery program used by law firms to find evidence quickly. I’ve blogged about performance tuning Relativity, and today I’m going to go a little deeper to explain why DBAs have to be aware of Relativity database contents.

In Relativity, every workspace (case) lives in its own SQL Server database. That one database houses:

  • Document metadata – where the document was found, what type of document it is
  • Extracted text from each document – the content of emails, spreadsheets, files
  • Document tagging and highlighting – things the lawyers discovered about the documents and noted for later review
  • Workspace configuration – permissions data about who’s allowed to see what documents
  • Auditing trails – who’s searched for what terms, what documents they’ve looked at, and what changes they made

For performance tuners like me, that last one is kinda interesting. I totally understand that we have to capture every activity in Relativity and log it to a table, but log-sourced data has different performance and recoverability requirements than other e-discovery data.


However, I don’t recommend doing this by default across all your databases. This technique is going to instantly double the number of databases you have and make your management much more complex. However, I do recommend reviewing your largest workspaces to see if AuditRecord is consuming half or more of the database space. If so, consider partitioning their AuditRecord tables to get faster backups, database maintenance jobs, and restores.

At the risk of sounding like a fanboy, this is one of the reasons I love working with the kCura folks. They really care about database performance, they take suggestions like this, and they implement it in a way that makes a real difference for customers.

This is also why database administrators need to:

  1. Understand the real business purpose of the biggest tables in their databases
  2. Build working, productive relationships with their software vendors
  3. Come up with creative approaches to ease SQL Server pains
  4. Help the vendors implement these approaches in software


If you're a Relativity shop, Brent's one of those "must go to dba guys," which this post makes very apparent...


Related Past Post XRef:
Making SQL Server a happy kCura Relativity camper (and your users too)

sp_AskBrent - Your new, "OMG, my SQL Server is sooo slow" free uber SP from Brent Ozar
Two SQL Server Resources that you might want to take another look at...

Free Training SQL Server Training DVD’s (or online) from Quest (reg-ware) - 12 Sessions, Two DVD’s, Zero cost…

"How to Develop Your DBA Career" Free eBook (and posters and whitepapers and more [oh my])