All Articles - SQL Authority with Pinal Dave

SQL SERVER – 2005 – Performance Dashboard Reports

April 2, 2007

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

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.

SQL SERVER – Fix : Error 701 There is insufficient system memory to run this query

March 27, 2007

Pinal Dave

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

SQL Download, SQL Error Messages, SQL Scripts, SQL Server DBCC, SQL Server Security, SQL Stored Procedure

Generic Solution: Check the settings for both min server memory (MB) and max server memory (MB). If max server memory (MB) is a value close to the value of min server memory (MB), then increase the max server memory (MB) value. Check the size of the virtual memory paging file.…

SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

March 25, 2007

Pinal Dave

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

SQL Function, SQL Index, SQL Scripts

SELECT @@IDENTITY It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. @@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is…

SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer

March 23, 2007

Pinal Dave

SQL Performance

SQL Scripts, SQL Server, SQL Server DBCC, SQL Server Security, SQL Stored Procedure, SQL Utility

DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory. Let us learn how to clean cache.

SQL SERVER – Fix: Error Msg 128 The name is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

March 22, 2007

Pinal Dave

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

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

Error Message: Server: Msg 128, Level 15, State 1, Line 3 The name is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted. Causes: This error occurs when using a column as the DEFAULT value of another column when a table is…