• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

sql query performance analyze?

Hi,

I have several stored procedures which queries several tables to look for given primary key value.

The stored procedures are called from the.NET desktop application.
This part of sql queries are running extremely slow.

Is there any tool I can use to analyze the performance of the sql queries and find a way to improve them?

I have several inner joins in the query.
0
dkim18
Asked:
dkim18
4 Solutions
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
> Is there any tool I can use to analyze the performance of the sql queries and find a way to improve them?

Yes, SQL 2008 has the Database Engine Tuning Advisor.  It does require the analyst to understand the tool, and while it can suggest some possible improvements, it doesn't yet serve as a replace for human expert.
0
 
Jared_SCommented:
Query efficiency can't be ignored, but the right index might speed up your query too.

Here are some server wide scripts that evaluate indexes, you might run it and look for high-impact indexes that would affect your query.

I found these on a site a while back, and I'd reference the author here if I could remember where I got them.

This will evaluate missing indexes on your sql server:

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
GO

DECLARE @percent_lvl  int;
DECLARE @min_rows int;

SET @percent_lvl = 50;
SET @min_rows = 20;


WITH	missing_index_impact AS (
		        SELECT	
				        dm_db_missing_index_groups.index_handle,
				        SUM(
				            (dm_db_missing_index_group_stats.avg_total_user_cost * dm_db_missing_index_group_stats.avg_user_impact *
				                (dm_db_missing_index_group_stats.user_seeks + dm_db_missing_index_group_stats.user_scans))
				            ) AS "total_impact",
				        (100.00 *
				            SUM(dm_db_missing_index_group_stats.avg_total_user_cost * dm_db_missing_index_group_stats.avg_user_impact *
				                (dm_db_missing_index_group_stats.user_seeks + dm_db_missing_index_group_stats.user_scans)) /
                                 SUM(SUM(dm_db_missing_index_group_stats.avg_total_user_cost *
                                    dm_db_missing_index_group_stats.avg_user_impact *
                                    (dm_db_missing_index_group_stats.user_seeks + dm_db_missing_index_group_stats.user_scans))) OVER()
                         ) AS "percent_impact",
				        ROW_NUMBER() OVER(ORDER BY SUM(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)) DESC ) AS rn
		        FROM	sys.dm_db_missing_index_groups AS dm_db_missing_index_groups
		        JOIN	sys.dm_db_missing_index_group_stats AS dm_db_missing_index_group_stats
		          ON	dm_db_missing_index_groups.index_group_handle = dm_db_missing_index_group_stats.group_handle
		        GROUP
		           BY	dm_db_missing_index_groups.index_handle),
		 agg_missing_index_impact AS (
 		        SELECT	missing_index_impact_1.index_handle,
		                missing_index_impact_1.total_impact,
		                SUM(missing_index_impact_2.total_impact) AS running_total_impact,
		                missing_index_impact_1.percent_impact,
		                SUM(missing_index_impact_2.percent_impact) AS running_total_percent,
		                missing_index_impact_1.rn
		        FROM	missing_index_impact AS missing_index_impact_1
		        JOIN	missing_index_impact AS missing_index_impact_2
		          ON	missing_index_impact_1.rn <= missing_index_impact_2.rn
		        GROUP
		           BY	missing_index_impact_1.index_handle, missing_index_impact_1.total_impact,
		                missing_index_impact_1.percent_impact, missing_index_impact_1.rn
		        HAVING	SUM(missing_index_impact_2.percent_impact) - missing_index_impact_1.percent_impact >= @percent_lvl
			        OR	missing_index_impact_1.rn <= @min_rows
		 ),
		 missing_index_details AS (
	 		    SELECT	dm_db_missing_index_details.index_handle,
			            dm_db_missing_index_details."statement",
			            dm_db_missing_index_details.equality_columns,
			            dm_db_missing_index_details.inequality_columns,
			            dm_db_missing_index_details.included_columns
	            FROM	sys.dm_db_missing_index_details AS dm_db_missing_index_details
		 )
		 
SELECT	agg_missing_index_impact.rn,
		missing_index_details."statement",
		agg_missing_index_impact.running_total_impact,
		agg_missing_index_impact.total_impact,
		agg_missing_index_impact.running_total_percent,
		agg_missing_index_impact.percent_impact,
		missing_index_details.equality_columns,
		missing_index_details.inequality_columns,
		missing_index_details.included_columns
FROM	agg_missing_index_impact
JOIN	missing_index_details
  ON	agg_missing_index_impact.index_handle = missing_index_details.index_handle
ORDER
   BY	agg_missing_index_impact.rn ASC;

Open in new window

and this will generate statements to create the missing indexes (but doesn't execute the statements)
SELECT 
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure, 
  'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) 
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement 
  + ' (' + ISNULL (mid.equality_columns,'') 
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END 
    + ISNULL (mid.inequality_columns, '')
  + ')' 
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, 
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Open in new window


Just be careful not to inundate your db's with too many indexes.
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
> I'd reference the author here if I could remember where I got them.

Looks like Michael Smith's Prioritize Missing Index Recommendations (2005).
0
 
dave_tillerCommented:
You could also use the Estimated Execution Plan to look at what part of the query is the most resource intensive.  It will also tell you if you are doing scans or seeks.  Also, what is the health of your TempDB?  If you look at Activity Monitor, under Disk I/O, is the TempDB one of the most taxed databases?
0
 
Scott PletcherSenior DBACommented:
Yes, IF you understand it, the best method is to look at the Actual (not Estimated) Query Plan.


>> I have several inner joins in the query. <<

Joins typically work much better with a corresponding index on the column(s) being joined.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now