There are lots of cases you may want to comment/uncomment multiple lines in DAX scripts. You can save lots of development time if you know the shortcuts.
Here you go:
Continue reading “Quick Tips: Comment/Uncomment Multiple Lines in DAX Scripts”
A while ago I wrote a blog post about Azure SQL Data Warehouse and Power BI which I explained how to install a new instance of Azure SQL Data Warehouse and how to visualise your Azure SQL DW data in Power BI. In this post I explain how to query an Azure SQL DW in SSMS and Visual Studio.
Prior the latest release of SQL Server Management Studio (SSMS) 2016, the only available tool for querying an Azure SQL Data Warehouse was SQL Server Data Tools (SSDT) for Visual Studio 2013 or 2015. Here is how you can use SSDT 2015 to query an Azure SQL Data Warehouse:
Continue reading “Query Azure SQL Data Warehouse in SSMS and SSDT”
Today I came across a cube processing performance issue with one of our clients. So I started a step-by-step troubleshooting including optimising named queries. In some cases the named queries were actually querying some SQL views from the source data warehouse.
After all, I created about 35 new indexes and I needed to justify that all of those indexes are really used. As I processed the faulty cube several times during my step-by-step troubleshooting process it seemed all of those indexes were used.
But, I knew that I created some indexes that covered by some of the new ones and those indexes won’t be used.
I needed to rebuild all the indexes, however, rebuilding all of those indexes from SSMS UI would be such a pain. So I needed to do a batch index rebuild.
So I googled and I’ve found some scripts which actually are doing the job, but, all of them were using cursors. Sadly, I hate cursors so they are the last item in my book. Indeed, I’ll never use cursors until it’s absolutely necessary and there is no other better choices.
Therefore, I decided to do it in my way and I wrote the following script. I thought I’d be happy to share it with you guys as it might help some of you as well.
declare @ix varchar(max), @tbl varchar(max), @counter int, @CustomIx Varchar(max)
declare @table table (id int, tbl varchar(max), ix varchar(max))
set @CustomIx = ‘YOUR_INDEX_NAME_STARS_WITH’ –Custom index name will be like MY_IX_***
insert into @table (id, tbl, ix)
SELECT ROW_NUMBER() over (order by ix.[NAME]) id
, OBJECT_NAME(ixstat.[OBJECT_ID]) AS [OBJECT NAME]
, ix.[NAME] AS [INDEX NAME]
FROM SYS.DM_DB_INDEX_USAGE_STATS AS ixstat
INNER JOIN SYS.INDEXES AS ix
ON ix.[OBJECT_ID] = ixstat.[OBJECT_ID]
AND ix.INDEX_ID = ixstat.INDEX_ID
WHERE OBJECTPROPERTY(ixstat.[OBJECT_ID],‘IsUserTable’) = 1
and ix.[NAME] like @CustomIx+‘%’
set @counter= (select max(id) from @table)
while @counter >=1
begin
set @ix = (select ix from @table where id = @counter)
set @tbl = (select tbl from @table where id = @counter)
exec(‘ALTER INDEX ‘+@ix+‘ ON [dbo].[‘+@tbl+‘] REBUILD PARTITION = ALL ‘)
print @tbl + ‘.’ + @ix + ‘ Rebuild successful’
set @counter-=1
end
The SQL Server 11.0 release (code named “Denali”) introduces a new data warehouse query acceleration feature based on a new type of index called the columnstore. Columnstore indexing is officially announced in SQL Server 2012. It is working based on xVelocity memory optimised technology and it improves data warehouse query performance significantly. Due to the fact that data warehousing, decision support systems and business intelligence applications are growing very quickly, we need to be able to read and process very large data sets quickly and accurately into useful information and knowledge. Columnstore index technology is especially appropriate for data warehousing data sets. It improves the common data warehousing queries’ performance significantly.
Continue reading “Digging into SQL Server 2012 columnstore index”
