Stairway to Columnstore Indexes Level 2: Columnstore Storage (SQLServerCentral 4/21/2017)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Stairway to Columnstore Indexes Level 2: Columnstore Storage]( - [SQL Clone Trivia Quiz - win a $10 gift card every weekday in April]( - [Extending DevOps practices to SQL Server databases]( - [Integrity–A Key Ingredient to Leadership]( (From the SQLServerCentral Blogs) - [When should I add an index?]( (From the SQLServerCentral Blogs) Featured Script - [Getting resource name from blocked process report]( The Voice of the DBA How do you come up with creative ideas? Most of us are in the business of coming up with or developing solutions to problems. Some of these problems are things we have run into before. In those situations, the solution is perhaps well known or has solved the problem before. Still, many problems we face are new or are different enough that we cannot just use a solution we have used previously because it just doesn’t quite work. In these cases, it takes a creative mind to come up with new ideas that will become the solution to the problem. So how do you go about coming up with creative ideas? There are a couple of ways I have seen it done in the past. A great place to start is, getting together with co-workers and doing a brain storming session. There are a couple of benefits here. One, you are working with other people’s creativity, not just your own. Two, you can bounce your crazy ideas off some other people and see if any of them stick. It seems that often, when working together as a group, you can come up with some creative ideas that end up being good solutions. Another way you can come up with creative ideas is to give yourself freedom to think outside the box. It is difficult to describe, but so often we stifle our own ideas and creative thinking. We dismiss them as too farfetched. Or we only have a narrow perspective on how a problem can be solved. When we try to let go of the limitations we put on our thinking, we are freed up to have some creative thoughts. Now, don’t get me wrong, a lot of these thoughts or ideas will be pure rubbish. That is okay, because even ideas that will never work in a million years, might lead to another thought or idea that will work. When you don’t allow yourself the freedom to dream up a creative idea, you miss the opportunity to walk down a new path that may lead you to your final solution. Finally, it takes time. Nothing seems to hinder the creative process more than time constraints. Most of us have deadlines, projects that need to be done yesterday. The issue is, if you are only allowing yourself five minutes or a few hours to decide on a solution, you probably won’t be coming up with something all that creative. Give it a day, or two. You might be surprised as your subconscious mind continues to think about it and all of a sudden you come up with a great idea. In my experience, if you brainstorm with others, give yourself the freedom to think some new unshackled thoughts and give yourself enough time to mull it over, you can come up with some creative solutions. You never know with enough time, what path your creative thinking will lead you down. Hopefully, to a new solution to your problem. Let us know how you go about coming up with creative solutions to your problems? Ben Kubicek from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( --------------------------------------------------------------- The Voice of the DBA Podcasts - I'm taking a short break from podcasts, and my apologies to all. I've been ill and my voice has been a bit sore, so I'm trying to limit the recording I do for a few weeks. Hopefully I'll be back soon. ADVERTISEMENT [SQL Prompt]( Write, format, and refactor SQL effortlessly with SQL Prompt Writing SQL is 50% faster with SQL Prompt. Your SQL code can be formatted just the way you like it, you can create and share snippets with your team, and with tab coloring you’ll never deploy to the wrong environment again. [Download your free trial.]( [SQL Source Control]( How to track every change to your SQL Server database See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. [Learn more.]( Featured Contents  [] [Stairway to Columnstore Indexes Level 2: Columnstore Storage]( Hugo Kornelis from [SQLServerCentral.com]() To fully appreciate just how different columnstore indexes are, and why work so well in reporting and online analytical processing (OLAP) workloads, but not for online transaction processing (OLTP), we must first look at the traditional “rowstore” indexes.[More »](Series/124326/) ---------------------------------------------------------------  [] [SQL Clone Trivia Quiz - win a $10 gift card every weekday in April]( To celebrate the recent launch of their new database provision tool, Redgate are giving you the chance to win a $10 Amazon or Starbucks gift card every weekday this month. To enter the prize draw, just answer the daily SQL Clone trivia question on the right-hand side of the homepage.[More »]( ---------------------------------------------------------------  [] [Extending DevOps practices to SQL Server databases]( In this free demo webinar, Grant Fritchey and Arneh Eskandari will show how Redgate tools enable you to push and pull database changes in Git, then set up an automated database build and deployment process using TeamCity and Octopus Deploy. [More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Integrity–A Key Ingredient to Leadership]( CYates from [SQLServerCentral Blogs]( When I have the opportunities to speak, whether local or elsewhere, on leadership I am often asked what are some...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [When should I add an index?]( Kenneth Fisher from [SQLServerCentral Blogs]( We all know indexes are good and I’m hoping everyone knows you can have too many indexes. That means we...[More »]( Question of the Day Today's Question (by Jeff Atherton): Which one of the following has the correct syntax for the Row Number function and will not generate an error? Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: T-SQL. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT [Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud]( With Microsoft SQL Server 2016, a variety of new features and enhancements to the data platform deliver breakthrough performance, advanced security, and richer, integrated reporting and analytics capabilities. In this ebook, we introduce new security features: Always Encrypted, Row-Level Security, and dynamic data masking; discuss enhancements that enable you to better manage performance and storage: TemDB configuration, query store, and Stretch Database [Get your copy from Amazon today](. Yesterday's Question of the Day Yesterday's Question (by Jagadish Kumar Punnapu): On a table having 10 million rows, 4 non-clustered indexes and 1 clustered index have to be created. In this scenario, which order of creation is recommended? Answer: Create the clustered index first and then create the 4 non-clustered indexes Explanation: If a clustered index is created on a heap with several existing non-clustered indexes, all the non-clustered indexes must be rebuilt so that they contain the clustering key value instead of the row identifier (RID). [click here]( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Featured Script [Getting resource name from blocked process report]( Darko Martinovic from [SQLServerCentral.com]() Added on 10.4.2017. - .Net source code is available on [click here]( ------------------------------------------------------------------------ When you analyzing blocking problems, first choice is that you look what sys.dm_os_waiting_tasks will display. It means blocking occurs right now, and you know blocker and blocking spid's. In that case it is easy to determine resource which is subject of blocking. SELECT DTL.[resource_type] AS [resource type] ,CASE WHEN DTL.[resource_type] IN ('DATABASE', 'FILE', 'METADATA') THEN DTL.[resource_type] WHEN DTL.[resource_type] = 'OBJECT' THEN OBJECT_NAME(DTL.resource_associated_entity_id) WHEN DTL.[resource_type] IN ('KEY', 'PAGE', 'RID') THEN (SELECT (CASE WHEN s.name IS NOT NULL THEN s.name + '.' ELSE '' END) + OBJECT_NAME(p.[object_id]) FROM sys.partitions p INNER JOIN sys.objects o ON o.object_id = p.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE p.[hobt_id] = DTL.[resource_associated_entity_id]) ELSE 'Unidentified' END AS [Parent Object] ,DTL.[request_mode] AS [Lock Type] ,DTL.[request_status] AS [Request Status] FROM sys.dm_os_waiting_tasks wt INNER JOIN sys.dm_tran_locks DTL ON DTL.lock_owner_address = WT.resource_address WHERE wt.blocking_session_id IS NOT NULL; But, if you analyzing blocking problems off-line, using trace or extended events, you have to count only on information found in blocked process report. The resource name is most important information in the blocked process report. Unfortunately this information is only available as encoded. The purpose of this script is to decode this information. I PART using T-SQL --@waitResource is written in blocked process report as a attribute of blocked-process/process element DECLARE @waitResource as nvarchar(128) = 'KEY: 41:72057594544062464 (b14200e25741)' -- replace this string with your wait resource string SELECT DBO.[GetResourceName]( @waitResource, default); -- Will return t-sql to evaluate SELECT sc.name + '.' + so.name FROM MYDB_NAME.sys.partitions AS p JOIN MYDB_NAME.sys.objects AS so ON p.object_id = so.object_id JOIN MYDB_NAME.sys.indexes AS si ON p.index_id = si.index_id AND p.object_id = si.object_id JOIN MYDB_NAME.sys.schemas AS sc ON so.schema_id = sc.schema_id WHERE p.hobt_id = 72057594098286592 --2. Example. Evaluation DECLARE @waitResource as nvarchar(128) DECLARE @sql as nvarchar(max) DECLARE @resCon as nvarchar(256) SET @waitResource = 'KEY: 10:72057594098286592 (b14200e25741)' --second parametar name should be the same as --the name of first parametar in sp_executesql SET @sql = DBO.GetResourceName(@waitResource, '@resourceName') EXEC sp_executesql @sql , N'@resourceName nvarchar(max) output' , @resCon OUTPUT; SELECT @resCon -- Will return resource name -- II . PART using SQLCLR -- Although it seems that CLR is better solution, there is a limitation when processing PAGE information. -- Clr function does not allwed using dbcc or create temp table. Workaround is to use CLR stored procedure to -- determine resource name SELECT [dbo].[GetResourceNameClr]('PAGE: 25:1:6077390') --KEY: 25:72057600909443072 (8b56a42c5bc8) SELECT [dbo].[GetResourceNameClr]('KEY: 25:72057600909443072 (8b56a42c5bc8)') --OBJECT: 25:1239779574:0 SELECT [dbo].[GetResourceNameClr]('OBJECT: 25:1239779574:0') -- 'PAGE: 25:1:6077390 EXEC [dbo].[GetResourceNameFromPageClr] 25 ,1 ,6077390 [More »]( Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [Ssrs 2014 migration to 2016.]( - Hi, I'm trying to migrate a 2014 Ssrs solution to Ssrs 2016, and had got to the point I felt we... [A little tale and a seven questions about SQL partition]( - The tale Once up a time a small software shop had a application used Firebird DBMS. The small shop managed to do... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [Displaying correct $Amount]( - Hey there folks. Was wondering if I could get a little help on this one. Based on the last query,... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [Help SQL query, count and group by day on multiple fields]( - Hi All, I have a table with records, which are determined by transaction_type. I would therefore like to have a count of... [Estimated v Actual rows in query plan]( - Hi Guys, This is general question initially without any DDL or query plan (my company won't allow it), therefore I am... [Help with SQL Loop Query]( - Hi Guys, I am using below While Loop Syntax but for some reason, I can't update the records or it... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [Record Insert fails in database table]( - Hi Guys, I have an issue where an insert of a record in a table fails due to the following error: 'Numeric... [SSRS Report Schedule - getting info from the DB - What do these numbers mean ? -]( - Please see the attached output....  Can anyone re-write this query ( May be using  some CASE statements withing SELECT )  and make... [Transactional replication keeps applying snapshot]( - Hi, I created a new snapshot and applied it. It ran the scripts to truncate all the tables and drop the... [SQL help - Need to know the first date of previous month and last date of previous month...]( - Should be an easy one right ? I like it in YYYYMMDD format [** Urgent !!! ** Connection Timeout Expired !]( - I have this error randomly while connecting to Sql Server instance : Connection Timeout Expired. The timeout period elapsed while attempting... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Query Help]( - I have several tables that I am working with trying to get some data: SALES_ORDERS S -  header data SALES_ORDER_LINES SL -  Order#, Order_line#,... [how to create random values for 1 million records or 10,000 records]( - CREATE TABLE dbo.RandomData (  RowId INT IDENTITY(1,1) NOT NULL,  firstname VARCHAR(10), lastname varchar(10),city varchar(10),state varchar(10),country varchar(30),  age NUMERIC(5),salary numeric(10,2),vacationhrs numeric(5),phoneno numeric(7)... [Do it all in C# instead?]( - Possibly a bit of a Friday afternoon question. I've been working on a product for about 10 years. The architecture consists of... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [how to update gender = M or F in a table that has gender values as null]( - Hi Any help on how to update gender = M or F in a table that has gender values as null need a... [Need to set library for CREATE VIEW in EXEC @SQL script]( - I have an EXEC @SQL script which creates a view. Reason for the EXEC @SQL script is that the library and... --------------------------------------------------------------- [Reporting Services]( : [Reporting Services]( [Open SSRS report in Excel vai hyperlink.]( - How to setup hyperlink to open report in Excel or in PDF from SSRS in SharePoint Integrated Mode with two parameter... --------------------------------------------------------------- [Programming]( : [Powershell]( [Issue with powershell command for service down]( - Hi All, I have migrated (sort of) from the SQL areas :). So I am trying to get an email sent when... --------------------------------------------------------------- [SQL Server 2005]( : [Administering]( [Deadlocks on Clustered Index]( - hi all i have a very simple control table with 1 record, this record manages number increments for various ranges such... [SQL Server job to delete old backups]( - Hi, I need to create a job that cleans backups and log backups that are older than a month. I do... This email has been sent to {EMAIL}. To be removed from this list, please click [here.]( If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.--------------------------------------------------------------- This newsletter was sent to you because you signed up at [SQLServerCentral.com](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com