6

How are you measuring the size of your tables and indexes? The way SQL Server works is once the database is allocated disk space, it still retains that disk space and only marks it empty / for re-use internally when data is reduced / removed until a SHRINK or similar operation occurs that forces the release of that acquired space back to the disk. So ...


4

An alternative solution would be to stop worrying about index fragmentation. In my company, we were having similar issues with index rebuilds blocking and generally being resource-intensive on a large ERP database and another large data warehouse database in SQL Server 2016 Enterprise. We have since stopped index rebuilds and reorganizes completely and ...


4

There is something which is incredibly wrong here. Your tables are tiny! Just for comparison, I did a reorg on a clustered table with 450,000 rows and 22,000 pages having 42% fragmentation level. It took 1 second. This was on my 3 years old laptop. You need to do some troubleshooting here. I would restore the database from a backup taken before such reorg to ...


4

You're parsing the XML wrong. Since sql_text is an action, you need to do this: value('(action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)')


3

Since the log file is only growing on the secondary replica, something is preventing transaction log truncation on that database (but not on the primary). You'll need to check the value of log_reuse_wait_desc in sys.databases on the secondary replica to see why the transaction log isn't being cleared there. Since log_reuse_wait_desc is currently showing &...


3

I think your conclusion that these two numbers should match up is fair. That measurement of write latency from sys.dm_io_virtual_file_stats should provide similar numbers to the "Logical Disk" ➡ "Avg. Disk sec/Write" Perfmon counter. Make sure that you are comparing "apples to apples" as much as possible. The default for that ...


3

I'm wondering what the best way would be to keep a nonclustered index for looking by accountId without all the fragmentation You've got the partition key in the nonclustered index, so it's on the partition scheme (by default), and only the head partition is going to get inserts and additional fragmentation. So you can rebuild the older partitions rarely and ...


3

If you intend to use the cloned database for production use, specify the VERIFY_CLONEDB option which also clears the statistics in the cloned database. VERIFY_CLONEDB Verifies the consistency of the new database. This option is required if the cloned database is intended for production use. Enabling VERIFY_CLONEDB also disables statistics and query store ...


2

I believe this is by design because the DBCC CLONEDATABASE documentation specifies system statistics are copied. My interpretation is that includes not only stats blobs but row counts as well. To rectify the row counts, execute DBCC UPDATEUSAGE with the COUNT_ROWS option: DBCC UPDATEUSAGE('CloneDBIssue_Clone') WITH COUNT_ROWS;


2

I try to answer to all your questions: If I create indexes on the CentralDB's table, will the views use it? Yes for sure, if the indexed columns are used in the views, like the Department Code in your example. Are Columnstore Indexes better choice? Yes and No, it depends on numbers of rows and the usage of the central table. It's something you have to ...


2

We can use APPLY to calculate the position of the various things we need, we use each APPLY in series like variables in procedural code. We assume that the number is the start of the string, followed by arbitrary letters, a slash, another number, then more letters. Note: no tally function needed here: select cost, size, strength, ...


1

If you intend to be able to update/insert through the view, it will likely not work. Please see my post here: http://nedotter.com/archive/2017/07/dml-for-memory-optimized-tables-in-partitioned-views/ Rows cannot transition to/from the memory-optimzed side by changing the key/partitioning value. Depending on the version of SQL you are running, it will either ...


1

Fisrt, you cannot create indexed views when the tables are spread over different databases. Second, SQL Server have special DMVs with name beginning as dm_db_missing index_* that will be filled with demands of creating indexes needed for the executed queries. So let it works a lot (one month) and look at the content of theeses views to create the appropriate ...


1

the problem is the error severity of the backup exception. I simulated your situation with this script: BACKUP DATABASE [test] TO DISK = N'X:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak' WITH NOFORMAT, NOINIT, NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 BACKUP DATABASE [test] TO DISK = N'C:\DB\SQL\2019\MSSQL15....


1

try to give the VCO AD object full control on the listener AD object. To do this, go to AD user and computer, locate the listener object, open properties, security and verify if the virtual cluster object is here and if it has full control on this object. Then retry the failover.


1

In my case Netlogon service was is disabled mode on secondary server hence we received this error. Once we enabled the same database came into sync.


1

There are more compact ways to write this, but this illustrates a way to do it. First select the territories and periods that Anne was looking after that territory. Then pull all the sales where the transaction date falls in one of the periods when Anne was covering the territory where the sale was made. Then sum up the sales. WITH territory_periods AS ( ...


1

I've been having deadlock problems for years on change_tracking tables. Here is a few things that I've done to reduce them. #1 : Check the size of your change_tacking tables and your retention period The bigger those tables get, the more likely you'll get deadlocks while querying them. Here is a query to get the size of the change_tracking tables. SET ...


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