File Folder Location: PROFILER.EXE C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn D:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\ Running under a different domain… If you create a cmd file, you might need to copy over the cmd file to the profiler directory and run it from there. To filter to only Stored Procedures… Events -> Check... » read more
SQL Server Management Studio interface will prevent you from using domain other than the one you are currently login as. To use another domain, use a .bat file runas. SSMS 17 SSMS 18 Profiler
Create a bat file using the following…
Money vs Decimal(9,2) Precision defines total length of the number. Scale represents only position of the delimiter. Decimal(9, 2) will store numbers like 1234567.89 that is 9 digits so it will be 5 bytes. Decimal(9,2) = X,XXX,XXX.XX
Detaching a Database Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data files and transaction log files. These files can then be used to attach the database to any instance of SQL Server, including the server from which the database was detached. You cannot detach... » read more
Database Backup Database Info Use the following script to get information about the database backup file. Database Restore Note: Folder must be created first. Check Progress Sources: https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-to-a-new-location-sql-server?view=sql-server-2017
Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can deallocated and returned to the file system. To shrink... » read more
An I/O latch wait occurs when a page is accessed for reading or writing but the page is not available in the buffer pool. It causes waits on PAGEIOLATCH_EX or PAGEIOLATCH_SH, depending upon the type of request. These wait types can indicate an I/O bottleneck. You can query the sys.dm_os_wait_stats DMV to find latch wait statistics.... » read more
Use the following instead of default restore settings will speed up database restore. Use the following to determine buffercount to set to… MaxTransferSize: 4032 KBBufferCount: 96Sets Of Buffers: 2Total buffer space: 756 MBMemory limit: 1023 MB Max Transfer Size * Buffer Count * Sets of Buffers = Total Buffer Space 4032 * 96 * 2... » read more
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-2017