6

b) which tables, index, columns that benefit from a WITH FULLSCAN command? From my point of view you aren't gathering the right data to answer that question. If you're looking for improvements that you can make just by analyzing statistics on the database, I can only think of two query performance issues that can be caused by using sampled statistics ...


5

This is a normalisation issue as timestamp is functionally dependent on non_unique_id. So there should be another table with primary key non_unique_id and the corresponding timestamp. In SQL Server/Azure SQL Database you can enforce this with an indexed view though. Create a view with schema binding and definition SELECT non_unique_id, timestamp, ...


5

You should be able to track the DTCTransaction event in SQL Trace or XEvents. Or you could just shut down MSTDC (not in production, of course) and see what fails. Also note that updates to .NET's SqlClient enables some distributed transaction scenarios with Azure SQL Database without MSDTC. See Distributed transactions across cloud databases


4

Metadata visibility configuration doesn't work in the same was as permissions for User tables - the rights are included implicitly whenever rights to underlying objects are granted. Take the following example from the docs: SELECT name, object_id FROM sys.tables WHERE name = 'myTable'; GO The above returns no results for users that have been ...


4

The insertion of records from the RAW table into the dbo table got rolled back; BUT The truncation of the RAW table did not roll back. No, there was no rollback at all, and here is the repro. With xact_abort off that is your default set option I created 2 tables, I then open transaction end do 2 inserts one of which(the second one) fails, I added ...


4

If you are using Azure SQL Servers rather than Managed Instances or on-prem instances in VMs†, and are trying to create SQL Agent jobs†, then you can not: SQL Agent Jobs are not supported for Azure SQL. There are a number of ways to automate tasks in Azure SQL. The official way it is heading appears to be Elastic Database Jobs, though this feature is still ...


4

