SQL Performance Tuning – 1 Overview

SQL performance tuning is a huge topic, and you need to know many detailed info of behavior with different version of OS and SQL. I will share my general approach of such work in 4 – 5 posts. Before Tuning The first thing you should keep in mind is end users don’t care SQL, they…


PowerShell and Control-M

PowerShell is the best scripting tool for various tasks of Microsoft products, Control-M is one popular automation tool in enterprise. But the current Control-M could not call PowerShell script directly.  I work in a customer project recently to migrate their legacy database maintenance jobs from dos batch to PowerShell, which scheduled by Control-M.  Here are my findings and sample code…


SQL16: Database in Delete-Only Mode

What will happen when the disk of In-memory OLTP checkpoint file pair is full in SQL Server 2016? Here is the learning from a recent test: #1 Change in SQL 2016 This is the change in SQL Server 2016: We will mark the database in delete-only mode (DOM) for memory-optimized tables when we are out of…


Ask Me Question

I’m Shiyang Qiu, technical lead for SAP on Azure in Greater China, Microsoft Global Black Belt team. I’m one of the Microsoft Certified Solutions Master – Data Platform in Asia. In the past decade I support Microsoft customer to adopt new features, design data tier of the high data volume high performance applications and BI projects, and dedicate to move mission…


DBCC CLONEDATABASE can not Copy Encrypted Objects

There is a new command DBCC CLONEDATABASE from latest SQL Server 2014 SP2, SQL Server 2016 SP1 release. It’s designed to collect the database objects definition, index statistic and query store info without table data. It’s useful when support team need to investigate the performance issue like suboptimal execution plan remotely. Using DBCC CLONEDATABASE could keep…


SQL16 Full Backup Size Difference on Primary Replica and Secondary Replica

Database full backup could be taken from Always On Availability Groups Active Secondary with COPY_ONLY option. In a recent SQL2016 project, we observe that the copy only full backup size are different when taking backup from Primary Replica and Secondary Replica.


Reporting Service Execution Log Retention

The default logging retention value is 60 days in SSRS. If you want to modify the retention period, you could use SQL Server Management Studio to connect to SSRS instance, check the Advanced page of Server Properties, and change the value of ExecutionLogDaysKept. From the GUI it reads “Valid values for this property include -1…


SQL16 Reporting Service Execution Log for Mobile Report

Update: SQL 2016 SP1 makes the change! One area of feedback we rec’d was around the inability to see any logs in the report catalog related to specific mobile reports.  With this release, you now can see basic information in the execution logs for mobile reports, including the folder path, report name, user who ran the…


Data Warehouse, Clustered Columnstore Indexes and SAS

Clustered Columnstore Indexes (CCI) is a game changer for data warehouse workload, which is available from SQL Server 2014 Enterprise Edition. I worked for a PoC project last year to adopt Clustered Columnstore Indexes for data warehouse. The PoC method is simple but the improvement is very significant. Below is some detailed. The Pain and…


%ErrorLevel% and $LASTEXITCODE

This is the follow-up post of the previous PowerShell and Control-M. There is one scenario I haven’t discussed: how to get the cmd.exe batch result from PowerShell. This is not a new question but there is no clear answer you could find in Internet. Here is a post from PowerShell team: ErrorLevel equivalent, you could…