Category Archives: SQL Server 2012

November 2014 Cumulative Updates for SQL Server 2012

Microsoft has released Cumulative Updates for SQL Server 2012 Service Pack 1 and Service Pack 2. Note that RTM is no longer supported and you should be moving to the SP2 branch soon. SQL Server 2012 Service Pack 1 Cumulative Update #13 KB #3002044 Build #11.0.3482 13 fixes (10 public) Relevant for Service Pack 1 only (11.0.3000 -> 11.0.3481) SQL Server 2012 Service Pack 2 Cumulative Update #3 KB #3002049 Build #11.0.5556 40 fixes (33 public) Relevant for Service Pack 2 only (11.0.5058 -> 11.0.5555)…(read more)

SQL Server Diagnostic Information Queries for September 2014

I revised a number of the queries this month in all five versions of the script. I have also added several new queries to the SQL Server 2012 and SQL Server 2014 versions of the script. Here are the current query counts for each version:

SQL Server 2014         72 Queries

SQL Server 2012         69 Queries

SQL Server 2008 R2    65 Queries

SQL Server 2008         59 Queries

SQL Server 2005         51 Queries

Rather than having a separate blog post for each version, I have just put the links for all five major versions in this single post. There are two separate links for each version. The first one on the top left is the actual query script, and the one below on the right is the matching blank results spreadsheet.  

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results

The basic idea is that you should run each query in the set, one at a time (after reading the directions). It is not really a good idea to simply run the entire batch in one shot, especially the first time you run these queries on a particular server, since some of these queries can take some time to run, depending on your workload and hardware.

You need to click on the top left square of the results grid in SSMS to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet. There are also some comments on how to interpret the results after each query.

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database. Running the database-specific queries while being connected to the master database is a very common mistake that I see people making.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries.

It is also very important that you are running the correct version of the script that matches the major version of SQL Server that you are running. There is an initial query in each script that tries to confirm that you are using the correct version of the script for your version of SQL Server.

If you want to understand how to better run and interpret these queries, you should consider listening to my latest Pluralsight course, which is SQL Server 2014 DMV Diagnostic Queries – Part 1. This course is short and to the point (only 67 minutes), and I think you will enjoy it!

Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

Filed under: Diagnostic Queries, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 Tagged: DMV, DMV Queries

September 2014 Cumulative Updates for SQL Server 2012

The SQL Server team has released two cumulative updates for SQL Server 2012. SQL Server 2012 Service Pack 2 Cumulative Update #2 KB Article: KB #2983175 Build # is 11.0.5548 Currently there are 50 public fixes listed (52 total) Relevant for builds 11.0.5058 -> 11.0.5547. Do not attempt to install on SQL Server 2012 RTM (any build < 11.0.3000) or SP1 (any build < 11.0.5058), or any other major version. If you are on a different branch, see this blog post on blogs.sqlsentry.com for information…(read more)

SSIS Design Patterns Training in London 8-11 Sep!

A few seats remain for my course SQL Server Integration Services 2012 Design Patterns to be delivered in London 8-11 Sep 2014. Register today to learn more about: New features in SSIS 2012 and 2014 Advanced patterns for loading data warehouses Error handling The (new) Project Deployment Model Scripting in SSIS The (new) SSIS Catalog Designing custom SSIS tasks Executing, managing, monitoring, and administering SSIS in the enterprise Business Intelligence Markup Language (Biml) BimlScript ETL Instrumentation…(read more)

SQL Server 2012 Service Pack 2 Cumulative Update #1 is available!

The SQL Server team has released SQL Server 2012 SP2 Cumulative Update #1. This cumulative updates Service Pack 2 to include the fixes from SP1 CU#10 and a few from CU#11, including the fix for the online index rebuild corruption issue I discussed recently on SQLPerformance.com . It also marks the first time in the SQL Server 2012 timeframe that both cumulative update branches are on roughly the same schedule, which makes many of us happy I’m sure. 🙂 KB Article: KB #2976982 Build # is 11.0.5532…(read more)

SQL Server 2012 Service Pack 1 Cumulative Update #11 is available!

The SQL Server team has released SQL Server 2012 SP1 Cumulative Update #11. This cumulative update includes a fix for the online index rebuild corruption issue I discussed recently on SQLPerformance.com . KB Article: KB #2975396 Build # is 11.0.3449 Currently there are 32 public fixes listed (32 total) Relevant for builds 11.0.3000 -> 11.0.3448. Do not attempt to install on SQL Server 2012 RTM (any build < 11.0.3000) or any other major version. If you are on a different branch, see this blog…(read more)

