Creating index becomes extremely slow when all rows are in one partition

Creating index becomes extremely slow when all rows are in one partition

This article is contributed. See the original author and article here.

A customer reported that they found creating indexes sometimes become very slow in SQL2017. We analyzed this issue and found below symptom

  1. This issue happens when creating index on partition table. But all rows are in one partition.

  2. This issue happens when the database compatibility level is 140. When we change database compatibility level to 100, issue will disappear.


It seems it’s CE issue. We need to check execution plan. However, we are not able to get execution plan for ‘create index’ query in SSMS directly. Alternatively, we found below methods to get an ongoing actual execution plan.


1)  Choose ‘Include Actual Execution Plan’. Get session id =56




2) On another session , run this query every minutes to get ongoing actual execution plan


SELECT * FROM sys.dm_exec_query_statistics_xml(56);



New CE —  under 140 compatibility level


This table has 100 partitions, but all rows are in one partition. We can see this table has 216213923 rows.





Then we got ongoing actual execution plan. We found ‘Actual Number of Rows’ were more than the total number of rows of entire table.




We captured Xevent trace as well. It seems SQL SERVER sort 216213923 rows again and again.  we guess the new CE did the sort 100 times for the entire table.




We checked source codes. We found new CE use a new function CSelCalcHistogramComparison to calculate partition selectivity. Since all rows are in one partition in our case, the selectivity was calculated to 1. Therefore it failed to push down the partition ID predicate to index scan. So it executed 100 times full table scan and sort.


Microsoft has noticed this issue and has fixed it. We can enable trace flag  -T4199 to fix this issue.


Release: SQL Server Migration Assistant (SSMA) v8.15

This article is contributed. See the original author and article here.


SQL Server Migration Assistant (SSMA) Access, DB2, MySQL, Oracle, and SAP ASE (formerly SAP Sybase ASE) allows users to convert a database schema to a Microsoft SQL Server schema, deploy the schema, and then migrate data to the target SQL Server (see below for supported versions).



What’s new?

The latest release of SSMA enhances each “branch” of the tool with improved naming for statements loaded from files, revamped assessments reports compatible with modern browsers and an updated Azure AD authentication mechanism which uses the authority provided by the Azure SQL database.


In addition, this release includes the following:

  • SSMA for Access now ignores auto-created indexes for foreign keys

  • SSMA for DB2 has been enhanced with:

    • A fix for a bug related to conversion of MIN/MAX aggregate functions with date/time arguments

    • A fix for a bug  in VARCHAR_FORMAT emulation function when DD placeholder is used

    • Improve type mappings for TIME data type

    • Improve conversion of ROUND and TRUNC functions with numeric arguments

  • SSMA for SAP ASE now allows you to hide system tables and views (exclude them from conversion).

  • SSMA for Oracle now adds a setting to use full type specification for %type and %rowtype attributes




Supported sources and target versions

Source: For the list of supported sources, please review the information on the Download Center for each of the above SQL Server Migration Assistant downloads.

Target: SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, Azure SQL Database, an Azure SQL Database managed instance, and  Azure SQL Data Warehouse (Azure Synapse Analytics)*.

*Azure SQL Data Warehouse (Azure Synapse Analytics) is supported as a target only when using SSMA for Oracle.



SQL Server Migration Assistant documentation

Time Zone and Daylight Saving Support for Schedule Trigger

Time Zone and Daylight Saving Support for Schedule Trigger

This article is contributed. See the original author and article here.

[Following material applies to Schedule Triggers in Azure Data Factory. Learn more about Schedule Trigger]


We are really excited to release Time Support for Schedule Trigger in Azure Data Factory. Going forward, you can create schedule triggers in your local time zone, without the need to convert timestamps to Coordinated Universal Time (UTC) first. Furthermore, in this release, we are also adding support for Daylight Saving auto-adjustment: for time zones that observe Daylight Saving, auto change schedule trigger time twice a year (e.g. 8AM daily trigger will fire at 8AM, whether it’s PST or PDT)


Note: the changes only applies to new triggers created with time zone other than UTC. Existing ones will continue to follow UTC world clock.


To create a Schedule Trigger in local time zone in UX portal

  1. Create new trigger and select Schedule for type

  2. Specify the start date in the desired time zone (e.g. 9AM 2020-10-30 Pacific Time, choose 9:00 AM 2020-10-30). The default value is current time in UTC timestamp

  3. Specify the desired time zone. 

    • Despite we are showing UTC offset in Standard time, e.g. Pacific Time (US & Canada) shows UTC-8, it adjusts to Summer time as expected

    • Time Zone option applies to StartTime, EndTime, and ScheduleExecutionTime

    • Changing time zone will not update StartTimeEndTimeand ScheduleExecutionTime settings. Please make sure that these values are specified correctly in your desired time zone

  4. Click OK and publish your triggers

Time Zone setting other than UTCTime Zone setting other than UTC


To create a Schedule Trigger in local time zone with JSON schema

  • timeZone property now takes values other than “UTC”

  • When timeZone is set to “UTC”, timestamps are expected in format ‘yyyy-MM-ddTHH:mm:ssZ

  • When timeZone is set to values other than “UTC”, timestamps are expected in format ‘yyyy-MM-ddTHH:mm:ss’, without the suffix Z

  • Per ISO 8601 standard, Z suffix to timestamp mark the datetime to UTC and will render timeZone setting useless

  • Meanwhile missing Z suffix for UTC time zone will cause an error upon trigger activation

