Category Archives: SQL Server Management Studio – SSMS

Query Azure SQL Data Warehouse in SSMS and SSDT

Azure SQL Data Warehouse in SSMS and SSTD

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.

Requirements

Querying Azure SQL Data Warehouse from 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:

  • Open SQL Server Data Tools 2015
  • Click “SQL Object Explorer” from View menu

SQL Server Object Explorer Visual Studio

  • Click “Add SQL Server”

Add Server to SQL Server Object Explorer Visual Studio

Connect to Azure SQL Data Warehouse in SQL Server Object Explorer Visual Studio

  • Enter “Server Name”
  • If you don’t recall server name then open a web browser and log into Azure portal
  • Click “SQL databases”
  • Click any desired Azure SQL Data Warehouse you created before. Make sure the database is “Online”

Azure SQL Data Warehouse in Azure Portal

Continue reading Query Azure SQL Data Warehouse in SSMS and SSDT

Batch Index Rebuild without Using Cursor

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

Continue reading Batch Index Rebuild without Using Cursor

Digging into SQL Server 2012 columnstore index

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