|
|
-
|
|
When a transaction happens, say for an example data is bening inserted, there are mainly two things happens:
1. data page in the buffer cache is updated.
2. Data is written to the log cache.(This is to ensure the ACID property incase of any rollback.)
When the transaction is in COMMITTED phase......
|
|
-
|
|
These wait types are indicative of a shortage of memory objects.This special type of wait occurs when a task is waiting for a thread-safe memory object. The wait time might increase when there is contention caused by multiple tasks trying to allocate memory from the same memory object.This wait type......
|
|
-
|
|
Analysis of waitstats is the first step that I would take with any performance tuning activities on a system. Thats important because it is an easy and efficient way to understand the issues with a system at a very high level. There are many different wait types assocaited in SQL server which gives ......
|
|
-
|
|
How to restrict number of connections to a database Today, one of my friend at workstation asked me "How to restrict the number of users connected to a DB in a SQL server"?
Initially, my answer was as easy as to configure the user connection option to the required number using sp_configure.
sp_con......
|
|
-
|
|
The below procedure will return the size occupied by the indexes in a table. This will be helpful to measure the size before and after the maintenance activities.
Create Proc SQLZealot_FindIndexSize( @OBJECT_NAME VARCHAR(255) )
As
Begin
DECLARE @temp TABLE
(
indexID BIGINT,
......
|
|
-
|
|
I have come across many times in a situation that I need to check the existence of a procedure in multiple databases in a given server.
Hope, this would help you guys too...
Create Proc SQLZealot_FindProcInDBs (@ProcName Varchar(500))
As
BEGIN
Create Table #TempDBNames (ID int identity(1,1)......
|
|
-
|
|
In this post, am going to explain how implicit conversion would lead to a performance issues in query processing.
Implicit conversions are results of mixing various datatypes for processing. When implicit conversion happens in a query, it denotes with a “Compute Scalar”operator. A very ......
|
|
-
|
|
All of us know the basic statement of SQL as insert/update/delete.
And am sure of one thing Insert is for inserting the data and delete is for deleting the data.Both are in-place operation.
Have you ever thought What is an Update in SQL server? - Is it an in-pace operation or multi-phase(delete fo......
|
|