Pinal Dave - SQL Server Performance Tuning Expert

SQL SERVER – T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005

April 3, 2007

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

SQL Download, SQL Scripts, SQL Stored Procedure

I was doing paging in SQL Server 2000 using Temp Table or Derived Tables. I decided to checkout new function ROW_NUMBER() in SQL Server 2005. ROW_NUMBER() returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.…

SQL SERVER – 2005 – Performance Dashboard Reports

April 2, 2007

Pinal Dave

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

SQL Download

The Microsoft SQL Server 2005 Performance Dashboard Reports are used to monitor and resolve performance problems on your SQL Server 2005 database server. The SQL Server instance being monitored and the Management Studio client used to run the reports must both be running SP2 or later. Common performance problems that…

SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive.

April 1, 2007

Pinal Dave

SQL Tips and Tricks

SQL Backup and Restore, SQL Data Storage, SQL Error Messages, SQL Scripts, SQL Server, SQL Server Security, SQL Stored Procedure, SQL TempDB

If you ever find your TEmpDB to be full and if you want to move TempDB, you will find this blog post very helpful.

Here is the error message which may come across.

Event ID: 17052
Description: The LOG FILE FOR DATABASE ‘tempdb’ IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free
Up SOME LOG SPACE

SQL SERVER – 2005 Best Practices Analyzer (February 2007 CTP)

March 31, 2007

Pinal Dave

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

Best Practices, Software Development, SQL Download

Microsoft has released a tool called the Microsoft SQL Server Best Practices Analyzer. With this tool, you can test and implement a combination of SQL Server best practices and then implement them on your SQL Server. The SQL Server 2005 Best Practices Analyzer gathers data from Microsoft Windows and SQL…

SQL SERVER – Index Seek Vs. Index Scan (Table Scan)

March 30, 2007

Pinal Dave

SQL Performance

SQL Index, SQL Joins, SQL Scripts, SQL Server

Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

SQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By

March 29, 2007

Pinal Dave

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

SQL Function, SQL Joins, SQL Scripts

This question is asked many times to me. What is difference between DISTINCT and GROUP BY? A DISTINCT and GROUP BY usually generate the same query plan, so performance should be the same across both query constructs. GROUP BY should be used to apply aggregate operators to each group. If…

SQL SERVER – Fix : Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON

March 28, 2007

Pinal Dave

SQL

SQL Download, SQL Error Messages, SQL Scripts, SQL Server, SQL Server Security

This error occurs when the user has attempted to insert a row containing a specific identity value into a table that contains an identity column. Run following commands according to your SQL Statement. Let us learn about the IDENTITY_INSERT.