We have a pair of SQL 2019 Enterprise instances hosted in identically spec'd and configured VMs in Azure. They for a simple primary/secondary availability group array, containing a small number of databases across a small number of AGs. The single main production DB utilises a large (multi-billion row) column store table populated by production code, and used for read-only analytical reporting queries - these queries also join to row store tables for additional information. We want to offload the read-only queries onto the read-only AG secondary to spread the load on the db, so we've to the listener with the `ApplicationIntent=ReadOnly` parameter - this works well and queries are issued against the secondary. However, the duration of the read-only queries is regularly 10x slower than against the primary DB. Comparing the identical execution plans, I can see that all of the additional time is taken reading the column store table. Repeatedly re-running the query to ensure caching does not make any difference on the secondary. But on the primary I am seeing cache-related performance improvements, returning results instantly on re-running. Also, temporarily suspending the AG data sync didn't have any effect. Comparing the IO stats, they are both similar. Comparing memory used in the Column Store object pool and Row Store buffer pools, again both similar. Really at a loss here - I would have expected that the secondary, being under much less load, but with identical resources would be performing if anything better rather than consistently and significantly worse. Has anyone come across this scenario and have any suggestions how I can improve or at least justify this situation?

I'm trying to disentangle our backup strategy, and also troubleshoot the overnight backup of the Azure VM and of the SQL DBs to local storage on the Azure VM, via an agent job and maintenance plan. The VM is only used for the SQL DBs and storing associated load files. The managed backup of the VM starts at 11am and completes in 5-7 hours - this possibly seems too long? The SQL DB backup job starts at midnight and is taking 4+ hours to backup DBs totalling 1.15TB and the log file shows repeated Error: 18210, Severity: 16, State: 1 and Error: 3041, Severity: 16, State: 1 errors. From looking at the successful backup log entries, there seem to be two backups going on - one with type=disk and another with type=virtual device. My questions are: Is the Azure VM managed backup also backing up the SQL DBs? Is it likely that overlapping this with a SQL DB backup likely to cause a clash in some way? Thanks!

We have two ways of provisioning SQL Server on an Azure virtual machine, one is creating the VM and then upload the binaries of SQL server and do the installation manually, the second one is using SQL Server on Azure virtual machines which provides images of the OS and the desired version on SQL Server. The question applies to the second option. Is there a way to provision a SQL Server virtual machine on azure with a named instance or does it always install the default?

This was a question I was battling with earlier today and eventually figured out an answer. Wouldn't mind something better, but wanted to provide this to whomever also needed. First, on an Azure VM you get a D:\ drive for free which is SSD. The caveat is that it usually gets destroyed when a VM is restarted. MS best practice for high write volume is to use this drive for tempdb. What they don't go into is that it's not formatted 64kb. The hidden pagefile.sys resides here, so you need to keep that into account if you try to reformat (it'll fail). From this: https://cloudblogs.microsoft.com/sqlserver/2014/09/25/using-ssds-in-azure-vms-to-store-sql-server-tempdb-and-buffer-pool-extensions/ You'll want to alter their startup powershell script to something more like below, where I'm first removing the pagefile entirely, then formatting to 64k, creating the file as the script usually would, then putting the pagefile.sys back before starting the startup services. ``` $SQLService=”SQL Server (MSSQLSERVER)” $SQLAgentService=”SQL Server Agent (MSSQLSERVER)” $tempfolder=”D:\SQLTEMP” if (!(test-path -path $tempfolder)) { (Get-WmiObject -Class Win32_PageFileSetting).Delete() Format-Volume -DriveLetter D -FileSystem NTFS -AllocationUnitSize 65536 -NewFileSystemLabel "Temporary Storage" -Confirm:$false New-Item -ItemType directory -Path $tempfolder Set-WMIInstance -Class Win32_PageFileSetting -Arguments @{ Name = 'D:\pagefile.sys';} } Start-Service $SQLService Start-Service $SQLAgentService ``` Does anyone have anything better or see any holes in this process? For a little more automation/help for others, I also script the above script out as well as the triggers to create it and run it 30s after startup using below. This basically allows someone to automatically do the steps in the cloudblogs article I reference. ``` #1 - Set Services to manual startup so that windows scheduler will start after tempdb adjusted Set-Service -Name MSSQLSERVER -Startup

We want to create an Always On Availability group where the primary replica will be On-premises and the secondary replica will be on an Azure VM. I have researched and I know it's possible to do this, but in all the examples I find, they always have a secondary replica on-prem as well and then the 3rd VM is the one that goes to azure. We only want to have 2 replicas, one on-prem and one on azure. Is this possible? I have read the documentation to use the "Add Azure Replica Wizard" and one of the prerequisites is: Your availability group must contain on-premise availability replicas. I'm not sure if that means that I have to have at least one secondary replica on-prem or if it only means that I have to create the availability group first on-prem and then only add the secondary replica. Thanks for your help and guidance!