SQL Server table partitioning is natively range partitioning. Although you can implement list partitioning (i.e. partitioning columns exactly match boundaries), take care to ensure exact boundary matches to avoid excessive data movement and logging during SPLIT operations. Consider creating a CHECK constraint on the fact table matching the boundary values (e....


4

This service has to do with the Always Encrypted with Enclaves feature. I have sent a note to the team to get this more explicitly documented for customers. Some scenarios will break if that service is not enabled. Conor Cunningham


4

I don't think indexing is your (main) problem here. There are some strange and troubling things related to timing in that execution plan. The first of which is a disparity between duration and CPU: <QueryTimeStats CpuTime="93275" ElapsedTime="315874" /> The query ran for 5 minutes, but only used 1.5 minutes of CPU time (at DOP 1). This difference ...


3

I ran into that problem a few minutes and found a way to remove the fulltext stuff from a bacpac. I found this powershell script (but fixed my backup by hand, because it was faster than trying to understand and use the script) https://gist.github.com/JohnLBevan/6a8876f71a25aa600254d7127cf1a819 Rename your backup to zip extract model.xml and Origin.xml ...


3

which effectively yields two identical indexes. The indexes are not identical. The CI includes all of the other columns, while the PK has only the ItemId key column on the leaf. So the Clustered Index is probably 100x the size of the non-clustered index. For instance COUNT(*) and paging queries will be significantly more expensive with only the clustered ...


3

Question: Can I restore a differential backup on azure managed instance? No. Borrowing from my answer here, Managed Instance only supports the restore of full backups; no log or diff. See this thread: Lesson Learned #82: Azure SQL Database Managed Instance supports only COPY_ONLY restoring a database backup And the limited syntax diagram for RESTORE in ...


3

It sounds like you are using the email address as a key, which is not good practise. Anything that can change is not a good candidate to be a key value which includes email addresses and phone numbers. Other reasons it is not a good idea include: It could be NULL: maybe there are people in your target audience who don't use email so won't have an address? ...


3

Using Azure SQL DB (not managed instance) and it most definitely uses the CLR for things like geo-spatial and AT TIME ZONE I have been having this same problem and could not find a solution. Working with MS support and this is what we came up with as a possible resolution - just did it 10 minutes ago, but seems legit: The hypothesis is that the CLR ...


3

First of all, no need to worry as this is normal behaviour. The link in your answer From msdocs on Data space allocated gives us the info needed: The amount of space allocated grows automatically, but never decreases after deletes. This behavior ensures that future inserts are faster since space does not need to be reformatted. This space allocated ...


3

Database Mail is available on SQL Server on Azure IaaS and my suggestion is to configure it using SendGrid email delivery service (available on Azure Marketplace) that supplies your virtual machine with an SMTP relay. Leverage this service to setup database mail for your alerting or reporting needs. Here you will find how to setup the SendGrid service on ...


3

DTU is a blended measure of CPU, memory, and data I/O and transaction log I/O…. This means that there is no definitive measure of equating x DTU = x CPU, X Memory etc. Since, you have Premium P11 tier - this means that you have 1750 DTUs. Based on this blog post - By Andy Mallon, below is the CPU-to-DTU-to-Service Tier mapping : +--------------+------+-----...


3

You seem to want to do data replication in almost real-time. Why haven’t you considered SQL Server data replication or even better, an availability group? Our data warehouse is setup as a cluster-less availability group and the TB sized DBs are kept in sync.


3

Yes, you can Copy the SQL database with encryption from one Azure subscription to another. But you have to move your database along with an Azure SQL Server. Not just the database itself. You can follow my steps as a workaround. I tested this successfully with the following steps: Subscription 1 Create a new resource group, an Azure SQL Server and Azure ...


3

Azure SQL Database does not support SQL Agent. Depending what the job does, i.e. if it is calling a stored proc only then you can run it from an on-prem sql server agent job or SQL Managed Instance via Linked server. Create a linked server on the on-prem Create a job in the on-prem sql server which would call the SP in Azure. Or you can Azure Automation :...


3

The server has 96 GBs of RAM and a 3.4 Ghz processor The elastic pool is mainly for smaller workloads, and multi-tenant applications. For lift-and-shift of existing SQL Servers, you're normally better-off with Azure SQL Database Managed Instance. Only Managed Instance (or SQL Server on an Azure VM) supports cross-database queries, SQL Server Linked ...


2

In Azure you cannot restore on an existing database. (c) Apparently, not really. You CAN restore a .bacpac file into existing Azure SQL database, for example, in a case when you really need to keep the same instance of Azure SQL database, as it's incorporated in Azure environment. A necessary condition for that is: the target DB must be empty, i.e. like ...


2

Once a database is migrated to Hyperscale, it cannot be migrated directly to a non-Hyperscale service tier. At present, the only way to migrate a database from Hyperscale to non-Hyperscale is to export/import using a BACPAC file or other data movement technologies (Bulk Copy, Azure Data Factory, Azure Databricks, SSIS, etc.) Source is here. ...


2

Time spent automatically updating statistics is included in compile time. The stats update takes 10 minutes, and the compile time for a query that triggers an auto stats update includes that time - which is why the query takes over 10 minutes every now and then. The modification threshold for an automatic stats update, per the documentation, is sqrt(table ...


2

Depends on the Azure offering you want to use. Azure SQL Database You can get Geo-replication setup which is very straight forward and will give you a read-only secondary. Azure Portal : Geo replication Azure SQL Managed Instance : You can use regular transaction replication (under preview) or geo-replication. Azure Managed Instance


2

Azure SQL Database is designed to scale up and down based on your workload. I cannot answer your question "is it ok to change the tier to S0 every weekend for cost savings and change back to s6 on Monday" with yes or no. You also mentioned usage is very minimal. S0 gives you maximum 10 DTU. Do you know for your "very minimal" workload 10 DTU is enough or no....


2

Not sure if this is the issue, but you should use ERROR_NUMBER() not @@error in your catch block. In any case something like this should work reliably, without PK voliations: SET nocount ON; begin transaction IF NOT EXISTS(SELECT * FROM MyTable with (updlock, holdlock) WHERE ID = @ID) begin INSERT INTO Table(ID, Value, TimeChanged) VALUES (@id, 'xxx', ...


2

This query will show you any schema which includes objects shipped by Microsoft: SELECT s.name FROM sys.schemas s WHERE EXISTS ( SELECT 1 FROM sys.objects o WHERE o.is_ms_shipped = 1 AND o.schema_id = s.schema_id ) ORDER BY s.name; If you run that, you'll see dbo and sys listed. This query shows the owner for each schema present in ...


2

When do index rebuilds occur in Azure SQL? Are they automatic or manually selected in a maintenance window? The index rebuilds, at the time of writing the answer, in SQL Server is not automatic, I hope you are not mixing it with automatic index tuning. There is no "SQL Server agent" in Azure SQL database you have the option to schedule jobs via Elastic Job ...


2

Use SQL Auditing to track from where those statements are coming. Set-AzureRmSqlDatabaseAuditing -ResourceGroupName "resourceGroup" -ServerName "SQL Server Name" -DatabaseName "AdventureWorksLT" -StorageAccountName "storageAccount" -AuditActionGroup "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "FAILED_DATABASE_AUTHENTICATION_GROUP", "...


Only top voted, non community-wiki answers of a minimum length are eligible