SQL Performance Archives - Page 72 of 100 - SQL Authority with Pinal Dave

SQL SERVER – Find Statistics Update Date – Update Statistics

January 25, 2010

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

Statistics are one of the most important factors of a database as it contains information about how data is distributed in the database objects (tables, indexes etc). It is quite common to listen people talking about not optimal plan and expired statistics. Quite often I have heard the suggestion to…

SQL SERVER – Execution Plan – Estimated I/O Cost – Estimated CPU Cost – No Unit

January 22, 2010

Pinal Dave

SQL Performance

Execution Plan, SQL Server

During the SQL Server Optimization training, I enjoy teaching the Execution Plan. I am always sure that questions related to the estimated cost will be raised by attendees. Following are some common questions related to costs:

SQL SERVER – Initializing a Merge Subscription Without a Snapshot

January 15, 2010

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Replication

During recent course of Disaster Recovery and Performance Tuning, I had very interesting conversation with students regarding Initializing a Merge Subscription Without a Snapshot and Initializing a Transactional Subscription Without a Snapshot. After the discussion when we were looking at MSDN pages one thing caught my notice was the note…

SQL SERVER – Find Busiest Database

January 14, 2010

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Scripts

In my recent training I was asked to how to find which is the busiest database in any SQL Server Instance. What he really meant by this is which database was doing lots of read and write operation. To find the answer to this question I decided to look into…

SQL SERVER – Fragmentation – Detect Fragmentation and Eliminate Fragmentation

January 12, 2010

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts

Q. What is Fragmentation? How to detect fragmentation and how to eliminate it? A. Storing data non-contiguously on disk is known as fragmentation. Before learning to eliminate fragmentation, you should have a clear understanding of the types of fragmentation. We can classify fragmentation into two types: Internal Fragmentation: When records…

SQL SERVER – Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON

January 4, 2010

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Error Messages, SQL Index, SQL Scripts

In one of my recent training course, I was asked question regarding what is the importance of setting IGNORE_DUP_KEY = ON when creating unique nonclustered index. Here is the short answer: When nonclustered index is created without any option the default option is IGNORE_DUP_KEY = OFF, which means when duplicate…

SQL SERVER – Difference Temp Table and Table Variable – Effect of Transaction

December 28, 2009

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

Best Practices, Database, SQL Scripts, SQL Transactions

Few days ago I wrote an article on the myth of table variable stored in the memory—it was very well received by the community. Read complete article here: SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth. Today, I am going to write an article…