SQL Server Internal Versions SQL Server Versions Database Internal Versions and Compatibility Levels Useful Query SELECT SERVERPROPERTY(‘ServerName’) ‘ServerName’ , @@VERSION ‘@@version’ , SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) ‘ComputerNamePhysicalNetBIOS’ , SERVERPROPERTY(‘MachineName’) ‘MachineName’ , SERVERPROPERTY(‘InstanceName’) ‘InstanceName’ , SERVERPROPERTY(‘IsClustered’) ‘IsClustered’ , SERVERPROPERTY(‘BuildClrVersion’) ‘BuildClrVersion’ , SERVERPROPERTY(‘Collation’) ‘Collation’ , SERVERPROPERTY(‘CollationID’) ‘CollationID’ , SERVERPROPERTY(‘ComparisonStyle’) ‘ComparisonStyle’ …
Remove Procedure Cache and Reset Wait Stats
Remove Procedure Cache and Reset Wait Stats Remove all elements from the plan cache for the entire sql server instance DBCC FREEPROCCACHE; Flush the cache and suppress the regular completion message DBCC FREEPROCCACHE WITH NO_INFOMSGS; Remove all elements from the plan cache for a specific …
Common Sense RDBMS Patterns
Common Sense Relational and Development Patterns : RDBMS_Best_Practices Great Articles on SQL Magazine Talk: SQL Magazine Best Practices Part One SQL Magazine Best Practices Part Two RBAR – If you are a relational database professional, you know RBAR ( Row By Agonizing Row ) is …
SQL Server System Info
SQL Server System Info This script provides SQL Server instance level configurations. GitHub Gist : https://gist.github.com/I90Runner/7bc31a10897a2168816073db781d5e77 /****************************************************************************************************** ** Project: Operations ** Issue: Gather SQL Server System Information ** ShortDesc: Gather SQL Server System Information. These commands usually gets executed by SQL Server during startup ** …
SQL Server :Transfer Logins from One Instance to Another Instance
Transfer Logins from One Instance to Another Instance — Login: sampleuser CREATE LOGIN [sampleuser] WITH PASSWORD = 0x02000F2A96350B8D3 HASHED, SID = 0xB30C7C1721D970A4WE34343A434A433A43AW43A453Q49A276CE0E, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF USE master GO IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE …
SQL Server AlwaysOn Availability Groups
SQL Server Always on Availability Related Queries: How do I switch Primary / Secondary on Switch Always on Availability Groups ( Swap Primary / Secondary ): When the automatic failover is setup , the Primary / Secondary gets swapped during maintenance tasks. To swap back …
SQL Server Mirroring
SQL Server Mirroring Related Queries: How do I swap Primary and Secondary on Mirroring Databases ? When the automatic failover is setup , the Primary / Secondary gets swapped during maintenance tasks. To swap back the Primary/Secondary , connect to the server which’s primary and run …
SQL Server Hardware Selection
SQL Server Hardware Selection , licensing cost and BenchMark Tools.
TempDB Contention
TempDB contention: TempDB plays very crucial in well performing SQL Server engine. These are few simple design decisions , which helps to avoid the performance bottlenecks in the long run. As a general rule, if the number of logical processors is less than or equal to …
Concurrency and Storage Needs
Concurrency and Storage Needs : Requirements: We operate on K-12 Online Summative Market. Our Needs are: When Kids log-in to take the test, they should be able to login without any issues. Should be able to store their responses and score them on the fly …