We've inherited some SQL server 2014 databases, they are running on Azure IaaS. We're getting complaints about performance and trying to get a grip on it. We're starting to look at CPU performance, and run the following test (all tests are running on database machine itself, ie not via network): ``` SET NOCOUNT ON DECLARE @Loops INT SET @Loops = 1 DECLARE @RESULT INT WHILE @Loops <= 2000000 BEGIN IF COALESCE('123', '456') = '456' SET @RESULT = 1 SET @Loops = @Loops + 1 END GO ``` On Production machine, 16 Virtual Processors (20 to 30% busy), we get the following result: ``` Completes in 3 minutes ``` On test machine, 4 Virtual Processors (also around 20 to 30% busy) we get the following: ``` Completes in 1 second ``` I've tried running a few benchmarks on the production machine, but they don't show any problems, and always show the production machine CPU at least twice as powerful as test machine. Is there some SQL Server setting that might be configured wrongly? Any idea how to troubleshoot this issue? Other Infrastructure Information: - Both databases have - "max degree of parallelism"set to 1 - "cost threshold for parallelism" set to 5 - Azure VM storage is Premium SSD (P30) - Production (slow) is Standard_D14_v2 - Test (fast) is Standard_D12_v2 - Data, temp and logs are separated - Data is striped across 3 striped P20 or P30 - temp on 4 striped P20 or P30 - log on single P20 or P30 Note the 'problem' database is running always on Availability Group, we are wondering if that may be a factor here.

Is there a way to change the default SQL collation directly from the "Create virtual machine" wizard in Azure ? The SQL VM images from the gallery are set for SQL_Latin1_General_CP1_CI_AS. Browsing through the wizard's SQL Server settings, there's nothing on collation. I've also searched through the parameters (listed below) in the generated templates, but again nothing on collation. Am I missing some hidden way of doing this ? Using SQL 2016 right now, but seems to be the same for other versions. ``` "sqlConnectivityType": { "type": "string" }, "sqlPortNumber": { "type": "int" }, "sqlStorageDisksCount": { "type": "int" }, "sqlStorageWorkloadType": { "type": "string" }, "sqlStorageDisksConfigurationType": { "type": "string" }, "sqlStorageStartingDeviceId": { "type": "int" }, "sqlStorageDeploymentToken": { "type": "int" }, "sqlAutopatchingDayOfWeek": { "type": "string" }, "sqlAutopatchingStartHour": { "type": "string" }, "sqlAutopatchingWindowDuration": { "type": "string" }, ``` Later edit October 2018: Checked again and still no collation options in the wizard or template.

I have a stored procedure which returns results from an indexed view via a covering index. Usually, it runs fast (~10ms), sometimes it can run up to 8 seconds. Here's an example random execution (note: this isn't a slow one, but the query text is the same apart from the value passed through): ``` declare @p2 dbo.IdentityType insert into @p2 values(5710955) insert into @p2 values(5710896) insert into @p2 values(5710678) insert into @p2 values(5710871) insert into @p2 values(5711103) insert into @p2 values(6215197) insert into @p2 values(5710780) exec ListingSearch_ByLocationAndStatus @statusType=1,@locationIds=@p2 ``` Here's the SPROC: ``` ALTER PROCEDURE [dbo].[ListingSearch_ByLocationAndStatus] @LocationIds IdentityType READONLY, @StatusType TINYINT AS BEGIN SET NOCOUNT ON; SELECT -- lots of fields FROM [dbo].[ListingSearchView][a] WITH (NOEXPAND) INNER JOIN @LocationIds [b] ON [a].[LocationId] = [b].[Id] WHERE [a].[StatusType] = @statusType OPTION (RECOMPILE); ``` (note: i added the `OPTION (RECOMPILE)` hint recently after some advice, but it hasn't helped. Here's the covering index (note: the view also has a clustered index on `ListingId`, which is unique) ``` CREATE NONCLUSTERED INDEX [IX_ListingSearchView_ForAPI] ON [dbo].[ListingSearchView] ( [LocationId] ASC, [StatusType] ASC ) INCLUDE ( -- all the fields in the query) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO ``` I put a profiler trace on, with showplan XML statistics. Here's a slow one (6 seconds), and the relevant plan: Looks exactly as i'd expect, and is the same plan when the query is fast. Here's the zoom in on the costly part of the plan, if that helps: Here is the full schema of the view/backing tables, if that helps: https://pastebin.com/wh1sRcbQ Notes: - Indexes have been defrag'd, statistics up to date. - Orig

I'm setting up a SaaS system, where we're planning to give each customer their own database. The system is already set up so that we can easily scale out to additional servers if the load becomes too great; we're hoping to have thousands, or even tens of thousands of customers. Questions - Is there any practical limitation on the number of micro-databases you can/should have on one SQL Server? - Can it affect performance of the server? - Is it better to have 10,000 databases of 100 MB each, or one database of 1 TB? Additional information When I say "micro-databases", I don't really mean "micro"; I just mean that we're aiming for thousands of customers, so each individual database would only be a thousandth or less of the total data storage. In reality, each database would be around the 100MB mark, depending on how much usage it gets. The main reason to use 10,000 databases is for scalability. Fact is, V1 of the system has one database, and we have had some uncomfortable moments when the DB was straining under the load. It was straining CPU, memory, I/O - all of the above. Even though we fixed those problems, they made us realize that at some point, even with the best indexing in the world, if we're as successful as we hope to be, we simply can't put all our data in one big honkin' database. So for V2 we're sharding, so we can split the load between multiple DB servers. I've spent the last year developing this sharded solution. It's one license per server, but anyway that's taken care of since we're using VMs on Azure. Reason the question comes up now is because previously we were offering only to large institutions and setting up each one ourselves. Our next order of business is a self-service model where anyone with a browser can sign up and create their own database. Their databases will be much smaller and much more numerous than the large institutions. We tried Azure SQL Database Elastic Pools. Performance was very disappointing, so we switched back to

Is there anyway to change a column data type so that it will accept only negative values? It's SQL Server 2014 Azure.