For example, a trigger created in UTC time zone would look like this


    "properties": {
        "name": "MyTrigger",
        "type": "ScheduleTrigger",
        "typeProperties": {
            "recurrence": {
                "frequency": "Minute",
                "interval": 15,
                "startTime": "2017-12-08T00:00:00Z",
                "endTime": "2017-12-08T01:00:00Z",
                "timeZone": "UTC"
        "pipelines": [{
                "pipelineReference": {
                    "type": "PipelineReference",
                    "referenceName": "Adfv2QuickStartPipeline"
                "parameters": {
                    "inputPath": "adftutorial/input",
                    "outputPath": "adftutorial/output"



Some of the time zones we support include:

Time Zone UTC Offset (Non-Daylight Saving) timeZone Value Observe Daylight Saving Time Stamp Format
Coordinated Universal Time 0 UTC No ‘yyyy-MM-ddTHH:mm:ssZ’
Pacific Time (PT) -8 Pacific Standard Time Yes ‘yyyy-MM-ddTHH:mm:ss’
Central Time (CT) -6 Central Standard Time Yes ‘yyyy-MM-ddTHH:mm:ss’
Eastern Time (ET) -5 Eastern Standard Time Yes ‘yyyy-MM-ddTHH:mm:ss’
Greenwich Mean Time (GMT) 0 GMT Standard Time Yes ‘yyyy-MM-ddTHH:mm:ss’
Central European Standard Time +1 W. Europe Standard Time Yes ‘yyyy-MM-ddTHH:mm:ss’
India Standard Time (IST) +5:30 India Standard Time No ‘yyyy-MM-ddTHH:mm:ss’
China Standard Time +8 China Standard Time No ‘yyyy-MM-ddTHH:mm:ss’


Fine prints on Monitoring if you programmatically monitor your Schedule Trigger runs: as per usual, trigger time of a schedule trigger run is specified as a UTC timestamp in return value. Please convert to local time zone on client side.

Webinar, November 17th: Keep employees connected and engaged in remote and blended work environments

Webinar, November 17th: Keep employees connected and engaged in remote and blended work environments

This article is contributed. See the original author and article here.

Discover real world techniques that empower individuals and teams to stay connected using Microsoft 365.  Join this webinar on Tuesday, November 17 and learn how to make the most of current and coming features for highly collaborative working and how to play to our strengths when we can’t all connect face-to-face.


yammer webinar.png

Click here to join the webinar. 


Walk through: 

  1. The shift to remote work now, blended next. Empowering workers

  2. What are the risks to collaborative team-working

    • Disconnected teams

    • Meeting overload

    • Bias to in-person working

  3. What are the opportunities?

    • Working to our strengths

    • Asynchronous working

    • Flexibility and preference

  4. Techniques to thrive as a connected team and Microsoft 365 to level the playing field (some new features in red)

    • Team manuals and charters (in wiki, OneNote), team virtual store (SharePoint)

    • Making meetings work

    • Opening up – inviting others 

    • Making the team a safe place

    • Posts and threads. The future of work


Speaker Bio

Andrew Pope is owner/partner of Consultancy Innosis in the United Kingdom (with Alister Webb in Australia), and advises large and medium-sized organisations throughout Europe and the USA on digital strategy and leadership, virtual team & community building, and how to leverage collaboration tools for business impact.

Andrew is currently advising a UK Government Agency on their blended working strategy for Microsoft Teams and a Dutch manufacturing firm on digital teamworking skills in Microsoft 365. He has also worked with some well-known organisations on their digital collaboration strategies as well as devising engagement programs. He also works as a facilitator, moderating two future workplace peer-to-peer groups, one in Oslo and one in Amsterdam

He has recently created a game to develop engagement and adoption strategies for digital teams, recently launched via a series of virtual interactive workshops and a chatbot for Microsoft Teams.

Andrew is also co-author, with Alister, of Designing Collaboration – a series of books and tools to boost digital collaboration, team and community building.

[ANNOUNCEMENT] Productivity for Humans Series: Meet Jerry Holy from the Special Olympics

[ANNOUNCEMENT] Productivity for Humans Series: Meet Jerry Holy from the Special Olympics

This article is contributed. See the original author and article here.

We are thrilled to launch our third Humans of IT Productivity for Humans episode featuring Jerry Holy, a Data Specialist from the Special Olympics organization! Hear from Jerry about how he uses Microsoft 365 tools to stay productive, and why it’s important to infuse humanity in tech.


Special Olympics 1.JPG


Productivity for Humans series: Meet Jerry Holy from Special Olympics: In this episode, meet Jerry Holy, a 24-year-old Special Olympics athlete and Data Specialist at Special Olympics International (SOI), living with neurodiversity (ADHD, Asperger’s). Jerry shares about his experience using Microsoft 365 tools such as Power BI, Excel, and Microsoft Teams to stay productive and build a meaningful career. He also encourages everyone to celebrate who they are while embracing the power of technology. 


Want to learn more about the Productivity for Humans series? Click here

Hear from everyday Humans of IT as they share about their personal stories of how M365 solutions help them stay productive despite challenging life circumstances


Interested in viewing more Humans of IT-related video content? Check out our new dedicated page in the Microsoft Virtual Hub where you can get access to additional resources to help you navigate your personal human-centered productivity journey!


Do YOU use M365 tools and solutions to empower yourself or help your loved ones be more productive? Share your stories with us in the comments below, or apply to be a guest blogger on our Humans of IT blog!


We look forward to having you join us on a human-centered productivity journey.