SQL Performance Archives - Page 98 of 100 - 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 – 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 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.