Most important SQL Server wait types

Before I discuss the most important wait type list , I want to say that really there is no definitive list of most important sql wait types. Understanding what is normal and the context of the wait type is essential when discussing wait types. For example , you may see the sql wait type WRITELOG as high on the list of waits on a SQL Server. This may or may not be important depending on the "normal" behaviour. Benchmarking your systems is a great way of understanding the patterns your systems. Based on monitoring and analysing various SQL Server performance... Read more →

How to monitor Blocked Processes with SQL Alert and email sp_whoisActive report

Question: How can I monitor SQL Server blocked processes with a SQL Server alert and capture the details for analysis. I’d like the alert to trigger when there is more than one blocked process. When the blocked process triggers the SQL alert, I want sp_whoisActive to execute , capture details and email the report. sp_whoisActive is one of the tools I use when troubleshooting SQL Server performance issues Read more on sp_WhoIsActive utility for SQL Server troubleshooting Answer: There are multiple ways of achieving this goal. Monitoring SQL blocked processes is an excellent way of identifying tuning opportunities. The method... Read more →

Msg 5075 - The database collation cannot be changed if a schema-bound object depends on it

Question: I executed a ALTER DATABASE COLLATE statement and received the following error message. How can I fix and proceed with ALTER DATABASE COLLATE procedure? ALTER DATABASE My database COLLATE Latin1_General_100_CI_AS_KS ; Msg 5075, Level 16, State 1, Line 1 The object 'CK_MyCheckConstraint_Uid' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation. Answer: The reason for this message is you haven’t met one or more of these conditions: 1) Must have an exclusive lock on the database - read... Read more →

SQL Error 1204 - cannot obtain a LOCK resource at this time

A DBA reported the error message “Error: 1204, Severity: 19, State: 4. cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.” The message appeared during the daily SQL Server Error log report which checks for SQL Server Error log messages of interest to DBAs. The SQL error 1204 message appears when memory thresholds are met. SQL Server cannot obtain more memory from the Operating System. The memory is used by other resources... Read more →

SQL Lock Escalation FAQ

SQL Lock Escalation – Everything you ever wanted to know but were to afraid to ask. What is lock escalation? Lock escalation is the SQL Server process of consolidating granular multiple locks into a less granular locks. By placing a lock at a higher level , such as escalating multiple row locks to a table lock , less resources are used but at the cost of decreased concurrency. E.g On Table_A there are a number of locks held on Heap Data Pages. The threshold is met , which triggers the lock escalation. The intent table lock changes into an Exclusive... Read more →