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

SQL SERVER – Fragmentation – Detect Fragmentation and Eliminate Fragmentation

January 12, 2010

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

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…

SQL Authority News – Training SQL Server Query Optimization And Performance Tuning

December 24, 2009

Pinal Dave

SQL Performance

SQL Scripts, SQL Server, SQL Training

Earlier this year we had offered Query Optimization course and it was sold out in minutes. Due to popular demand we are offering the same course in the very first week of next year. The title of the course is ‘MS SQL Server Query Optimization And Performance Tuning‘.

This three day course is an intensive course designed to give attendees an in-depth look at the query optimization and performance tuning concepts and methods found in SQL Server. This course is designed to prepare the SQL Server developers and administrators for a transition to SQL Server while discussing best practices for a variety of topics.

SQL SERVER – Fillfactor, Index and In-depth Look at Effect on Performance

December 16, 2009

Pinal Dave

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

SQL Index, SQL Scripts

I would like to start this post with an interesting question: Where in MS SQL Server is “100” equals to “0”? And I am not talking about data types now.. Today I will be presenting the answer to this question and some topics related to it. Creating Indices in SQL…

SQL SERVER – Difference TempTable and Table Variable – Table Variable in Memory a Myth

December 15, 2009

Pinal Dave

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

SQL Scripts

Recently, I have been conducting many training sessions at a leading technology company in India. During the discussion of temp table and table variable, I quite commonly hear that Table Variables are stored in memory and Temp Tables are stored in TempDB. I would like to bust this misconception by…

SQL SERVER – 2008 Star Join Query Optimization

December 4, 2009

Pinal Dave

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

Best Practices, Business Intelligence, Data Warehousing, SQL Joins

Business Intelligence (BI) plays a significant role in businesses nowadays. Moreover, the databases that deal with the queries related to BI are presently facing an increase in workload. At present, when queries are sent to very large databases, millions of rows are returned. Also the users have to go through…