Differences Between TRUNCATE TABLE and DELETE

May 19, 2013

If you want to remove data from a table, you can do this in a couple of ways using TRUNCATE TABLE or DELETE. A common approach is to use DELETE to remove all rows in the table but TRUNCATE TABLE offers an alternative and I will list what the differences between the two are. DELETE –Delete rows with filter DELETE FROM SchemaName.Table WHERE… –Delete all rows DELETE SchemaName.Table –Delete all rows DELETE FROM SchemaName.Table –Delete using JOINS DELETE T1 FROM SchemaName.Table1 AS T1 JOIN SchemaName.Table2 AS T2 ON T1.Column1 = T2.Column1 –Delete using IN DELETE FROM SchemaName.Table1 WHERE Column1 IN (SELECT Column1 FROM SchemaName.Table2) –Delete using EXISTS DELETE T1 FROM SchemaName.Table1 AS T1 WHERE EXISTS (SELECT * FROM SchemaName.Table2 AS [...]

Read the full article →

Using DBCC FREEPROCCACHE to Clear SQL Server Procedure Cache

May 14, 2013

It is possible to clear out the entire SQL Server procedure cache using DBCC FREEPROCCACHE The procedure cache is where SQL Server will cache execution plans after they have been compiled. The benefit of this caching is that there is no need for the execution plans to be compiled at run time. This compiling operation typically consumes resource and slows down the execution time of the query. The obvious disadvantage of clearing out the plan cache is that all execution plans for your SQL Server instance are recompiled upon execution which may slow things down temporarily until the cache is re-populated. Great for development, give thought before executing in production. You may be under pressure to quickly get performance back [...]

Read the full article →

The OLEDB Wait Type and How to Reduce It

January 13, 2013
reduce oledb wait type

Onwards we go in this series on SQL Server wait types. In this post, I look at the OLEDB wait type. Books Online lists this as Occurs when SQL Server calls the SQL Server Native Client OLEDB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider. “OLEDB” stands for Object Linking Embedding Database. OLEDB is an API used by SQL Server when retrieving data from a remote server or datasource. Linked server queries, remote procedure calls, BULK INSERT operations, full text search and queries to external data sources, for example Excel all contribute to OLEDB wait type. The wait type occurs when the remote system or network connection [...]

Read the full article →

Have You Set Yourself Any Goals for the New Year?

January 12, 2013
goals for the new year

“New year, new start” – I’ve heard that saying several times and it could mean the start of many different things. A new diet, a new fitness regime, a new job or complete career change. Giving up a vice, a new qualification, a new life – there will be others I’m sure. I don’t usually do new years resolutions but this year I have set myself some new targets and they are related to my profession as a DBA. SQL Server Certification I wrote about this before in my posts on SQL Server 2012 certification Whether you agree or disagree as to whether there is any benefit to certification is your opinion and I have certainly changed my mind on [...]

Read the full article →

Reducing SQL Server CXPACKET Wait Type

December 4, 2012

In my last post, I wrote about how SQL Server schedules tasks to be executed. It’s important to be  able to understand this when when trying to analyse wait types and statistics in SQL Server. For this post, I will be looking at the CXPACKET wait type and what you can do to reduce it. I mentioned before that the wait types need translation – MSDN describes this as: Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem. This is a parallel processing wait type where many threads are used to satisfy the query. Each thread deals with a subset of the data. [...]

Read the full article →

An Overview of SQL Server Task Scheduling

November 25, 2012

In my previous post, I touched upon SQL Server wait types and in order to understand wait types in SQL Server, it’s important to know first how the server schedules tasks. On each CPU core, only a single thread or “worker” can be running at any given time. Each CPU, be it logical or physical inside your SQL Server is assigned a scheduler and it is responsible for managing the work which is carried out by threads. You can view the schedulers on your SQL Server by running this code: SELECT * FROM sys.dm_os_schedulers; Schedulers are responsible for managing user threads and internal operations. If you run that DMV, you can see these types of status in there: VISIBLE ONLINE (DAC) – [...]

Read the full article →

An Introduction to SQL Server Wait Types and Stats

November 10, 2012
sql server wait types

Ok, so in this post, I wanted to go over SQL Server wait types and statistics  - what they are and how to check them. My idea is then to start a series of posts which go into more detail about individual SQL Server wait types, their causes and how to improve them. At the time of writing this post, I do not know about all of the wait types in SQL Server and there are many of them. Some of them I have experience with, some of them I haven’t so this is a partially a learning experience for me, as well as hopefully providing you with informative content so that you can better understand your own SQL Server [...]

Read the full article →

How to Delete Millions of Rows using T-SQL with Reduced Impact

October 9, 2012

In this post, I talk about deleting millions of rows in SQL Server whilst keeping impact low. Deleting millions of rows in one transaction can throttle a SQL Server TRUNCATE TABLE – We will presume that in this example TRUNCATE TABLE is not available due to permissions, that foreign keys prevent this operation from being executed or that this operation is unsuitable for purpose because we don’t want to remove all rows. When you run something like the following to remove all rows from your table in a single transaction, DELETE FROM ExampleTable SQL Server sets about the process of writing to the transaction log all of the changes to be applied to the physical data. It will also decide [...]

Read the full article →

Resetting table identity value using DBCC CHECKIDENT

September 23, 2012
dbcc checkident

DBCC CHECKINDENT can be used to reset a tables identity value. So I will create a small table to demonstrate this. CREATE TABLE Table_1 ( ID INT IDENTITY(1,1) NOT NULL, DateAdded DATETIME NOT NULL ) Insert some rows…. INSERT INTO Table_1(DateAdded) VALUES(GETDATE()); INSERT INTO Table_1(DateAdded) VALUES(GETDATE()); INSERT INTO Table_1(DateAdded) VALUES(GETDATE()); INSERT INTO Table_1(DateAdded) VALUES(GETDATE()); INSERT INTO Table_1(DateAdded) VALUES(GETDATE()); Check the current value which produces the below output…. DBCC CHECKIDENT ('Table_1', NORESEED); Checking identity information: current identity value ’5′, current column value ’5′. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Now we will reset the identity value so that the next time we insert data, the value will be 10…. DBCC CHECKIDENT('Table_1', RESEED, 9); Checking identity information: [...]

Read the full article →

Using sp_change_users_login to fix SQL Server orphaned users

September 19, 2012
use sp_change_users_login to fix sql server orphaned users

I’m going to be looking at sp_change_users_login as a continuation to a previous post where I looked at a couple of ways to transfer logins from one SQL Server to another and touched upon the issue of the orphaned ”security identifier” (SID). A typical scenario that arises is when the DBA quickly realises that the logins on the SQL Server cannot access the database. They try and add the login to the database as a user and are presented with the error: Error 15023: User already exists in current database. sp_change_users_login to the rescue! I first saw this error a number of years ago and due to my complete lack of experience at the time, one of my first thoughts was that I would [...]

Read the full article →

← Previous Entries