New answers tagged

0

As Aaron mentioned you need to mention more details about table and index definition which will help us to help you in answering why the scan. I am not covering the cost answer as it’s nicely stated in comments why you see that. However just looking at predicate there might be a possibility that all required columns does not suffice from existing NC index. ...


0

I made a test today after reading this post. I have a very fragmented table on SQL Server 2019. The Table itself is 3GB and the index is 6GB. I inspected 1 table only with the query: SELECT OBJECT_NAME(ips.OBJECT_ID), i.NAME, ips.index_id, index_type_desc, avg_fragmentation_in_percent, avg_page_space_used_in_percent, ...


0

The exact similar question was asked on new Microsoft Q and A Site. I created a 2 node WSFC with AG configured for SQL Server databases. To simply put WSFC did not initiated a failover because the parameter Failure-Condition Level is default 3. And as per Microsoft official document Configure a flexible automatic failover policy for an Always On availability ...


1

I used a solution mentioned here. It worked for me. Check the SQL Server Service Account name. Check and verify the Certificate name. (It is configured in SQL Server Configuration Manager --> SQL Server Network Configuration --> Protocols for MSSQLSERVER --> Right CLick here and go to Properties --> In 'Certificate' tab, you can see a ...


2

Neither of these roles has an enumerated list of permissions. db_owner has all permissions in a database, and sysadmin is exempt from permissions checking on both the server and every database. There are a handful of system procedures that are hardcoded to check for sa or perhaps dbo.


3

I would start by saying that best person to analyze dump in MS guy or person who knows about it. I would just try to point out some basics from the log you posted At 19:46 maxworkers- 2944 workers created 789 oldest pending taskwait time 0 pending task 4 At 19:51 maxworkers- 2944 workers created 982 oldest pending taskwait time 256987 pending task 165 ...


2

You should be able to store the files on the NAS. A bigger problem will be moving between PCs. Each machine will be its own instance of SQL Server. It will not be possible for both instances to own the database files at the same time. You will have to attach the file to one instance, work on that machine, detach from that machine, go to the other and attach ...


0

The statement below will first create your groups based upon the PartIDC of those records with a Step = 1, and then use that for the subsequent steps. We then model the results and join to that to update the table: ;WITH Step1 AS ( SELECT PartIDC, PartIDX, FlagStatus, HasReplacement, groupId = ROW_NUMBER() ...


0

Check if it works for you. Please, notice this code is meant to work with groups containing no more than 3 steps each. ;WITH CTE_Replacement AS ( --Creates the groups by selecting only the rows with step 1 SELECT PartIDC, PartIDX, FlagStatus, HasReplacement, ROW_NUMBER() OVER(ORDER BY PartIDC) AS groupId, ...


5

You're not alone on seeing sync latency with AGs when doing index rebuilds. From Sean Gallardy's blog post SQL Server Index Maintenance – You’re Doing It Wrong (emphasis mine): In the worst of the cases, which does seem to happen quite often, it can bring the entire server to its knees, causing long blocking waits, filling up the log, causing remote AG ...


2

...I suppose it’s not just MS SQL version, Service Pack is also associated. Yes. You can read the lifecycle policy on service packs here. When a new service pack is released, Microsoft provides either 12 or 24 months of support for the previous service pack, varying according to the product family (for example, Windows, Office, Servers, or Developer tools)....


2

You have to build a bridge, which you should already have, so you should think about your design The only useful and not redundant information that has the table payment is LFN and to be honest put this into the payment_positions, every information you want you can get from payment_positions then: CREATE TABLE [payment] ( [LFN] INTEGER, [OUR_RECNO] ...


-2

In case you need a failover to happen if your primary SQL Server instance experiences a slow disk read or something, you can try the approach explained here: Configure a flexible automatic failover policy for an AAG. Exactly, you can set the FAILURE_CONDITION_LEVEL AAG parameter higher than default (3, corresponds to "any critical server error"), ...


0

A reboot of a cluster node that has a resource group that's configured to automatically start forces the WSFC to start that resource on a surviving node, implementing HA feature of SQL Server AAG. This action also triggers SQL Server instance to promote an existing synchronous secondary replica to primary. I think you can avoid automatic failover by setting ...


1

My question is: If you try to rebuild online just non-clustered indexes on a heap table with those also be single threaded or can those go parallel? It can be multi threaded, the restriction is ONLY to alter table rebuild command when done with ONLINE option. I would like to quote from Enabling Compression on Heap MSDN Blog (1) If you use ONLINE option ...


2

From my explanation of the full calculation: One part of the answer is about data type precedence, one part is about operator precedence and the last part is about precision, scale, and length. Let us start with operator precedence where we learn that multiplication is carried out before division. So for column (a) the multiplication of factor 0.15 and ...


Top 50 recent answers are included