SQL Server 2012 Service Pack 2 is available – but there’s a catch!

Service Pack 2 is available: http://www.microsoft.com/en-us/download/details.aspx?id=43340 The build number is 11.0.5058, and this includes fixes up to and including SQL Server 2012 SP1 CU #9. (The complete list of fixes is exhaustive, including all fixes from SP1 CU #1 -> #9, but the post-CU #9 fixes are listed here: http://support.microsoft.com/KB/2958429 However, if you may be affected by the regression bug I talked about earlier today , which could lead to data loss or corruption during online…(read more)

White Paper on Analysis Services Tabular Large-scale Solution #ssas #tabular

Since the first beta of Analysis Services 2012, I worked with many companies designing and implementing solutions based on Analysis Services Tabular. I am glad that Microsoft published a white paper about a case-study using one of these scenarios: An Analysis Services Case Study: Using Tabular Models in a Large-scale Commercial Solution. Alberto Ferrari is the author of the white paper and many people contributed to it. The final result is a very technical document based on a case study, which provides a level of detail that I don’t see often in other case studies (which are usually more marketing-oriented).

This white paper has the following structure:

  • Requirements (data model, capacity planning, client tool)
  • Options considered (SQL Server Columnstore Indexes, SSAS Multidimensional, SSAS Tabular)
  • Data Model optimizations (memory compression, query performance, scalability)
  • Partitioning and Processing strategy for near real-time latency
  • Hardware selection (NUMA analysis, Azure VM tests)
  • Scalability tests (estimation of maximum users per node)

If you are in charge of evaluating Tabular as analytical engine, or if you have to design your solution based on Tabular, this white paper is a must read. But if you just want to increase your knowledge of Analysis Services, you will find a lot of useful technical information. That said, my favorite quote of the document is the following one, funny but true:

[…] After several trials, the clear winner was a video gaming machine that one guy on the team used at home. That computer outperformed any available server, running twice as fast as the server-class machines we had in house.

At that point, it was clear that the criteria for choosing the server would have to be expanded a bit, simply because it would have been impossible to convince the boss to build a cluster of gaming machines and trust it to serve our customers.  But, honestly, if a business has the flexibility to buy gaming machines (assuming the machines can handle capacity) – do this.

Owen Graupman, inContact

I want to write a longer discussion about how companies are adopting Tabular in scenarios where it is the hidden engine of a more complex solution (and not the classical “BI system”), because it is more frequent than you might expect (and has several advantages over many alternative approaches).

Write DAX queries in Report Builder #ssrs #dax #ssas #tabular

If you use Report Builder with Reporting Services, you can use DAX queries even if the editor for Analysis Services provider does not support DAX syntax. In fact, the DMX editor that you can use in Visual Studio editor of Reporting Services (see a previous post on that), is not available in Report Builder. However, as Sagar Salvi commented in this Microsoft Connect entry, you can use the DAX query text in the query of a Dataset by using the OLE DB provider instead of the Analysis Services one. I think it’s a good idea to show the steps required.

First, create a Data Source using the OLE DB connection type, and provide the connection string the provider (Provider), the server name (Data Source) and the database name (Initial Catalog), such as:
Provider=MSOLAP;Data Source=SERVERNAME\TABULAR;Initial Catalog=AdventureWorks Tabular Model SQL 2012

image

Then, create a Dataset using the data source previously defined, select the Text query type, and write the DAX code in the Query pane:

image

You can also use the Query Designer window, that doesn’t provide any particular help in writing the DAX query, but at least can show a preview of the result of the query execution.

image

I hope DAX will get better editors in the future… in the meantime, remember you can use DAX Studio to write and test your DAX queries, and DAX Formatter to improve their readability!

If you want to learn the DAX Query Language, I suggest you watching my video Data Analysis Expressions as a Query Language on Project Botticelli!

SQL Server 2012 Service Pack 1 Cumulative Update #10 is available!

The SQL Server team has released SQL Server 2012 SP1 Cumulative Update #10. KB Article: KB #2954099 Build # is 11.0.3431 Currently there are 36 public fixes listed (38 total) Relevant for builds 11.0.3000 -> 11.0.3430. Do not attempt to install on SQL Server 2012 RTM (any build < 11.0.3000) or any other major version….(read more)