Slow - Running Queries

--Slow-Running Queries

--http://msdn.microsoft.com/en-us/library/ms177500(v=sql.105).aspx

1.PERFMON : The Windows System Monitor can be used to monitor the performance of SQL Server and non-SQL Server related components
2.Profiler: Use SQL Server Profiler to help identify the slow query or queries
            Use the sys.dm_exec_query_stats and sys.dm_exec_requests dynamic management views to find similar queries that collectively consume a large number of resources
3.Showplan: Analyze query performance by producing a Showplan, which can be a text, XML, or
   graphical representation of the query execution plan that the query optimizer generates. You can produce a Showplan using Transact-SQL SET options, SQL Server Management Studio, or SQL Server Profiler.


Example: This example shows how many logical and physical reads are used by SQL Server as it processes the statements.

SET STATISTICS IO ON;
GO
SELECT * FROM dbo.Table_Name
GO
SET STATISTICS IO OFF;


4. Using Statistics to Improve Query Performance

Example:

SELECT name AS "Name",
    is_auto_create_stats_on AS "Auto Create Stats",
    is_auto_update_stats_on AS "Auto Update Stats",
    is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;
GO

--The following example sets AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS to ON
USE AdventureWorks2008R2;
GO
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_UPDATE_STATISTICS ON;
GO





Queries or updates that take longer than expected to execute can be caused by a variety of reasons. Slow-running queries can be caused by performance problems related to your network or the computer where SQL Server is running. Slow-running queries can also be caused by problems with your physical database design.

There are a number of common reasons for slow-running queries and updates:

Slow network communication.

Inadequate memory in the server computer, or not enough memory available for SQL Server.

Lack of useful statistics

Lack of useful indexes.

Lack of useful indexed views.

Lack of useful data striping.

Lack of useful partitioning.