How to handle Perennial statement of “Our Database is SLOW”? I have been involved with Database and Product Development for a decade in different Verticals. I have heard this statement more than I could count “Database is Slow”,”Database is the reason”. I worked for .COM …
Snapshot Isolation in SQL Server
Snapshot Isolation in SQL Server Wiki Article on SnapShot Isolation : http://en.wikipedia.org/wiki/Snapshot_isolation Issues with SnapShot Isolation: http://sqlperformance.com/2014/06/sql-performance/the-snapshot-isolation-level Kimberly Tripp Video on Isolation Levels : http://download.microsoft.com/download/6/7/9/679B8E59-A014-4D88-9449-701493F2F9FD/HDI-ITPro-TechNet-mp4video-MCM_11_SnapshotIsolationLecture(4).m4v Kendra Little on SnapShot Isolatioin : http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/ Microsoft Link: https://msdn.microsoft.com/en-us/library/ms188277(v=sql.105).aspx https://msdn.microsoft.com/en-us/library/bb522682.aspx SQL Team Link : http://www.sqlteam.com/article/transaction-isolation-and-the-new-snapshot-isolation-level Idera Short article on TempDB : http://sqlmag.com/site-files/sqlmag.com/files/uploads/2014/01/IderaWP_Demystifyingtempdb.pdf Jim Gray …
SQL Server Locking
Microsoft Link : https://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx Locks held by Current Processes: SQL Server 2008 R2 https://technet.microsoft.com/en-us/library/ms189497(v=sql.105).aspx SQL Team Primer on Locks : http://www.sqlteam.com/article/introduction-to-locking-in-sql-server Kalen Delany Crisp explanation on Update Locks: http://sqlblog.com/blogs/kalen_delaney/archive/2009/11/13/update-locks.aspx UPDATE locks are not really a separate kind of lock, but rather are a hybrid of …
Random GUID or Sequential GUID
Random GUID or Sequential GUID SQL Server has two in-built functions for creating uniqueidentifier. NEWID() NEWSEQUENTIALID() New NEWSEQUENTIALID would help to reduce page splits and fragmentation because of sequential nature of the values returned from this function. NEWID() is truly (almost) random and it would …
More CPU’s in SQL Server : Does it Help or Hurt? (MAXDOP)
More CPU’s in SQL Server : Does it Help or Hurt? (MAXDOP): Common sense tells us , having more CPU’s mean more processing power , that means things would be done faster. Hold on for a sec..Its SQL Server. Common Sense isn’t applicable here. PUN …
SQL Handle and Plan Handle to Text – Quick Reference
SQL Handle to Text – Quick Reference DECLARE @pStartOffSet INT DECLARE @pEndOffSet INT DECLARE @pSQLHandle VARBINARY(64) SELECT @pSQLHandle = 0x030008001374fc0232620901c7a300000100000000000000 ,@pStartOffSet = 1788,@pEndOffSet = 3042 SELECT SUBSTRING(st.text, (@pStartOffSet/2)+1, ((CASE @pEndOffSet WHEN -1 THEN DATALENGTH(st.text) ELSE @pEndOffSet END – @pStartOffSet)/2) + 1) AS statement_text FROM sys.dm_exec_sql_text(@pSQLHandle) …
MERGE Statement and Deadlocks
MERGE Statement and Deadlocks: We have MERGE SQL Statement to satisfy our functional needs. Instead of writing separate Insert / Update (UPSERT) Statement we decided to use MERGE Statement because of its simplicity in syntax. MERGE test.Target AS t USING(SELECT ItemID, Score, TargetText FROM @SrcTable …
Issues with Shrinking or Truncating SIMPLE Recovery Mode Database
Issues with Shrinking or Truncating SIMPLE Recovery Mode Database I had a database with log file size of 30 Gig and Data File with 20 Gig. I wanted to shrink the database but I was not able to . When I ran “DBCC OPENTRAN“, I …
SQL Server Login Properties
SQL Server Login Properties USE [master] GO SELECT [name] AS [SQL_User] ,LOGINPROPERTY([name], ‘PasswordLastSetTime’) AS [PasswordLastResetDT] ,LOGINPROPERTY([name], ‘BadPasswordCount’) AS [BadPasswordCount] ,LOGINPROPERTY([name], ‘BadPasswordTime’) AS [BadPasswordDT] ,LOGINPROPERTY([name], ‘HistoryLength’) AS [HistoryLength] ,LOGINPROPERTY([name], ‘IsExpired’) AS [IsExpired] ,LOGINPROPERTY([name], ‘IsLocked’) AS [IsLocked] ,LOGINPROPERTY([name], ‘IsMustChange’) AS [IsMustChange] ,LOGINPROPERTY([name], ‘LockoutTime’) AS [LockoutTime] FROM [sys].[sql_logins] …
Truncate Log – Rebuild Indexes – Statistics
Change FULL Recovery Model to SIMPLE and Shrink Log SELECT sys.databases.name , SUM(size) * 8 / 1024 AS [Total_disk_pace_MB] FROM sys.databases JOIN sys.master_files ON sys.databases.database_id = sys.master_files.database_id GROUP BY sys.databases.name ORDER BY 2 DESC , sys.databases.name; USE master; DECLARE @isql VARCHAR(2000) , @dbname VARCHAR(64) , …