Drop and Recreate Foreign Key Constraint
Getting foreign key constraint error when trying to repopulate a table…
Partition columns for a unique index must be a subset of the index key
Error: Column ‘xxxxx’ is partitioning column of the index ‘PK_xxxxxxxx’. Partition columns for a unique index must be a subset of the index key. Could not create constraint or index. Solution: Since both biTable01Id, dtStartDateTime columns are in the partition column, both must be in the Primary Key column as well.
Create Index Status Monitor Progress
Starting with SQL Server 2014 a new troubleshooting capability is to monitors real time query progress with the DMV sys.dm_exec_query_profiles which is the base for Live Query Statistics new functionality for SQL Server 2016, there are two ways to enable real time query monitoring: Session scope: By enabling SET STATISTICS XML ON; or SET STATISTICS PROFILE ON;... » read more
Increase Performance of Existing Stored Procedure
Execution Plan in SSMS Run Stored Procedure in SSMS with “Execution Plan” enabled. SSMS will recommend index to be created. Improve Query Statement Convert subqueries to main queries. Note: This may not always increase performance. Have to run the both queries side by side to test.
Delete User Account from SQL Server Database
Note: Make sure the user you are about to delete does not own anything in the database, else you will get an error message saying unable to delete user. Make sure the user does not own any of the SQL jobs.
Remove Duplicate Record
Look for duplicate records. Remove duplicate records
Remote Query Timeout
Now the common misconception with this option is probably because of the following screen within SQL Server Management Studio (Server Properties -> Connections) where we see the following. So the first checkbox allows remote connections to the server so we could assume that the remote query timeout value underneath (here set to 600, the default)... » read more
SQL Compare and Indexes
If need to include indexes during SQL compare, make sure the following boxes are unchecked in the “options” section… Ignore indexes Ignore performances indexes Adding Missing Index
Failed to load expression host assembly. Details: Could not load file or assembly or one of its dependencies. The system cannot find the file specified. (rsErrorLoadingExprHostAssembly)
Error: Got the following error after upgrading SSRS. Failed to load expression host assembly. Details: Could not load file xxxxxxx or assembly or one of its dependencies. The system cannot find the file specified. (rsErrorLoadingExprHostAssembly) Fix: For SQL Server 2014 to 2016, copy the missing dll to the new folder. From… C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting... » read more